Using Views to Monitor Primary, Physical, and Snapshot Standby Databases

1.V$DATABASE
The following query displays the data protection mode, data protection level, database role, and switchover status for a primary, physical standby or snapshot standby database:

SELECT PROTECTION_MODE, PROTECTION_LEVEL, –
DATABASE_ROLE ROLE, SWITCHOVER_STATUS –
FROM V$DATABASE;
The following query displays fast-start failover status:

SELECT FS_FAILOVER_STATUS "FSFO STATUS", -
FS_FAILOVER_CURRENT_TARGET TARGET, -
FS_FAILOVER_THRESHOLD THRESHOLD, -
FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" –
FROM V$DATABASE;

2. V$MANAGED_STANDBY
The following query displays Redo Apply and redo transport status on a physical standby database:

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,-
BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
------- ------------ ---------- ---------- ---------- ----------
RFS ATTACHED 1 947 72 72
MRP0 APPLYING_LOG 1 946 10 72
The sample output shows that a RFS process completed archiving a redo log file with a sequence number of 947 and that Redo Apply is actively applying an archived redo log file with a sequence number of 946. Redo Apply is currently recovering block number 10 of the 72-block archived redo log file.

3.V$ARCHIVED_LOG
The following query displays information about archived redo log files that have been received by a physical or snapshot standby database from a primary database:

SQLSELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, -
NEXT_CHANGE# FROM V$ARCHIVED_LOG;

THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ------------- ------------
1 945 74651 74739
1 946 74739 74772
1 947 74772 7474
The sample output shows that three archived redo log files have been received from the primary database.

4. V$LOG_HISTORY
The following query displays archived log history information:

SQLSELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, -
NEXT_CHANGE# FROM V$LOG_HISTORY;

5.V$DATAGUARD_STATUS
The following query displays messages generated by Data Guard events that caused a message to be written to the alert log or to a server process trace file:

SQLSELECT MESSAGE FROM V$DATAGUARD_STATUS;