--===============在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,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‘,0) apply_lag,‘transport lag‘,0) transport_lag,‘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,TIMESTAMP) ????????????????????????????? OVER(order by TIMESTAMP)) * 24 * 60 * 60), ???????????????????????????? 0, ???????????????????????????? 1, ???????????????????????????? (TIMESTAMP - lag(TIMESTAMP, ????????????????????? 1) "KB/sec", ??????????????? round((blocks * block_size) / (1024 * 1024) / ????????????????????? decode(((TIMESTAMP - lag(TIMESTAMP, ????????????????????? 3) "MB/sec", ??????????????? round(((lead(TIMESTAMP,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;
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|