Oracle ASM File Copy

ASM File Copy

標籤: 
ASM File Copy
這是一個存儲過程包,是除了RMAN之外又一個用於執行遷移任務的工具包,可以完成如下的遷移:ASM->ASM、ASM->OS Flie、OS File->ASM、OS File->OS File。
使用DBMS_FILE_TRANSFER遷移表空間和數據文件
 
流程:
1.Tablespace Offline
2.建立Source Dir & Target Dir
3.檔案copy ,Dbms_file_transfer.copy_file
4.更改檔案路徑,alter database  rename file
5.Tablespace Online
 
 


 

 
從文件系統到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.
 
查詢directory的位址:
SQL>Select * from dba_directories;
 
create directory source_dir as '/u01/oradata/oracl';
create directory dest_dir as '/u01/copies';
dbms_file_transfer格式:
範例:
begin
  DBMS_FILE_TRANSFER.COPY_FILE(
      source_directory_object => 'SOURCE_DIR',
      source_file_name => 'example01.dbf',
      destination_directory_object => 'DEST_DIR',
      destination_file_name => 'example01_copy.dbf');
  END;
/
 
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> 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.
 
******************************************************
 
   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> 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-
 
 
Reference:

 

http://rritw.com/a/bianchengyuyan/C__/20130226/313105.html