Skip to main content

Cloud

How to Migrate Data From One Oracle Autonomous DB to Another

Oracle Enterprise Management Updates

If you’re looking to copy over data from one Oracle Autonomous Database (ADB) to another, you are probably wondering how to go about doing that. OCI offers a whole database clone capability which is straightforard to use and provides a clone of the whole autonomous database within the same Oracle Cloud tenant. However, if you’re looking to migrate to an Autonomous Database on another tenant, the clone feature won’t work. Consequently, we are left with Data Pump export/import to do the job. But how would Data Pump work if we don’t have access to the Autonomous Database linux box? This blog helps clarify that (not how to access ADB’s operating system 🙂 ), but how to run Data Pump and access the DMP files generated on ADB.

There are 3 steps to doing this:

  • Run Data Pump Export using SQL Developer. Refer to my previous blog post here on how to do that. This will generate an export .dmp file on ADB’s data pump directory. By looking at the data pump export log, take note of the name of the exported .dmp file as we will need that in the next step.

 

  • Copy the dump file, exported from Step 1, to Oracle Cloud Object Storage. For this step, we may use the Object Storage of either the source or target tenant. After doing this, in the data import step below, we can pull the .dmp file from any tenant, as long as we have the corresponding authentication token in order to access its Object Storage. But we don’t have access to SSH into the linux machine that hosts ADB! To get the exported file, we are going to use the PL/SQL PUT_OBJECT function. Open a SQL worksheet connected to the source ADB (where the data pump export happened) and run the following function in SQL Developer.
    BEGIN
    DBMS_CLOUD.PUT_OBJECT
    (credential_name => '<Autonomous DB credential generated in Step 1>',
    object_uri => '<URI of the file object  that will be created on Object Storage>',
    directory_name => '<the name of the data pump export directory used in Step 1>',
    file_name => '<filename.DMP generated in Step 1');
    END;

    Note that the object_uri can be obtained from the properties of the target bucket on Object Storage. Following is an example of what the uri looks like: ‘https://objectstorage.us-phoenix-1.oraclecloud.com/n/axlbfoc/b/BucketName/o/DumpFile.DMP’

  • Run Data Pump Import using SQL Developer against the target ADB. Refer to my previous blog here on how to do that.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mazen Manasseh, Director of Business Analytics

Mazen is a Director of Business Analytics at Perficient and an accomplished professional services leader with 20 years of being a customer advocate. An analytics solutions delivery expert in functional domains covering Supply Chain, Financials, HCM, Projects and Customer Experience. Being a thought leader in the business analytics space, he conducted numerous business training sessions and spoke at technology conferences around analytics and machine learning.

More from this Author

Follow Us