Oracle 11g R2 ADG Monitoring

-- = Monitoring the recovery process of DG at standby =

V$managed_standby

V$archived_standby

V$archive_desc_status

V$log_history

-- check the status in the main library

Select dest_name,status,error from v$archive_dest

-- query the last received and applied archive logs on standby

-- the difference between archived_seq# and applied_seq# is the number of logs that need to be applied on primary on standby.

-- this just shows the available and applied logs on standby

-- it is possible that logs are not transferred 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# needs to be compared with the last archive log on primary

-- the last archive log on primary can be obtained from sequence# on v$log_history

-- execute on 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 indicates that logs are transferred from primary through log transfer service.

-- execute on standby

-- Note: registrar='RFS' and applied='YES' 's archive log can be safely removed from standby's archive log location.

Select thread#,sequence#,applied,registrar

From v$archived_log

-- in the administrative recovery operation, there are various processes in standby, and the process status can be seen from v$managed_standby.

Select process,sequence#,status

From v$managed_standby

-- View basic statistics of DG

-- execute on standby

Set linesize 150

Column value format a20

Select * from v$dataguard_stats

Set linesize 2000

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')

)

-- execute on 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) / (1024 * 1024), 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

one,

(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

one,

(TIMESTAMP-lag (TIMESTAMP, 1, TIMESTAMP)

OVER (order by TIMESTAMP)) * 24 * 60 * 60)

3) "MB/sec"

Round ((lead (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 --在备用数据库上检查是否有archive redo log gaps SQL>

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP