Note:

Use Oracle Cloud Infrastructure Database Migration Service for real time data migration

Introduction

Oracle Cloud Infrastructure Database Migration (OCI Database Migration) is a fully managed service that provides a high performing,self-service experience for migrating databases to Oracle Cloud Infrastructure (OCI). Database Migration runs as a managed cloud service separated from your tenancy and resources. The service operates as a multitenant service in a OCI Database Migration service tenancy and communicates with your resources using Private Endpoints (PEs). PEs are managed by Database Migration. One of the biggest advantages of using OCI Database Migration is it eliminates complex infrastructure preparation. We only need to provide the connectivity between the source and the target databases via the OCI Database Migration service.

Objectives

This tutorial details the steps for OCI Database Migration service using with Oracle GoldenGate Marketplace for real time data migration from on-premises databases to OCI. It details how to migrate the Oracle 12c database hosted on the VM Instance (assuming this is our on-premises source database) to the OCI DB System 19c pluggable database instance (this is going to be our target database).

Source database: SASPRD, Version: 12.2.0.2 Target ATP: RAPPRD, Version: 19.17.0.0.0

Prerequisites

Task 1: Configure Oracle GoldenGate Microservices for Data Replication

  1. Log in to the OCI Console and navigate to the Marketplace.

    Marketplace

  2. Under All Applications, search for “Oracle GoldenGate for Oracle – Database Migrations” and select it.

    Create stack

  3. Click Launch Stack to create the necessary resources, and then click Next.

    Create stack

  4. Select your settings as shown in the following images.

    Network Settings

    Instance Settings

    Create OGG Deployment

    Oracle GoldenGate

  5. Navigate to the Compute. Under Instances, you should be able to see the Oracle Golden Gate Microservices VM is created. Note the public IP of the server.

  6. Using the web browser, load the Oracle GoldenGate Service Manager. If you get any connection timeout error, check ingress rule (443) in Security List.

  7. The service Manager username is oggadmin. Log in to the Oracle GoldenGate server as the OPC User to get the password. You will find a JSON document – ogg-credentials.json under the OPC home directory. Get oggadmin user credential as below.

-bash-4.2$ cat /home/opc/ogg-credentials.json
{"username": "oggadmin", "credential": "***********"}

Oracle GoldenGate

Before registering the source and target database with OCI Database Migration service, complete the following mandatory steps as described below.

Task 2: Prepare the Source Database for Migration

Database sasprd

  1. Create GoldenGate User on the saspdb1 database.

    CREATE USER c##ggadmin IDENTIFIED BY c##ggadmin_password DEFAULT TABLESPACE
users TEMPORARY TABLESPACE temp;
ALTER USER c##ggadmin QUOTA 100M ON USERS;
GRANT UNLIMITED TABLESPACE TO c##ggadmin;
GRANT CONNECT, RESOURCE TO c##ggadmin container=all;
GRANT SELECT ANY DICTIONARY TO C##GGADMIN container=all;
GRANT CREATE VIEW TO C##GGADMIN container=all;
GRANT EXECUTE ON dbms_lock TO c##ggadmin container=all;
EXEC dbms_goldengate_auth.GRANT_ADMIN_PRIVILEGE('c##ggadmin',container=>'all');

On PDB SASPRD_PDB1

    Oracle GoldenGate

    Oracle GoldenGate

     CREATE USER ggadmin IDENTIFIED BY ggadmin_password DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp;
 ALTER USER ggadmin QUOTA 800M ON USERS;
 GRANT UNLIMITED TABLESPACE TO ggadmin;
 GRANT CONNECT, RESOURCE TO ggadmin;
 GRANT SELECT ANY DICTIONARY TO GGADMIN;
 GRANT CREATE VIEW TO GGADMIN;
 GRANT EXECUTE ON dbms_lock TO ggadmin;
 EXEC dbms_goldengate_auth.GRANT_ADMIN_PRIVILEGE('ggadmin');

 Set below parameters

 Alter system set global_names=false;                    
 alter system set STREAMS_POOL_SIZE=800m;
 Enable ARCHIVELOG if it is not already enabled.
 Enable supplemental logging.
 sql > SELECT supplemental_log_data_min, force_logging FROM v$database;
 sql > ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
 sql > ALTER DATABASE FORCE LOGGING;

 Enable Golden Gate Replication 
 sql > ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
 Create database directory. This is the location where the dump files are created.
 sql > CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle/product/datapump';

 Preparing the Target Database for Migration:
 Database :-> rapprd

 Create GoldenGate User on the target database. In this tutorial, we have created ggadmin user in the PDB. You do not need to create any user in the container database for the target database.

    Oracle GoldenGate

  CREATE USER ggadmin IDENTIFIED BY ggadmin_password DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;
  ALTER USER ggadmin QUOTA 100M ON USERS;
  GRANT UNLIMITED TABLESPACE TO ggadmin;
  GRANT CONNECT, RESOURCE TO ggadmin;
  GRANT SELECT ANY DICTIONARY TO GGADMIN;
  GRANT CREATE VIEW TO GGADMIN;
  GRANT EXECUTE ON dbms_lock TO ggadmin;
  EXEC dbms_goldengate_auth.GRANT_ADMIN_PRIVILEGE('ggadmin');

  Set below parameters on  

  Alter system set global_names=false;                    
  alter system set STREAMS_POOL_SIZE=300m;
  Enable ARCHIVELOG if it is not already enabled.
  Enable supplemental logging
  Create database directory. This is the location where the dump files are stored.
  sql > CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle/product/datapump';

Now we are ready to register the source and the target databases with the OCI Database Migration service.

Task 3: Register the source and the target databases with the OCI Database Migration service

  1. Navigate to Database Migration on the cloud console and click Registered Databases.

  2. Click Register Database to register our first database.

  3. Enter the source database details and click Next.

    Oracle GoldenGate

    Oracle GoldenGate

    • Connection String : Source server Host name/server IP address + Port + Service name.

      Oracle GoldenGate

  4. Once all required details are completed, click Register.

  1. We have completed registering our source and target databases with the OCI Database Migration service. Now we can create a migration job. Navigate to Migrations and click Create Migration.

  2. Provide a name and click Next.

Oracle GoldenGate Oracle GoldenGate

  1. For Initial Load, select the option Data Pump via object storage and then select the bucket.

    Oracle GoldenGate Oracle GoldenGate

  2. Select the Use Online Replication option, enter the GoldenGate hub details and click Create.

Oracle GoldenGate Oracle GoldenGate

Task 4: Task Validation

  1. Once the migration job is created, validate it before starting the actual migration. Click Validate to start the validation process.

    Oracle GoldenGate

    • Validation goes in serval phases and if your configuration is all good, you should be able to see validation status green as shown in the following screen.

    Oracle GoldenGate

  2. Once the Validation Phase is successfully completed, you can start the actual data migration job.

    • Here we have options to pause after starting the replication. It is recommended to pause before switchover and do it after discussing with application team for having a planned activity. This is the actual cutover after which the changes cannot be reverted.

  3. Finally, once the migration job is successfully completed and all steps are showing green status,then it is the time for application folks to validate their application data.

    Oracle GoldenGate

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.