Oracle 11g R2 ADG Monitoring

--=============== monitoring of DG Recovery process in standby ==================

V$managed_standby
V$archived_standby
V$archive_desc_status
V$log_history

--View status in the main library
Select Dest_name,status,error from V$archive_dest;

--Query the last received and applied archive log on the standby
The difference between--archived_seq# and applied_seq# is the number of logs on the primary that need to be applied on the standby
--This just shows the available logs and applied logs on the standby
--it is possible that the log has not been uploaded from primary to standby, in which case more logs need to be synchronized to standby

Select archived_thread#,archived_seq#,applied_thread#,applied_seq#
From V$archive_dest_status;

-The above archived_seq# need to be compared to the last archived log on the primary
The last archived log on the--primary can be obtained from the sequence# on the V$log_history
--performed on the primary

Select Max (sequence#) Latest_archive_log
From V$log_history;

--Detailed procedures for managing the recovery process for each archive log can be obtained from V$archived_log
RFS in--registrar means that logs are transferred from primary through the Log Transport service.
--performed on the standby
--Note: registrar= ' RFS ' and applied= ' YES ' archive logs can be safely removed from the standby archive log location

Select Thread#,sequence#,applied,registrar
From V$archived_log;

--In the administrative recovery operation, there are various processes in standby that can see the process state from V$managed_standby.

Select Process,sequence#,status
From V$managed_standby;

--View DG's basic statistics
--performed on the standby
Set Linesize 150
Column value Format a20
SELECT * from V$dataguard_stats;

Set linesize
Select Sysdate,sum (apply_finish) apply_finish,
sum (apply_lag) Apply_lag,
sum ( Transport_lag) Transport_lag,
sum (startup_time) startup_time,
min (time_computed) time_computed
from
(
Select
Decode (name, ' Apply Finish time ', To_number (substr (value,2,2)) *86400+to_number (substr (value,5,2)) * 3600 +to_number (substr (value,8,2)) *60 +to_number (substr (value,11,2)), 0) Apply_finish,
Decode (name, ' Apply lag ', To_number (substr (value,2,2)) *86400+to_number (substr (value,5,2)) *3600 +to_number (substr (value,8,2)) *60 +to_number (substr (value,11,2)), 0) Apply_lag,
Decode (name, ' Transport lag ', To_number (substr (value,2,2)) *86400+to_number ( SUBSTR (value,5,2)) *3600 +to_number (substr (value,8,2)) *60 +to_number (substr (value,11,2)), 0) Transport_lag,
Decode (name, ' Estimated startup time ', value,0) startup_time,
time_computed
from V$dataguard_stats
where Name in (
' Apply finish time ',
' apply lag ',
' estimated startup time ',
' transport lag ')
)

--performed on the standby
Set linesize 140
Column Timestamp Format A20
Column Facility? Format A24
Column Severity? Format A13
Column Message?? Format A60 trunc
?
Select
??? To_char (timestamp, ' yyyy-mon-dd HH24:MI:SS ') timestamp,
??? Facility,
??? Severity,
??? Message
From
??? V$dataguard_status
Order by
??? Timestamp;

SELECT *
?? From (select TIMESTAMP,
??????????????? Completion_time "Archtime",
??????????????? sequence#,
??????????????? Round ((blocks * block_size)/(1024x768 * 1024x768), 1) "Size Meg",
??????????????? Round (Timestamp-lag (TIMESTAMP, 1, TIMESTAMP)
?????????????????????? Over (order by TIMESTAMP) * 24 * 60 * 60,
????????????????????? 1) "Diff (sec)",
??????????????? Round ((blocks * block_size)/1024/
????????????????????? Decode (((Timestamp-lag (TIMESTAMP, 1, TIMESTAMP)
????????????????????????????? Over (order by TIMESTAMP)) * 24 * 60 * 60),
???????????????????????????? 0,
???????????????????????????? 1,
???????????????????????????? (Timestamp-lag (TIMESTAMP, 1, TIMESTAMP)
????????????????????????????? Over (order by TIMESTAMP)) * 24 * 60 * 60),
????????????????????? 1) "Kb/sec",
??????????????? Round ((blocks * block_size)/(1024 * 1024)/
????????????????????? Decode (((Timestamp-lag (TIMESTAMP, 1, TIMESTAMP)
????????????????????????????? Over (order by TIMESTAMP)) * 24 * 60 * 60),
???????????????????????????? 0,
???????????????????????????? 1,
???????????????????????????? (Timestamp-lag (TIMESTAMP, 1, TIMESTAMP)
????????????????????????????? Over (order by TIMESTAMP)) * 24 * 60 * 60),
????????????????????? 3) "Mb/sec",
??????????????? Round ((TIMESTAMP, 1, TIMESTAMP) over (order by TIMESTAMP)-
????????????????????? Completion_time) * 24 * 60 * 60,
????????????????????? 1) "Lag (sec)"
?????????? From V$archived_log A, v$dataguard_status DGs
????????? where a.name = replace (DGs. MESSAGE, ' Media Recovery Log ', ')
??????????? and DGs. FACILITY = ' Log Apply Services '
????????? ORDER BY TIMESTAMP Desc)
? where RowNum < 10;

--physical Standby/maximum Performance
Select Database_role,log_mode,protection_mode,protection_level from V$database;

Select Db_unique_name from V$dataguard_config

--Check if there is a archive redo log on the standby database gaps
Sql>select thread#, low_sequence#, high_sequence# from V$archive_gap;

Oracle 11g R2 ADG Monitoring