Transferring Oracle Files Between Databases in ASM

.

Why do we need to transfer Oracle files between databases? There may be lots of cases depending on your imagination, and my case here is sending archivelogs in a dataguard configuration. Dataguard ships the archivelog files automatically, even if the standby database is read-write (Snapshot Standby Feature) in 11g. But in 10gR2, if you’re “Using a Physical Standby Database for Read/Write Testing and Reporting”, archivelog shipping stops while the physical standby database in Read-Write. What i want to do is adding an 11g feature to 10g and ship the archivelogs from primary to standby via a script.
What options do we have for this purpose?


DBMS_FILE_TRANSFER

DBMS_FILE_TRANSFER package provides three procedures to move Oracle files. COPY_FILE procedure moves files between directories within a database. However GET_FILE and PUT_FILE procedures contacts to a remote database and transfer files from or to remote database.
Note: When transferring files in use, you should bring them offline to guarantee consistency.
Let’s see the usage of PUT_FILE procedure:



Syntax:

DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2,
database_link_name IN VARCHAR2);

Don’t forget to grant READ on the source directory to source database user and grant WRITE on destination directory to destination database user.

Create the directory on the source database:
CREATE OR REPLACE DIRECTORY SOURCEDIR AS '+FRA/SID/ARCHIVELOG' ;
GRANT READ ON DIRECTORY SOURCEDIR TO "SOURCEUSER";

Create the directory on the destination database:
CREATE OR REPLACE DIRECTORY DESTDIR AS '+FRA/SID/ARCHIVELOG ' ;
GRANT WRITE ON DIRECTORY DESTDIR TO "DESTUSER";

Create the Database Link on the source database:

CREATE DATABASE LINK "TESTLINK " CONNECT TO DESTUSER IDENTIFIED BY PASSWORD USING 'INST2';

Start file transfer:

CONNECT sourceuser/password@inst1
BEGIN
SYS.DBMS_FILE_TRANSFER.PUT_FILE ( ' SOURCEDIR' , 'source_file_name' , ' DESTDIR ' , 'destination_file_name','TESTLINK' ) ;
END ;

In my dataguard case I didn’t need to create directory on the destination (standby) database. After creating the source directory on the primary side, I used the same directory name for the destination (of course after the archivelog was applied on standby). I executed the procedure as follows:

BEGIN
SYS.DBMS_FILE_TRANSFER.PUT_FILE ( 'ARCHDIR' , 'thread_1_seq_204982.1897.709557909' , ' ARCHDIR ' , 'thread_1_seq_204982','TESTLINK' ) ;
END ;

When you use the destination file name same with the source file name in an OMF environment you get the following error, so you must use a different destination file name.

ORA-19504: failed to create file "+FRA/…/thread_1_seq_204982.1897.709557909"
ORA-17502: ksfdcre:4 Failed to create file +FRA/…/thread_1_seq_204982.1897.709557909
ORA-15046: ASM file name '+FRA/… /thread_1_seq_204982.1897.709557909' is not in single-file creation form

FTP proxy Method

Another option for file transfer in ASM is FTP proxy Method. You must have Oracle XML DB installed for this option. XML DB uses the virtual folder /sys/asm to access ASM files. An example usage of this future is:

ftp> open server1 port1
ftp> user username1
Password required for USERNAME1
Password: password-for-username1
ftp> cd /sys/asm/FRA/SID/ARCHIVELOG
ftp> proxy open server2 port2
ftp> proxy user username2
Password required for USERNAME2
Password: password-for-username2
ftp> proxy cd /sys/asm/FRA/SID/ARCHIVELOG
ftp> proxy put thread_1_seq_204982.1897.709557909
ftp> proxy get thread_1_seq_204982.1897.709557909

Refer to Using FTP and Oracle XML DB Protocol Server and Commanding ASM By Arup Nanda for more information.

11g ASM cp Command

Next option is ASM cp command which is an new feature of 11g. (Not suitable for my 10g case)
Syntax for remote ASM copy is:

ASMCMD> cp [srcfile] username@hostname.SID.port:path

username can be any username in the ASM instance that has the system privilege. Port number is required if the listener doesn’t listen from default port 1521. Also don’t forget that ASM instance needs to be registered with the listener.

For example:

ASMCMD> cp +FRA/SID/ARCHIVELOG/thread_1_seq_204982.1897.709557909
sys@ORCL.+ASM: +FRA/SID/ARCHIVELOG/thread_1_seq_204982

Find more information about ASM cp command here.

RMAN Convert

Last option is RMAN but unfortunately not directly to ASM. You can copy an ASM file to file system, ftp the converted file to remote host then convert again into remote ASM. I’m not sure if anyone chose this way where we have simpler options, but it’s good to know this capability of RMAN.

Here is how we use RMAN Convert command to copy files between ASM and file system.:

RMAN> convert datafile "+DATAFILE/tbs_21.f" format "/tmp/conv_df_%U";

5 comments:

transfer files said...

ASM enables you to change the storage configuration without having to take the database offline.

transfer files

Tani said...

Very useful feature is there in 11g. Dataguard ships the archivelog files automatically, even if the standby database is read-write.
sap support packs

Priya Y said...

Which is very much useful for me and impressed by reading.Your information is very hep full to SAP OS DB MIGRATION Learners. we are providing the
best SAP OS DB MIGRATION ONLINE TRAINING with experienced IT Faculty.
Best Post about SAP Courses and the Training. Helps the readers to get better knowledge on Current SAP Technologies.
SAP OS DB MIGRATION ONLINE TRAINING

Priya Y said...

If the prerequisites are not met, we must inform you that SAP cannot provide any SAP Support Line support for your migration. All support during the migration, when solving inconsistencies and performance problems, and if data loss occurs, can be provided by SAP OS DB MIGRATION ONLINE TRAINING only as a consulting service that is subject to charge.

SAP OS DB MIGRATION ONLINE TRAINING

Priya Y said...

SAP OS DB MIGRATION ONLINE TRAINING Check optimally prepares you for a successful migration and supports smooth continued operations on the new platform.
The SAP OS DB MIGRATION ONLINE TRAINING is mandatory if you are going to migrate a productive
system.

SAP OS DB MIGRATION ONLINE

TRAINING