Using Golden Gate logdump utility, line by line and summary mode

Logdump 1 >ghdr on

Logdump 2 >detail on

Logdump 3 >detail data

Logdump 4 >open ep000000003

Current LogTrail is /software/ogg/191/dirdat/01/out/ep000000003

Logdump 5 >n

Logdump 6 >n

Logdump 7 >n

...

...

After reading header metadata and some records, one can see the record in question

Logdump 29 >n

___________________________________________________________________

Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)

UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)

RecLength  :    36  (x0024)   IO Time    : 2026/01/30 20:29:22.983.254

IOType     :   134  (x86)     OrigNode   :   255  (xff)

TransInd   :     .  (x03)     FormatType :     R  (x52)

SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)

AuditRBA   :      18680       AuditPos   : 87598608

Continued  :     N  (x00)     RecCount   :     1  (x01)

2026/01/30 20:29:22.983.254 GGSUnifiedUpdate     Len    36 RBA 2571

Name: USER_A.TABLE_A  (TDR Index: 1)

After  Image:                                             Partition x0c   G  s

 1000 0000 0100 0c00 0000 0800 5465 7374 2031 3031 | ......Test 101

 0100 0c00 0000 0800 5465 7374 2031 3032           | ......Test 102

Before Image          Len    20 (x00000014)

BeforeColumnLen     16 (x00000010)

Column     1 (x0001), Len    12 (x000c)

 0000 0800 5465 7374 2031 3031                     | ....Test 101

After Image           Len    16 (x00000010)

Column     1 (x0001), Len    12 (x000c)

 0000 0800 5465 7374 2031 3032                     | ....Test 102

It is possible to view a summary of the report:

./logdump

Logdump 1 > OPEN /software/ogg/191/dirdat/01/in/et000000004

Logdump 2 > GHDR ON

Logdump 3 > DETAIL ON

Logdump 4 > DETAIL DATA 

Logdump 5 > LOG TO full_file_dump.txt

Logdump 6 > COUNT

Logdump 7 > LOG STOP

Logdump 8 > EXIT

Example:

./logdump

Logdump 1 > OPEN /software/ogg/191/dirdat/01/in/et000000004

Logdump 2 > GHDR ON

Logdump 3 > DETAIL ON

Logdump 4 > DETAIL DATA 

Logdump 5 > LOG TO full_file_dump.txt

Logdump 6 > COUNT

Logdump 7 > LOG STOP

Logdump 8 > EXIT

Logdump 30 >open /software/ogg/191/dirdat/01/in/et000000004

Current LogTrail is /software/ogg/191/dirdat/01/in/et000000004

Logdump 31 >GHDR ON

Logdump 32 >DETAIL ON

Logdump 33 >DETAIL DATA

Logdump 34 >LOG TO /software/ogg/191/dirdat/et000000004_full_dump.txt

--- Session log /software/ogg/191/dirdat/et000000004_full_dump.txt opened 2026/02/01 12:19:24.549.821 ---

Logdump 35 >COUNT

LogTrail /software/ogg/191/dirdat/01/in/et000000004 has 10 records

Total Data Bytes               1866

  Avg Bytes/Record              186

Delete                            2

Insert                            2

Update                            1

RestartOK                         2

Metadata Records                  2

Others                            1

Before Images                     2

After Images                      5

Average of 9 Transactions

    Bytes/Trans .....        101

    Records/Trans ...          1

    Files/Trans .....          1

                                                   Partition x00

Total Data Bytes                 78

  Avg Bytes/Record               26

RestartOK                         2

Metadata Records                  1

After Images                      2

*FileHeader*                                       Partition x00

Total Data Bytes               1388

  Avg Bytes/Record             1388

Others                            1

LAB_QANFV_ALLQQ.ACTION_TYPES                       Partition x00

Total Data Bytes                244

  Avg Bytes/Record              244

Metadata Records                  1

LAB_QANFV_ALLQQ.ACTION_TYPES                       Partition x0c

