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:
- Export Data into Dump Files
- Transfer Data Dump Files over to Oracle Cloud Object Storage
- Import Data Dump Files into ADW Using SQL Developer
Export Data into Dump Files
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.