有时有这样一种需求:就是要复制ASM files到文件系统上。
这样就可以利用很多OS命令来对文件进行操作或利用OS命令复制文件了。
下面有三种不同的方法:
一。 利用DBMS_FILE_TRANSFER.COPY_FILE过程
$ rman target /
RMAN> list backup of controlfile
2> ;
List of Backup Sets
===================
BS Key Type LV Size
------- ---- -- ----------
1558 Full 3.28M
Control File Included: Ckp SCN: 300262359 Ckp time: 22-NOV-11
Backup Set Copy #1 of backup set 1558
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:13 22-NOV-11 YES TAG20111122T154412
List of Backup Pieces for backup set 1558 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
1748 1 AVAILABLE +MCS_RECO/mcspsr/backupset/2011_11_22/ncnnf0_tag20111122t154412_0.3741.767895643
Backup Set Copy #2 of backup set 1558
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:13 22-NOV-11 YES TAG20111122T154412
List of Backup Pieces for backup set 1558 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
1799 1 AVAILABLE /export/home/oracle/test/MCSPSR_h2msaa2i_1_2
$ . oraenv
ORACLE_SID = [+ASM1] ? MCSPSR_1
The Oracle base for ORACLE_HOME=/orahome/oracle/product/11.2.0/dbhome_1 is /orahome/oracle
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 23 03:07:49 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> create directory test_files_dir as '+MCS_RECO/mcspsr/backupset/2011_11_22/';
Directory created.
SQL> SQL> create directory test_copy_files_dir as '/export/home/oracle/henry';
Directory created.
SQL> BEGIN
2 DBMS_FILE_TRANSFER.COPY_FILE('test_files_dir',
3 'ncnnf0_tag20111122t154412_0.3741.767895643',
4 'test_copy_files_dir',
5 'copy_file_test.ctl');
6 END;
7 /
BEGIN
*
ERROR at line 1:
ORA-19504: failed to create file "/export/home/oracle/henry/copy_file_test.ctl"
ORA-27040: file create error, unable to create file
SVR4 Error: 2: No such file or directory
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2
SQL> !
$ cd /export/home/oracle/henry
/bin/ksh: /export/home/oracle/henry: not found
$ mkdir /export/home/oracle/henry
$ exit
SQL> BEGIN
DBMS_FILE_TRANSFER.COPY_FILE('test_files_dir',
'ncnnf0_tag20111122t154412_0.3741.767895643',
'test_copy_files_dir',
'copy_file_test.ctl');
END;
/
2 3 4 5 6 7
PL/SQL procedure successfully completed.
SQL> !
$ pwd
/export/home/oracle
$ cd henry
$ ls -lrth
total 6768
-rw-r----- 1 oracle asmadmin 3.3M Nov 23 03:10 copy_file_test.ctl
1* select file_name from dba_data_files where rownum<3
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
+DATA/mcspsr/datafile/mcsp_ias_orasdpm.261.761945935
+DATA/mcspsr/datafile/mcsp_ess.266.761945943
SQL> !
二。利用RMAN中的convert 命令
Or copy the file with rman convert command.
$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Nov 23 03:15:12 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: MCSPSR (DBID=2132798743)
RMAN> convert datafile "+DATA/mcspsr/datafile/mcsp_ess.266.761945943" format "/export/home/oracle/henry/datafile_test_%U.dbf";
Starting conversion at target at 23-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1051 instance=MCSPSR_1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1231 instance=MCSPSR_1 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=+DATA/mcspsr/datafile/mcsp_ess.266.761945943
converted datafile=/export/home/oracle/henry/datafile_test_data_D-MCSPSR_I-2132798743_TS-MCSP_ESS_FNO-37_hemsbgg4.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at target at 23-NOV-11
RMAN> exit
Recovery Manager complete.
$ pwd
/export/home/oracle
$ cd henry
$ ls -lrth
total 211712
-rw-r----- 1 oracle asmadmin 3.3M Nov 23 03:10 copy_file_test.ctl
-rw-r----- 1 oracle asmadmin 100M Nov 23 03:16 datafile_test_data_D-MCSPSR_I-2132798743_TS-MCSP_ESS_FNO-37_hemsbgg4.dbf
三。利用ASMCMD中的cp 命令$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base for ORACLE_HOME=/export/grid/app/11.2.0/grid is /export/grid/app/grid
$ asmcmd
ASMCMD> ls
DATA/
MCS_RECO/
RPT_DATA/
RPT_RECO/
ASMCMD> cd MCS_RECO/
ASMCMD> ls
MCSPSR/
dpdumps/
ASMCMD> cd MCSPSR/
ASMCMD> ls
ARCHIVELOG/
BACKUPSET/
CONTROLFILE/
DUMPSET/
ONLINELOG/
ASMCMD> cd BACKUPSET/
ASMCMD> ls
2011_11_22/
2011_11_23/
snapcf_mcspsr_1.f
ASMCMD> cd 2011_11_22/
ASMCMD> ls
annnf0_TAG20111122T021736_0.4017.767848013
annnf0_TAG20111122T044226_0.1728.767853753
annnf0_TAG20111122T044226_0.1792.767853755
annnf0_TAG20111122T075504_0.2145.767868039
annnf0_TAG20111122T075504_0.2218.767865337
annnf0_TAG20111122T075504_0.2446.767867159
annnf0_TAG20111122T075504_0.2455.767867127
annnf0_TAG20111122T075504_0.2456.767866247
annnf0_TAG20111122T075504_0.2460.767866239
annnf0_TAG20111122T075504_0.3426.767868045
annnf0_TAG20111122T075504_0.3629.767868947
annnf0_TAG20111122T075504_0.3843.767865339
annnf0_TAG20111122T102752_0.2476.767874479
annnf0_TAG20111122T102752_0.3462.767874479
annnf0_TAG20111122T102752_0.3766.767874915
annnf0_TAG20111122T105315_0.2144.767878005
annnf0_TAG20111122T105315_0.2377.767878011
annnf0_TAG20111122T105315_0.2482.767876029
annnf0_TAG20111122T105315_0.2484.767876027
annnf0_TAG20111122T105315_0.2490.767876991
annnf0_TAG20111122T105315_0.2491.767877023
annnf0_TAG20111122T105315_0.3877.767879925
annnf0_TAG20111122T105315_0.3897.767878985
annnf0_TAG20111122T105315_0.4024.767878953
annnf0_TAG20111122T133431_0.2522.767885677
annnf0_TAG20111122T133431_0.3183.767885679
annnf0_TAG20111122T142712_0.2120.767892605
annnf0_TAG20111122T142712_0.2338.767890773
annnf0_TAG20111122T142712_0.3500.767888873
annnf0_TAG20111122T142712_0.3688.767889793
annnf0_TAG20111122T142712_0.3740.767890693
annnf0_TAG20111122T142712_0.3996.767888875
annnf0_TAG20111122T142712_0.4000.767891643
annnf0_TAG20111122T142712_0.4023.767891715
annnf0_TAG20111122T142712_0.4055.767889813
annnf0_TAG20111122T170352_0.2283.767898237
annnf0_TAG20111122T170352_0.3744.767898239
annnf0_TAG20111122T170352_0.4059.767898695
ncnnf0_TAG20111122T154412_0.3741.767895643
nnndf0_TAG20111122T154412_0.2304.767893459
nnndf0_TAG20111122T154412_0.3652.767893461
nnsnf0_TAG20111122T154412_0.3098.767895649
ASMCMD> cp ncnnf0_tag20111122t154412_0.3741.767895643 /export/home/oracle/henry/cp_test.dbf
copying +MCS_RECO/MCSPSR/BACKUPSET/2011_11_22/ncnnf0_tag20111122t154412_0.3741.767895643 -> /export/home/oracle/henry/cp_test.dbf
ASMCMD> exit
$ pwd
$ cd /export/home/oracle/henry
$ ls -lrth
total 218480
-rw-r----- 1 oracle asmadmin 3.3M Nov 23 03:10 copy_file_test.ctl
-rw-r----- 1 oracle asmadmin 100M Nov 23 03:16 datafile_test_data_D-MCSPSR_I-2132798743_TS-MCSP_ESS_FNO-37_hemsbgg4.dbf
-rw-r----- 1 grid oinstall 3.3M Nov 23 03:32 cp_test.dbf