Total Data Bytes                156

  Avg Bytes/Record               31

Delete                            2

Insert                            2

Update                            1

Before Images                     2

After Images                      3

Logdump 36 >LOG STOP

Logging stopped

Logdump 37 >EXIT

oracle@qanfv-1-dbs-1b:/software/ogg/191>%

Logdump 30 >open /software/ogg/191/dirdat/01/in/et000000004

Current LogTrail is /software/ogg/191/dirdat/01/in/et000000004

Logdump 31 >GHDR ON

Logdump 32 >DETAIL ON

Logdump 33 >DETAIL DATA

Logdump 34 >LOG TO /software/ogg/191/dirdat/et000000004_full_dump.txt

--- Session log /software/ogg/191/dirdat/et000000004_full_dump.txt opened 2026/02/01 12:19:24.549.821 ---

Logdump 35 >COUNT

LogTrail /software/ogg/191/dirdat/01/in/et000000004 has 10 records

Total Data Bytes               1866

  Avg Bytes/Record              186

Delete                            2

Insert                            2

Update                            1

RestartOK                         2

Metadata Records                  2

Others                            1

Before Images                     2

After Images                      5

Average of 9 Transactions

    Bytes/Trans .....        101

    Records/Trans ...          1

    Files/Trans .....          1

                                                   Partition x00

Total Data Bytes                 78

  Avg Bytes/Record               26

RestartOK                         2

Metadata Records                  1

After Images                      2

*FileHeader*                                       Partition x00

Total Data Bytes               1388

  Avg Bytes/Record             1388

Others                            1

LAB_QANFV_ALLQQ.ACTION_TYPES                       Partition x00

Total Data Bytes                244

  Avg Bytes/Record              244

Metadata Records                  1

LAB_QANFV_ALLQQ.ACTION_TYPES                       Partition x0c

Total Data Bytes                156

  Avg Bytes/Record               31

Delete                            2

Insert                            2

Update                            1

Before Images                     2

After Images                      3

Logdump 36 >LOG STOP

Logging stopped

Logdump 37 >EXIT

oracle@qanfv-1-dbs-1b:/software/ogg/191>%

Golden Gate issue in Oracle 19 - LOGICAL_REPLICATION

Issue
In Oracle 19, a new field was added to USER_TABLES: logical_replication

In addition to running ADD TRANDATA and having the table supplemental logging, as can be validated in DBA_LOG_GROUPS, the table also must have USER_TABLES.logical_replication set to ENABLED.

When USER_TABLES.logical_replication is set to DISABLED, the Primary Key data is not written to the before and after image in Extract Files.

In this setup, INSERT and DELETE would work, but UPDATE Transactions are failing to be replicated, and replicat would fail with mapping error

To Fix it, change USER_TABLES.logical_replication to ENABLED, and restart Extract on Source.

Fix

ALTER TABLE USER_A.TABLE_A ENABLE LOGICAL REPLICATION;

Evidences
The Error would be coming in REPLICAT, as it fails to process the UPDATE Transaction
This error would be coming in Site B replicat logs:

2026-01-30 20:33:11  INFO    OGG-02243  Opened trail file /software/ogg/191/dirdat/01/in/et000000004 at 2026-01-30 20:33:11.076300.

2026-01-30 20:33:11  WARNING OGG-02760  ASSUMETARGETDEFS is ignored because trail file /software/ogg/191/dirdat/01/in/et000000004 contains table definitions.

2026-01-30 20:33:11  INFO    OGG-03506  The source database character set, as determined from the trail file, is UTF-8.

2026-01-30 20:33:11  INFO    OGG-06505  MAP resolved (entry USER_A.TABLE_A): MAP "USER_A"."TABLE_A", TARGET USER_A.TABLE_A.

2026-01-30 20:33:11  INFO    OGG-02756  The definition for table USER_A.TABLE_A is obtained from the trail file.

2026-01-30 20:33:11  INFO    OGG-06511  Using following columns in default map by name: ACTION_ID, ACTION_NAME.

