Note:

Deploy Oracle GoldenGate Microservices Across Regions in Oracle Cloud Infrastructure using Oracle Database

Introduction

Oracle GoldenGate microservices architecture (MA) is a replication tool that lets you manage and configure data replication across multiple database environments. It uses RESTful services and a microservices-based architecture to simplify the management, configuration, and monitoring of cloud deployments.

image

Oracle GoldenGate 23ai:

Oracle GoldenGate 23ai Evaluation History

image

Oracle GoldenGate 23ai Features

image

Oracle GoldenGate Implementation

In this tutorial, we will use source and target databases as Oracle Database 23ai and Oracle GoldenGate 23ai microservices as a service in OCI, to replicate data from source database (DB) Ashburn region to target DB San Jose region. Oracle GoldenGate can be deployed in either region. We will deploy Oracle GoldenGate in the San Jose region and connect both source and target database using the same Oracle GoldenGate deployment.

image

Note: : This is simplified Diagram. Actual Network name may differ.

Environment:

OCI Region Compartment VCN Subnet CDB PDB Vault Key Secrete Golden Gate
Ashburn Database/NonProd VCN_ASH Pub/Pri_VCN_ASH VMDB1 VMDB1_PDB1 Vault_ASH NA NA NA
San Jose Database/NonProd VCN_SJ Pub/Pri_VCN_SJ VMDB2 VMDB2_GG Vault_SJ GGKey oggadmin OGG_Dep_23ai

Audience

This tutorial is intended for system administrators and database users to learn about Oracle GoldenGate microservices. It is assumed that readers are familiar with Oracle GoldenGate technology, web technologies, OCI and have a general understanding of UNIX platforms.

Objectives

Prerequisites

Task 1: Provision Oracle GoldenGate Deployment

To provision Oracle GoldenGate deployment as service in OCI, minimum policies are required for the user who is going to create the deployment and connections. Additional task during maintenance is to stop and start the deployment.

The following policies are required:

allow group <identity-domain>/<group-name> to manage goldengate-family in compartment <compartment-name>
allow group <identity-domain>/<group-name> to manage virtual-network-family in compartment <compartment-name>
allow service goldengate to {idcs_user_viewer, domain_resources_viewer} in tenancy
allow group <identity-domain>/<group-name> to manage secret-family in <location>
allow group <identity-domain>/<group-name> to use keys in <location>
allow group <identity-domain>/<group-name> to use vaults in <location>
allow service goldengate to use keys in <location>
allow service goldengate to use vaults in <location>

To provision Oracle GoldenGate deployment, go to the OCI Console and select the respective region and compartment.

  1. Click Oracle Database and select GoldenGate.

  2. Select deployment and click Create deployment.

  3. Enter Name of the deployment, create Compartment under which Oracle GoldenGate deployment must reside, select Deployment or testing, enter OCPU count. We have kept the auto scaling disabled as of now, which can be enabled later. Select Subnet, license type and click Next.

    image

  4. Select the deployment type, Version of Oracle GoldenGate and GoldenGate instance name, enter Administrator username as oggadmin and give the compartment name where the secret is created in Prerequisites section, select the Password secret from the drop-down menu and click Create.

    image

    It will take a few minutes for Oracle GoldenGate deployment to be created. Oracle GoldenGate deployment will be created under compartment (root)/Database/NonProd.

Task 2: Log in to the Oracle GoldenGate Console

  1. Once Oracle GoldenGate deployment is completed, open the Deployment and click Launch Console.

    image

  2. Log in using Oracle GoldenGate deployment Username and Password used during deployment creation.(Ex.oggadmin/password)

    image

Task 3. Prepare the Source and Target Database

Note: Starting with Oracle GoldenGate 23ai, root-level extract is not supported. This implies that the user privileges are assigned at the PDB level only and the c##ggadmin user is not used with Oracle GoldenGate 23ai. Oracle GoldenGate 23ai with Oracle Database allows the implementation of pluggable databases (PDBs) for source and target. Extract is registered for a specific PDB, which is called a per-PDB extract.

  1. Ensure force logging is enabled on the source database.

    select name, force_logging from v$database;
ALTER DATABASE FORCE LOGGING;
select name, force_logging from v$database;

  2. Enable the minimal supplemental logging.

    SELECT supplemental_log_data_min "Minimum", supplemental_log_data_pk  "Primary key", supplemental_log_data_ui  "Unique Key",
