Standby Database Queries

QUERY TO FIND information of PRIMARY OR STANDBY DATABASE

SELECT database_role,
db_unique_name INSTANCE,
open_mode,
protection_mode,
protection_level,
switchover_status
FROM   v$database;

QUERY TO FIND MESSAGES AND ERRORS

SELECT message
FROM   v$dataguard_status; — “RUN THIS QUERY ON STANDBY DATABASE”

QUERY TO FIND STANDBY DATABASE background processes

SELECT process,
status,
thread#,
sequence#,
block#,
blocks
FROM   v$managed_standby; — “RUN THIS QUERY ON STANDBY DATABASE”

QUERY TO FIND Received archived logs

SELECT registrar,
creator,
thread#,
sequence#,
first_change#,
next_change#
FROM   v$archived_log; — “RUN THIS QUERY ON STANDBY DATABASE”

QUERY TO CHECK log APPLIED status

SELECT ‘Last Log applied :’                              Logs,
To_char(next_time, ‘DD – MON – YY :HH24 :MI :SS’) TIME
FROM   v$archived_log
WHERE  sequence# = (SELECT Max(sequence#)
FROM   v$archived_log
WHERE  applied = ‘YES’)
UNION
SELECT ‘Last Log received :’                             Logs,
To_char(next_time, ‘DD – MON – YY :HH24 :MI :SS’) TIME
FROM   v$archived_log
WHERE  sequence# = (SELECT Max(sequence#)
FROM   v$archived_log);

QUERY TO FIND TIME OF LAST APPLIED LOG

SELECT To_char(Max(first_time), ‘hh24 :mi :ss dd / mm / yyyy’)
FROM   v$archived_log
WHERE  applied = ‘YES’;

QUERY TO FIND last sequence# received and the last sequence# Applied to Standby Database

SELECT al.thrd “Thread”,
almax   “Last Seq Received”,
lhmax   “Last Seq Applied”
FROM   (SELECT thread#        thrd,
Max(sequence#) almax
FROM   v$archived_log
WHERE  resetlogs_change# = (SELECT resetlogs_change#
FROM   v$database)
GROUP  BY thread#) al,
(SELECT thread#        thrd,
Max(sequence#) lhmax
FROM   v$log_history
WHERE  resetlogs_change# = (SELECT resetlogs_change#
FROM   v$database)
GROUP  BY thread#) lh
WHERE  al.thrd = lh.thrd;

QUERY TO FIND list of defined archive destinations

SELECT thread#,
dest_id,
destination,
gvad.status,
target,
schedule,
process,
mountid mid
FROM   gv$archive_dest gvad,
gv$instance gvi
WHERE  gvad.inst_id = gvi.inst_id
AND destination IS NOT NULL
ORDER  BY thread#,
dest_id;

QUERY TO FIND Archivelog DIFFERENCE

SELECT a.thread#,
b. last_seq,
a.applied_seq,
a. last_app_timestamp,
b.last_seq – a.applied_seq ARC_DIFF
FROM   (SELECT thread#,
Max(sequence#) applied_seq,
Max(next_time) last_app_timestamp
FROM   gv$archived_log
WHERE  applied = ‘YES’
GROUP  BY thread#) a,
(SELECT thread#,
Max(sequence#) last_seq
FROM   gv$archived_log
GROUP  BY thread#) b
WHERE  a.thread# = b.thread#;

QUERY TO FIND LAST LOG APPLIED ON STANDBY

SELECT thread#,
Max(sequence#)
FROM   v$archived_log
WHERE  applied = ‘YES’
GROUP  BY thread#; — “RUN THIS QUERY ON STANDBY DATABASE”;

QUERY TO RETREIVE Archive Lag Histogram

SELECT *
FROM   v$standby_event_histogram; — “RUN THIS QUERY ON STANDBY DATABASE”

QUERY TO RETREIVE Redo switch report ON PRIMARY DATABASE

SELECT A.*,
Round(A.count# * B.avg# / 1024 / 1024) Daily_Avg_Mb
FROM   (SELECT To_char(first_time, ‘YYYY-MM-DD’) DAY,
Count(1)                          Count#,
Min(recid)                        Min#,
Max(recid)                        Max#
FROM   gv$log_history
GROUP  BY To_char(first_time, ‘YYYY-MM-DD’)
ORDER  BY 1 DESC) A,
(SELECT Avg(bytes) AVG#,
Count(1)   Count#,
Max(bytes) Max_Bytes,
Min(bytes) Min_Bytes
FROM   gv$log) B;