2026-01-30 20:33:11  INFO    OGG-06510  Using the following key columns for target table USER_A.TABLE_A: ACTION_ID.

2026-01-30 20:33:11  WARNING OGG-01431  Aborted grouped transaction on USER_A.TABLE_A, Mapping error.

2026-01-30 20:33:11  WARNING OGG-01003  Repositioning to rba 2571 in seqno 4.

2026-01-30 20:33:11  WARNING OGG-01151  Error mapping from USER_A.TABLE_A to USER_A.TABLE_A.

Source Context :

  SourceModule            : [er.replicat.errors]

  SourceID                : [er/replicat/reperrors.cpp]

  SourceMethod            : [repError]

  SourceLine              : [1810]

  ThreadBacktrace         : [15] elements

                          : [/software/ogg/191/libgglog.so(CMessageContext::AddThreadContext())]

                          : [/software/ogg/191/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]

                          : [/software/ogg/191/libgglog.so(_MSG_QualTableName_QualTableName(CSourceContext*, int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition))]

                          : [/software/ogg/191/replicat(ggs::er::ReplicatContext::repError(short, int, char const*, extr_ptr_def*, ggs::gglib::gglcr::CommonLCR const*, std_rec_hdr_def*, char*, ObjectMetadata*, bool))]

                          : [/software/ogg/191/replicat()]

                          : [/software/ogg/191/replicat(ggs::er::ReplicatContext::processRecord(ggs::gglib::gglcr::CommonLCR const*, ggs::gglib::gglcr::CommonLCR*, extr_ptr_def*&, extr_ptr_def*&, bool&, int&, bool, RepCsn&))]

                          : [/software/ogg/191/replicat(ggs::er::ReplicatContext::processReplicatLoop())]

                          : [/software/ogg/191/replicat(ggs::er::ReplicatContext::run())]

                          : [/software/ogg/191/replicat()]

                          : [/software/ogg/191/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())]

                          : [/software/ogg/191/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]

                          : [/software/ogg/191/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]

                          : [/software/ogg/191/replicat(main)]

                          : [/lib64/libc.so.6(__libc_start_main)]

                          : [/software/ogg/191/replicat()]

2026-01-30 20:33:11  ERROR   OGG-01296  Error mapping from USER_A.TABLE_A to USER_A.TABLE_A.

What to check on SOURCE DB:

SELECT supplemental_log_data_min FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN

--------------------------------

YES

SELECT supplemental_log_data_min, 

       supplemental_log_data_pk, 

       supplemental_log_data_ui, 

       supplemental_log_data_all

FROM V$DATABASE;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_PK SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_ALL

------------------------- ------------------------ ------------------------ -------------------------

YES                       NO                       NO                       NO

SELECT log_group_name, 

       table_name, 

       owner, 

       log_group_type, 

       always

  FROM DBA_LOG_GROUPS

 WHERE OWNER = 'USER_A' 

   AND TABLE_NAME =  'TABLE_A';

LOG_GROUP_NAME   TABLE_NAME  OWNER   LOG_GROUP_TYPE      ALWAYS

---------------- ----------- ------- ------------------- -----------

GGS_119994       TABLE_A     USER_A  USER LOG GROUP      ALWAYS

SYS_C00197420    TABLE_A     USER_A  PRIMARY KEY LOGGING ALWAYS

SYS_C00197421    TABLE_A     USER_A  UNIQUE KEY LOGGING  CONDITIONAL

SYS_C00197422    TABLE_A     USER_A  FOREIGN KEY LOGGING CONDITIONAL

SYS_C00197423    TABLE_A     USER_A  ALL COLUMN LOGGING  ALWAYS

SELECT owner, table_name, logical_replication 

  FROM DBA_TABLES 

 WHERE owner='USER_A' 

  AND table_name='TABLE_A';

OWNER      TABLE_NAME      LOGICAL_REPLICATION

