How to Check Oracle Standby Database Mount Status and Activation State Using SQL Queries


2 views

For Oracle databases (including legacy versions like 8i), the most reliable way to check if a standby database is mounted is by querying the V$DATABASE view. Here's the diagnostic query:

SELECT 
    NAME, 
    OPEN_MODE, 
    DATABASE_ROLE,
    CONTROLFILE_TYPE
FROM 
    V$DATABASE;

Key indicators in the results:

  • OPEN_MODE will show 'MOUNTED' for standby databases
  • DATABASE_ROLE will display 'PHYSICAL STANDBY'
  • CONTROLFILE_TYPE will indicate 'STANDBY'

To detect if a standby database has been activated (converted to primary), use this enhanced query:

SELECT 
    INSTANCE_NAME, 
    DATABASE_STATUS, 
    INSTANCE_ROLE,
    (SELECT SWITCHOVER_STATUS FROM V$DATABASE) AS SWITCHOVER_STATUS
FROM 
    V$INSTANCE;

Critical status values to monitor:

  • INSTANCE_ROLE changes from 'PHYSICAL STANDBY' to 'PRIMARY_INSTANCE' after activation
  • SWITCHOVER_STATUS shows 'TO PRIMARY' during transition
  • DATABASE_STATUS becomes 'ACTIVE' after activation

For legacy Oracle 8i environments, use this alternative approach:

SELECT 
    a.NAME, 
    a.OPEN_MODE, 
    a.DATABASE_ROLE,
    b.STATUS AS STANDBY_STATUS
FROM 
    V$DATABASE a,
    V$DATAGUARD_STATUS b
WHERE 
    b.FACILITY = 'Log Apply Services';

Here's a complete shell script example that checks both conditions:

#!/bin/ksh
# Oracle Standby Status Checker

DB_STATUS=$(sqlplus -s /nolog <

When dealing with ambiguous states like your example where the database shows 'ACTIVE' but is actually recovering logs, consider these additional checks:

-- Check managed recovery status
SELECT PROCESS, STATUS, THREAD#, SEQUENCE# 
FROM V$MANAGED_STANDBY 
WHERE PROCESS LIKE 'MRP%';

-- Verify archive log application
SELECT 
    ARCHIVED_THREAD#, 
    ARCHIVED_SEQ#, 
    APPLIED_THREAD#, 
    APPLIED_SEQ#
FROM 
    V$ARCHIVE_DEST_STATUS;

html

When working with Oracle standby databases, two critical states require verification:

  1. Whether the database is properly mounted as standby (ALTER DATABASE MOUNT STANDBY DATABASE executed)
  2. Whether the database has been activated (ALTER DATABASE ACTIVATE STANDBY DATABASE executed)

For modern Oracle versions (10g+):

SELECT open_mode, database_role 
FROM v$database 
WHERE database_role = 'PHYSICAL STANDBY' 
AND open_mode LIKE 'MOUNT%';

For legacy Oracle 8i systems:

SELECT status FROM v$instance 
WHERE status = 'MOUNTED';

The most reliable method across versions:

SELECT database_role, switchover_status 
FROM v$database 
WHERE database_role != 'PHYSICAL STANDBY' 
OR switchover_status = 'TO PRIMARY';

When dealing with older Oracle 8i instances showing ACTIVE status but functioning as standby:

SELECT log_archive_dest_status FROM v$archive_dest_status 
WHERE destination = 'STANDBY';

Additional check for recovery operations:

SELECT process, status FROM v$managed_standby 
WHERE process LIKE 'MRP%';

Sample shell script snippet for automation:

#!/bin/bash
result=$(sqlplus -s /nolog << EOF
connect / as sysdba
SET HEADING OFF
SET FEEDBACK OFF
SELECT CASE 
  WHEN database_role = 'PHYSICAL STANDBY' THEN 'STANDBY_MOUNTED'
  WHEN database_role = 'PRIMARY' THEN 'ACTIVATED'
  ELSE 'UNKNOWN_STATE'
END AS db_state
FROM v\$database;
EXIT;
EOF
)

case $result in
  *STANDBY_MOUNTED*) perform_standby_operations ;;
  *ACTIVATED*) alert_activation_detected ;;
  *) log_unknown_state ;;
esac

For environments where SQL access is restricted:

  • Check alert log for mount/activation messages
  • Monitor Oracle services using lsnrctl status
  • Verify standby-specific processes (MRP) in OS process list