Golden Gate’s primary goal is to ensure all the transactions from the Source to the Target location are in full sync or not, vice versa from the Target location to the source location in case of Bi-Directional Active/Active replication.
Below are the various checks to be accountable if the DBA is monitoring the replication. Probably more can be added here is my list as of now.
– Does Replicat is still applying or finished?
– Verify the tables/DML statistics from the Replicat Process
– Replicat process statistics
– Replicat current information/status
– what time was the last replicat applied on the target
– Let’s read the trail file.
The easiest method to know whether the replicat is still pending with any transactions, use the below command. If it says YES, that means there is no more redo. Since in our case, it is “NO” that refers still replicat is working or applying.
GGSCI (ORA-X2 as ggadmin@PRODT) 11> send inrep1, logend Sending LOGEND request to REPLICAT INREP1 ... NO
We can also track which tables replication is in progress and how many transactions are performed, how many inserts, updates, deletes, and various other information can be fetched using stats.
GGSCI (ORA-X2 as ggadmin@PRODT) 12> stats inrep1 Sending STATS request to REPLICAT INREP1 ... Start of Statistics at 2021-07-05 11:45:13. Integrated Replicat Statistics: Total transactions 73544.00 Redirected 0.00 Replicated procedures 0.00 DDL operations 0.00 Stored procedures 0.00 Datatype functionality 0.00 Operation type functionality 0.00 Event actions 0.00 Direct transactions ratio 0.00% Replicating from SCOTT.DB_BATCH_INFO to SCOTT.DB_BATCH_INFO: *** Total statistics since 2021-07-05 11:41:55 *** Total inserts 3.00 Total updates 2.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 5.00 *** Daily statistics since 2021-07-05 11:41:55 *** Total inserts 3.00 Total updates 2.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 5.00 *** Hourly statistics since 2021-07-05 11:41:55 *** Total inserts 3.00 Total updates 2.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 5.00 *** Latest statistics since 2021-07-05 11:41:55 *** Total inserts 3.00 Total updates 2.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 5.00 Replicating from SCOTT.DB_BATCH_INFO to GGADMIN.EXCEPTIONS: *** Total statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Daily statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Hourly statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Latest statistics since 2021-07-05 11:41:55 *** No database operations have been performed. Replicating from SCOTT.DB_DOSSIER to SCOTT.DB_DOSSIER: *** Total statistics since 2021-07-05 11:41:55 *** Total inserts 72151.00 Total updates 144296.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 216447.00 *** Daily statistics since 2021-07-05 11:41:55 *** Total inserts 72151.00 Total updates 144296.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 216447.00 *** Hourly statistics since 2021-07-05 11:41:55 *** Total inserts 72151.00 Total updates 144296.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 216447.00 *** Latest statistics since 2021-07-05 11:41:55 *** Total inserts 72151.00 Total updates 144296.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 216447.00 Replicating from SCOTT.DB_DOSSIER to GGADMIN.EXCEPTIONS: *** Total statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Daily statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Hourly statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Latest statistics since 2021-07-05 11:41:55 *** No database operations have been performed. Replicating from SCOTT.DB_DOSSIER_HISTO_STATUT to SCOTT.DB_DOSSIER_HISTO_STATUT: *** Total statistics since 2021-07-05 11:41:55 *** Total inserts 144298.00 Total updates 72150.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 216448.00 *** Daily statistics since 2021-07-05 11:41:55 *** Total inserts 144298.00 Total updates 72150.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 216448.00 *** Hourly statistics since 2021-07-05 11:41:55 *** Total inserts 144298.00 Total updates 72150.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 216448.00 *** Latest statistics since 2021-07-05 11:41:55 *** Total inserts 144298.00 Total updates 72150.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 216448.00 Replicating from SCOTT.DB_DOSSIER_HISTO_STATUT to GGADMIN.EXCEPTIONS: *** Total statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Daily statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Hourly statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Latest statistics since 2021-07-05 11:41:55 *** No database operations have been performed. Replicating from SCOTT.DB_EVT to SCOTT.DB_EVT: *** Total statistics since 2021-07-05 11:41:55 *** Total inserts 951041.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 951041.00 *** Daily statistics since 2021-07-05 11:41:55 *** Total inserts 951041.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 951041.00 *** Hourly statistics since 2021-07-05 11:41:55 *** Total inserts 951041.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 951041.00 *** Latest statistics since 2021-07-05 11:41:55 *** Total inserts 951041.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 951041.00 Replicating from SCOTT.DB_EVT to GGADMIN.EXCEPTIONS: *** Total statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Daily statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Hourly statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Latest statistics since 2021-07-05 11:41:55 *** No database operations have been performed. Replicating from SCOTT.DB_EVT_HISTO_STATUT to SCOTT.DB_EVT_HISTO_STATUT: *** Total statistics since 2021-07-05 11:41:55 *** Total inserts 951041.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 951041.00 *** Daily statistics since 2021-07-05 11:41:55 *** Total inserts 951041.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 951041.00 *** Hourly statistics since 2021-07-05 11:41:55 *** Total inserts 951041.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 951041.00 *** Latest statistics since 2021-07-05 11:41:55 *** Total inserts 951041.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 951041.00 Replicating from SCOTT.DB_EVT_HISTO_STATUT to GGADMIN.EXCEPTIONS: *** Total statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Daily statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Hourly statistics since 2021-07-05 11:41:55 *** No database operations have been performed. *** Latest statistics since 2021-07-05 11:41:55 *** No database operations have been performed. End of Statistics.
Few more monitoring replicat process, we can use the below commands to interpret the replicat process.
GGSCI (ORA-X2 as ggadmin@PRODT) 13> lag replicat inrep1 Sending GETLAG request to REPLICAT INREP1 ... Last record lag 3,298 seconds. Low watermark lag: 3,342 seconds High watermark lag: 3,342 seconds Low watermark position: 173746302593. High watermark position: 173746302593. GGSCI (ORA-X2 as ggadmin@PRODT) 14> info inrep1 REPLICAT INREP1 Last Started 2021-07-05 11:41 Status RUNNING INTEGRATED Checkpoint Lag 00:56:02 (updated 00:00:06 ago) Process ID 24323 Log Read Checkpoint File ./dirdat/rt000000013 2021-07-05 10:50:08.903435 RBA 449405837
In a few cases, the extract itself did not make any trail files, and in a few cases, the pump was not sent trail files from source to target or vice versa, so we have to verify each process like below.
GGSCI (ORA-X1) 4> lag extract inext1 2021-07-05 11:47:38 INFO OGG-14054 Lag from heartbeat table requires DBLOGIN. Sending GETLAG request to EXTRACT INEXT1 ... Last record lag 2 seconds. At EOF, no more records to process GGSCI (ORA-X1) 5> lag extract pmp1 2021-07-05 11:47:42 INFO OGG-14054 Lag from heartbeat table requires DBLOGIN. Sending GETLAG request to EXTRACT PMP1 ... Last record lag 5 seconds. At EOF, no more records to process GGSCI (ORA-X1) 6>
what time is the last replicat applied on the target? We can also read the report file which consists of the timestamp of each operation.
2021-07-05 11:41:56 INFO OGG-06505 MAP resolved (entry SCOTT.DB_EVT_HISTO_STATUT): Map "SCOTT"."DB_EVT_HISTO_STATUT" TARGET GGADMIN.EXCEPTIONS , COLMAP ( rep_name = @GETENV('GGENVIRONM
ENT', 'GROUPNAME') , TABLE_NAME = @GETENV ('GGHEADER', 'TABLENAME') , ERRNO = @GETENV ('LASTERR', 'DBERRNUM') , DBERRMSG = @GETENV ('LASTERR', 'DBERRMSG') , OPTYPE = @GETENV ('LASTERR', 'OPTYPE') , ERRTYPE = @GETENV ('LASTERR', 'ERRTYPE') , LOGR
BA = @GETENV ('GGHEADER', 'LOGRBA') , LOGPOSITION = @GETENV ('GGHEADER', 'LOGPOSITION') , COMMITTIMESTAMP = @GETENV ('GGHEADER', 'COMMITTIMESTAMP') , GGS_FILENAME = @GETENV('GGFILEHEADER', 'FILENAME') , CDRFAIL = @GETENV('DELTASTATS','CDR_RESOLU
TIONS_FAILED') , CDRSUC = @GETENV('DELTASTATS','CDR_RESOLUTIONS_SUCCEEDED') , CDRDETECT = @GETENV('DELTASTATS','CDR_CONFLICTS')) , INSERTALLRECORDS , EXCEPTIONSONLY.
2021-07-05 11:41:56 INFO OGG-06510 Using the following key columns for target table GGADMIN.EXCEPTIONS: LOGRBA, LOGPOSITION, COMMITTIMESTAMP.
2021-07-05 11:41:56 INFO OGG-06074 Unique constraint columns used in dependency calculation for table GGADMIN.EXCEPTIONS: LOGRBA, LOGPOSITION, COMMITTIMESTAMP.
2021-07-05 11:41:56 INFO OGG-10188 Instantiation CSN filtering on table SCOTT.DB_EVT_HISTO_STATUT has ended, now passing transactions starting at CSN 173,738,817,853.
2021-07-05 11:42:33 INFO OGG-01021 Command received from GGSCI: STATS.
2021-07-05 11:42:56 INFO OGG-08549 813,263 records processed as of 2021-07-05 11:42:56.663228 (rate 13,463, delta 13,463).
2021-07-05 11:43:22 INFO OGG-01021 Command received from GGSCI: GETLAG.
2021-07-05 11:43:52 INFO OGG-01971 The previous message, 'INFO OGG-01021', repeated 1 times.
2021-07-05 11:43:55 INFO OGG-01021 Command received from GGSCI: STATS lagend.
2021-07-05 11:43:56 INFO OGG-08549 1,539,747 records processed as of 2021-07-05 11:43:56.731109 (rate 12,781, delta 12,094).
2021-07-05 11:44:38 INFO OGG-02232 Switching to next trail file /u01/app/oracle/product/OGG_19.1.0/ogghome_1/dirdat/rt000000013 at 2021-07-05 11:44:38.968326 due to EOF. with current RBA 999,999,880.
2021-07-05 11:44:50 INFO OGG-01021 Command received from GGSCI: LAGEND.
2021-07-05 11:44:56 INFO OGG-01971 The previous message, 'INFO OGG-01021', repeated 1 times.
2021-07-05 11:44:56 INFO OGG-08549 2,229,717 records processed as of 2021-07-05 11:44:56.955496 (rate 12,339, delta 11,456).
2021-07-05 11:45:05 INFO OGG-01021 Command received from GGSCI: LOGEND.
2021-07-05 11:45:13 INFO OGG-01021 Command received from GGSCI: STATS.
GGSCI (ORA-X2 as ggadmin@PRODT) 27> !
info inrep1
REPLICAT INREP1 Last Started 2021-07-05 11:41 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 24323
Log Read Checkpoint File ./dirdat/rt000000013
2021-07-05 10:52:38.001714 RBA 610774298
GGSCI (ORA-X2 as ggadmin@PRODT) 28>
Strings of the file to ensure the transactions are written on the target by pump process.
(oracle):PRODT@ORA-X2:/u01/app/oracle/product/OGG_19.1.0/ogghome_1/dirdat> strings rt000000000 ?uri:pvzvd4008::u01:app:oracle:product:OGG_19.1.0:ogghome_1:PMP15 Auri:pvzvd4008::u01:app:oracle:product:OGG_19.1.0:ogghome_1:INEXT16 ./dirdat/rt0000000007 172828404721 172829925270 ,............................................ Linux1 3.10.0-1160.59.1.el7.x86_643 ##1 SMP Tue Feb 21 13:10:56 PST 20214 x86_642 PRODS2 PRODS3 GOracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production 19.15.0.0.09 GMT3 PMP11 PRODSZ SCOTT.DB_ADRESSE ID_ADRESSE@ ID_PERSONNE@ S_COMMENTAIRE@ S_LIGNE_1@ S_LIGNE_2@ S_LIGNE_3@ S_LIGNE_4@ S_LIGNE_5@ S_LIGNE_6@ CD_INSEE@ CD_PAYS@ CD_POSTAL@ NO_TELEPHONE@ NO_BUREAU@ NO_MOBILE@ NO_FAX@ S_EMAIL@ B_NPAI CD_ORIGINE_ADRESSE@ DT_CREATION ID_UTILISATEUR@ REPCONFLICT_TS 403674172 303109310 1900-01-01:00:00:00 1900-01-01:00:00:00.000000000T AAzbAbAAFAAOtVdAA4 1728284047216 0.5.32.5189587i 403674174 303109310 1900-01-01:00:00:00 1900-01-01:00:00:00.000000000T AAzbAbAAOAAOxzBAA2 1728293725516 0.2.20.5163699i 403674176 303109310 1900-01-01:00:00:00 1900-01-01:00:00:00.000000000T AAzbAbAAQAAO6xOAA4 1728299251916 0.9.2.5097684i 403674178 303109310 1900-01-01:00:00:00 1900-01-01:00:00:00.000000000T AAzbAbAAQAAO6xOAA5 1728299252706 0.14.29.3707246i (oracle):PRODT@ORA-X2:/u01/app/oracle/product/OGG_19.1.0/ogghome_1/dirdat> Going forward, I will add more ways to troubleshoot, monitor, and interpret the Golden Gate Processes. Stay Tuned!
