oracle secdiff,Oracle 11g R2 ADG 监控

--===============在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;