---------- --------------- ---------------------

USER_A     TABLE_A         DISABLED

ALTER TABLE USER_A.TABLE_A ENABLE LOGICAL REPLICATION;

SELECT owner, table_name, logical_replication 

  FROM DBA_TABLES 

 WHERE owner='USER_A' 

  AND table_name='TABLE_A';

OWNER      TABLE_NAME      LOGICAL_REPLICATION

---------- --------------- ---------------------

USER_A     TABLE_A         ENABLED

du vs df giving wrong disk usage results

du vs df giving wrong disk usage results

In OS, oracle is reported as using 168 Gb

While in oracle, the usage is only 78 Gb

How can that be?

Space usage in Linux:

find . -type f -printf '%s %p\n'| sort -nr | head -40

Filesystem                     Size  Used Avail Use% Mounted on

/dev/mapper/oravg2-ora_db      196G  168G   19G  91% /oracle_db/db3

At this point , many files were dropped using the Oracle syntax

Example:

Drop some big tablespaces, each one is several Gb in size.

DROP TABLESPACE OLD_TRANSACTION_202401 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE OLD_TRANSACTION_202402 INCLUDING CONTENTS AND DATAFILES;

SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name = 'OLD_TRANSACTION_202401';

No Data Found

See now space usage in Linux:

df -hP | grep db3

Filesystem                     Size  Used Avail Use% Mounted on

/dev/mapper/oravg2-ora_db      196G  168G   19G  91% /oracle_db/db3

No change in space usage, how can that be?

Check space usage with du

>cd /oracle_db

>du -sh *

78G     db3

du report usage of 78Gb, which is in sync with oracle stats, but df report a wrong result.

A common cause is that the file was deleted by a process, but is still in a deleted status, and was not deleted by the OS.
indeed this was the case here:

>lsof | grep deleted

/oracle_db/db3/db_igt/OLD_TRANSACTION_202401_1.dbf (deleted)

oracle     37484    oracle  424u      REG             253,17  4404027392    2621478 /oracle_db/db3/db_igt/OLD_TRANSACTION_202402_1.dbf (deleted)

oracle     37484    oracle  428u      REG             253,17  3670024192    2621524 

Theory:

When Linux mount point usage as reported by df does not align with the sum of file sizes reported by du, several common scenarios can explain the discrepancy

1. Open but Deleted Files

A file may be deleted using rm, but if a process still holds an open file descriptor to it, the disk space occupied by that file is not immediately released. 

It remains allocated until the process either closes the file or terminates. 

This is a very common reason for df showing higher usage than du.

For Oracle, a simple solution is to bounce the instance.

There are other options, not related to Oracle scenario

After oracle restart

>df -hP | grep ora

Filesystem                     Size  Used Avail Use% Mounted on

/dev/mapper/oravg2-ora_db      196G   78G  109G  42% /oracle_db/db3

Issue fixed!

ORA-39346: data loss in character set conversion for object COMMENT

Error in impdp
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
ORA-39346: data loss in character set conversion for object COMMENT:"NEW_SCHEMA"."SOME_COLUMN"

What to check:

SELECT column_name, comments 

  FROM USER_COL_COMMENTS 

 WHERE column_name = 'TS_E_LAST_IN_NET_EVENT';

Solution:
Replace the comment, with valid text without "garbage" characters
COMMENT ON COLUMN SOME_TABLE.SOME_COLUMN IS 

'Some Valid Comment Text';

Purpose: Purge specific sql_id from Shared Pool.

Purpose: Purge specific sql_id from Shared Pool.

It could be useful if a sql have too many child cursors, which can cause "library cache: mutex X" wait events.

CREATE OR REPLACE PROCEDURE purge_sp_by_sql AS
  v_sql_str     VARCHAR2(1000);
  v_sql_id      VARCHAR2(13);
  v_address     RAW(8);
  v_hash_value  NUMBER;
  v_param       VARCHAR2(1000);
  v_child_records_before  INTEGER;
  v_child_records_after  INTEGER;
  v_module_name SGA_W_LOG.procedure_name%TYPE; 
  v_msg_str     SGA_W_LOG.data%TYPE;
