DBA Hub

📋Steps in this guide1/8

Copy asm file from one server to another server DBACLASS

Copy asm file from one server to another server, using dbms_file_transfer or using asmcmd cp command, we can achive this.

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

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
2

Section 2

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

Code/Command (click line numbers to comment):

1
2
asmcmd cp  +B2CWMARC/EXPDUMP/test.dmp sys/[email protected].+ASM1:+NEWTST/TESTDB2/TEMPFILE/test1.dbf
copying +B2CWMARC/EXPDUMP/test.dmp -> 172.21.46.133:+NEWTST/TESTDB2/TEMPFILE/test1.dbf
3

Section 3

You can specify the asm port also: 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/[email protected].+ASM1:+NEWTST/TESTDB2/TEMPFILE/test1.dbf ASMCMD-8201: (Bad argc for RDBMS:ASMCMD-8201)

Code/Command (click line numbers to comment):

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

Section 4

Enable trace as below: $ export DBI_TRACE=1 Now run the cp command From the trace, find error and fix it and then retry the operation

Code/Command (click line numbers to comment):

1
2
3
4
5
6
asmcmd cp  +B2CWMARC/EXPDUMP/test.dmp sys/[email protected].+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
5

Section 5

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)
6

Section 6

Create directory on target db (where file need to be copied) Create database link on target db ( pointing to source db )

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> create directory SOURCE_DUMP as '+B2CWMARC/EXPDUMP';

Directory created.

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

Grant succeeded.

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

Directory created.

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

Grant succeeded.
7

Section 7

Below 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 :

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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
8

Section 8

Now check whether file has copied or not REFERENCE : How to Copy asm files between remote ASM instances using ASMCMD command (Doc ID 785580.1)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
set timing on
BEGIN
dbms_file_transfer.get_file('SOURCE_DUMP',
'test.dmp',
'SOURCEDB',
'TARGET_DUMP',
'test.dmp');
END;
/

PL/SQL procedure successfully completed.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!