Copying files from ASM to file system

Written By askMLabs on Sunday, January 9, 2011 | 4:00 PM

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.

[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

[oracle@dhcppc1 datafiles]$ pwd
/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

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

SQL>

Hope it helps

SRI

Share this article :

+ comments + 6 comments

sshdba

February 20, 2012 at 4:03 PM

Great Post Murthy. BTW do you have a post for dataguard configuration of a RAC primary(using ASM) to Single Instance Standby (using Filesystem)

learnwithme11g

February 21, 2012 at 10:43 AM

Unfortunately no.

sasa

May 21, 2012 at 6:35 PM

if do you need all datafiles?
I can use dbms_file_transfer.copy_file(source_directory_object =>
‘TEST_DIR’, destination_directory_object => ‘TARGET_DIR’);
END;

nano

November 19, 2013 at 12:28 PM

Method 2 : Using RMAN
You have to replace:
SQL> select file_name,status from dba_data_files where file_id=8;
by
SQL> select file_id, file_name,status from dba_data_files where tablespace_name=’TS1′;
FILE_ID FILE_NAME STATUS
———— ————————————– ———
8 +DG1/testdb/datafile/ts1.256.739204397 AVAILABLE
to find datafile's id number.

Srikrishna Murthy Annam

November 19, 2013 at 4:10 PM

Thank you Nano

MakSportsMD

April 18, 2023 at 1:58 AM

Finding the best sports medicine doctor is one of the most significant tasks. But in maksportsmd, you can get the best treatment for your injuries. It's a rehabilitation center with trained and experienced therapists. If your body is dealing with any such issues, Contact our doctors and get the best consultation.

askMLabs