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.
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 :
- 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.
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
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 :
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
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 :
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);
9. Verify whether the schemas have been imported along with the required datas.
If you like my work, please do like, comment and share