This blog post comes with a high-level overview of the options available to migrate Database from On-Premise to Autonomous Database Cloud.
If you are new to Oracle Autonomous Database, it is the database on Cloud to automatically patch, upgrade and tune itself. For an overview, Two Types (ADW, ATP) and Two deployment options (Serverless and Dedicated), check HERE.
First, let’s talk about the different options available to load the data.
Data Loading Options:
When you migrate the Database to the Autonomous Database, there are two options available to load data:
- From On-Premise: You can directly load your data from your On-premise server to the Autonomous Database.
- From Object Storage Cloud: You can also load your data to Cloud Object Storage first, and then migrate to the Autonomous Database from Object Storage Cloud. This is a fast process for Database Migration.
Different Methods to Migrate to Autonomous Database Cloud:
Now coming to the different methods available to migrate the Database from On-Premise to Autonomous Database Cloud, there are mainly four Migration methods:
- Using DBMS_CLOUD package
- Using Data Pump
- Using SQL Loader
- Using Oracle GoldenGate
1. Using DBMS_CLOUD:
The package DBMS_CLOUD supports loading from files in the following Cloud Services: Oracle Cloud Infrastructure Object Storage, Oracle Cloud Infrastructure Object Storage Classic, Azure Blob Storage, and Amazon S3. For data loading from files in the Cloud, you need to store your object storage credentials in your Autonomous Data Warehouse first, and then use the DBMS_CLOUD.COPY_DATA procedure to load the data.
Below is the High-Level Migration Process using the DBMS_CLOUD:
- Copy all the data file(s) to Object Storage on Cloud.
- Store your Object Storage credentials in your Autonomous Database (ADB).
- Copy the data file into an existing table using:
- Check the status of the operation.
2. Using Data Pump:
Oracle Data Pump provides very fast bulk data movement between Oracle Databases and Autonomous Data Warehouse. Using this method, you can import data from Data Pump files kept on Oracle Cloud Infrastructure Object Storage, Microsoft Azure, AWS S3, and Oracle Cloud Infrastructure Object Storage Classic. You can save your data to your Cloud Object Store and use the Oracle Data Pump to load data to the Autonomous Data Warehouse.
Below is the High-Level Migration Process using the Data Pump:
- Export data in the Schema Mode.
- Copy the dump file(s) to object Storage on Cloud.
- Store your Object Storage credentials in ADB.
- Import using the impdp method.
- Check the Logfiles for any issue.
3. Using SQL Loader:
You can use Oracle SQL Loader to load data from local files in your machine to Oracle Autonomous Data Warehouse. Using SQL Loader is suitable for loading small amounts of data as the load performance depends on the network bandwidth between your client and the Autonomous Data Warehouse.
Below is the High-Level Migration Process using the SQL Loader:
- Configure the connection wallet & variables.
- Gather one or more data files.
- Create a control file (optional).
- Create a table in destination DB.
- Load using the SQL Loader.
- Check Log, Bad, and Discard files.
4. Using Oracle GoldenGate:
You can also use GoldenGate to replicate data to Autonomous Data Warehouse using the Oracle GoldenGate On-Premises and Oracle GoldenGate Cloud Service. Make sure you use the Oracle GoldenGate On-Premises releases 126.96.36.199.2 and later as a source as only these versions are certified with Oracle Autonomous Data Warehouse Cloud. Other Sources could be Oracle Database Cloud Service or Oracle Exadata Cloud Service on Oracle Cloud. However, you can’t set up the Oracle Autonomous Data Warehouse Cloud Database as a source Database for Oracle GoldenGate On-Premises and only non-integrated Replicats are supported with Oracle Autonomous Data Warehouse Cloud.
Below is the High-Level Migration Process using Oracle GoldenGate:
- Configure the Autonomous Database for Replication by creating the required schema, target tables, new target user, etc.
- Obtain the Autonomous Database client credentials.
- Configure Oracle GoldenGate On-Premises for replication by transferring the client credentials zip file, configuring sqlnet.ora, and tnsnames.ora, etc.
- Configure Oracle GoldenGate Manager and non-integrated Replicats to deliver to the Autonomous Database.
This is all about Migration to Autonomous Database and methods available for the same in nutshell.
This post is from our Oracle Autonomous Database Cloud Specialist: 1Z0-931 Certification Training Program (For FREE MasterClass, click Here).
- Loading Data with Autonomous Data Warehouse.
- How to Migrate to Oracle Autonomous Database Cloud?
- [Video] Oracle Autonomous Database Overview: ADW, ATP, Serverless & Dedicated Infrastructure
- Everything You Must Know About [1Z0-931] Oracle Autonomous Database Cloud Certified Specialist
- Creating Oracle Autonomous Data Warehouse 18c on OCI
- How to Connect to an Autonomous Database on Oracle Cloud?
- Oracle Autonomous Database Cloud Specialist: [1Z0-931] Training Program
- Oracle Autonomous Database Cloud Certification [1Z0-931]: Step-By-Step Activity Guides To Clear The Exam
Next Task For You:
- If you don’t have an Oracle Cloud Public Account, then create one by using the Step-by-Step Activity Guide HERE. (Register For FREE)
- Build Your First Autonomous Database on OCI using the Steps HERE.
- Connect to your Autonomous Database by watching the [Video] How to Connect to the Autonomous Database on Oracle Cloud.
- Follow Oracle Autonomous Database Cloud Certification [1Z0-931]: Step-by-Step Activity Guides To Clear The Exam.
If you want to take your career to the next level by becoming an Oracle Autonomous Database Cloud Specialist even if you are a beginner, then I would like to invite you to Join my FREE Masterclass on How To Be Oracle Autonomous Database Cloud Specialist [1Z0-931] In 7 Weeks. Click on the Image Below to Register NOW!
Click on the Image Below to Join our FREE Community Group.