Oracle之Data Guard监控

10.2.7.2 监控恢复进度


1.查看进程的活动状态:

V$magaged_standby视图用于显示物理standby数据库相关进程的当前状态。

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


2.检查REDO应用进度(显示归档文件路径配置信息及redo的应用情况)

select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status='VALID';

3.检查归档文件路径和创建信息(通过分析v$archived_log的视图，可以都看到归档文件的一些附加信息，创建时间，创建进程，归档序号，是否被应用)

select name,creator,sequence#,applied,completion_time from v$archived_log;

NAME                                                         CREATOR  SEQUENCE#
------------------------------------------------------------ ------- ----------
APPLIED   COMPLETION_T
--------- ------------
/data/ora11g/oradata/oracle9i/archive/1_3168_769214827.dbf   ARCH          3168
YES       08-OCT-12

/data/ora11g/oradata/oracle9i/archive/1_3169_769214827.dbf   ARCH          3169
YES       08-OCT-12

/data/ora11g/oradata/oracle9i/archive/1_3170_769214827.dbf   ARCH          3170
YES       08-OCT-12


4.查询归档历史(可以查看一些已被应用归档的信息)

select first_time,first_change#,next_change#,sequence# from v$log_history;


FIRST_TIME   FIRST_CHANGE# NEXT_CHANGE#  SEQUENCE#
------------ ------------- ------------ ----------
06-OCT-12       2759378618   2759656504       3160
07-OCT-12       2759656504   2759796830       3161
07-OCT-12       2759796830   2760138060       3162
07-OCT-12       2760138060   2760360774       3163
07-OCT-12       2760360774   2760698339       3164
07-OCT-12       2760698339   2761034500       3165
07-OCT-12       2761034500   2761365383       3166
07-OCT-12       2761365383   2761689126       3167
08-OCT-12       2761689126   2761940714       3168
08-OCT-12       2761940714   2762313589       3169
08-OCT-12       2762313589   2762532019       3170


  通过修改上面的SQL语句，查询到最后的归档文件(可以看出最后被应用的是，3171)

select thread#,max(sequence#) as "last_applied_log" from v$log_history group by thread#;

   THREAD# last_applied_log
---------- ----------------
         1             3171

  查询V$ARCHIVED_LOG视图中的app列获得相同的功能

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

   THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
         1       3169 YES
         1       3170 YES
         1       3171 IN-MEMORY


5.查看物理Standby数据库未接收的日志文件(从primary端获取)

select local.thread#,local.sequence# from (select thread#,sequence# from v$archived_log where dest_id =1) local where local.sequence# not in (select sequence# from v$archived_log where dest_id =2 and thread# =local.thread#);


10.2.7.3  监控日志应用服务

1.查看当前数据库的基本信息(v$database信息)--数据库角色，保护模式，保护级别


select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from v$database;

DATABASE_ROLE    DB_UNIQUE_NAME                 OPEN_MODE
---------------- ------------------------------ --------------------
PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATUS
-------------------- -------------------- --------------------
PHYSICAL STANDBY oradb2                         READ ONLY WITH APPLY
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  NOT ALLOWED


再比如，查询failover后快速启动的信息：

select fs_failover_status,fs_failover_current_target,fs_failover_threshold,fs_failover_observer_present from v$database;

FS_FAILOVER_STATUS     FS_FAILOVER_CURRENT_TARGET     FS_FAILOVER_THRESHOLD
---------------------- ------------------------------ ---------------------
FS_FAIL
-------
DISABLED                                                                  0


2.查看当前REDO应用和REDO传输服务的活动状态

select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING               1       3171     632832       1607
ARCH      CLOSING               1       3170     630784       1844
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1       3169     630784       1858
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1       3172     126151    1048576(开始应用3172的日志)
RFS       IDLE                  1       3172     126150          2


3.检查应用模式(是否开启了实时应用),其中MANAGED REAL TIME APPLY代表打开了实时应用

select recovery_mode from v$archive_dest_status where dest_id =1;


RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY


4.DATA GUARD事件(V$DATAGUARD_STATUS)，这个是不便访问到alert.log时，可以临时访问本视图查看一些与Data Guard相关的信息。

select message from v$dataguard_status;


MESSAGE
--------------------------------------------------------------------------------
ARC1: Completed archiving thread 1 sequence 3086 (0-0)
ARC0: Beginning to archive thread 1 sequence 3087 (2737922547-2738253677)
Media Recovery Waiting for thread 1 sequence 3088 (in transit)
ARC0: Completed archiving thread 1 sequence 3087 (0-0)
ARC3: Beginning to archive thread 1 sequence 3088 (2738253677-2738579991)
Media Recovery Waiting for thread 1 sequence 3089
ARC3: Completed archiving thread 1 sequence 3088 (0-0)
ARC1: Beginning to archive thread 1 sequence 3089 (2738579991-2738831349)
Media Recovery Waiting for thread 1 sequence 3090 (in transit)
ARC1: Completed archiving thread 1 sequence 3089 (0-0)
ARC0: Beginning to archive thread 1 sequence 3090 (2738831349-2739137854)