In this post we will see how we can migrate an On-premise Oracle database to AWS RDS using Oracle Datapump utility and integration with S3.

For more posts on AWS RDS please check the INDEX page or visit our YouTube channel

There are various methods to migrate your on-premise databases to AWS RDS as we saw in our other posts. The links are given below :

Heterogeneous Database Migration using AWS SCT

Migration and Replication (Full Load + CDC) of On-Premise Oracle 11G database to AWS RDS Oracle 19c

Full Load Migration of On-premise Oracle Database to AWS RDS Oracle

RMAN Tasks in AWS RDS Oracle

Daily Tasks in AWS RDS Oracle

Steps to integrate S3 with Amazon Oracle RDS

Steps to upgrade AWS Oracle RDS

  • You can use Oracle SQL Developer to import a simple, 50 MB database.
  • You can use AWS Database Migration Service (AWS DMS) to import data into an Amazon RDS DB instance.
  • You can use Oracle Data Pump to import complex databases, or databases that are pretty large in size, say several hundred megabytes or several terabytes in size

Here we will migrate our database using Oracle Data Pump which is the preferred way to move large amounts of data from an Oracle installation to an Amazon RDS DB instance.

Best Practices

Please follow the below best practices to import data into an Amazon RDS for Oracle DB instance using Oracle Data Pump 

  • Perform imports in schema or table mode to import specific schemas and objects.
  • Limit the schemas you import to those required by your application.
  • Do not import in full — This is suggested, since Amazon RDS for Oracle does not allow access to SYS or SYSDBA administrative users. It might corrupt the Oracle data dictionary and affect the database stability if you are  importing in full mode, or importing schemas for Oracle-maintained components.
  • Amazon RDS for Oracle DB instances do not support importing dump files that were created using the Oracle Data Pump export parameters TRANSPORT_TABLESPACES, TRANSPORTABLE, or TRANSPORT_FULL_CHECK.

Pre-requisites with regards to S3 bucket :

  • The Amazon S3 bucket must be in the same AWS Region as the DB instance.
  • The Amazon S3 bucket must be configured for Amazon RDS integration

You also need to ensure that you have enough space in both source and target databases to store the dumpfiles

Steps :

1. Here we will migrate 2 schemas : Infra and Infraxpertzz

2.  I wont be covering how to take a logical export from your source database. We will take expdp backup of schemas : infra,infraxpertzz

3. Let us transfer the dumpfile from source system to S3 bucket. We will achieve this using AWS CLI tool.

Below is the link on how to configure your AWS CLI Tool :

https://youtu.be/tk5n-UUpGS0

C:\Users\INFRA>aws configure

AWS Access Key ID [****************G25P]: A*********************G

AWS Secret Access Key [****************yOg1]: c*********************0zg

Default region name [us-east-1]: ap-south-1

Default output format [json]: Press enter if the default output format you need is json

 

Now we need to check what S3 buckets are available :

C:\Users\INFRA>aws s3 ls

2020-09-17 20:03:17 awsdump1

 

Let us now check what objects are present in the S3 bucket :

C:\Users\INFRA>aws s3 ls s3://awsdump1

 

Upload the dumpfile to S3 :

C:\Users\INFRA>aws s3 cp C:\Users\INFRA\Desktop\infra.dmp s3://awsdump1/

upload: Desktop\infra.dmp to s3://awsdump1/infra.dmp

4. Change the privileges of the uploaded dumpfile to public

5. Upload all of the files from the DATA_PUMP_DIR directory to an Amazon S3 bucket named awsdump1

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(     

                 p_bucket_name    =>  ‘awsdump1‘,             

                 p_directory_name =>  ‘DATA_PUMP_DIR‘)    

    AS TASK_ID FROM DUAL;              

6. Verify whether the file has been copied to your RDS directory :

select * from table(rdsadmin.rds_file_util.listdir(p_directory => ‘DATA_PUMP_DIR‘));

7. Create the required schemas (infra and infraxpertzz as per our example) and grant required privileges. Also create the tablespace

create user infra identified by <password>;

grant create session, resource to infra;

alter user infra quota 100M on users;

 

create smallfile tablespace INFRAXPERTZZ datafile size 1G autoextend on maxsize 5G;

create user INFRAXPERTZZ identified by <password>;

grant create session, resource to INFRAXPERTZZ;

alter user INFRAXPERTZZ quota 100M on INFRAXPERTZZ;

 

8. Run the import :

DECLARE

AWSDUMP NUMBER;

BEGIN

hdnl := DBMS_DATAPUMP.OPEN( operation => ‘IMPORT’, job_mode => ‘SCHEMA‘, job_name=>null);

DBMS_DATAPUMP.ADD_FILE( handle => AWSDUMP, filename => ‘infra.dmp’,

directory => ‘DATA_PUMP_DIR‘, filetype => dbms_datapump.ku$_file_type_dump_file);

DBMS_DATAPUMP.ADD_FILE( handle => AWSDUMP, filename => ‘sample_imp.log’, directory => ‘DATA_PUMP_DIR‘, filetype => dbms_datapump.ku$_file_type_log_file);

DBMS_DATAPUMP.METADATA_FILTER(AWSDUMP,’SCHEMA_EXPR’,’IN (”INFRA”,”INFRAXPERTZZ“)’);

DBMS_DATAPUMP.START_JOB(AWSDUMP);

END;

/

9. Verify whether the schemas have been imported along with the required datas.

If you like my work, please do like, comment and share

For more posts on AWS RDS please check the INDEX page or visit our YouTube channel