Skip to content
Oracle DBA Scripts & Database Utilities

Logical Standby SCN Analysis (what_scn.sql)

What This Script Does

Section titled “What This Script Does”

This script analyzes logical standby database SCN (System Change Number) progression by:

  • Showing all logical standby processes and their current SCN positions
  • Identifying apply processes with minimum SCN values
  • Highlighting processes with specific error status codes (16123, 16124)
  • Displaying overall logical standby progress information

Script

Section titled “Script”
set lines 140 numw 16
vol status format a40 trunc


select logstdby_id,pid,type,status,high_scn from v$logstdby where type != 'APPLIER'
union all
select logstdby_id,pid,type,status,high_scn from v$logstdby l where type = 'APPLIER' and high_scn =
       ( select min(high_scn) from v$logstdby l1 where l.type = l1.type )
union all
select logstdby_id,pid,type,status,high_scn from v$logstdby l where type = 'APPLIER' and high_scn =
       ( select min(high_scn) from v$logstdby l1 where l.type = l1.type and l1.status_code  in ( 16123 ) )
union all
select logstdby_id,pid,type,status,high_scn from v$logstdby l where type = 'APPLIER' and high_scn =
       ( select min(high_scn) from v$logstdby l1 where l.type = l1.type and l1.status_code in ( 16124 ) )
order by 4 desc
/


set lines 80
select * from v$logstdby_progress;

Usage

Section titled “Usage”
SQL> @what_scn.sql

This script must be run on a logical standby database.

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$LOGSTDBY
  • SELECT on V$LOGSTDBY_PROGRESS

Sample Output

Section titled “Sample Output”
LOGSTDBY_ID        PID TYPE                             STATUS                                  HIGH_SCN
----------- ---------- -------------------------------- -------------------------------- ---------------
          1       1234 READER                           READING LOG                        12345678901234
          1       2345 PREPARER                         APPLYING                           12345678901234
          1       3456 BUILDER                          APPLYING                           12345678901234
          1       4567 APPLIER                          APPLYING                           12345678901230
          1       5678 APPLIER                          APPLYING                           12345678901230


LOGSTDBY_ID  TYPE             STATUS                              APPLIED_TIME                    NEWEST_TIME                     APPLIED_SCN      NEWEST_SCN        READ_scn applied_lwm_scn spill_scn
----------- -------- -------------------------------- ------------------------------ ------------------------------ -------------- -------------- ----------- ----------- ----------
          1 LOGICAL  APPLYING                         15-JAN-24 14:30:45            15-JAN-24 14:35:22             123456789      123456790    123456790   123456789      0

Key Output Columns

Section titled “Key Output Columns”

V$LOGSTDBY View

Section titled “V$LOGSTDBY View”
  • LOGSTDBY_ID: Logical standby identifier
  • PID: Process ID of the logical standby process
  • TYPE: Process type (READER, PREPARER, BUILDER, APPLIER)
  • STATUS: Current status of the process
  • HIGH_SCN: Highest SCN processed by this process

V$LOGSTDBY_PROGRESS View

Section titled “V$LOGSTDBY_PROGRESS View”
  • APPLIED_TIME: Timestamp of last applied transaction
  • NEWEST_TIME: Timestamp of newest available transaction
  • APPLIED_SCN: Last SCN applied to the standby
  • NEWEST_SCN: Newest SCN available from primary
  • READ_SCN: SCN being read by log reader
  • APPLIED_LWM_SCN: Low watermark SCN for applied transactions

Understanding Process Types

Section titled “Understanding Process Types”

READER

Section titled “READER”
  • Reads redo logs from primary database
  • Should show “READING LOG” status
  • HIGH_SCN shows latest SCN read

PREPARER

Section titled “PREPARER”
  • Prepares LogMiner dictionary
  • Status should be “APPLYING” when healthy
  • Processes dependency information

BUILDER

Section titled “BUILDER”
  • Builds logical change records (LCRs)
  • Status should be “APPLYING” during normal operation
  • Handles complex object transformations

APPLIER

Section titled “APPLIER”
  • Applies transactions to standby database
  • Multiple applier processes can exist
  • Minimum SCN among appliers shows apply bottleneck

Common Use Cases

Section titled “Common Use Cases”

  1. Lag Analysis

    • Compare APPLIED_SCN vs NEWEST_SCN
    • Identify lagging applier processes
    • Monitor SCN progression rates

  2. Performance Troubleshooting

    • Find bottleneck processes
    • Check for process errors
    • Validate parallel apply efficiency

  3. Health Monitoring

    • Verify all processes are running
    • Check for status anomalies
    • Monitor SCN advancement

Status Code Analysis

Section titled “Status Code Analysis”

Error Codes

Section titled “Error Codes”
  • 16123: Specific logical standby error condition
  • 16124: Another logical standby error condition
  • Check Oracle documentation for specific error details

Common Status Values

Section titled “Common Status Values”
  • APPLYING: Normal operation
  • READING LOG: Log reader active
  • WAITING FOR LOG: Waiting for new redo
  • ERROR: Process encountered error

Troubleshooting

Section titled “Troubleshooting”

High Lag Scenarios

Section titled “High Lag Scenarios”
  1. Slow Reader: Check network/storage performance
  2. Slow Preparer: Review dictionary build time
  3. Slow Builder: Check for complex transformations
  4. Slow Applier: Consider adding parallel apply

Process Issues

Section titled “Process Issues”
  1. Stopped Processes: Check alert logs for errors
  2. Error Status: Review DBA_LOGSTDBY_EVENTS
  3. Uneven SCNs: May indicate process coordination issues

Performance Optimization

Section titled “Performance Optimization”
-- Increase parallel apply processes
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE PARALLEL 4;


-- Check apply parameters
SELECT name, value
FROM v$parameter
WHERE name LIKE '%logstdby%';
Section titled “Related Scripts”