Oracle Golden Gate – Performance Toolkit Monitor

Applicable to Oracle Golden Gate version 12.1 and 12.2

Download ‘Oracle Golden Gate – Performance Toolkit Monitor’ – OGG - PTM 

While writing this post it's available on below URL.

Download: http://www.oracle.com/technetwork/middleware/goldengate/oracle-goldengate-exchange-3805527.html

Previously it was hosted on below URL and having OGGPTK.jar

https://java.net/projects/oracledi/downloads/download/GoldenGate/OGGPTK.jar

To Enable OGG- PTM  - We must fulfill below pre-requisite.

In each of the GG Instance that you want to monitor, do the following in ggsci: 

      1. edit params ./GLOBALS

 add: ENABLEMONITORING

      2. create the datastore

 in ggsci: create datastore

Note: Using the ENABLEMONITORING parameter requires a license for the Golden Gate Management Pack. 

How to Launch:

Best thing is that, you don't need to logged-in to server to monitor the performance of OGG instance.

You can invoked as many as PTM session from your PC.

Open Command Prompt  (Considering that you have workstation on Windows OS)

• Run >> cmd 
• Check JAVA Version: java -version

    If JAVA is not installed then first download JDK and then try.

• java -jar \ogg-performancetoolkit-3810657.jar

Conection Name: As per your choice

Host : hostname or IP where OGG instance is running

Port  : OGG Manager Port (Check mgr.prm or ggsci > info mgr)

Click on 'Save'

Now go to File >> Connect - Now you can see the 'Connection Name' which you just have saved.

Click on that 'Connection Name'.

Please note, It shows all processes - including threads of 'coordinated' replicates. Here R_RA1*** and R_RA2*** are actually threads of co-ordinated replicates R_RA1 and R_RA2 respectively. 

Click on any process, will launch detail page on right side panel.

Process Performance

You can see below tabs on detail window

• Process Performance
• Thread Performance
• Status and Configuration
• Trail Files
• Cache Statistics
• Database Statistics
• Queue Statistics
• Inflight Transactions

Thread Performance

Status and Configuration

Below you can see another three tabs - 

Log - Its ggserr.log

status - Stop/Start history of process

Files - Location of Configuration FIles

Trail Files

Cache Statistics

It gives the similar information while executing 'SEND EXTRACT group_name, CACHEMGR {CACHESTATS | CACHEQUEUES | CACHEPOOL n}'

Database Statistics

Queue Statistics

Inflight Transactions

For Replication Process.

For Manager Process

You can change the Refresh Speed

You can explore this toolkit monitor and it has almost all required details to quickly reach to bottleneck in GG Setup if any.

Hope this is helpful..

We can also monitor through web browser and Curl - via RESTFUL web services URL 

FYI: Oracle Golden Gate version 12.3 Microservice Architecture is also based on REST API

Extended Monitoring Points - All Processes

http://xx.xxx.xxx.xx:7811/mpointsx

http://xx.xxx.xxx.xx:7811/groups

Click on Hyper Link

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.