BEGIN
  v_module_name := 'purge_sp_by_sql'; 
  
  SELECT sql_id, address, hash_value 
    INTO v_sql_id, v_address, v_hash_value
    FROM SYS.V_$SQLAREA
   WHERE sql_text LIKE 'MERGE into AAA_B_CCC_DDDDDD o using%' 
     AND ROWNUM < 2;
 
  SELECT loaded_versions INTO v_child_records_before 

    FROM SYS.V_$SQLAREA WHERE sql_id = v_sql_id;

   --Format is: 'ADDRESS,HASH_VALUE',
  v_param := v_address||','||v_hash_value;
  SYS.DBMS_SHARED_POOL.purge (v_param,'C');
  
  SELECT loaded_versions INTO v_child_records_after 

    FROM SYS.V_$SQLAREA WHERE sql_id = v_sql_id;
   
  v_msg_str :=  'Child Records Stats for sql_id : '||v_sql_id||' Before: '||v_child_records_before||' After: '||v_child_records_after;
  INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified) 
  VALUES (v_module_name, v_msg_str, SYSDATE);
  commit;
END purge_sp_by_sql;

Default Oracle accounts with default password

==========================================
Oracle accounts with default password

==========================================

SELECT * FROM DBA_USERS_WITH_DEFPWD ORDER BY 1;

USERNAME                       PRODUCT

------------------------------ --------------------------------------

APPQOSSYS

DBSFWUSER                      DB Service FireWall USER

DBSNMP

DIP

GGSYS

GSMCATUSER

ORACLE_OCM

OUTLN

REMOTE_SCHEDULER_AGENT         Oracle Scheduler

SYS$UMF                        Unified Manageability Framework

SYSRAC

WMSYS

XDB

=====================

Accounts Details

=====================

APPQOSSYS

Used for storing or managing all data and metadata required by Oracle Quality of Service Management.

DBSFWUSER

The account used to run the DBMS_SFW_ACL_ADMIN package.

DBMS_SFW_ACL_ADMIN package provides API for managing service-level Access Control Lists (ACLs). 

DBSNMP

Used by Management Agent of Oracle Enterprise Manager to monitor and manage the database.

DIP

Used by Directory Integration Platform (DIP) to synchronize the changes in Oracle Internet Directory with the applications in the database.

GGSYS

The internal account used by Oracle GoldenGate. It should not be unlocked or used for a database login.

GSMCATUSER

The account used by Global Service Manager to connect to the Global Data Services catalog.

ORACLE_OCM

This account contains the instrumentation for configuration collection used by the Oracle Configuration Manager.

OUTLN

Centrally manages metadata associated with stored outlines. Supports plan stability, which enables maintenance of the same execution plans for the same SQL statements.

REMOTE_SCHEDULER_AGENT

The account to disable remote jobs on a database. 

This account is created during the remote scheduler agent configuration. 

You can disable the capability of a database to run remote jobs by dropping this user.

SYSRAC

The account used to administer Oracle Real Application Clusters (RAC).

WMSYS

The account used to store the metadata information for Oracle Workspace Manager.

XDB

Used for storing Oracle XML DB data and metadata.

==========================================

These accounts, should be locked by default

==========================================

SELECT DBA_USERS.username, 

       DBA_USERS.account_status 

  FROM DBA_USERS, 

       DBA_USERS_WITH_DEFPWD

 WHERE DBA_USERS.username =  DBA_USERS_WITH_DEFPWD.username

 ORDER BY DBA_USERS.username;

USERNAME                       ACCOUNT_STATUS

------------------------------ --------------------------------

APPQOSSYS                      EXPIRED & LOCKED

DBSFWUSER                      EXPIRED & LOCKED

DBSNMP                         EXPIRED & LOCKED

