Oracle GoldenGate Microservices 21c Configuration

Preparing Database 19c for Oracle GoldenGate Microservices 21c with Unidirectional Replication Configuration

1. PRE-REQUISITES FOR REPLICATION:

In order for GoldenGate Microservices Architecture to replicate data from Source database to target database, there are some pre-requisite steps to be performed on both Source & Target Database like.

• Set Replication Parameter: Set ENABLE_GOLDENGATE_REPLICATION to TRUE.
• Create Global User: For CDB environments, create a global user using the C## prefix.
• Assign Permissions: Grant the DBA role to the newly created user.
• Enable Supplemental Logging: Turn on supplemental logging at the database level.
• Enforce Logging: Set FORCE LOGGING to TRUE to ensure all changes are captured.
• Enable Archive Log Mode: Ensure the database is running in ARCHIVELOG mode.

— 1.1. PRE-REQUISITE ON SOURCE DATABASE:

— — 1.1.1 SET ENABLE GOLDENGATE REPLICATION

SQL> SELECT supplemental_log_data_min, force_logging, log_mode, cdb FROM v$database;

SQL> ALTER DATABASE FORCE LOGGING;
SQL> SHOW PARAMETER enable_goldengate_replication;

SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL> SHOW PARAMETER enable_goldengate_replication;
SQL> SELECT supplemental_log_data_min, force_logging, log_mode, cdb FROM v$database;

— — 1.1.2. VERIFY DATABASE IN ARCHIVE LOG MODE

As you can see the ARCHIVE LOG MODE is enabled already.

As you can see the ARCHIVE LOG MODE is enabled already.

— — 1.1.3. CREATE GLOBAL USER & GRANT DBA ROLE

A. CREATE TABLESPACE GGS_TBLSP

SQL> SELECT * FROM v$tablespace;
SQL> CREATE TABLESPACE ggs_tbs;

SQL> SET LINES 300 PAGES 300
SQL> COL file_name FOR a60
SQL> COL tablespace_name FOR a15
SQL> SELECT tablespace_name, file_id, file_name, bytes/1024/1024, maxbytes/1024/1024, status 
     FROM dba_data_files 
     WHERE tablespace_name='GGS_TBS';

B. CREATE A USER IN DATABBASE

We will create user with the same name specified in the GLOBALS Parameter file.

SQL> CREATE USER ggadminsource IDENTIFIED BY oracle DEFAULT TABLESPACE ggs_tbs;
SQL> GRANT DBA TO ggadminsource;
SQL> CONN ggadminsource/oracle;
SQL> SHOW USER;

SQL> EXEC dbms_goldengate_auth.grant_admin_privilege('ggadminsource');

— 1.2. PRE-REQUISITE ON TARGET DATABASE:

— — 1.2.1. SET ENABLE GOLDENGATE REPLICATION

SQL> SELECT supplemental_log_data_min, force_logging, log_mode, cdb FROM v$database;

SQL> ALTER DATABASE FORCE LOGGING;
SQL> SHOW PARAMETER enable_goldengate_replication;

SQL> ALTER SYSTEM SET enable_goldengate_replication=TRUE;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
SQL> SHOW PARAMETER enable_goldengate_replication;
SQL> SELECT supplemental_log_data_min, force_logging, log_mode, cdb FROM v$database;

— — 1.1.2. VERIFY DATABASE IN ARCHIVE LOG MODE

As you can see the ARCHIVE LOG MODE is enabled already.

— — 1.2.3. CREATE GLOBAL USER & GRANT DBA ROLE

A. CREATE TABLESPACE GGS_TBLSP

SQL> SELECT * FROM v$tablespace;
SQL> CREATE TABLESPACE ggs_tbs;

SQL> SET LINES 300 PAGES 300
SQL> COL file_name FOR a60
SQL> COL tablespace_name FOR a15
SQL> SELECT tablespace_name, file_id, file_name, bytes/1024/1024, maxbytes/1024/1024, status 
     FROM dba_data_files 
     WHERE tablespace_name='GGS_TBS';

B. CREATE A USER IN DATABBASE

We will create user with the same name specified in the GLOBALS Parameter file.

SQL> CREATE USER ggadmintarget IDENTIFIED BY oracle DEFAULT TABLESPACE ggs_tbs;
SQL> GRANT DBA TO ggadmintarget;
SQL> CONN ggadmintarget/oracle;
SQL> SHOW USER;

SQL> EXEC dbms_goldengate_auth.grant_admin_privilege('ggadmintarget');

2. CREATE USERS & TABLES IN DATABASE:

In order for GoldenGate Microservices Architecture to replicate data from Source database to target database, there are some pre-requisite steps to be performed on both Source & Target Database.