supplemental_log_data_fk  "Foreign Key",supplemental_log_data_all "All" FROM  v$database;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SELECT supplemental_log_data_min "Minimum", supplemental_log_data_pk  "Primary key", supplemental_log_data_ui  "Unique Key",
supplemental_log_data_fk  "Foreign Key",supplemental_log_data_all "All" FROM  v$database;

  3. Change the parameter for Oracle GoldenGate extract.

    Show parameter enable_goldengate_replication
show parameter streams_pool_size
ALTER SYSTEM SET enable_goldengate_replication=TRUE SCOPE=BOTH;
alter system set streams_pool_size=2G scope=both sid='*';
Show parameter enable_goldengate_replication
show parameter streams_pool_size

  4. Run the following query in source database (PDB).

    alter session set container=VMDB1_PDB1;
create tablespace ggs_tbs;
create tablespace gguser_tbs;
create user GGADMIN identified by PPassword_123 default tablespace ggs_tbs quota unlimited on ggs_tbs profile C##GGADMINPROFILE;

GRANT CONNECT, RESOURCE TO GGADMIN;
GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TO GGADMIN;
GRANT OGG_CAPTURE TO GGADMIN;
grant select any dictionary to GGUSER;
grant select any transaction to GGUSER;

  5. Run the following query in target database (PDB).

    alter session set container=VMDB1_GG;
create tablespace ggs_tbs;
create tablespace gguser_tbs;

create user GGADMIN identified by PPassword_123 default tablespace ggs_tbs quota unlimited on ggs_tbs profile C##GGADMINPROFILE;

GRANT CONNECT, RESOURCE TO GGADMIN;
GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TO GGADMIN;
GRANT OGG_APPLY, OGG_APPLY_PROCREP  TO GGADMIN;
grant select any dictionary to GGUSER;
grant select any transaction to GGUSER;

Task 4. Create Connection to the Source and Target Database

Once Oracle GoldenGate deployment and database are ready, we need to make a connection between Oracle GoldenGate service and the database.

  1. Log in to the OCI Console, select region and compartment.

  2. Click Oracle Database and select GoldenGate.

  3. Select Connections and click Create connection.

  4. Enter the Name of the connection, the Compartment where you want the connection to be created, Type for the database and click Next.

    image

  5. To create a connection to the source database, enter the following information and click Create.

    • Select Enter database information as the database is in another region.
    • Database connection string: Enter IP as hostname because hostname would require DNS to resolve it.
    • Database username: Enter GGADMIN which was created in Task 3.
    • Database user password: Enter database user password.
    • Traffic routing method: Select Dedicated Endpoint.
    • Session mode: Select Direct.
    • Subnet Public: Enter Subnet-VCN_SJ.

    image

    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.192)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = vmdb1_pdbsub02090909270.vcnash.oraclevcn.com)))

    Once the connection is created, open the connection detail under GoldenGate, click Assigned deployment and Assign deployment.

    image

  6. Like source, create connection OGG_VMDB2_SJ to target database and assign deployment. Only difference, as Oracle GoldenGate and Oracle Database are in the same region and VCN, we can directly select the database as shown in the following screenshot.

    image

    As the database is in the same Region, we can directly select the database and enter the database username and password for GGADMIN created in Task 3.

    image

  7. Validate source and target connection in Oracle GoldenGate. Once a connection is created, log in to the Oracle GoldenGate Console. Click DB Connections, it will show you all the connections. Click source and target connection and ensure that both connections are getting resolved.

    image

