My Oracle Support Banner

DELAYED READ_SCN AND LONG TRANSACTION WAITING WITH XIDSEQ 4294967295 (Doc ID 249361.1)

Last updated on NOVEMBER 10, 2023

Applies to:

Oracle Database Backup Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Oracle Enterprise Manager - Version 9.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.

Purpose

The logical standby has many redo logs that have been applied but they are in CURRENT status.  Also, the 
read_scn is not moving and is back behind from the applied_scn.

From v$logstdby there is a record showing error ora-16125: large transaction x.y.z is waiting on another 
transaction.

   APPLIED_SCN APPLIED_         READ_SCN READ_TIM NEWEST_SCN NEWEST_T
---------------- -------- ---------------- -------- ---------- --------
       145324423 17/09/03        137402493 09/09/03  145324423 17/09/03


PID             TYPE            STATUS                                           HIGH_SCN
--------------- --------------- ---------------------------------------- ----------------
5697            COORDINATOR     ORA-16116: no work available
5640            READER          ORA-16127: stalled waiting for additional 
                                transactions to be applied

5642            BUILDER         ORA-16116: no work available                    137403490
5646            PREPARER        ORA-16126: loading table or sequence obj        137403702
                                ect number 6488

5648            ANALYZER        ORA-16116: no work available                    137403486
5652            APPLIER         ORA-16125: large transaction 16 17 42949        137402509
                                67295 is waiting on another transaction

This situation could lead to the following  problems:

* If the number of redo logs on current status is high, the disk space will be filled avoiding new 
redologs comming from the primary database.   

* If the apply services failed, on the next start it will beging from the first redo with current status, 
taking a long time to go through the complete set of redo logs.

* While the redo logs are in CURRENT status, they can not be removed from the system.   Also, as no new 
redos can be received, the GAP between the logical and the primary will increase.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.