Oracle ASM实例同filesystem间的文件传输
一般来讲，在ASM实例和文件系统之间传输文件，可以采用dbms_transfer_file包和rman实现外，或者FTP方式，FTP方式需要XML DB支持，目前还没有学会，因而先记录下前面三种方式…1:使用dbms_file_transfer在文件系统和asm实例间传输文件，同样适用于10g
[*]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 directoryasm_dir as '+DATA/ogg1/datafile';
[*]Directory created.
[*]
[*]SQL> create directoryfile_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
[*]2dbms_file_transfer.copy_file('file_dir','transfer_test01.dbf','asm_dir','transfer_test01.dbf');
[*]3end;
[*]4/
[*]
[*]PL/SQL procedure successfully completed.
[*]
[*]# su - grid
[*]$ asmcmd
[*]ASMCMD> cd +data/ogg1/datafile
[*]ASMCMD> ls -l
[*]Type RedundStripedTime SysName
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y COPY_FILE.267.776809311
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y EXAMPLE.265.773713189
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y SYSAUX.257.773712985
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y SYSTEM.256.773712985
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y UNDOTBS1.258.773712985
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y 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 RedundStripedTime SysName
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y COPY_FILE.267.776809311
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y EXAMPLE.265.773713189
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y SYSAUX.257.773712985
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y SYSTEM.256.773712985
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y UNDOTBS1.258.773712985
[*]DATAFILEUNPROTCOARSE MAR 01 20:00:00Y 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
[*]$ 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 Mar1 20:49 system01.dbf
[*]-rw-r----- 1 oracle asmadmin 101M Mar1 20:17 transfer_test01.dbf
[*]host command complete
页:
[1]