Migrating Data from Oracle Databases to Autonomous Data Warehouse

Creating a new Oracle Autonomous Data Warehouse (ADW) Cloud instance is a quick task. But migrating data over from one or more other Oracle databases will require a few steps. This blog series leverages the Oracle Data Pump technique to export and import schemas from Oracle DB into ADW. The overall process consists of 3 mains steps:

1. Export Data into Dump Files
   1. Option 1: Export DB Schemas Using SQL Developer
   2. Option 2: Export DB Schemas Using Data Pump Command
2. Transfer Data Dump Files over to Oracle Cloud Object Storage
   1. Option 1: Swift REST Interface to Upload Files to Oracle Object Storage
   2. Option 2: OCI CLI Utility to Upload Files to Oracle Object Storage
3. Import Data Dump Files into ADW Using SQL Developer

Export Data into Dump Files

Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

There are 2 ways I will explain on how this can be done: the first uses SQL Developer (my preferable approach) and the second uses the command line export command. With SQL Developer, you may close your computer for long export operations, and come back to SQL Developer later to easily find the execution status and log of the export where you expect it to be, without having to worry about where the logs are located.

Transfer Data Dump Files over to Oracle Cloud Object Storage

In order for the data pump import to work on the Autonomous Data Warehouse, the dump files need to be accessible. I will demonstrate how to transfer them to Oracle Cloud Object Storage (OSS). If the data dump files are relatively small, you may be able to just upload them through the internet browser window when you navigate to the Oracle Object Store. However, for larger data dumps, you will run into timeout issues and therefore require a more reliable method for the data transfer. There are 2 ways to do this for large files: first is using the SWIFT REST Interface (my preferable approach), and second is to use the OCI CLI (Command Line Interface) utility. On Oracle Cloud Compute, the SWIFT REST technique doesn’t require anything to be installed. (On Windows, you want to make sure curl is available). For example, you can SSH into the Cloud Compute of DBaaS and execute a curl command directly. On the other hand, OCI CLI requires an installation of the CLI utility.

Import Data Dump Files into ADW

To import the Data Dump Files from the Object Store, SQL Developer can be used to run a Data Pump Import Wizard. Similar to the export process, if the import process takes a lot of time, you don’t have to wait for it. When you open up SQL Developer again later, you will easily find the status of the execution and the log details.