DIP                            EXPIRED & LOCKED

GGSYS                          EXPIRED & LOCKED

GSMCATUSER                     EXPIRED & LOCKED

ORACLE_OCM                     EXPIRED & LOCKED

OUTLN                          EXPIRED & LOCKED

REMOTE_SCHEDULER_AGENT         EXPIRED & LOCKED

SYS$UMF                        EXPIRED & LOCKED

SYSRAC                         EXPIRED & LOCKED

WMSYS                          EXPIRED & LOCKED

XDB                            EXPIRED & LOCKED

==========================================

To set account password to random password and lock account

==========================================

BEGIN 

  FOR r_user IN (SELECT username FROM DBA_USERS_WITH_DEFPWD WHERE username NOT LIKE '%XS$NULL%') LOOP 

    DBMS_OUTPUT.PUT_LINE('Password for user '||r_user.username||' will be changed.'); 

    EXECUTE IMMEDIATE 'ALTER USER ''||r_user.username||'' IDENTIFIED BY ''||DBMS_RANDOM.STRING('a',16)||''ACCOUNT LOCK PASSWORD EXPIRES'; 

  END LOOP;

END;

/

==========================================
Reference

==========================================

https://benchmarks.cisecurity.org/tools2/oracle/CIS_Oracle_Database_11g_R2_Benchmark_v2.2.0.pdf

Change Notification PL/SQL Example #2

Permissions

As SYS:
GRANT EXECUTE ON SYS.DBMS_CHANGE_NOTIFICATION TO XXX_YYYYY_ZZZZZ;

GRANT CHANGE NOTIFICATION TO XXX_YYYYY_ZZZZZ;

Objects to test Notification from PL/SQL:

CREATE TABLE NOTIFICATIONS (

  id NUMBER,

  message VARCHAR2(1000),

  notification_date DATE )

TABLESPACE IGT_TABLE;

CREATE SEQUENCE notifications_seq  NOMAXVALUE NOMINVALUE NOCYCLE NOCACHE;

Firewall
Applicable if application is running on another server. 

Once registration was done, there should be an entry in DBA_CHANGE_NOTIFICATION_REGS

SELECT callback FROM DBA_CHANGE_NOTIFICATION_REGS

The result should be something like:

net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.40)(PORT=47632))?PR=0

net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.30.40)(PORT=47633))?PR=0

Each JVM would have is own port it is listening to.
Port numbers by default start at 47632, and incremented by 1

Ports 47632,47633, etc should be opened in Firewall.

From Oracle IP to the JVM IP (in this example, to IP 10.20.30.40)

Flow
1. Create Callback Procedure
2. Create registration between Callback Procedure and SQL that reflect a change.

3. Run DML + commit.
4. Check Notification took place, by checking Oracle DBA_CHANGE_NOTIFICATION_REGS and Application table (NOTIFICATIONS).

Callback Procedure

CREATE OR REPLACE PROCEDURE callback_proc (ntfnds IN SYS.CHNF$_DESC) IS
  regid NUMBER;
  tbname VARCHAR2(60);
  event_type NUMBER;
  numtables NUMBER;
  operation_type NUMBER;
  numrows NUMBER;
  row_id VARCHAR2(2000);
  l_message         VARCHAR2(4000) := NULL;
