Overview: Databases have been the central repository for our most critical data. They are core to running local applications as well as web-based applications. In Cloud Deployment When we shift/Deploy our asset from on premise to cloud we need to migrate our database too from on premise to cloud. The objective of this document is to show the process of migrating database from local server to cloud using AWS RDS (Relational Database Services).
DB :Oracle
Tool Used: Oracle SQL Developer
Precondition: Oracle DB should be created in Cloud RDS(Amazon Relational Database Service)
Steps:
1. Login into local DB with Admin credentials and Create Database backup or dump using SQL Developer
Follow below steps to create Database Dump
a)Open Oracle SQL Developer and connect to Local DB with Admin Credentials
b) To access the DBA Navigator, select View > DBA
c) Click "+" in the DBA window to select a connection.
d)Select Local DB connection
e) Expand LocalDB > Data Pump. This is where you can view your Export and Import Jobs.
f) Right-click Data Pump, select Data Pump Export Wizard...
g) Accept the default settings for Connection and Data or DDL in Export Wizard - Step 2 of 7. Select Schemas for Types to be Exported in Data Pump. Click Next.
h) Add the schema and click on Next
i)Accept the default setting for Filter and Click Next
j) Accept the default for TableData and Click Next
k) Accept the default log file name and click Next
l) Click on the File name and rename the dump file as EXPDATdatapump%U.DMP.This dump file will also be saved in the DATA_PUMP_DIR directory.
Any existing Dump files will prevent the export. Therefore, select Delete Existing Dump Files radio button and click Next. m)Modify the Job name if required and Click Next
m)Modify the Job name if required and Click Next
n)Click on Finish
o)Check the final status
2. Connect to Cloud RDS newly created Oracle DB with Admin credentials
Create a database user with username and password and give required permission and resource:
create user <username> connect to <username> identified by <password>;
grant create session, resource to <username>;
alter user <username> quota 100M on users;
3.Switch to local DB
3.1:create a link to target cloud DB
create database link <linkname> connect to <username> identified by <password>using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<CloudDBEndPoint>)(PORT=1521))(CONNECT_DATA=(SID=orcl)))';
Note: Link name should be unique/new and username /password should be cloud db username and password
3.2. Transfer DUMP file from local to AWS target RDS
>>>>>>>>>>>>>>>>>below query will copy the dump file to AWS RDS
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DATA_PUMP_DIR',
source_file_name => '<.dmp filename>',
destination_directory_object => 'DATA_PUMP_DIR',
destination_file_name => '<.dmp filename>',
destination_database => '<linkName>'
);
END;
/
4. Switch to Cloud AWS RDS Oracle DB
4.1 Execute Below Query
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => '<.dmp filename>', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''<username>')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/
5. Connect to the Cloud DB and Check for Tables to confirm the Migration.
