10.2.7.2 监控恢复进度
1.查看进程的活动状态:
Vmagaged_standby视图用于显示物理standby数据库相关进程的当前状态。 select process,client_process,sequence#,status from vmanaged_standby;
2.检查REDO应用进度(显示归档文件路径配置信息及redo的应用情况)
select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from varchivedeststatuswherestatus=′VALID′;3.检查归档文件路径和创建信息(通过分析varchived_log的视图,可以都看到归档文件的一些附加信息,创建时间,创建进程,归档序号,是否被应用)
select name,creator,sequence#,applied,completion_time from varchived_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 vlog_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 vlog_history group by thread#; THREAD# last_applied_log ---------- ---------------- 1 3171 查询VARCHIVED_LOG视图中的app列获得相同的功能
select thread#,sequence#,applied from varchived_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 varchived_log where dest_id =1) local where local.sequence# not in (select sequence# from varchived_log where dest_id =2 and thread# =local.thread#); 10.2.7.3 监控日志应用服务 1.查看当前数据库的基本信息(vdatabase信息)--数据库角色,保护模式,保护级别
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from vdatabase;DATABASEROLEDBUNIQUENAMEOPENMODE−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−PROTECTIONMODEPROTECTIONLEVELSWITCHOVERSTATUS−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−PHYSICALSTANDBYoradb2READONLYWITHAPPLYMAXIMUMPERFORMANCEMAXIMUMPERFORMANCENOTALLOWED再比如,查询failover后快速启动的信息:selectfsfailoverstatus,fsfailovercurrenttarget,fsfailoverthreshold,fsfailoverobserverpresentfromvdatabase;
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 vmanaged_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 varchive_dest_status where dest_id =1;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
4.DATA GUARD事件(VDATAGUARDSTATUS),这个是不便访问到alert.log时,可以临时访问本视图查看一些与DataGuard相关的信息。selectmessagefromvdataguard_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)
1.查看进程的活动状态:
Vmagaged_standby视图用于显示物理standby数据库相关进程的当前状态。 select process,client_process,sequence#,status from vmanaged_standby;
2.检查REDO应用进度(显示归档文件路径配置信息及redo的应用情况)
select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from varchivedeststatuswherestatus=′VALID′;3.检查归档文件路径和创建信息(通过分析varchived_log的视图,可以都看到归档文件的一些附加信息,创建时间,创建进程,归档序号,是否被应用)
select name,creator,sequence#,applied,completion_time from varchived_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 vlog_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 vlog_history group by thread#; THREAD# last_applied_log ---------- ---------------- 1 3171 查询VARCHIVED_LOG视图中的app列获得相同的功能
select thread#,sequence#,applied from varchived_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 varchived_log where dest_id =1) local where local.sequence# not in (select sequence# from varchived_log where dest_id =2 and thread# =local.thread#); 10.2.7.3 监控日志应用服务 1.查看当前数据库的基本信息(vdatabase信息)--数据库角色,保护模式,保护级别
select database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status from vdatabase;DATABASEROLEDBUNIQUENAMEOPENMODE−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−PROTECTIONMODEPROTECTIONLEVELSWITCHOVERSTATUS−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−PHYSICALSTANDBYoradb2READONLYWITHAPPLYMAXIMUMPERFORMANCEMAXIMUMPERFORMANCENOTALLOWED再比如,查询failover后快速启动的信息:selectfsfailoverstatus,fsfailovercurrenttarget,fsfailoverthreshold,fsfailoverobserverpresentfromvdatabase;
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 vmanaged_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 varchive_dest_status where dest_id =1;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
4.DATA GUARD事件(VDATAGUARDSTATUS),这个是不便访问到alert.log时,可以临时访问本视图查看一些与DataGuard相关的信息。selectmessagefromvdataguard_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)