Copy ASM Files with DBMS_FILE_TRANSFER

You can copy ASM Files using DBMS_FILE_TRANSFER pl/sql package. Lets see how to copy an archive log file from ASM storage to local disk.

RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile

RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=129 instance=rdba1 devtype=DISKvalidation succeeded for archived logarchive log filename=+FRA/rdba/archivelog/2009_03_29/thread_1_seq_2197.485.682769833 recid=490 stamp=682769833Crosschecked 1 objects

RMAN> exit
Recovery Manager complete.

C:\>sqlplus / as sysdba

SQL> create directory XXARCsrc as '+FRA/rdba/archivelog/2009_03_29';
Directory created.

SQL> create directory XXARCdest as 'C:\';
Directory created.

SQL> grant read, write on directory XXARCsrc to public;
Grant succeeded.

SQL> grant read,write on directory XXARCdest to public;
Grant succeeded.

SQL> execute dbms_file_transfer.copy_file('XXARCsrc','thread_1_seq_2197.485.682769833','XXARCdest','arc2197');

Now look for the archive file arc2197 in local disk in c-drive.

Note: This way you can copy any file from ASM to the disk.

To view the files on ASM use the ASMCMD tool,

SET oracle_home=C:\oracle\product\10.2.0\asm_1
SET oracle_sid=+ASM1

C:\>asmcmd
ASMCMD> ls
/BKP
/DATA
/FRA

ASMCMD> cd data
ASMCMD> ls
RDBA/

ASMCMD> cd rdba

C:\>set oracle_home=C:\oracle\product\10.2.0\db_1

C:\>set oracle_sid=+ASM1

C:\>asmcmd
ASMCMD> ls
BKP/
DATA/
FRA/

ASMCMD> cd data

ASMCMD> ls
DB_UNKNOWN/
RDBA/

ASMCMD> cd db_unknown
ASMCMD> ls
PARAMETERFILE/

ASMCMD> cd parameterfile

ASMCMD> ls
SPFILE.268.665669211

ASMCMD> cd ..
ASMCMD> cd ..
ASMCMD> cd rdba

ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
spfilemol.ora

ASMCMD> cd datafile

ASMCMD> ls
ARCHDATA.279.667315727
AUTOSCAN.278.667315457
COMPANY.275.667315065
COMPANY_IDX.273.667314429
EMPLOYEE.271.667314047
EMP_IDX.284.667316721
EXAMPLE.264.663930173
INDX.290.667317683
INDX_MOL1.269.667313539
INDX_MOL2.281.667316185
MAIN_MOL.277.667315275
MAIN_MOL.282.667316309
MOL_DNRD.289.667317613
MOL_DNRD_INDEX.288.667317545
OTHERS.280.667315911
OTHER_IDX.276.667315173
SYSAUX.257.663930059
SYSTEM.256.663930057
TEMPORARAY.287.667317499
TNT.272.667314321
TNT_IDX.274.667314579
TNT_IDX1.291.667317753
TOOLS.285.667317351
TUNE_INDEX.270.667313981
UNDOTBS1.258.663930059
UNDOTBS2.265.663930307
USERS.259.663930059
USR.286.667317429
VISA_CANCEL_CHNG.283.667316489