使用DBMS_FILE_TRANSFER迁移ASM中的表空间和数据文件

这是一个存储过程包，是除了RMAN之外又一个用于执行迁移任务的工具包，可以完成如下的迁移：ASM->ASM、ASM->OS Flie、OS File->ASM、OS File->OS File。

    使用DBMS_FILE_TRANSFER迁移表空间和数据文件

   从文件系统到ASM

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/sysaux01.dbf

/u01/oracle/10g/oradata/gt10g/users01.dbf

/u01/oracle/10g/oradata/gt10g/gtlions01.dbf

/u01/oracle/10g/oradata/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  rename 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

--------------------------------------------------------------------------------

/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

+DATA01/gt10g/datafile/gtlions01.dbf

+DATA01/gt10g/datafile/gtlions02.dbf

 

6 rows selected.

 

SQL>

   从ASM到文件系统

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/sysaux01.dbf

/u01/oracle/10g/oradata/gt10g/users01.dbf

+DATA01/gt10g/datafile/gtlions.263.808393465

+DATA01/gt10g/datafile/gtlions.262.808393513

 

6 rows selected.

--我们将迁移表空间gtlions的两个数据文件

--创建源和目标目录

SQL> create or replace directory dir1 as'/u01/oracle/10g/oradata/gt10g/';

 

Directory created.

 

SQL> create or replace directory dir2 as'+DATA01/gt10g/datafile/';

 

Directory created.

 

SQL> desc dbms_file_transfer;

PROCEDURE COPY_FILE

 ArgumentName                  Type                    In/Out Default?

 ----------------------------------------------------- ------ --------

 SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN

 SOURCE_FILE_NAME               VARCHAR2                IN

 DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN

 DESTINATION_FILE_NAME          VARCHAR2                IN

PROCEDURE GET_FILE

 ArgumentName                  Type                    In/Out Default?

 ----------------------------------------------------- ------ --------

 SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN

 SOURCE_FILE_NAME               VARCHAR2                IN

 SOURCE_DATABASE                VARCHAR2                IN

 DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN

 DESTINATION_FILE_NAME          VARCHAR2                IN

PROCEDURE PUT_FILE

 ArgumentName                  Type                    In/Out Default?

 ----------------------------------------------------- ------ --------

 SOURCE_DIRECTORY_OBJECT        VARCHAR2                IN

 SOURCE_FILE_NAME               VARCHAR2                IN

 DESTINATION_DIRECTORY_OBJECT   VARCHAR2                IN

 DESTINATION_FILE_NAME          VARCHAR2                IN

 DESTINATION_DATABASE           VARCHAR2                IN

 

--开始传输文件

SQL> begin

  2  dbms_file_transfer.copy_file('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.

--重新定位文件位置

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

--------------------------------------------------------------------------------

/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/oradata/gt10g/gtlions02.dbf

 

6 rows selected.

   需要注意的目标文件如果是存放在ASM中，那么指定生成的文件是alias，实际还是会生成固定格式的文件：

[oracle@gtser1 ~]$ asmcmd ls -l data01/gt10g/datafile

Type     Redund  Striped  Time             Sys  Name

DATAFILE UNPROT  COARSE   FEB 26 13:00:00  Y   COPY_FILE.262.808407481

DATAFILE UNPROT  COARSE   FEB 26 13:00:00  Y   COPY_FILE.263.808407501

DATAFILE UNPROT  COARSE   FEB 25 16:00:00  Y   GTLIONS.256.808328331

DATAFILE UNPROT  COARSE   FEB 25 15:00:00  Y   GTLIONS.258.808326729

DATAFILE UNPROT  COARSE   FEB 25 15:00:00  Y   GTLIONS.265.808326737

DATAFILE UNPROT  COARSE   FEB 25 16:00:00  Y   GTLIONS.281.808328327

DATAFILE UNPROT  COARSE   FEB 25 16:00:00  Y   SYSAUX.272.808328281

DATAFILE UNPROT  COARSE   FEB 25 16:00:00  Y   SYSTEM.271.808328265

DATAFILE UNPROT  COARSE   FEB 25 16:00:00  Y   UNDOTBS1.273.808328327

                                            N    gtlions01.dbf =>+DATA01/GT10G/DATAFILE/COPY_FILE.262.808407481

                                           N    gtlions02.dbf =>+DATA01/GT10G/DATAFILE/COPY_FILE.263.808407501

-The End-