Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
This document covers steps involved in refreshing an Oracle database schema where source and target both are AWS RDS databases
Prerequisites:
RDS Instances with enough free space
EC2 Instance to perform export-import- May be referred as a Jump Instance
Note: All the expdp/impdp commands and sql connections to be triggered from EC2 instance
Database Link Creation
Create a database link on Source DB
Command
1 | create database link <dblink_name> connect to <username> identified by <password> using '<tns entry of RDS instance'; |
Note: the above link must be created in source RDS Database
Schema backup
Take the backup of the both the target and source schema.
Command:
1 2 3 | expdp parfile=username/password@database_name schemas=<schema_name> directory= 'DATA_PUMP_DIR' dumpfile=dumpfile.dmp logfile=logfile.log |
Where Database_name is the TNS entry for source/target database.
This dump file will be transferred from source to target and used in import.
“Use DBMS_FILE_TRANSFER to place the exported dump file to an Amazon RDS DB instance
Command:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> BEGIN DBMS_FILE_TRANSFER.PUT_FILE( source_directory_object => 'DATA_PUMP_DIR', source_file_name => 'dumpfile.dmp', destination_directory_object =>'DATA_PUMP_DIR' , destination_file_name => 'dumpfile.dmp', destination_database => '<DB_LINK>' ); END; / |
Import on target Instance
Source DB server:
“Use Data Pump to import the data file on the DB instance
Create impdp parfile and start the import.
1 2 3 4 5 6 7 | userid='username/password@targetserver' DUMPFILE=dumpfile.dmp DIRECTORY='DATA_PUMP_DIR' remap_schemas=’<Source_schema:Destination_schema>’ |
Check if the schema is imported to RDS.
Command:
1 | SQL> select object_type,count(*),status from dba_objects where owner='username' group by object_type,status order by object_type; |
thank you!!!!!!!!!