top of page
  • Writer's pictureBalaaji Dhananjayan

ASM to ASM file copy using DBMS_FILE_TRANSFER

Updated: Jun 27, 2020


There will be occasions where we may have to refresh databases and not all time we will have space in our regular OS disks..

What can we do? Well, lets try to make use of FRA space where you always have space or you can easily make space.


Environment Details:

Source Database : DBGENRE_SR

Target Database : DBGENRE_TR


Create directory on source db: --> (Prereqs is create a dir BACKUP_EXPORT in +FRA location)

create directory SOURCE_ASM_FILE_TRNSFR as '+FRA/BACKUP_EXPORT';

Directory created.

grant read,write on directory SOURCE_ASM_FILE_TRNSFR to public;

Grant succeeded.


Create directory on target db:

create directory TARGET_ASM_FILE_TRNSFR as '+FRA/BACKUP_EXPORT'';

Directory created.

grant read,write on directory TARGET_ASM_FILE_TRNSFR to public;

Grant succeeded.


Create database link on target db:

Before creating a DB Link from source to target, lets make sure tnsentry for both source and target are in place.

create public database link DBGENRE_SR connect to <username> identified by "<Password>" using 'DBGENRE_SR';

Database link created.

Lets test the connection now...


select sysdate from dual@DBGENRE_SR;

---

X -- Perfect!! This is what we need to see.

---


Syntax for DBMS_FILE_TRANSFER


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;

/


For single file transfer -- Execute in target


set timing on

BEGIN

dbms_file_transfer.get_file('SOURCE_ASM_FILE_TRNSFR', 'full_db_bkp_01.dmp', 'DBGENRE_SR', 'TARGET_ASM_FILE_TRNSFR', 'full_db_bkp_01.dmp');

END;

/

For multiple files:

set timing on begin dbms_file_transfer.get_file('SOURCE_ASM_FILE_TRNSFR','full_db_bkp_01.dmp','DBGENRE_SR','TARGET_ASM_FILE_TRNSFR','full_db_bkp_01.dmp'); dbms_file_transfer.get_file('SOURCE_ASM_FILE_TRNSFR','full_db_bkp_02.dmp','DBGENRE_SR','TARGET_ASM_FILE_TRNSFR','full_db_bkp_02.dmp'); dbms_file_transfer.get_file('SOURCE_ASM_FILE_TRNSFR','full_db_bkp_03.dmp','DBGENRE_SR','TARGET_ASM_FILE_TRNSFR','full_db_bkp_03.dmp'); dbms_file_transfer.get_file('SOURCE_ASM_FILE_TRNSFR','full_db_bkp_04.dmp','DBGENRE_SR','TARGET_ASM_FILE_TRNSFR','full_db_bkp_04.dmp'); dbms_file_transfer.get_file('SOURCE_ASM_FILE_TRNSFR','full_db_bkp_05.dmp','DBGENRE_SR','TARGET_ASM_FILE_TRNSFR','full_db_bkp_05.dmp'); end; /


107 views0 comments

Recent Posts

See All

Comments


bottom of page