BEGIN
  regid := ntfnds.registration_id;
  numtables := ntfnds.numtables;
  event_type := ntfnds.event_type;
  --INSERT INTO nfevents VALUES(regid, event_type);
  l_message := 'Registration Level. regid : '||regid||', event_type: '||event_type;
  INSERT INTO NOTIFICATIONS (id, message, notification_date)
  VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
  COMMIT;

  IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN
    FOR i IN 1..numtables LOOP
      tbname := ntfnds.table_desc_array(i).table_name;
      operation_type := ntfnds.table_desc_array(i).opflags;
      --INSERT INTO nftablechanges VALUES(regid, tbname, operation_type);
      l_message := 'Table Level. regid: '||regid||', tbname : '||tbname||', operation_type: '||operation_type;
      INSERT INTO NOTIFICATIONS (id, message, notification_date)
      VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
      COMMIT;

      /* Optionally, send the table name and operation_type to a client-side listener using UTL_HTTP */
      /* If interested in ROWIDs, obtain them as follows */
      IF (BITAND(operation_type, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
        numrows := ntfnds.table_desc_array(i).numrows;
      ELSE
        numrows := 0;   /* ROWID INFO NOT AVAILABLE */
      END IF;
      /* The body of the loop is not executed when numrows is ZERO */
      FOR j IN 1..numrows LOOP
        row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
        --INSERT INTO nfrowchanges VALUES(regid, tbname, row_id);
        l_message := 'Row Level: regid: '||regid||', tbname : '||tbname||', row_id: '||row_id;
        INSERT INTO NOTIFICATIONS (id, message, notification_date)
        VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
        COMMIT;
        /* Optionally, send out ROWIDs to a client-side listener using UTL_HTTP */
      END LOOP;
    END LOOP;
  END IF;
  COMMIT;
END callback_proc;

Create the Registration with callback Procedure
DECLARE
  v_reg_info    SYS.CHNF$_REG_INFO;
  v_regid       NUMBER;
  v_upd_time    IPN_INVALIDATE_TRIGGER.ts_update_time%TYPE;
  qosflags      NUMBER;
BEGIN
  -- Create registration info: callback procedure, quality of service (include ROWIDs), operations filter (UPDATE only), timeout=0, purge_on_ntfn=0
  qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
  
  v_reg_info := SYS.CHNF$_REG_INFO('XXX_YYYYY_ZZZZZ.callback_proc', qosflags, 0, 0, 0);
 
  -- Begin registration
  v_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start(v_reg_info);
  -- Register the query (must return at least one row; limit to avoid multiple fetch errors)
  SELECT ts_update_time INTO v_upd_time FROM IPN_INVALIDATE_TRIGGER WHERE module_name = 'SUB_API';
  -- End registration
  DBMS_CHANGE_NOTIFICATION.reg_end;
  
  DBMS_OUTPUT.PUT_LINE('Registration ID for this query: ' || v_regid);
END;
/

PL/SQL procedure successfully completed

--Make the Update

UPDATE XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER SET ts_update_time = TO_CHAR(SYSDATE,'YYYYMMSS hh24:mi:ss') WHERE module_name = 'SUB_API';
1 row updated
COMMIT;

--Check Change Notification happened
SELECT * FROM DBA_CHANGE_NOTIFICATION_REGS
USERNAME         REGID REGFLAGS CALLBACK                                    OPERATIONS_FILTER CHANGELAG TIMEOUT    TABLE_NAME
--------------- ------ -------- ------------------------------------------
----------------- --------- ---------- ---------------------------------------
XXX_YYYYY_ZZZZZ   1520       13 plsql://XXX_YYYYY_ZZZZZ.callback_proc?PR=0                            0         0 4294967295 XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER

SELECT * FROM NOTIFICATIONS

        ID MESSAGE          
---------- --------------------------------------------------------------------------
NOTIFICATION_DATE
-----------------
         2 Registration Level. regid : 1520, event_type: 6                            20250809 16:16:28
         3 Table Level. regid: 1520, tbname : XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER,
20250809 16:16:28
         4 Row Level: regid: 1520, tbname : XXX_YYYYY_ZZZZZ.IPN_INVALIDATE_TRIGGER, 20250809 16:16:28

If still not working, Debug with traces

Open trace files:

ALTER SYSTEM SET events '10867 trace name context forever, level 10';

 

Then: 

Recreate the activity

This should generate oracle tracing

Trace file would be generated under directory 'diagnostic_dest'

SELECT * FROM V$PARAMETER WHERE name = 'diagnostic_dest'

To turn of the trace files:

ALTER SYSTEM SET EVENTS '10867 trace name context off';