Oracle ASM实例同filesystem间的文件传输 |
发布时间: 2012/9/3 16:41:28 |
一般来讲,在ASM实例和文件系统之间传输文件,可以采用dbms_transfer_file包和rman实现外,或者FTP方式,FTP方式需要XML DB支持,目前还没有学会,因而先记录下前面三种方式… SQL> desc dbms_file_transfer; PROCEDURE COPY_FILE Argument Name 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 Argument Name 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 Argument Name 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> select file_name from dba_data_files; FILE_NAME -------------------------------------------- +DATA/ogg1/datafile/users.259.773712985 +DATA/ogg1/datafile/undotbs1.258.773712985 +DATA/ogg1/datafile/sysaux.257.773712985 +DATA/ogg1/datafile/system.256.773712985 +DATA/ogg1/datafile/example.265.773713189 SQL> create user transfer_test default tablespace transfer_test identified by 123456 account unlock; User created. SQL> create tablespace transfer_test datafile '/u01/app/Oracle/oradata/ogg1/transfer_test01.dbf' size 100M; Tablespace created. SQL> create directory asm_dir as '+DATA/ogg1/datafile'; Directory created. SQL> create directory file_dir as '/u01/app/Oracle/oradata/ogg1'; Directory created. SQL> grant connect,resource to transfer_test; Grant succeeded. SQL> create table transfer_test.t1 as select * from dba_source; Table created. SQL> analyze table transfer_test.t1 compute statistics; Table analyzed. SQL> select count(*) from transfer_test.t1; COUNT(*) ---------- 633054 SQL> alter tablespace transfer_test offline; Tablespace altered. SQL> begin 2 dbms_file_transfer.copy_file('file_dir','transfer_test01.dbf','asm_dir','transfer_test01.dbf'); 3 end; 4 / PL/SQL procedure successfully completed. [root@oel1 ~]# su - grid [grid@oel1 ~]$ asmcmd ASMCMD> cd +data/ogg1/datafile ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 01 20:00:00 Y COPY_FILE.267.776809311 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y EXAMPLE.265.773713189 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y SYSAUX.257.773712985 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y SYSTEM.256.773712985 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y UNDOTBS1.258.773712985 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y USERS.259.773712985 N transfer_test01.dbf => +DATA/OGG1/DATAFILE/COPY_FILE.267.776809311 SQL> alter database rename file '/u01/app/Oracle/oradata/ogg1/transfer_test01.dbf' to '+data/ogg1/datafile/transfer_test01.dbf'; Database altered. SQL> alter tablespace transfer_test online; Tablespace altered. QL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/ogg1/datafile/users.259.773712985 +DATA/ogg1/datafile/undotbs1.258.773712985 +DATA/ogg1/datafile/sysaux.257.773712985 +DATA/ogg1/datafile/system.256.773712985 +DATA/ogg1/datafile/example.265.773713189 +DATA/ogg1/datafile/transfer_test01.dbf 6 rows selected. SQL> select count(*) from transfer_test.t1; COUNT(*) ---------- 633054 2: 11g的asm实例可以直接使用cp命令来实现,从asm实例的cp帮助信息上看,已经可以直接从本地数据库直接cp到远端数据库了! ASMCMD> cp /u01/app/Oracle/oradata/ogg1/transfer_test01.dbf +data/ogg1/datafile/test01.dbf copying /u01/app/Oracle/oradata/ogg1/transfer_test01.dbf -> +data/ogg1/datafile/test01.dbf ASMCMD> pwd +data/ogg1/datafile ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 01 20:00:00 Y COPY_FILE.267.776809311 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y EXAMPLE.265.773713189 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y SYSAUX.257.773712985 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y SYSTEM.256.773712985 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y UNDOTBS1.258.773712985 DATAFILE UNPROT COARSE MAR 01 20:00:00 Y USERS.259.773712985 N test01.dbf => +DATA/ASM/DATAFILE/test01.dbf.268.776809913 N transfer_test01.dbf => +DATA/OGG1/DATAFILE/COPY_FILE.267.776809311 3:使用rman的convert命令来实现,同样适用于10g [Oracle@oel1 ~]$ rman target / Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 1 20:44:22 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: OGG1 (DBID=3952830770) RMAN> convert datafile '+data/ogg1/datafile/SYSTEM.256.773712985' format '/u01/app/Oracle/oradata/ogg1/system01.dbf'; Starting conversion at target at 2012-03-01-20:47:08 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile conversion input file name=+DATA/ogg1/datafile/system.256.773712985 converted datafile=/u01/app/Oracle/oradata/ogg1/system01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:56 Finished conversion at target at 2012-03-01-20:49:07 RMAN> host "ls -lh /u01/app/Oracle/oradata/ogg1/"; total 821M -rw-r----- 1 Oracle asmadmin 721M Mar 1 20:49 system01.dbf -rw-r----- 1 Oracle asmadmin 101M Mar 1 20:17 transfer_test01.dbf host command complete 本文出自:亿恩科技【www.enkj.com】 |