Oracle 11g R2 ADG 監控

標籤：round   進程   esc   詳細   sage   startup   tab   maximum   redo   

--===============在standby監控DG的恢複過程==================

v$managed_standby
v$archived_standby
v$archive_desc_status
v$log_history

--在主庫查看狀態
select dest_name,status,error from v$archive_dest;

--在standby上查詢最後收到和應用的歸檔日誌
--archived_seq# 和 applied_seq#的差值就是standby上需要應用primary上的日誌數量
--這個只是顯示standby上的可用日誌和已應用日誌
--有可能日誌沒有從primary傳到standby，這種情況下更多日誌需要同步到standby

select archived_thread#,archived_seq#,applied_thread#,applied_seq#
from v$archive_dest_status;

--上面的archived_seq#需要與primary上的最後歸檔日誌相比較
--primary上的最後歸檔日誌可以從v$log_history上的sequence#得到
--在primary上執行

select max(sequence#) latest_archive_log
from v$log_history;

--每個歸檔日誌的管理恢複進程的詳細過程可以從v$archived_log得到
--registrar中的RFS表示日誌從primary通過日誌傳輸服務傳輸過來
--在standby上執行
--備忘：registrar=‘RFS‘並且applied=‘YES‘的歸檔日誌可以從standby的歸檔日誌位置安全移除

select thread#,sequence#,applied,registrar
from v$archived_log;

--在管理恢複操作中，在standby有各種進程，可以從v$managed_standby看到進程狀態。

select process,sequence#,status
from v$managed_standby;

--查看DG的基本統計資訊
--在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‘ )
)

--在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,
???????????????????????????? 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(((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;

Oracle 11g R2 ADG 監控