Task 5: Extract Configuration

  1. Add trandata.

    Note: Ensure that you enable table-level supplemental by adding TRANDATA. If schema-level supplemental logging is already enabled, you can skip these steps.

    1. To add trandata, first we need to make the connection to the source database from Oracle GoldenGate Console. Click the source database connection.

    2. Click +, select Table or Schema and enter the Table Name or Schema Name.

      In case of table level replication, select Table and enter table name in format as <Schema_Name.Table_Name>.

    3. Select All Columns if table does not have any keys and select Prepare CSN Mode as nowait.

      image

    4. Once trandata is added, you can search for it by giving the table or schema name in the search bar as shown in the following image.

      image

  2. Create an Integrated Extract.

    1. Connect to source database (PDB) OGG_VMDB1_PDB1_ASH connection in the Oracle GoldenGate Console and create CheckPoint Table.

      image

    2. Connect to source database (PDB) OGG_VMDB1_PDB1_ASH connection in the Oracle GoldenGate Console and create Heartbeat Table for target database only.

      image

  3. Add an Integrated Extract.

    1. Once trandata is added successfully, add the Integrated Extract for mentioned tables or schemas. Select the extract from the Oracle GoldenGate navigation menu. Click + in the extract section.

    2. Select Integrated Extract, enter Process Name (max 8 characters) and Description.

      image

    3. Select OracleGoldenGate as the source credential Domain from the drop-down menu, credential Alias, Trail, Encryption Profile and so on.

      image

    4. In the Managed Option section, select Profile Name, Auto Restart, Max Retries, Retry Delay Minutes, Retries Window and click Next.

      image

    5. Once the default parameter file is created, add additional necessary parameters in the parameter file, if required and click Create.

      image

      Parameter used in the DEV extract.

      EXTRACT EX_VMDB1
USERIDALIAS OGG_VMDB1_PDB1_ASH DOMAIN OracleGoldenGate
EXTTRAIL et
TABLE GG_ASH.*;

  4. Start the Integrated Extract.

    1. Once extract is created, click Start.

      image

      Once extract is running successfully, it will show in the Running status.

      image

    2. Expand Extract Process, it will show many sections like Checkpoint, Statistics and Report. Currently you can see the report. Click Refresh on the right side to refresh the report or statistics.

      image

Task 6: Initial Data Load

  1. Take SCN based export dump of the mentioned schema from source PDB.

    $mkdir -p /u01/app/oracle/dump

SQL>  alter session set container=VMDB1_PDB1;
set lines 300 pages 100
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a100
select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='DUMP' ;

create directory dump as '/u01/app/oracle/dump';
grant read,write on directory DUMP to GG_ASH;

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   20049713

expdp GG_ASH/PPassword_123@VMDB1_PDB1_ASH directory=dump dumpfile=GG_ASH.dmp logfile=GG_ASH.log schemas=GG_ASH FLASHBACK_SCN=20049713

  2. Import the data in the target PDB.

    Note: In this tutorial, we have given DBA access to the GG_ash but you can give access as per requirement.

    $mkdir -p /u01/app/oracle/dump/
SQL> alter session set container=VMDB2_GG1;
SQL> create user GG_ash identified by PPassword_123 default tablespace ggs_tbs quota unlimited on ggs_tbs ;
SQL> grant DBA to GG_ash;
SQL> create directory dump as '/u01/app/oracle/dump';
SQL> grant read,write on directory DUMP to GG_ASH;
[oracle@sjvmdb2 admin]$ cp /tmp/GG_ASH.dmp /u01/app/oracle/dump/GG_ASH.dmp
[oracle@sjvmdb2 admin]$ impdp GG_ASH/PPassword_123@VMDB2_GG1 directory=dump dumpfile=GG_ASH.dmp logfile=imp_GG_SJ.log

Task 7: Replicat Configuration

  1. Create Integrated Replicat.

    1. To add trandata, first we need to make the connection to the source database from Oracle Goldengate Console. Select Replicate from left pane and click + in Replicats.

    2. Select Integrated Replicat, enter Replicat Name, Description and click Next.

      image

    3. Enter Replicat Trail (same as given in Extract Process - Task 5), Encryption Profile, Domain, Alias, CheckPoint Table (created during Extract - Task 5) and Log location.

      image

    4. In the Managed Option section, select Profile Name, Auto Restart, Max Retries, Retry Delay, Retries Window and click Next.

      image

    5. Once the default parameter file is created. Add additional necessary parameters in the parameter file, if required and click Create.

      image

  2. Start the Replicat from flashback_SCN.

    1. Once Replicat process is created, click three dots and Start with Options.

      image

    2. Select Start Point as After CSN and CSN number which is the SCN number we used as flashback SCN while taking the export. Enable the Filter Duplicates and click Start.

      image

    3. Click Replicat, it will show many sections like Checkpoint, Statistics and Report. Click Report and Refresh on the right side to refresh the report or statistics.

      image

Task 8: Validate Data Replication

  1. Once Extract, and Replicat are configured, log in to Oracle GoldenGate Console and click Home. You can see both Extract and Replicat are running.

  2. Insert some data in source database (PDB), the same should reflect in the target database (PDB).

    image

  3. Connect to source database.

    image

  4. Connect to target database.

    image

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.