We also load some dummy data (Schema, Tables, and Data) in database:

• Source Setup: Create the source schema and dept11 table on the ggate1 database.
• Target Setup: Create the target schema and dept22 table on the ggate2 database.
• Replication Goal: Sync data from source.dept11 to target.dept22 across the two databases.

— 2.1. CREATE DATA IN SOURCE DATABASE:

— — 2.1.1. CREATE USER ‘SOURCE’ AND GRANT DBA

SQL> CREATE USER source IDENTIFIED BY oracle;
SQL> GRANT DBA TO source;
SQL> CONN source/oracle;
SQL> SHOW USER;

SQL> CREATE TABLE dept11 (
     deptno NUMBER(2,0),
     dname  VARCHAR2(14),
     loc    VARCHAR2(13),
     CONSTRAINT pk_dept PRIMARY KEY (deptno)
     );

— 2.2 CREATE DATA IN TARGET DATABASE:

— — 2.2.1. CREATE USER ‘TARGET AND GRANT DBA

SQL> CREATE USER target IDENTIFIED BY oracle;
SQL> GRANT DBA TO target;
SQL> CONN target/oracle;
SQL> SHOW USER;

SQL> CREATE TABLE dept22 (
     deptno NUMBER(2,0),
     dname  VARCHAR2(14),
     loc    VARCHAR2(13),
     CONSTRAINT pk_dept PRIMARY KEY (deptno)
     );

3. CREATE CREDENTIALSTORE IN GOLDENGATE:

— 3.1. CREATE CREDENTIALSTORE IN SOURCE GOLDENGATE:

NOTE:

• Mandatory Credentials: Creating credentials is required in GoldenGate Microservices, whereas it remains optional in GoldenGate Classic.

You can test the credentialstore by clicking the connect to database icon

To disconnect click the same icon again.

— 3.2 . CREATE CREDENTIALSTORE IN TARGET GOLDENGATE:

4. CREATE EXTRACT PROCESS:

— 4.1. ENABLE TRANDATA IN SOURCE TABLE:

You can use the below query to check if the supplemental logging is enabled on the table. You should see log group created.

You can also check the table with supplemental logging from GG Console as show below:

In GodenGate Microservices Oracle GoldenGate Procedural Replication is supported for an Oracle Database release 12.2 or later.

— 4.2. CREATE INTRGRATED EXTRACT:

Use the extract trail file location as following:

Remember the default trail directory in GoldenGate Microservices is:

Fill the following field to create Integrated Extract:

Once you click next it will take you to its parameter file where you can add the tables to be extracted and other parameters as needed.

Add the following parameter in the parameter file and click Create & Run:

You can monitor the database alert log to check the Integrated Extract progress while being created:

You can check the trail file location; the extract trail file will be created.

You can use the following query to get the capture details.

Get Adnaan Ahmed Khan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

SQL> set lines 300 pages 300

SQL> col capture_name for a30

SQL> col source_database for a20

SQL> select capture_name,start_scn,status,source_database from dba_capture;

5. CREATE EXTRACT PROCESS:

You can see that the Remote Trail file is created on the target server.

6. CREATE REPLICAT PROCESS ON TARGET:

— 6.1. CREATE CHECKPOINT TABLE:

— 5.2 . CREATE REPLICAT PROCESS:

You need to add the required parameters in the replicat parameter file.

Check the REPLICAT details:

You can also check the report of Replicat to check if any issues:

7. PERFORM REPLICATION TESTING:

Connect to source database with source user and insert data into table dept11.

You can check the source database alert log to check the extract progress.

Go to Deployment1 Administration service and click on Extract detail.

Check the statics to verify the Insert capture.

Check the Extract report to get more Information.

Go to Deployment1 Distribution Service and click on Path details.

Check the statistics to get Information on DMLs captured.

Go to Deployment2 Receiver Service and Click on Path details.

Here you can monitor the Network & File IO Statistics.

Go to Deployment2 Administration Service and Click on Replicat Details.

Check the Replicat statistics to get Information on Table Statistics during Replication.

Check the Replicat report to get more Information and to perform troubleshooting.

Check the Target database alert log to monitor Replication progress.

Connect to Target database and verify the data replicated to the table dept22.

Now update the LOC column on the Source database.

You can see the Extract Trail file size increases.

Check the Extract Statistics on Source Deployment1.

Check the Distribution Path Statistics on Source Deployment1.

You can notice the Remote Trail file size increases.

Check the Receiver Service Network & File IO Statistics on Target Deployment2.

Now Check the Replicat Statistics on Target Deployment2.

Now finally Verify the Source Updated data is successfully Replicated into the Target table dept22.

Thank You !