Copying files from ASM to file system

ASM

In the present article i am going to describe the various methods that we have to copy(Not moving,just copying) a datafile from ASM storage to file system storage.

1) Using ASMCMD Utility ( Latest method )
2) Using RMAN ( alternative method )
3) Using DBMS package ( old method )

In each method, we will copy the file from ASM to file system.

Method 1 : Using ASMCMD Utility

In this method, we are copying the datafile ìTS1.256.739191187î in diskgroup DG1 from ASM to ìts2.dbfî on to the file system.
[[email protected] datafiles]$ pwd
/u01/datafiles
[[email protected] datafiles]$ ls
[[email protected] datafiles]$

[[email protected] ~]$ asmcmd
ASMCMD> ls -lt
State Type Rebal Name
MOUNTED EXTERN N DATA/
MOUNTED EXTERN N DG1/
MOUNTED EXTERN N FRA/
MOUNTED EXTERN N OCR/
ASMCMD> cd dg1
ASMCMD> cd testdb/datafile
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE DEC 31 13:00:00 Y TS1.256.739191187
ASMCMD> cp TS1.256.739191187 /u01/datafiles/ts2.dbf
copying +dg1/testdb/datafile/TS1.256.739191187 -> /u01/datafiles/ts2.dbf
ASMCMD>

[[email protected] datafiles]$ ls -lrt
total 525208
-rw-róñ 1 oracle oinstall 314580992 Dec 31 13:17 ts2.dbf
[[email protected] datafiles]$

Method 2 : Using RMAN

[[email protected] datafiles]$ pwd
/u01/datafiles
[[email protected] datafiles]$ ls
[[email protected] datafiles]$
SQL> create tablespace ts1 datafile ë+DG1í size 20m;

Tablespace created.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=íTS1′;

TABLESPACE_NAME STATUS
óóóóó óóó
TS1 ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME STATUS
óóóóóóóóóóóóóóóóñ óóó
+DG1/testdb/datafile/ts1.256.739204397 AVAILABLE

[[email protected] datafiles]$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 ñ Production on Fri Dec 31 14:34:15 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: TESTDB (DBID=2521935115)
using target database control file instead of recovery catalog

RMAN> copy datafile 8 to ë/u01/datafiles/ts1.dbfí;

Starting backup at 31-DEC-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DG1/testdb/datafile/ts1.256.739204397
output file name=/u01/datafiles/ts1.dbf tag=TAG20101231T143435 RECID=7 STAMP=739204478
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-DEC-10

RMAN>

[[email protected] datafiles]$ ls -lrt
total 525208
-rw-róñ 1 oracle dba 314580992 Dec 31 14:40 ts1.dbf
[[email protected] datafiles]$

Method 3 : Using DBMS package

SQL> select file_name from dba_data_files;
FILE_NAME
óóóóóóóóóóóóóóóóóóóóóóóóóóñ
+DATA/testdb/datafile/users.259.738606665
+DATA/testdb/datafile/undotbs1.258.738606665
+DATA/testdb/datafile/sysaux.257.738606663
+DATA/testdb/datafile/system.256.738606661
+DATA/testdb/datafile/example.269.738607053
+DATA/testdb/datafile/test.271.738755277
+TESTDG/testdb/datafile/test1.257.738755445
+DG1/testdb/datafile/ts1.256.739191187

8 rows selected.

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> create directory TEST_DIR as ë+DG1/testdb/datafile/í;

Directory created.

SQL> create directory TARGET_DIR as ë/u01/datafiles/í;

Directory created.

SQL> !ls -lrt /u01/datafiles/
total 0

SQL> BEGIN
dbms_file_transfer.copy_file(source_directory_object =>
ëTEST_DIRí, source_file_name => ëts1.256.739191187í,
destination_directory_object => ëTARGET_DIRí,
destination_file_name => ëts1.dbfí);
END;
/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> SQL>
SQL> !ls -lrt /u01/datafiles/
total 307512
-rw-róñ 1 oracle dba 314580992 Dec 31 13:12 ts1.dbf

SQL> select file_name from dba_data_files where tablespace_name=íTS1′;

FILE_NAME
óóóóóóóóóóóóóóóóóóóóóóóóóóñ
+DG1/testdb/datafile/ts1.256.739191187