当前位置:操作系统 > Unix/Linux >>

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

使用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;
   www.zzzyk.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/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
--------------------------------------------------------------------------------
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,