用ASMCMD移动数据文件从ASM 到文件系统

测试环境11.2.0.1,单机,Linux 32
磁盘组信息
SQL> select b.name "DG_NAME",a.name "DISK_NAME", a.state, a.path, a.os_mb, a.total_mb, a.free_mb
     from v$asm_disk a, v$asm_diskgroup b
     where b.name='DATA' and a.group_number=b.group_number;

DG_NAME    DISK_NAME    STATE    PATH          OS_MB  TOTAL_MB   FREE_MB
---------- ------------ -------- ------------- ------ ---------- -------
DATA       VOL6         NORMAL   ORCL:VOL6     509    509        483
DATA       VOL7         NORMAL   ORCL:VOL7     509    509        483
测试表空间
SQL> create tablespace MYTBS,datafile '+DATA/DEMO/mytbs.dbf' size 100M;
tablespace created.
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files where tablespace_name='MYTBS';
TABLESPACE_NAME    FILE_ID  FILE_NAME                 BYTES/1024/1024
------------------ -------- ------------------------- ---------------
MYTBS              6        +DATA/demo/mytbs.dbf      100
SQL> create table test tablespace MYTBS as select * from dual connect by rownum<100;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
99
移动数据文件
--将MYTBS表空间OFFLIN
SQL> alter tablespace MYTBS offline;
Tablespace altered.
SQL>
--grid用户在ASMCMD工具中用CP命令拷贝该文件
ASMCMD> pwd
+DATA/DEMO
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
                         Y   DATAFILE/
                         N   mytbs.dbf => +DATA/DEMO/DATAFILE/MYTBS.256.738944349
ASMCMD>
ASMCMD> cp mytbs.dbf /u01/app/oracle/mytbs.dbf
copying +DATA/DEMO/mytbs.dbf -> /u01/app/oracle/mytbs.dbf
ASMCMD> exit
--退出ASMCMD,查看复制文件/u01/app/oracle/mytbs.dbf
[grid@vm11gr2] /u01/app/oracle> ls -ltr mytbs.dbf
-rw-rw-r-- 1 grid oinstall 104865792 Dec 28 14:27 mytbs.dbf
[grid@vm11gr2] /u01/app/oracle>

在数据库中rename该datafile
SQL> alter database rename file '+DATA/demo/mytbs.dbf' to '/u01/app/oracle/mytbs.dbf';
Database altered.
SQL> alter tablespace mytbs online;
Tablespace altered.
SQL> select tablespace_name,file_id,file_name,bytes/1024/1024 from dba_data_files where tablespace_name='MYTBS';
TABLESPACE_NAME    FILE_ID    FILE_NAME                     BYTES/1024/1024
------------------ ---------- ----------------------------- ---------------
MYTBS              6          /u01/app/oracle/mytbs.dbf     100
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name='MYTBS';
TABLESPACE_NAME    STATUS
------------------ -------
MYTBS              ONLINE
SQL> select file_name,status from dba_data_files where tablespace_name='MYTBS';
FILE_NAME                   STATUS
--------------------------- ---------
/u01/app/oracle/mytbs.dbf   AVAILABLE
SQL>

验证数据并清除测试环境
SQL> select count(*) from test;
COUNT(*)
----------
99
SQL> drop table test purge;
Table dropped.
SQL> drop tablespace mytbs including contents and datafiles;
Tablespace dropped.
SQL> 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11417069/viewspace-686495/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11417069/viewspace-686495/