How To Import Native Backup of An ORACLE Server Into RDS
This blog has been moved from medium to blogs.tensult.com. All the latest updates are available there. Subscribe to our newsletter to stay updated.
Nowadays most of the people are transferring their physical server environment to Cloud to reduce administrative intervention and increase the Flexibility and Scalability. AWS is treated as the most secure, reliable and flexible Cloud platform. So everyone is trying to migrate their on-premise environment to AWS. Today we are going to see, how to import native backup of an Oracle Server into AWS RDS.
I am going to import the dump to RDS through an Oracle DB instance.
- Copy the Dump to Oracle DB Instance. Then Login to the Oracle DB and Switch to oracle user using the command
sudo su — oracle
2. Login as SYSDBA using the command
rlsqlplus ‘/ as sysdba’
3. Just check the Dump log file and find the exact schema name from there,
4. Now we need to create a new schema with that name and need to grant necessary permissions. please login to the Oracle RDS with master username and password,
rlsqlplus “master_username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns-end-point-of-rds)(PORT=1521))(CONNECT_DATA=(SID=dbname)))”;
5. Now just create the schema and grant permissions,
create user Test_Schema identified by “test@123”;
grant create session, resource to Test_Schema;
grant connect to Test_Schema identified by “test@123”;
alter user quota 100M on users;
exit;
6 . Now login to oracle db server as sysdba,
rlsqlplus ‘/ as sysdba’
7. First you start your DB using the command startup
8. Now we need to create a Database link to Instance DB to RDS ,
create database link to_rds connect to <master_user_account> identified by <password> using ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<dns or ip address of remote db>)(PORT=<listener port>))(CONNECT_DATA=(SID=<remote SID>)))’;
9. Connection is established now and we can transfer our dump file to RDS through the link which we created earlier,
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => ‘DATA_PUMP_DIR’,
source_file_name => ‘Test_New.dmp’,
destination_directory_object => ‘DATA_PUMP_DIR’,
destination_file_name => ‘Test_New_Copied’,
destination_database => ‘to_rds’
);
END;
/
exit;
# Test_New.dmp : Dump file
#Test_New_Copied : Destination file name
10. Now we need to login to the RDS using master username,
rlsqlplus “master_username/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dns-end-point-of-rds)(PORT=1521))(CONNECT_DATA=(SID=dbname)))”;
11. Then Import the dumps using:
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => ‘IMPORT’, job_mode => ‘SCHEMA’, job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘FEDPRTL_NEWUI_full_Copied.dmp’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,’SCHEMA_EXPR’,’IN (‘’FEDPRTL_NEWUI’’)’);
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/
12. Our dump is successfully imported to our Schema which we created earlier. Please use the below command to check that,
select count(*) from dba_tables where owner=’Test_Schema’;
Yes, it’s replicated. So now we have seen how to Import Native backup to Oracle RDS using Oracle DB instance. Oracle is a little complex and so be careful while executing commands. We imported the dump through Oracle DB instance. If you know any other method, please ping me in the responses section.
Thanks to @jawad846 for helped me to write this blog.
