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;
/