Top Oracle Data guard queries

Here are some of the Top Oracle data guard queries/physical standby database queries for monitoring and troubleshooting

Table of Contents

How to check the archive log sequence on Primary Side

SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;

How to check the archive log sequence apply status on Standby Side/how to check data guard sync status in oracle

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

How to check Protection Level and Protection Mode

select protection_mode, protection_level from v$database;

How to check Database Role and log mode

select name,database_role role,log_mode from v$database;

How to Verify that managed recovery is running/how to check mrp process status in oracle

select process,status from v$managed_standby;

When managed recovery is running you will see an MRP process

Some possible statuses for the MRP are listed below:

ERROR -        This means that the process has failed.  See the alert
           log or v$dataguard_status for further information.

WAIT_FOR_LOG - Process is waiting for the archived redo log to be 
           completed.  Switch an archive log on the primary and 
                   requery v$managed_standby to see if the status changes
                   to APPLYING_LOG. 

WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved. 
           Review the alert log to see if FAL_SERVER has been called
                   to resolve the gap.

APPLYING_LOG - Process is applying the archived redo log to the standby
           database. Once the

How to check Dataguard status/How to check Data Guard is configured or not

select message, to_char(timestamp,'HH:MI:SS') timestamp
	from v$dataguard_status
	where severity in ('Error','Fatal')
	order by timestamp

How to check archive log gap status on Standby/query to check archive log gap in standby database

select * from v$archive_gap;

The V$ARCHIVE_GAP fixed view on a physical standby database only returns
the next gap that is currently blocking redo apply from continuing. After
resolving the identified gap and starting redo apply, query the
V$ARCHIVE_GAP fixed view again on the physical standby database to
determine the next gap sequence if there is one. Repeat this process
until there are no more gaps.

How to stop recovery on Physical Standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

How to start recovery on Physical standby/how to start mrp process in standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

How to enable real-time apply on Physical Standby

alter database recover managed standby database using current logfile disconnect;

How to verify if the real-time apply is enabled

select recovery_mode from v$archive_dest_status where dest_id = 2;

How to check the redo apply stats in Dataguard

select * from v$dataguard_stats;

How to check standby dest settings in Primary

select dest_id,archiver,transmit_mode,affirm,net_timeout,delay_mins,async_blocks
from v$archive_dest where target='STANDBY'

How to identify all the log files on standby

SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;

how to check data guard configuration

we can use dgmgrl

 dgmgrl 
connect /
show configuration

How to activate Standby Database

First, cancel the recovery, then

alter database activate standby database;
alter database open;

Related Articles