Use DBMS_FILE_TRANSFER to migrate tablespace and data files in ASM

Use DBMS_FILE_TRANSFER to migrate tablespaces and data files in ASM. This is a stored procedure package and a toolkit for executing migration tasks besides RMAN,
You can complete the following migration: ASM-> ASM, ASM-> OS Flie, OS File-> ASM, OS File-> OS File. Use DBMS_FILE_TRANSFER to migrate tablespaces and data files from the file system to ASMSQL> select name from v $ datafile; www.2cto.com NAME/u01/oracle/10g/oradata/gt10g/system01.dbf/u01/oracle/10g/oradata/gt10g/undotbs1.dbf/u01/oracle/10g/oradata/gt10g /sysaux01.dbf/u01/oracle/10g/oradata/gt10g/users01.dbf/u01/oracle/10g/oradata/gt10g/gtlions01.dbf/u01/oracle/10g/ora Data/gt10g/gtlions02.dbf 6 rows selected. SQL> alter tablespace gtlions offline; Tablespace altered. SQL> begin dbms_file_transfer.copy_file ('dir1', 'gtlions01. dbf', 'dir2', 'gtlions01. dbf'); end; 2/PL/SQL procedure successfully completed. SQL> begin dbms_file_transfer.copy_file ('dir1', 'gtlions02. dbf', 'dir2', 'gtlions02. dbf'); end; 2/PL/SQL procedure successfully completed. SQL> alter database ren Ame file '/u01/oracle/10g/oradata/gt10g/gtlions01.dbf' to '+ DATA01/gt10g/datafile/gtlions01.dbf'; Database altered. SQL> alter database rename file '/u01/oracle/10g/oradata/gt10g/gtlions02.dbf' to '+ DATA01/gt10g/datafile/gtlions02.dbf'; Database altered. SQL> alter tablespace gtlions online; Tablespace altered. SQL> select name from v $ datafile; NAME ----------------------------------------------------- Examples/u01/oracle/10g/oradata/gt10g/system01.dbf/u01/oracle/10g/oradata/gt10g/undotbs1.dbf/u01/oracle/10g/oradata/gt10g/Example /u01/oracle/10g/oradata/gt10g/users01.dbf + DATA01/gt10g/datafile/gtlions01.dbf + DATA01/gt10g/datafile/gtlions02.dbf 6 rows selected. SQL> from ASM to file system SQL> select name from v $ datafile; NAME ---------------------------------------------------------------- --------------/U01/oracle/10g/oradata/gt10g/system01.dbf/u01/oracle/10g/oradata/gt10g/undotbs1.dbf/u01/oracle/10g/oradata/gt10g/large /u01/oracle/10g/oradata/gt10g/users01.dbf + DATA01/gt10g/datafile/gtlions.263.808393465 + DATA01/gt10g/datafile/IPv6 rows selected. -- We will migrate two data files in the tablespace gtlions -- create the source and target directories SQL> create or replace directory dir1 as '/u01/oracle/10g/oradata/gt10g/'; Dire Ctory created. SQL> create or replace directory dir2 as '+ DATA01/gt10g/datafile/'; Directory created. SQL> desc dbms_file_transfer; procedcopyure _file ArgumentName Type In/Out Default? Certificate ------ -------- SOURCE_DIRECTORY_OBJECT VARCHAR2 IN SOURCE_FILE_NAME VARCHAR2 IN DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN DESTINATION_FILE_NAME VARCHAR2 INPROCEDURE GET_FILE ArgumentName Type In/Out Default? Certificate ------ -------- SOURCE_DIRECTORY_OBJECT VARCHAR2 IN SOURCE_FILE_NAME VARCHAR2 IN SOURCE_DATABASE VARCHAR2 IN DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN DESTINATION_FILE_NAME VARCHAR2 INPROCEDURE PUT_FILE ArgumentName Type In/Out Default? Certificate ------ -------- invalid VARCHAR2 IN SOURCE_FILE_NAME VARCHAR2 IN DESTINATION_DIRECTORY_OBJECT VARCHAR2 IN DESTINATION_FILE_NAME VARCHAR2 IN DESTINATION_DATABASE VARCHAR2 IN -- start File Transfer SQL> begin 2 bytes ('dir2', 'gtlions. 263.808393465 ', 'dir1', 'gtlions01. dbf'); 3 end; 4/PL/SQL procedure successfully completed. SQL> begin 2 dbms_file_transfer.copy_file ('dir2', 'gtlions. 262.808393513 ', 'dir1', 'gtlions02. dbf'); 3 end; 4/PL/SQL procedure successfully completed. -- reposition the file location SQL> alter database rename file '+ DATA01/gt10g/datafile/gtlions.263.808393465' to '/u01/oracle/10g/oradata/gt10g/gtlions01.dbf '; database altered. SQL> alter database rename file '+ DATA01/gt10g/datafile/gtlions.262.808393513' to '/u01/oracle/10g/oradata/gt10g/gtlions02.dbf'; Database altered. SQL> alter tablespace gtlions online; Tablespace altered. SQL> select name from v $ datafile; NAME users/u01/oracle/10g/oradata/gt10g/system01.dbf/u01/oracle/10g/oradata/gt10g/undotbs1.dbf/u01/oracle/10g/oradata/gt10g/ users/u01/oracle/10g/oradata/gt10g/users01.dbf/u01/oracle/10g/oradata/gt10g/gtlions01.dbf/u01/oracle/10g/oradata/gt10g/users 6 rows selected. if the target file is stored in ASM, specify whether the generated file is alias.
A fixed format file is generated: [oracle @ gtser1 ~] $ Asmcmd ls-l data01/clusters/datafileType Redund Striped Time Sys NameDATAFILE unprot coarse feb 26 13:00:00 Y release unprot coarse feb 26 13:00:00 Y release unprot coarse feb 25 16:00:00 Y release UNPROT COARSE FEB 15:00:00 Y release unprot coarse feb 25 15:00:00 Y release unprot coarse feb 25 16:00:00 Y release unprot coarse feb 25 16:00:00 Y release unprot coarse feb 25 16:00:00 Y release unprot coarse feb 25 16:00:00 Y release N Release => + DATA01/GT10G/DATAFILE/COPY_FILE.262.808407481 N gtlions02.dbf => + DATA01/GT10G/DATAFILE/COPY_FILE.263.808407501-The End-