In the world of Technology, we all know COPY and PASTE how much helpful for making any business operations. But in the field of Database, it is not same as in another area. Some different steps we have to use. Let’s have look on those steps which we use for Copy ASM File From One Server To Another Server.

Copying file between mount points between different servers is straight forward and can be done using scp or ftp command. But transfering file between asm diskgroups of different servers  will be a tricky one.

There are two methods to do it.

1. Using cp command of asmcmd

2. Using DBMS_FILE_TRANSFER package

1. Using cp command of asmcmd

SYNTAX:

asmcmd cp <SOURCE_FILE_NAME>  USERNAME/PASSWORD@TARGET_SERVER_IP:<TARGET_ASM_INSTANCE>:<TARGET_FILE_LOCATION>
connect to local asm instance(source-host)

export ORACLE_HOME=/crsapp/app/oracle/product/grid12c
export ORACLE_SID=+ASM1

asmcmd cp +B2CWMARC/EXPDUMP/test.dmp sys/oracle@target-host.+ASM1:+NEWTST/TESTDB2/TEMPFILE/test1.dbf

 

asmcmd cp +B2CWMARC/EXPDUMP/test.dmp sys/oracle@172.21.46.133.+ASM1:+NEWTST/TESTDB2/TEMPFILE/test1.dbf
copying +B2CWMARC/EXPDUMP/test.dmp -> 172.21.46.133:+NEWTST/TESTDB2/TEMPFILE/test1.dbf

You can specify the asm port also:

asmcmd cp –port 1521 +B2CWMARC/EXPDUMP/test.dmp sys/oracle@source-host.+ASM1:+NEWTST/TESTDB2/TEMPFILE/test1.dbf

 

NOTE:

If you are getting error like ASMCMD-8201: (Bad argc for RDBMS:ASMCMD-8201) , then enable trace to find the exact error.

ASMCMD> cp +B2CWMARC/EXPDUMP/test.dmp sys/oracle123@172.21.46.133.+ASM1:+NEWTST/TESTDB2/TEMPFILE/test1.dbf
ASMCMD-8201: (Bad argc for RDBMS:ASMCMD-8201)

Enable trace as below:

$export DBI_TRACE=1

Now run the cp command

asmcmd cp +B2CWMARC/EXPDUMP/test.dmp sys/oracle123@172.21.46.133.+ASM1:+NEWTST/TESTDB2/TEMPFILE/test1.dbf
-> DBI->connect(dbi:Oracle:host=172.21.46.133;port=1521;sid=+ASM1, sys, ****, HASH(0x101eb0a50))
connect using ‘(DESCRIPTION=(ADDRESS=(HOST=172.21.46.133)(PROTOCOL=tcp)(PORT=1521))(CONNECT_DATA=(SID=+ASM1)))’ !! ERROR: 1017 ‘ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)’ (err#0)
DBI->connect(dbi:Oracle:, , ****, HASH(0x101eb0a50))
!! ERROR: 1017 CLEARED by call to default_user method
{‘ora_session_mode’}=32768 ignored for invalid driver-specific attribute

 

From the trace, find error and fix it and then retry the operation

2. Using DBMS_FILE_TRANSFER package

In the below example, we will try to copy a file test.dmp from asm diskgroup of source-host to asm diskgroup of target-host.

Create directory on source db:(where file is present)

SQL> create directory SOURCE_DUMP as ‘+B2CWMARC/EXPDUMP’;

Directory created.

SQL> grant read, write on directory SOURCE_DUMP to public;

Grant succeeded.

 

Create directory on target db(where file need to be copied)

SQL> create directory TARGET_DUMP as ‘+NEWTST/TESTDB2/TEMPFILE’;

Directory created.

SQL> grant read, write on directory TARGET_DUMP to public;

Grant succeeded.

Create database link on target db ( pointing to source db)

SQL> create public database link SOURCEDB connect to dbatest identified by dbatest using ‘SOURCEDATABASE’;

Database link created.

SQL> elect sysdate from dual@SOURCEDB;

SYSDATE
———
08-FEB-17

 

Bow is the syntax for dbms_file_transfer get function.(which need to be run on target db)
set timing on
BEGIN
DBMS_FILE_TRANSFER.GET_FILE (
source_directory_object IN VARCHAR2,
source_file_name IN VARCHAR2,
source_database IN VARCHAR2,
destination_directory_object IN VARCHAR2,
destination_file_name IN VARCHAR2);
END;
/

 

On target db:

set timing on
BEGIN
dbms_file_transfer.get_file(‘SOURCE_DUMP’,
‘test.dmp’,
‘SOURCEDB’,
‘TARGET_DUMP’,
‘test.dmp’);
END;
/

PL/SQL procedure successfully completed.

Now check whether a file has copied or not.

Tagged:

About The Author

Leave a Reply