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