To configure Oracle GoldenGate replication, we need to create below processes. In this article, I will use Oracle Database as source and target.
High level steps:
- Install Oracle GoldenGate on source and target databases.
- Setup prerequisites.
- Setup connectivity.
- Create an user Credential.
- Configure Manager process on both sides.
- Initial Load.
- Configure Extract process on source database.
- Configure Pump process on source database.
- Configure Replicat process on target database.
Source DB Details:
- Host Name: orcl19c.localdomain
- IP: 192.168.56.10
- Database Name: orclprd
- Database and GG Version: 19c
- Schema: HR
Target DB Details:
- Host Name: orclprd.localdomain
- IP: 192.168.56.20
- Database Name: orcl
- Database and GG Version: 19c
- Schema: HR
Install Oracle GoldenGate on source and target databases:
Setup prerequisites:
- Setup database in Archive Log Mode
- Enabling supplemental and force logging
- ENABLE_GOLDENGATE_REPLICATION
- Create the administrator user/schema for GolgenGate
Setup database in Archive Log Mode:
Source DB# SQL> SELECT NAME, LOG_MODE FROM V$DATABASE; NAME LOG_MODE --------- ------------ ORCLPRD ARCHIVELOG Target DB# SQL> SELECT NAME, LOG_MODE FROM V$DATABASE; NAME LOG_MODE --------- ------------ ORCL ARCHIVELOG
Enabling supplemental and force logging:
Source DB: SQL> SELECT NAME, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING FROM V$DATABASE; NAME SUPPLEME FORCE_LOGGING --------- -------- --------------------------------------- ORCLPRD NO NO SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> SELECT NAME, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING FROM V$DATABASE; NAME SUPPLEME FORCE_LOGGING --------- -------- --------------------------------------- ORCLPRD YES YES Target DB: SQL> SELECT NAME, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING FROM V$DATABASE; NAME SUPPLEME FORCE_LOGGING --------- -------- --------------------------------------- ORCL NO NO SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> SELECT NAME, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING FROM V$DATABASE; NAME SUPPLEME FORCE_LOGGING --------- -------- --------------------------------------- ORCL YES YES
Set ENABLE_GOLDENGATE_REPLICATION:
Source DB: SQL> SHOW PARAMETER ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_goldengate_replication boolean FALSE SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH; System altered. SQL> SHOW PARAMETER ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_goldengate_replication boolean TRUE Target DB: SQL> SHOW PARAMETER ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_goldengate_replication boolean FALSE SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH; System altered. SQL> SHOW PARAMETER ENABLE_GOLDENGATE_REPLICATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_goldengate_replication boolean TRUE
Create the administrator user/schema for GolgenGate:
SQL> create tablespace tbs_gg datafile '/u01/oradata/ORCLPRD/tbs_gg.dbf' size 1G; Tablespace created. SQL> create user ggusr identified by ggusr_pass default tablespace tbs_gg quota unlimited on tbs_gg; User created. SQL> grant create session,connect,resource,alter system to ggusr; Grant succeeded. SQL> grant dba to ggusr; Grant succeeded. SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(grantee=>'ggusr', privilege_type=>'CAPTURE', grant_optional_privileges=>'*'); PL/SQL procedure successfully completed.
Create an user Credential:
Create an useralias for credential:
Configure Manager process on both sides:
Manager is the controller process that instantiates the Oracle GoldenGate processes, allocates port numbers, and performs file maintenance. We can create the manager process during installation or manually in later.
Create manager process:
GGSCI (orclprd.localdomain) 3> edit param mgr port 7809 DYNAMICPORTLIST 7809-7820 GGSCI (orclprd.localdomain) 4> info mgr Manager is DOWN! GGSCI (orclprd.localdomain) 6> start mgr Manager started. GGSCI (orclprd.localdomain) 7> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
GGSCI (orclprd.localdomain) 8> send mgr getportinfo detail Sending GETPORTINFO request to MANAGER ... Dynamic Port List Starting Index 0 Entry Port Error Process Assigned Program ----- ----- ----- ---------- ------------------- ------- 0 7809 0 1 7810 0 2 7811 0 3 7812 0 4 7813 0 5 7814 0 ... ... ... 23 7832 0
Add Checkpointtable:
GGSCI (orcl19c.localdomain) 6> dblogin USERIDALIAS GGUSR_ORCL
Successfully logged into database.
GGSCI (orcl19c.localdomain as ggusr@orcl) 7> add checkpointtable ggusr.chkpt
Successfully created checkpoint table ggusr.chkpt.
GGSCI (orcl19c.localdomain as ggusr@orcl) 8> info checkpointtable
ERROR: Missing checkpoint table specification.
GGSCI (orcl19c.localdomain as ggusr@orcl) 9> info checkpointtable ggusr.chkpt
Checkpoint table ggusr.chkpt created 2023-11-21 22:30:15.
Table Created on Database:
SQL> select count(*) from ggusr.chkpt;
COUNT(*)
----------
0
Add Trandata on Source Side for tables which will be replicated:
Add Trandata at Table Level:
GGSCI (orclprd.localdomain as GGUSR@orclprd) 12> ADD TRANDATA HR.EMPLOYEES 2023-11-17 20:03:31 INFO OGG-15132 Logging of supplemental redo data enabled for table HR.EMPLOYEES. 2023-11-17 20:03:31 INFO OGG-15133 TRANDATA for scheduling columns has been added on table HR.EMPLOYEES. 2023-11-17 20:03:32 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table HR.EMPLOYEES. 2023-11-17 20:03:32 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.EMPLOYEES ***** Oracle Goldengate support native capture on table HR.EMPLOYEES. Oracle Goldengate marked following column as key columns on table HR.EMPLOYEES: EMPLOYEE_ID. Validation: GGSCI (orclprd.localdomain as GGUSR@orclprd) 15> INFO TRANDATA HR.EMPLOYEES 2023-11-17 20:06:07 INFO OGG-10471 ***** Oracle Goldengate support information on table HR.EMPLOYEES ***** Oracle Goldengate support native capture on table HR.EMPLOYEES. Oracle Goldengate marked following column as key columns on table HR.EMPLOYEES: EMPLOYEE_ID. Logging of supplemental redo log data is enabled for table HR.EMPLOYEES. Columns supplementally logged for table HR.EMPLOYEES: "EMPLOYEE_ID". Prepared CSN for table HR.EMPLOYEES: 2209559
Add Trandata at Schema Level:
GGSCI (orclprd.localdomain as GGUSR@orclprd) 16> ADD TRANDATA HR.* 2023-11-17 20:06:47 INFO OGG-15132 Logging of supplemental redo data enabled for table HR.COUNTRIES. 2023-11-17 20:06:47 INFO OGG-15133 TRANDATA for scheduling columns has been added on table HR.COUNTRIES. 2023-11-17 20:06:47 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table HR.COUNTRIES. ... ... ... 2023-11-17 20:06:47 INFO OGG-15132 Logging of supplemental redo data enabled for table HR.DEPARTMENTS. 2023-11-17 20:06:47 INFO OGG-15133 TRANDATA for scheduling columns has been added on table HR.DEPARTMENTS. 2023-11-17 20:06:47 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table HR.DEPARTMENTS.
Initial Load:
Configure Extract process on source database:
GGSCI (orclprd.localdomain) 7> edit param EEMP EXTRACT EEMP SETENV (ORACLE_SID=ORCLPRD) USERIDALIAS GGUSR_ORCLPRD WARNLONGTRANS 4H CHECKINTERVAL 5M SKIPEMPTYTRANS BR BROFF CACHEMGR CACHESIZE 4GB, CACHEDIRECTORY /u01/goldengate/gg_dircache 5GB EXTTRAIL /u01/goldengate/db19/dirdat/em TABLE HR.EMPLOYEES; TABLE HR.DEPARTMENTS;
Add Extract:
GGSCI (orclprd.localdomain) 9> dblogin USERIDALIAS GGUSR_ORCLPRD
Successfully logged into database.
GGSCI (orclprd.localdomain as GGUSR@orclprd) 10> ADD EXTRACT EEMP, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (orclprd.localdomain as GGUSR@orclprd) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EEMP 00:00:00 00:01:12
GGSCI (orclprd.localdomain as GGUSR@orclprd) 18> ADD EXTTRAIL /u01/goldengate/db19/dirdat/em, EXTRACT EEMP, MEGABYTES 200
EXTTRAIL added.
GGSCI (orclprd.localdomain as GGUSR@orclprd) 20> INFO EEMP SHOWCH
EXTRACT EEMP Initialized 2023-11-20 13:50 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:03:22 ago)
Log Read Checkpoint Oracle Redo Logs
2023-11-20 13:50:43 Seqno 0, RBA 0
SCN 0.0 (0)
Current Checkpoint Detail:
Read Checkpoint #1
Oracle Redo Log
Startup Checkpoint (starting position in the data source):
Timestamp: 2023-11-20 13:50:43.000000
SCN: 0.0 (0)
Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
Timestamp: 2023-11-20 13:50:43.000000
SCN: 0.0 (0)
Current Checkpoint (position of last record read in the data source):
Timestamp: 2023-11-20 13:50:43.000000
SCN: 0.0 (0)
Write Checkpoint #1
GGS Log Trail
Current Checkpoint (current write position):
Sequence #: 0
RBA: 0
Timestamp: 2023-11-20 13:53:49.608007
Extract Trail: /u01/goldengate/db19/dirdat/em
Seqno Length: 6
Flip Seqno Length: Yes
Trail Type: EXTTRAIL
Header:
Version = 2
Record Source = U
Type = 10
# Input Checkpoints = 1
# Output Checkpoints = 1
Configuration:
Data Source = 3
Transaction Integrity = 1
Task Type = 0
Status:
Start Time = 2023-11-20 13:50:43
Last Update Time = 2023-11-20 13:50:43
Stop Status = G
Last Result = 0
GGSCI (orclprd.localdomain) 6> start eemp
Sending START request to MANAGER ...
EXTRACT EEMP starting
GGSCI (orclprd.localdomain) 14> info eemp
EXTRACT EEMP Last Started 2023-11-20 22:45 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 27698
Log Read Checkpoint Oracle Redo Logs
2023-11-20 22:45:44 Seqno 8, RBA 21059072
SCN 0.2470354 (2470354)
PUMP Process:
GGSCI (orclprd.localdomain) 4> view param PEMP EXTRACT PEMP SETENV (ORACLE_SID=ORCLPRD) PASSTHRU CACHEMGR CACHESIZE 2GB, CACHEDIRECTORY /u01/goldengate/gg_dircache 2GB EOFDELAYCSECS 1 FLUSHCSECS 1 RMTHOSE 192.168.56.20, MGRPORT 7809 RHTTRAIL /u01/gg_target/hr GETUPDATEBEFORES GETUPDATEAFTERS REPORTCOUNT EVERY 10 MINUTES, RATE TABLE HR.EMPLOYEES; TABLE HR.DEPARTMENTS;
GGSCI (orclprd.localdomain) 5> ADD EXTRACT PEMP, EXTTRAILSOURCE /u01/goldengate/db19/dirdat/em, EXTSEQNO 0 EXTRACT added. GGSCI (orclprd.localdomain) 6> ADD RMTTRAIL /u01/gg_target/dirdat/hr, EXTRACT PEMP, SEQNO 0, RBA 0 MEGABYTES 200 RMTTRAIL added.
Configure Replicat process on target database:
GLOBALS Parameter on setting on Target:
GGSCI (orcl19c.localdomain as GGUSR@orcl) 29> view param ./GLOBALS GGSCHEMA GGUSR CHECKPOINTTABLE ggusr.CHKPT ALLOWOUTPUTDIR /u01/gg_target
Replicat process parameter files:
GGSCI (orcl19c.localdomain as GGUSR@orcl) 28> view param REMP REPLICAT REMP SETENV (ORACLE_SID=ORCLPRD) USERIDALIAS GGUSR_ORCL MAP HR.EMPLOYEES, TARGET HR.EMPLOYEES; MAP HR.DEPARTMENTS, TARGET HR.DEPARTMENTS;
GGSCI (orcl19c.localdomain as GGUSR@orcl) 45> ADD REPLICAT REMP, EXTTRAIL /u01/gg_target/hr, CHECKPOINTTABLE GGUSR.CHKPT
REPLICAT added.
GGSCI (orcl19c.localdomain as GGUSR@orcl) 46> INFO REMP
REPLICAT REMP Initialized 2023-11-23 03:19 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint File /u01/gg_target/hr000000000
First Record RBA 0
GGSCI (orcl19c.localdomain as GGUSR@orcl) 47> START REMP
Sending START request to MANAGER ...
REPLICAT REMP starting
GGSCI (orcl19c.localdomain as GGUSR@orcl) 48> INFO REMP
REPLICAT REMP Last Started 2023-11-23 03:20 Status RUNNING
Checkpoint Lag 00:14:34 (updated 00:00:00 ago)
Process ID 7378
Log Read Checkpoint File /u01/gg_target/hr000000002
2023-11-23 03:05:34.898257 RBA 2253
Validation:
Insert a Record into table on Source DB: SQL> insert into hr.departments values (290, 'IT', 200, 1700); 1 row created. SQL> commit; Commit complete.
Extract Process:
GGSCI (orclprd.localdomain) 1> stats EEMP
Sending STATS request to EXTRACT EEMP ...
Start of Statistics at 2023-11-23 02:58:36.
Output to /u01/goldengate/db19/dirdat/em:
Extracting from HR.DEPARTMENTS to HR.DEPARTMENTS:
*** Total statistics since 2023-11-23 02:57:12 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
Replicate Process:
GGSCI (orcl19c.localdomain as GGUSR@orcl) 49> STATS REMP
Sending STATS request to REPLICAT REMP ...
Start of Statistics at 2023-11-23 03:20:16.
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 0.00
Mapped operations 0.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
Replicating from HR.DEPARTMENTS to HR.DEPARTMENTS:
*** Total statistics since 2023-11-23 03:20:09 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 1.00
