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)
