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.

Use Oracle ASH to draw oracle session block chain

Updated on : 14 Jul 2019, since the original version released on 12Jul2019 had #2 bugs, which is fixed now

Objective: On our day to day DBA life, there are chances we might end up needing the db session block chain from the past, if it is current or live issue - then you have V$WAIT_CHAINS to see the block chain live.But if it is from the past, how do you pull it?

DBA_HIST_ACTIVE_SESS_HISTORY stores the information, but it needs some effort in mapping the block chain with the volume of data we have to process.

Instead that, I just used oracle's hierarchial query (thanks to Tanel Poder for sparking the idea in me) and some join tweeks to come up with the below sqls.

Version tested: 11.2.0.1


SQL1 - use the below row source [this query gives the sample sorted session, what it is doing along with what is its blocker and what is that doing, in case there is no information, it marks that session as IDLE - with username marked as NOSESSION]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID
,a.BLOCKING_SESSION
,a.BLOCKING_SESSION_SERIAL#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select * from dashmap
order by 1,2,3,4,5;

Output:

Query 2: To get a map of block chain and other session details [this query gives you a over view of all the sessions - i) sessions with no blockers ii) sessions with blockers available in ash. The only caveat is you wont get the sessions with blockers, but the blockers arent visible in ash, see the hierarchial query looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I dont know today if there is way to get those sessions with blockers which arent available/visible in ash using hierarchial query]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 11:10:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select /* parallel(4) */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS leaf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
;

Output:

Query 3: To filter out only the session blocking chain . Remeber to read the conditions the query works on from last (query#2) [this query will filter out only blocked sessions with their blocker details, kind of chain identity]

with dash as
(
select /*+ parallel(4) materialize */
*
from dba_hist_active_sess_history
where cast(SAMPLE_TIME as date) between to_date('11-JUL-2019 11:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('11-JUL-2019 12:05:00','DD-MON-YYYY HH24:MI:SS')
),
do as
(
select /*+ parallel(4) materialize */
*
from dba_objects
where object_id in (select /*+ parallel(2) */ distinct(CURRENT_OBJ#) from dash)
),
du as
(
select /*+ materialize */ user_id,username
from dba_users
where user_id in (select /*+ parallel(2) */ distinct(user_id) from dash)
),
dashmap as
(
select /*+ parallel(4) materialize */ a.INSTANCE_NUMBER ino
,a.SAMPLE_TIME sampt
,a.SAMPLE_ID sampi
,a.SESSION_ID sid
,a.SESSION_SERIAL# s#
,a.SESSION_TYPE stype
,decode(a.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(a.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)) pgm
,b.username iuser
,a.SQL_ID sql
,a.EVENT
,a.WAIT_CLASS wc
,a.TIME_WAITED tw
,a.MACHINE mach
,e.owner own
,e.object_type obty
,e.object_name obnm
,e.subobject_name suobnm
,a.BLOCKING_INST_ID abino
,a.BLOCKING_SESSION absid
,a.BLOCKING_SESSION_SERIAL# abs#
,nvl(c.instance_number,99) bino
,nvl(c.session_id,9999999) bsid
,nvl(c.SESSION_SERIAL#,9999999) bs#
,nvl(c.SAMPLE_ID,99) bsample
,nvl(d.username,'NOSESSION') buser
,nvl(c.sql_id,'NOINFO') bsql
,nvl(decode(c.SESSION_TYPE,'BACKGROUND','BG_'||regexp_substr(c.program,'\(([[:alnum:]]+\))'),'FG_'||substr(a.program,1,15)),'NOINFO') bpgm
,nvl(c.event,'NOINFO') bevent
,nvl(c.WAIT_CLASS,'NOINFO') bwc
,nvl(c.TIME_WAITED,9999999) btw
,nvl(c.MACHINE,'NOINFO') bmach
,nvl(f.owner,'NOINFO') bown
,nvl(f.object_type,'NOINFO') bobty
,nvl(f.object_name,'NOINFO') bobnm
,nvl(f.subobject_name,'NOINFO') bsuobnm
from dash a
inner join du b on a.user_id=b.user_id
left join do e on a.current_obj#=e.object_id
left join (dash c
inner join du d on c.user_id=d.user_id
left join do f on c.current_obj#=f.object_id
) on (
a.BLOCKING_INST_ID=c.INSTANCE_NUMBER
and a.SAMPLE_ID=c.SAMPLE_ID
and a.BLOCKING_SESSION=c.SESSION_ID
and a.BLOCKING_SESSION_SERIAL#=c.SESSION_SERIAL#
)
)
select *
from
(
select /* parallel(4) materialize */
sampt
,ino
,sid
,s#
,stype
,pgm
,iuser
,sql
,event
,wc
,tw
,mach
,own
,obty
,obnm
,suobnm
,bino
,bsid
,bs#
,buser
,bsql
,bpgm
,bevent
,bwc
,btw
,bmach
,bown
,bobty
,bobnm
,bsuobnm
,RPAD('.', (level-1)*2, '.') ||ino||'~'||sid||'~'||s#||'~'||iuser AS tree
,level lvl
,CONNECT_BY_ROOT (ino||'~'||sid||'~'||s#||'~'||iuser) AS root_id
,LTRIM(SYS_CONNECT_BY_PATH((ino||'~'||sid||'~'||s#||'~'||iuser), '->'),'->') AS path
,CONNECT_BY_ISLEAF AS lf
,CONNECT_BY_ISCYCLE AS cycle
from dashmap
START WITH (abino is null
and absid is null
and abs# is null)
CONNECT BY NOCYCLE prior (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser) = (sampi||'~'||bino||'~'||bsid||'~'||bs#||'~'||buser)
ORDER SIBLINGS BY (sampi||'~'||ino||'~'||sid||'~'||s#||'~'||iuser)
)
where (lvl>1)
;

Output: 

Remember the caveat: The only caveat  of query#2 & query#3 is you wont get the sessions with blockers, but the blockers aren't visible in ash, see the hierarchical query (#2 & #3) looks for 2 conditions, a) sessions with no blockers and b) sessions with blockers & such blockers are available in ash, I don't know today if there is way to get those sessions with blockers which aren't available/visible in ash using hierarchical query]



If you find it difficult to follow this blog, please bear with me, I will write more explanation. Also if you want the excel view of the data, please get in touch with me through feedback button.

Foot notes - 19Jul23 - to get the output formatted

set time on

set timing on

set lines 2000

set pages 49999

col sampt for a30

col pgm for a18

col iuser for a20

col event for a30

col mach for a30

col own for a30

col obty for a30

col obnm for a30

col subonm for a30

col buser for a20

col bpgm for a18

col bevent for a30

col bmach for a30

col bown for a30

col bobty for a30

col bobnm for a30

col bsuobnm for a30

col tree for a70

col root_id for a70

col path for a700

set colsep ,

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

As usual thanks for the visit :)
Have a good day.