AWS RDS Oracle: How to import database dump?

Question

**Gist:** Cannot gain terminal access to then run Oracle or SQL Plus commands to import a database dump into Oracle RDS.

Struggling to import an Oracle database dump as there is no way to do this via AWS's RDS web console, and I have not been able to gain terminal access. Indeed, it does not seem that AWS RDS really wants the owner to use SSH access, as the IP address of the server instance is not readily displayed, but instead the expectation is that all work is done via the DB instance, and not the host server (it is a managed service, after all!).

**Situation**
*  An Oracle RDS instance was created a few months ago. At that time an 'impdp' import was done. 
* Now wish to redo this step, yet do not have means to gain terminal access. Alas, the knowledge of how the first import was done is no longer in the company... and we cannot gain terminal access using the Master username + password (known good credentials as these are used to log into the Oracle DB using DBeaver or Oracle SQL Developer tools).

* Endpoint is of format oracle.clpf12ab34.us-east-1.rds.amazonaws.com
* Master username: known (displayed on RDS console's configuration tab)
* Password: known

**Failed approaches to gain terminal access:**
1. When I attempt to ssh into the server, having stripped the DB Name "oracle" from the front, I observe error "ssh: Could not resolve hostname ... nodename nor servname provided, or not known"

2. Managed to ping the server, so attempted to ssh using masterusernam@ipaddress - yet this returns error "ssh: connect to host <> port 22: Operation timed out"

Note: on all my ECS instances access is done via PEM key files, and not using username/password pair. However, no PEM key-pair has been defined on AWS RDS. Therefore my only credential is the Oracle DB username/password.

**What next?**
I am aware that AWS services cannot reissue new credentials. Yet I would have anticipated the ability to use the information that I have to be able to do some administration of the Oracle DB server... and specifically to be able to run a database dump import (using Oracle's  'impdp' command).

The next approach is to manually conduct import... table by table, and with care for foreign key or constraint dependencies. This shall be laborious. Another approach is to dump this existing RDS instance and to create another. Ideally I would need to make use of Oracle DB version 12.1, as this is what I am needing to use for a project -- and importantly I need to have a patch level that would allow me to conduct the import (as the source database was Oracle 12.1 with patch from July 2021).

As always, your guidance and pointers are most welcome.
Wish you great end-of-week & weekend!

SV · Answer

Hello,
There is no server access available in RDS Oracle so there is no way to log into server and do an import. Here is a link to the documentation that explains how import can be done into RDS database by placing dump files into a S3 bucket - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html
If you are getting any errors then please open a support ticket to engage support to resolve the issues.

Vetrivel · Answer

Hello,

You can install Oracle datapump utility in any EC2 instance or in your local machine (based on the public accessibility settings of your RDS instance) and use that utility to import your data using the dump file which you have already uploaded in to the S3 bucket.

To do that, you must have integrated S3 with RDS instance already as per the document ["Amazon S3 integration"](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/oracle-s3-integration.html)

Follow the document ["Importing data with Oracle Data Pump and an Amazon S3 bucket"](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html#Oracle.Procedural.Importing.DataPump.S3) to accomplish the data import activity.

I-used-to-be-a-Buffalo · Answer

A couple of observations that may help: -- rdsadmin has the syntax to grant the master user privs to alternate accounts. -- you didn't mention any effort to make a client connection by means of sqlplus, sqlci, Toad, sqlDeveloper, etc. specifying the end node and port -- presuming your parameter group(s) is set up for network access. -- somewhat obviously, once you have command access your DBA account should be used to unlock and reset your main account. -- it's cumbersome, but should you have no other options, add APEX to your instance to pick up its built-in SQL Worksheet.

See also: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html

Finally, for guidance with using datapump for an AWS instance: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html

I-used-to-be-a-Buffalo · Answer

A couple of observations that may help: -- rdsadmin has the syntax to grant the master user privs to alternate accounts. -- you didn't mention any effort to make a client connection by means of sqlplus, sqlci, Toad, sqlDeveloper, etc. specifying the end node and port -- presuming your parameter group(s) is set up for network access. -- somewhat obviously, once you have command access your DBA account should be used to unlock and reset your main account. -- it's cumbersome, but should you have no other options, add APEX to your instance to pick up its built-in SQL Worksheet.

See also: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html

For notes on using rds datapump services: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html

I-used-to-be-a-Buffalo · Answer

A couple of observations that may help:
-- rdsadmin has the syntax to grant the master user privs to alternate accounts.
-- you didn't mention any effort to make a client connection by means of sqlplus, sqlci, Toad, sqlDeveloper, etc. specifying the end node and port -- presuming your parameter group(s) is set up for network access.
-- somewhat obviously, once you have command access your DBA account should be used to unlock and reset your master account.
-- it's cumbersome, but should you have no other options, add APEX to your instance to pick up its built-in SQL Worksheet.

-- some further reading on data pumping in RDS:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html

I-used-to-be-a-Buffalo · Answer

A couple of observations that may help: -- rdsadmin has the syntax to grant the master user privs to alternate accounts. -- you didn't mention any effort to make a client connection by means of sqlplus, sqlci, Toad, sqlDeveloper, etc. specifying the end node and port -- presuming your parameter group(s) is set up for network access. -- somewhat obviously, once you have command access your DBA account should be used to unlock and reset your main account. -- it's cumbersome, but should you have no other options, add APEX to your instance to pick up its built-in SQL Worksheet.

See also: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Oracle.Procedural.Importing.DataPump.html

AWS RDS Oracle: How to import database dump?

Relevant content