New in-built mechanism for Lag Monitoring in Oracle Golden Gate 12.2

New in-built mechanism for Lag Monitoring in GG 12.2

To implement new in-built mechanism for Lag Monitoring in GG 12.2, we must have fulfilled below two pre-requisites.

1.    OGG 12.2 on both source and target side.

2.    Trail files must be in latest format (FORMAT RELEASE 11.2 / or any other version not supported)

Configuring Heartbeat Table

1.    Add Heartbeat table on Target Side

2.    Add Heartbeat table on Source Side

3.    Login to Target Database SQL Plus and execute below query to check the Lag

select local_database, remote_database, incoming_heartbeat_age, incoming_path, incoming_lag from .GG_LAG

4.  From GGSCI – After login to Target DB, execute ‘lag’ command

Note:
Ignore CURRENT_LOCAL_TS column value, as it is populated as per UTC – GMT Time
zone. It design like this only.

Ignore OUTGOING_* columns of GG_LAG, as its for Active-Active – Bi-Directional 
Replication.

Changing Trail file format to latest version (12.2) from 11.2 in OGG 12.2

Note: Only if current EXTRACT File format is not latest aka. 12.2

If Trail files are with format 11.2 then need to remove ‘FORMAT RELEASE 11.2’ parameter from configuration file of Extract.

1.    STOP Extract, in which trail files are generated with format 11.2

2.    Remove ‘format release 11.2’ in EXTRAIL from Extract parameter file.

EXTTRAIL TRAILFILE_FULL_PATH, format release 11.2

To

EXTTRAIL TRAILFILE_FULL_PATH

3.    Rollover Extract

alter etrollover EXTRACT_NAME

2017-04-28 08:54:52 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically. 
EXTRACT altered. 

4.    Start Extract

5.    Check whether New SEQ. No – Trail file generated in /dirdat

6.    As highlighted in Step 3, do ALTER EXTSEQNO at PUMP/REPLICAT

Note : Though EXTRACT has rollover, REPLICATE was not aware about it and just stuck with old SEQ NO. While EXTRACT has already created few more trails file after that.

  

In this Example, we don’t have PUMP Extract, because source, downstream and target databases are running on the same machine (server).

So, we directly make required changes in REPLICAT process.

1. Check replicate process - Info

Log Read Checkpoint File still with the old file file seq. while Extract has rollover it and created mire trail files after that.

2. send logend REPLICATE_NAME

Sending LOGEND request to REPLICAT REPLICATE_NAME... 
YES. 

If Response is ‘YES’ then REPLICAT has processed entire Trail File upto End (EoF). No any transaction left to proceed.

3. stop REPLICATE_NAME

Sending STOP request to REPLICAT REPLICATE_NAME ... 

Request processed. 

Now, alter REPLICATE and start it with New SEQ. No.

4. alter REPLICAT REPLICATE_NAME, EXTSEQNO , EXTRBA 0 

2017-04-28 09:47:12 INFO OGG-06594 Replicat  has been altered through GGSCI. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start REPLICATE_NAME with NOFILTERDUPTRANSACTIONS option. 

REPLICAT altered. 

5. start  REPLICATE_NAME

6. info  REPLICATE_NAME

We can see, REPLICAT has processed to next seq.

Check the stats of both Extract and Replicate process after some time. They should be in sync.