Transfer the dump-file from On premise to RDS instance of AWS using DBMS_FILE_TRANSFER

Transfer the dump-file from On premise to RDS instance of AWS:
===============================================================


prerequisite :
============

• create public db link in source database pointing to AWS RDS instance
• DB link should be working fine

Command:

============

BEGIN

DBMS_FILE_TRANSFER.PUT_FILE(

source_directory_object =>'DATA_PUMP_DIR',

source_file_name =>'test1_full_db.dmp',

destination_directory_object =>'DATA_PUMP_DIR',

destination_file_name => 'test1_full_db.dmp',

destination_database =>'AWS_RDS'

);

END;

/

SQL>

SQL>

SQL>

SQL> BEGIN

DBMS_FILE_TRANSFER.PUT_FILE(

source_directory_object =>'DATA_PUMP_DIR',

source_file_name =>'test1_full_db.dmp',

destination_directory_object =>'DATA_PUMP_DIR',

destination_file_name => 'test1_full_db.dmp',

destination_database =>'AWS_RDS'

);

END;

/  2    3    4    5    6    7    8    9   10

Create public database link in oracle database

Create public database link in oracle database
================================================

ORCL TNS alias entry should be added in tnsnames.ora file and tnsping working fine


SQL> !tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-MAR-2020 15:09:24

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.ctttttb0k1yfde.as-sou-2.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))
OK (1340 msec)



SQL> create public database link AWS_RDS connect to admin identified by admin1234567890  using 'ORCL' ;

Database link created


using TNS
==============

create public database link  AWS_RDS_TNS connect to admin identified by admin1234567890
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.ctttttb0k1yfde.as-sou-2.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))';


SQL> create public database link  AWS_RDS_TNS connect to admin identified by admin1234567890
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.cttttb0k1yfde.as-so-3.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))';
  2
Database link created.

SQL> select * from dual@AWS_RDS_TNS;

D
-
X

SQL>


Drop public database link
=============================

SQL> drop public database link AWS_RDS_TNS;

Database link dropped.


SQL>

Create Directory on AWS RDS

Create Directory on AWS RDS
============================

Prerequisite: AWS RDS access required.



Command:-
============
exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'AWS_RDS_DIR');

output:
==========
SQL> exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'AWS_RDS_DIR');

PL/SQL procedure successfully completed.

SQL>

Query:-
=========

select DIRECTORY_NAME,DIRECTORY_PATH,OWNER from dba_directories;

output:
==========

SQL> select DIRECTORY_NAME,DIRECTORY_PATH,OWNER from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH                                                                   OWNER
------------------------------ -------------------------------------------------------------------------------- ----------
OPATCH_INST_DIR                /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/OPatch                  SYS
DATA_PUMP_DIR                  /rdsdbdata/datapump                                                              SYS
AWS_RDS_DIR                    /rdsdbdata/userdirs/01                                                           SYS
BDUMP                          /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace                                      SYS
OPATCH_SCRIPT_DIR              /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/QOpatch                 SYS
OPATCH_LOG_DIR                 /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/QOpatch                 SYS
ADUMP                          /rdsdbdata/admin/ORCL/adump                                                      SYS

7 rows selected.

SQL>