Copying files from ASM to file system
Posted by Srikrishna Murthy Annam on January 9, 2011
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
[oracle@dhcppc1 datafiles]$ pwd
/u01/datafiles
[oracle@dhcppc1 datafiles]$ ls
[oracle@dhcppc1 datafiles]$
[oracle@dhcppc1 ~]$ 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>
[oracle@dhcppc1 datafiles]$ ls -lrt
total 525208
-rw-r—– 1 oracle oinstall 314580992 Dec 31 13:17 ts2.dbf
[oracle@dhcppc1 datafiles]$
Method 2 : Using RMAN
/u01/datafiles
[oracle@dhcppc1 datafiles]$ ls
[oracle@dhcppc1 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
[oracle@dhcppc1 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>
[oracle@dhcppc1 datafiles]$ ls -lrt
total 525208
-rw-r—– 1 oracle dba 314580992 Dec 31 14:40 ts1.dbf
[oracle@dhcppc1 datafiles]$
Method 3 : Using DBMS package
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
SQL>