Oracle GoldenGate Replication Troubleshooting using GoldenGate logdump utility - Microservice Architecture

Oracle Golden Gate 21.3 - Usage of logdump to find the rba corresponding to a csn

Usage notes:
GoldenGate's logdump is a utility used to troubleshoot oracle golden gate replication issues with conflict transaction, unsupported operation etc...

Scenario in our case: In our case, we are going to use this to workaroud a bug 33701569

Our situation:

Replication is reluctant to start here...

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 5> start replicat rpt2;
2023-08-26T21:47:25Z  ERROR   OGG-12111  JSON element '/' does not match any schemas

========
Replicat info output:

OGG (http://127.0.0.1:9011 oggdep01 as ggtgt04t@GGTGT04T) 4> info replicat rpt2 detail
Replicat   RPT2      Initialized  2023-08-26 22:41   Status STOPPED
Parallel
Checkpoint Lag       00:00:00 (updated 00:03:56 ago)
Log Read Checkpoint  File src/r3000000000
                     First Record  RBA 0
Encryption Profile   LocalWallet
Current Log BSN value: (no data)

Low Watermark CSN value: (no data)

High Watermark CSN value: (no data)


Current directory    /
Report file          /oggdata/oggdep01/var/lib/report/RPT2.rpt (does not yet exist)
Parameter file       /oggdata/oggdep01/etc/conf/ogg/rpt2.prm
Checkpoint file      /oggdata/oggdep01/var/lib/checkpt/RPT2.cpr
Checkpoint table     ggadmin_tgt.ggs_checkpoint
Process file         /oggdata/oggdep01/var/run/RPT2.pcr
Error log            /oggdata/oggdep01/var/log/ggserr.log
========

The replicat isnt able to navigate through the extract file, is stuck @ rba 0 with extractfile seq 0.
No error message in file /oggdata/oggdep01/var/log/ggserr.log as well.
So now we need to navigate manually through the extract.

As part of the replication setup..

1. I enabled extract, extract was running.
2. RMAN backup was secured and restored in target using a scn 459732 

[This scn translates to "Incomplete Recovery applied until change 459732 time 08/26/2023 17:48:09" from GGTGT04T alert log when recovery finished]

Steps:
1. Launch logdump from ${GG_HOME} and turn on necessary env settings

FILEHEADER DETAIL
GHDR ON
DETAIL ON
USERTOKEN DETAIL
RECLEN 128

Actual output:

[oracle@vcentos79-oracle-sa1 ~]$ logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.


Logdump 73 >FILEHEADER DETAIL
GHDR ON
DETAIL ON
USERTOKEN DETAIL
RECLEN 128Logdump 74 >Logdump 75 >Logdump 76 >Logdump 77 >
Record length set to 128
Logdump 78 >

2. Open the necessary trail file

open /oggdata/oggdep01/var/lib/data/src/r3000000000
open /oggdata/oggdep01/var/lib/data/src/r3000000001

Actual output - follow next step for iteration through file.

tail file info:
[oracle@vcentos79-oracle-sa1 src]$ ls -altr
total 1280
drwxr-x---. 3 oracle oinstall     17 Jun 18 20:22 ..
-rw-r-----. 1 oracle oinstall   1329 Jun 18 21:26 es000000000
-rw-r-----. 1 oracle oinstall   2450 Jun 21 07:21 es000000001
-rw-r-----. 1 oracle oinstall   7202 Jun 25 17:06 es000000002
-rw-r-----. 1 oracle oinstall   1388 Jul  1 14:28 es000000003
-rw-r-----. 1 oracle oinstall  31225 Jul  1 16:31 es000000004
-rw-r-----. 1 oracle oinstall  51294 Aug 12 11:36 r2000000000
-rw-r-----. 1 oracle oinstall  11367 Aug 12 11:44 r2000000001
-rw-r-----. 1 oracle oinstall  13527 Aug 12 11:48 r2000000002
-rw-r-----. 1 oracle oinstall   7043 Aug 12 12:02 r2000000003
-rw-r-----. 1 oracle oinstall  20012 Aug 12 12:08 r2000000004
-rw-r-----. 1 oracle oinstall 690186 Aug 12 22:37 r2000000005
-rw-r-----. 1 oracle oinstall  71518 Aug 26 21:59 r3000000000       << file 0
drwxr-x---. 2 oracle oinstall    253 Aug 26 21:59 .
-rw-r-----. 1 oracle oinstall 380345 Aug 26 23:07 r3000000001       << file 1

[oracle@vcentos79-oracle-sa1 src]$ pwd
/oggdata/oggdep01/var/lib/data/src

[oracle@vcentos79-oracle-sa1 src]$ stat r3000000000
  File: ‘r3000000000’
  Size: 71518           Blocks: 144        IO Block: 4096   regular file
Device: fd08h/64776d    Inode: 524396      Links: 1
Access: (0640/-rw-r-----)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Context: system_u:object_r:unlabeled_t:s0
Access: 2023-08-27 10:51:35.847861771 +0100
Modify: 2023-08-26 21:59:20.180867510 +0100
Change: 2023-08-26 21:59:20.180867510 +0100
 Birth: -
[oracle@vcentos79-oracle-sa1 src]$

3. Set position in trailfile to 0 and perform filter on the csn

pos 0
filter include csn > 459732
show filter
next

Actual output:

Logdump 78 >open /oggdata/oggdep01/var/lib/data/src/r3000000000
Current log trail file is /oggdata/oggdep01/var/lib/data/src/r3000000000.

Logdump 79 >pos 0
Reading forward from RBA 0

Logdump 80 >filter include csn > 459732

Logdump 81 >show filter
Data filters are ENABLED
Include match any

 GGSTOKEN LOGCSN > 459732
Exclude match any

Logdump 82 >next
Filtering suppressed 66 records             <<< all the records are suppressed

Logdump 83 >filter clear

Logdump 84 >show filter
Data filters are ENABLED
Include match any
Exclude match any


Logdump 85 >open /oggdata/oggdep01/var/lib/data/src/r3000000001
Log trail file /oggdata/oggdep01/var/lib/data/src/r3000000000 closed.
Current log trail file is /oggdata/oggdep01/var/lib/data/src/r3000000001.

Logdump 86 >pos 0
Reading forward from RBA 0

Logdump 87 >filter include csn > 459732

Logdump 88 >show filter
Data filters are ENABLED
Include match any
 GGSTOKEN LOGCSN > 459732
Exclude match any

Logdump 89 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     L  (x4c)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   960  (x03c0)   IO Time    : 2023/08/26 17:48:28.000.000
IOType     :   135  (x87)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         14       AuditPos   : 258576
Continued  :     N  (x00)     RecCount   :     1  (x01)
Oracle RBA : 0x00000e.000001f9.0010
2023/08/26 17:48:28.000.000 GGSUnifiedPKUpdate   Len   960 RBA 36083
Name: GGADMIN.GG_HEARTBEAT_SEED  (TDR Index: 1)
After  Image:                                             Partition x4c   G  s
 de01 0000 0000 0c00 0000 0800 4747 5352 4330 3454 | ............GGSRC04T
 0100 1f00 0000 3230 3233 2d30 382d 3236 3a31 363a | ......2023-08-26:16:
 3437 3a32 382e 3035 3837 3831 3030 3002 0004 00ff | 47:28.058781000.....
 ff00 0003 000c 0000 0008 0047 4753 5243 3034 5404 | ...........GGSRC04T.
 0004 00ff ff00 0005 0004 00ff ff00 0006 0004 00ff | ....................
 ff00 0007 001f 00ff ff00 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000                               | ........
Before Image          Len   482 (x000001e2)
BeforeColumnLen    478 (x000001de)
Column     0 (x0000), Len    12 (x000c)
Column     1 (x0001), Len    31 (x001f)
Column     2 (x0002), Len     4 (x0004)
Column     3 (x0003), Len    12 (x000c)
Column     4 (x0004), Len     4 (x0004)
Column     5 (x0005), Len     4 (x0004)
Column     6 (x0006), Len     4 (x0004)
Column     7 (x0007), Len    31 (x001f)
Column     8 (x0008), Len    31 (x001f)
Column     9 (x0009), Len    31 (x001f)
Column    10 (x000a), Len    31 (x001f)
Column    11 (x000b), Len     8 (x0008)
Column    12 (x000c), Len     4 (x0004)
Column    13 (x000d), Len     4 (x0004)
Column    14 (x000e), Len    31 (x001f)
Column    15 (x000f), Len    31 (x001f)
Column    16 (x0010), Len    31 (x001f)
Column    17 (x0011), Len    31 (x001f)
Column    18 (x0012), Len     4 (x0004)
Column    19 (x0013), Len    10 (x000a)
Column    20 (x0014), Len    10 (x000a)
Column    21 (x0015), Len    31 (x001f)
After Image           Len   478 (x000001de)
Column     0 (x0000), Len    12 (x000c)
Column     1 (x0001), Len    31 (x001f)
Column     2 (x0002), Len     4 (x0004)
Column     3 (x0003), Len    12 (x000c)
Column     4 (x0004), Len     4 (x0004)
Column     5 (x0005), Len     4 (x0004)
Column     6 (x0006), Len     4 (x0004)
Column     7 (x0007), Len    31 (x001f)
Column     8 (x0008), Len    31 (x001f)
Column     9 (x0009), Len    31 (x001f)
Column    10 (x000a), Len    31 (x001f)
Column    11 (x000b), Len     8 (x0008)
Column    12 (x000c), Len     4 (x0004)
Column    13 (x000d), Len     4 (x0004)
Column    14 (x000e), Len    31 (x001f)
Column    15 (x000f), Len    31 (x001f)
Column    16 (x0010), Len    31 (x001f)
Column    17 (x0011), Len    31 (x001f)
Column    18 (x0012), Len     4 (x0004)
Column    19 (x0013), Len    10 (x000a)
Column    20 (x0014), Len    10 (x000a)
Column    21 (x0015), Len    31 (x001f)

Filtering suppressed 34 records

Logdump 90 >count
** Count begins at RBA 36,083
LogTrail /oggdata/oggdep01/var/lib/data/src/r3000000001 has 320 records
Total Data Bytes             307200
  Avg Bytes/Record              960
GGSPKUpdate                     320
After Images                    320
Average of 320 Transactions
    Bytes/Trans .....       1008
    Records/Trans ...          1
    Files/Trans .....          1

GGADMIN.GG_HEARTBEAT_SEED                          Partition x4c
Total Data Bytes             307200
  Avg Bytes/Record              960
GGSPKUpdate                     320
After Images                    320

If you want to see much more details - increasing the record length can help.

Logdump 91 >RECLEN 1024
Record length set to 1,024

Logdump 92 >n
___________________________________________________________________
Hdr-Ind    :     E  (x45)     Partition  :     L  (x4c)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :   960  (x03c0)   IO Time    : 2023/08/26 17:48:28.000.000
IOType     :   135  (x87)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :         14       AuditPos   : 258576
Continued  :     N  (x00)     RecCount   :     1  (x01)
Oracle RBA : 0x00000e.000001f9.0010
2023/08/26 17:48:28.000.000 GGSUnifiedPKUpdate   Len   960 RBA 36083
Name: GGADMIN.GG_HEARTBEAT_SEED  (TDR Index: 1)
After  Image:                                             Partition x4c   G  s
 de01 0000 0000 0c00 0000 0800 4747 5352 4330 3454 | ............GGSRC04T
 0100 1f00 0000 3230 3233 2d30 382d 3236 3a31 363a | ......2023-08-26:16:
 3437 3a32 382e 3035 3837 3831 3030 3002 0004 00ff | 47:28.058781000.....
 ff00 0003 000c 0000 0008 0047 4753 5243 3034 5404 | ...........GGSRC04T.
 0004 00ff ff00 0005 0004 00ff ff00 0006 0004 00ff | ....................
 ff00 0007 001f 00ff ff00 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0000 0800 | ....................
 1f00 ffff 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0000 0000 0009 001f 00ff ff00 | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0a00 1f00 ffff 0000 0000 0000 | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 000b 0008 0000 0004 0052 5854 330c 0004 00ff | ...........RXT3.....
 ff00 000d 0004 00ff ff00 000e 001f 00ff ff00 0000 | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0f00 1f00 0000 3230 3233 2d30 382d | ............2023-08-
 3236 2032 303a 3539 3a32 342e 3536 3831 3131 3030 | 26 20:59:24.56811100
 3010 001f 00ff ff00 0000 0000 0000 0000 0000 0000 | 0...................
 0000 0000 0000 0000 0000 0000 0000 0000 1100 1f00 | ....................
 ffff 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0000 0012 0004 00ff ff00 0013 | ....................
 000a 0000 0006 0034 3539 3738 3014 000a 0000 0006 | .......459780.......   <<<<<<<<<<< CSN>459732
 0034 3537 3636 3415 001f 0000 0032 3032 332d 3038 | .457664......2023-08
 2d32 3620 3136 3a33 333a 3537 2e30 3030 3030 3030 | -26 16:33:57.0000000
 3030 0000 0c00 0000 0800 4747 5352 4330 3454 0100 | 00........GGSRC04T..
 1f00 0000 3230 3233 2d30 382d 3236 3a31 363a 3438 | ....2023-08-26:16:48
 3a32 382e 3139 3039 3036 3030 3002 0004 00ff ff00 | :28.190906000.......
 0003 000c 0000 0008 0047 4753 5243 3034 5404 0004 | .........GGSRC04T...
 00ff ff00 0005 0004 00ff ff00 0006 0004 00ff ff00 | ....................
 0007 001f 00ff ff00 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0800 1f00 | ....................
 ffff 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0000 0009 001f 00ff ff00 0000 | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0a00 1f00 ffff 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 000b 0008 0000 0004 0052 5854 330c 0004 00ff ff00 | .........RXT3.......
 000d 0004 00ff ff00 000e 001f 00ff ff00 0000 0000 | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0f00 1f00 0000 3230 3233 2d30 382d 3236 | ..........2023-08-26
 2032 303a 3539 3a32 342e 3536 3831 3131 3030 3010 |  20:59:24.568111000.
 001f 00ff ff00 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0000 0000 0000 1100 1f00 ffff | ....................
 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 | ....................
 0000 0000 0000 0000 0012 0004 00ff ff00 0013 000a | ....................
 0000 0006 0034 3539 3738 3014 000a 0000 0006 0034 | .....459780........4
 3537 3636 3415 001f 0000 0032 3032 332d 3038 2d32 | 57664......2023-08-2
 3620 3136 3a33 333a 3537 2e30 3030 3030 3030 3030 | 6 16:33:57.000000000
Before Image          Len   482 (x000001e2)
BeforeColumnLen    478 (x000001de)
Column     0 (x0000), Len    12 (x000c)
Column     1 (x0001), Len    31 (x001f)
Column     2 (x0002), Len     4 (x0004)
Column     3 (x0003), Len    12 (x000c)
Column     4 (x0004), Len     4 (x0004)
Column     5 (x0005), Len     4 (x0004)
Column     6 (x0006), Len     4 (x0004)
Column     7 (x0007), Len    31 (x001f)
Column     8 (x0008), Len    31 (x001f)
Column     9 (x0009), Len    31 (x001f)
Column    10 (x000a), Len    31 (x001f)
Column    11 (x000b), Len     8 (x0008)
Column    12 (x000c), Len     4 (x0004)
Column    13 (x000d), Len     4 (x0004)
Column    14 (x000e), Len    31 (x001f)
Column    15 (x000f), Len    31 (x001f)
Column    16 (x0010), Len    31 (x001f)
Column    17 (x0011), Len    31 (x001f)
Column    18 (x0012), Len     4 (x0004)
Column    19 (x0013), Len    10 (x000a)
Column    20 (x0014), Len    10 (x000a)
Column    21 (x0015), Len    31 (x001f)
After Image           Len   478 (x000001de)
Column     0 (x0000), Len    12 (x000c)
Column     1 (x0001), Len    31 (x001f)
Column     2 (x0002), Len     4 (x0004)
Column     3 (x0003), Len    12 (x000c)
Column     4 (x0004), Len     4 (x0004)
Column     5 (x0005), Len     4 (x0004)
Column     6 (x0006), Len     4 (x0004)
Column     7 (x0007), Len    31 (x001f)
Column     8 (x0008), Len    31 (x001f)
Column     9 (x0009), Len    31 (x001f)
Column    10 (x000a), Len    31 (x001f)
Column    11 (x000b), Len     8 (x0008)
Column    12 (x000c), Len     4 (x0004)
Column    13 (x000d), Len     4 (x0004)
Column    14 (x000e), Len    31 (x001f)
Column    15 (x000f), Len    31 (x001f)
Column    16 (x0010), Len    31 (x001f)
Column    17 (x0011), Len    31 (x001f)
Column    18 (x0012), Len     4 (x0004)
Column    19 (x0013), Len    10 (x000a)
Column    20 (x0014), Len    10 (x000a)
Column    21 (x0015), Len    31 (x001f)
Logdump 93 >

YouTube video corresponding to the blog:

This closes the blog.