Skip to content
Oracle DBA Scripts & Database Utilities

Logical Dataguard Apply State (vldstate.sql)

What This Script Does

Section titled “What This Script Does”

This script provides a quick status check of logical standby databases by:

  • Showing whether real-time apply is enabled
  • Displaying the current state of the SQL Apply process
  • Setting appropriate date formats for timestamp clarity
  • Supporting remote database queries via database links

Script

Section titled “Script”
rem  vldstate.sql
rem
ttitle 'Logical Dataguard Apply State'
set linesize 80
col state  format A20 heading 'STATE'
col realtime_apply format a10 heading  'REALTIME'
set feedback off


alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
alter session set nls_timestamp_format='DD-MON-YYYY HH24:MI:SS';
alter session set nls_timestamp_tz_format='DD-MON-YYYY HH24:MI:SS.FF5';


define inst='@ngp1gl1'


SELECT realtime_apply, state
  FROM v$logstdby_state&inst
/

Usage

Section titled “Usage”
SQL> @vldstate.sql

To query a local standby (remove or modify the database link):

-- Edit the script and change:
define inst='@ngp1gl1'
-- To:
define inst=''

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$LOGSTDBY_STATE
  • Database link privileges if querying remote standby

Sample Output

Section titled “Sample Output”
Logical Dataguard Apply State


REALTIME   STATE
---------- --------------------
ENABLED    APPLYING_LOG

Key Output Columns

Section titled “Key Output Columns”
  • REALTIME: Real-time apply status
    • ENABLED: Changes applied as soon as received
    • DISABLED: Changes applied only from archived logs
  • STATE: Current SQL Apply state
    • APPLYING_LOG: Actively applying changes
    • IDLE: Waiting for new redo data
    • ERROR: Apply process encountered errors
    • LOADING DICTIONARY: Building LogMiner dictionary
    • INITIALIZING: Starting up SQL Apply

Understanding States

Section titled “Understanding States”

Normal Operating States

Section titled “Normal Operating States”
  • APPLYING_LOG: Healthy, actively processing
  • IDLE: Normal when caught up with primary

Problem States

Section titled “Problem States”
  • ERROR: Check DBA_LOGSTDBY_EVENTS for details
  • LOADING DICTIONARY: May indicate rebuild needed
  • Extended IDLE: Could indicate transport issues

Common Use Cases

Section titled “Common Use Cases”

  1. Health Checks

    • Quick standby status verification
    • Pre-maintenance validation
    • Post-change confirmation

  2. Monitoring Integration

    • Automated status checks
    • Alert on ERROR state
    • Track state changes

  3. Troubleshooting

    • First step in lag investigation
    • Verify apply is running
    • Check real-time apply configuration

Configuration Tips

Section titled “Configuration Tips”

Enable Real-time Apply

Section titled “Enable Real-time Apply”
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Disable Real-time Apply

Section titled “Disable Real-time Apply”
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
ALTER DATABASE START LOGICAL STANDBY APPLY;

Check Apply Parameters

Section titled “Check Apply Parameters”
SELECT name, value
FROM v$parameter
WHERE name LIKE '%logstdby%';

Troubleshooting

Section titled “Troubleshooting”

If state shows ERROR:

  1. Check alert log for detailed errors
  2. Query DBA_LOGSTDBY_EVENTS
  3. Verify primary database is accessible
  4. Check for skip rules blocking apply

If REALTIME is DISABLED but wanted:

  1. Stop and restart apply with IMMEDIATE
  2. Verify standby redo logs configured
  3. Check MAX_SGA and MAX_SERVERS settings
Section titled “Related Scripts”