Search code examples
oracleamazon-web-servicesexportrds

Exporting oracle table on AWS RDS that contains blobs and import into offline database

My scenario:

Database 1: Oracle on Local server offline, no internet access.

Database 2: Oracle RDS on AWS so no file system access to this one.

I need to export a table containing blobs from the RDS instance to my offline database. I'm having trouble finding any documentation on doing this. I only see steps for importing into an rds.

Solution

  • It is necessary to take several steps.

    1. Create a free instance of EC2(1 CPU core, 1G RAM, 30GD HDD ) or a paid instance with a large disk and CPU.
    2. Install a free database version of the Oracle XE version on EC2 instance(It is very easy and fast)
    3. Export a table containing blobs from the RDS instance to DUMP directory. Use DBMS_DATAPUMP to create a dump file.
    4. Create database link on RDS instance between RDS DB and Oracle XE DB.
    5. Copy the dump files from RDS instance to Oracle XE DB on EC2 uses the DBMS_FILE_TRANSFER.PUT_FILE via database link
    6. Copy files from the DUMP directory Oracle XE on EC2 instance via the sftp to local PC.
    7. Copy the dump files from your local PC to offline database DUMP directory
    8. import dump files into offline database