Oracle 11g Health Monitor Checks

概述说明： Health Monitor是11g引入的用于检查数据库的各个组件（包括文件、内存、事务处理完整性、 元数据和进程使用情况）。这些检查器将生成检查结果报表以及解决问题的建议。 可在数据库脱机（nomount）和联机（open或mount）的情况下执行,运行状况检查都可以在DB-online模式下运行。只有redo完整性检查和db结构完整性检查需要在数据库脱机模式下进行。 支持的检查条目： 说明：
Health Monitor是11g引入的用于检查数据库的各个组件（包括文件、内存、事务处理完整性、
元数据和进程使用情况）。这些检查器将生成检查结果报表以及解决问题的建议。
可在数据库脱机（nomount）和联机（open或mount）的情况下执行,运行状况检查都可以在DB-online模式下运行。只有redo完整性检查和db结构完整性检查需要在数据库脱机模式下进行。


支持的检查条目：

sql> select name,offline_capable,description,internal_check from v$hm_check;name                                O DESCRIPTION                                                       I----------------------------------- - ----------------------------------------------------------------- -HM Test Check                       Y Check for health monitor functionality                            YDB Structure Integrity Check        Y Checks integrity of all database files                            NCF Block Integrity Check            Y Checks integrity of a control file block                          NData Block Integrity Check          Y Checks integrity of a data file block                             NRedo Integrity Check                Y Checks integrity of redo log content                              NLogical Block Check                 N Checks logical content of a block                                 YTransaction Integrity Check         N Checks a transaction for corruptions                              NUndo Segment Integrity Check        N Checks integrity of an undo segment                               NNo Mount CF Check                   Y Checks control file in NOMOUNT mode                               YMount CF Check                      Y Checks control file in mount mode                                 YCF Member Check                     Y Checks a multiplexed copy of the control file                     YAll Datafiles Check                 Y Checks all datafiles in the database                              YSingle Datafile Check               Y Checks a data file                                                Ytablespace Check Check              Y Checks a tablespace                                               YLog Group Check                     Y Checks all members of a log group                                 YLog Group Member Check              Y Checks a particular member of a log group                         YArchived Log Check                  Y Checks an archived log                                            YRedo RevalIDation Check             Y Checks redo log content                                           YIO RevalIDation Check               Y Checks file accessibility                                         YBlock IO RevalIDation Check         Y Checks file accessibility                                         YTxn RevalIDation Check              N RevalIDate corrupted transaction                                  YFailure Simulation Check            Y Creates dummy failures                                            YDictionary Integrity Check          N Checks dictionary integrity                                       NASM Mount Check                     Y Diagnose mount failure                                            YASM Allocation Check                Y Diagnose allocation failure                                       NASM disk Visibility Check           Y Diagnose add disk failure                                         YASM file Busy Check                 Y Diagnose file drop failure                                        YASM Toomanyoff Check                Y Diagnose mount Failed because there were too many offline disks   YASM InsufficIEnt disks Check        Y Diagnose mount Failed because there were insufficIEnt disks       YASM InsufficIEnt Mem Check          Y Check to adjust memory on allocation failure                      Y30 rows selected.

健康检查的类型:

1.DB Structure Integrity Check—This check verifIEs the integrity of database files and reports failures if these files are inaccessible,corrupt or inconsistent. If the database is in mount or open mode,this check examines the log files and data files Listed in the control file. If the database is in NOMOUNT mode,only the control file is checked.

--mount\open
模式检查控制文件中列出的日志文件和数据文件。NOMOUNT模式，则仅检查控制文件。

2.Data Block Integrity Check—This check detects disk image block corruptions such as checksum failures,head/tail mismatch,and logical inconsistencIEs within the block. Most corruptions can be repaired using Block Media Recovery. Corrupted block information is also captured in the V$DATABASE_BLOCK_CORRUPTION vIEw. This check does not detect inter-block or inter-segment corruption.

--会捕获损坏的块信息记录在V$DATABASE_BLOCK_CORRUPTION视图中，不会检测块间或段间损坏。

3.Redo Integrity Check—This check scans the contents of the redo log for accessibility and corruption,as well as the archive logs,if available. The Redo Integrity Check reports failures such as archive log or redo corruption.

--在线日志及归档都会进行检查

4.Undo Segment Integrity Check—This check finds logical undo corruptions. After locating an undo corruption,this check uses PMON and SMON to try to recover the corrupted transaction. If this recovery fails,then Health Monitor stores information about the corruption in V$CORRUPT_XID_List. Most undo corruptions can be resolved by forcing a commit.

--PMON和SMON尝试恢复损坏的事务，当损坏的事务不能恢复时记录在V$CORRUPT_XID_List视图中。

5.Transaction Integrity Check—This check is IDentical to the Undo Segment Integrity Check except that it checks only one specific transaction.

--与4相同

6.Dictionary Integrity Check—This check examines the integrity of core dictionary objects,such as tab$ and col$. It performs the following operations:
1)VerifIEs the contents of dictionary entrIEs for each dictionary object.
--验证每个字典对象的字典条目的内容。
2)Performs a cross-row level check,which verifIEs that logical constraints on rows in the dictionary are enforced.
--执行跨行级别检查，强制执行验证字典中行的逻辑约束。
3)Performs an object relationship check,which verifIEs that parent-child relationships between dictionary objects are enforced.
--执行对象关系检查，强制执行验证字典对象之间的父子关系。

涉及到的字典：

The Dictionary Integrity Check operates on the following dictionary objects:
tab$,clu$,fet$,uet$,seg$,undo$,ts$,file$,obj$,ind$,icol$,col$,user$,con$,cdef$,ccol$,bootstrap$,objauth$,ugroup$,tsq$,syn$,vIEw$,typed_vIEw$,superobj$,seq$,lob$,coltype$,subcoltype$,ntab$,refcon$,opqtype$,dependency$,access$,vIEwcon$,icoldep$,dual$,sysauth$,objpriv$,defrole$,and ecol$.

手动运行健康检查

需要用到DBMS_HM.RUN_CHECK包：

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_hm.htm#ARPLS144

DBMS_HM.RUN_CHECK (   check_name     IN  VARCHAR2,-->  要调用的检查的名称,从V$HM_CHECK和V$HM_CHECK_ParaM视图查看，SELECT name FROM V$HM_CHECK WHERE INTERNAL_CHECK = ‘N‘检索可由用户手动运行的检查列表  ；      run_name       IN  VARCHAR2 := NulL,-->运行检查的名称，可不指定。   timeout        IN  NUMBER := NulL,-->运行的最长时间（以秒为单位），可不指定。   input_params   IN  VARCHAR2 := NulL);                   -->由名称，值对组成，由特殊字符‘;‘分割。具体值通过v$hm_check_param ，v$hm_check视图确定。

1)运行DBMS_HM.RUN_CHECK

BEGIN   DBMS_HM.RUN_CHECK(‘Dictionary Integrity Check‘,‘Dict_Check‘); END; /

用法参考：https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_hm.htm#ARPLS66419

2)sqlPLUS中查看报告

sql> SET LONG 100000sql> SET LONGCHUNKSIZE 1000sql> SET PAGESIZE 1000sql> SET linesIZE 512sql> SELECT DBMS_HM.GET_RUN_REPORT(‘Dict_Check‘) FROM DUAL;DBMS_HM.GET_RUN_REPORT(‘DICTCHECK‘)---------------------------------------------------------Basic Run information Run name                     : Dict Check Run ID                       : 55001 Check name                   : Dictionary Integrity Check Mode                         : MANUAL Status                       : COMPLETED Start Time                   : 2019-07-03 09:49:44.365864 +08:00 End Time                     : 2019-07-03 09:50:04.668621 +08:00 Error Encountered            : 0 Source IncIDent ID           : 0 Number of IncIDents Created  : 0input Paramters for the Run table_name=ALL_CORE_tableS CHECK_MASK=ALLvRun Findings And Recommendations Finding Finding name  : Dictionary Inconsistency Finding ID    : 55002 Type          : FAILURE Status        : OPEN Priority      : CRITICAL Message       : sql dictionary health check: analyzetime for object new than               sysdate 12 on object TAB$ Failed Message       : damaged rowID is AAAAACAABAAAACRAAC - description: Object               SYS.UNDO$ is referenced Finding Finding name  : Dictionary Inconsistency Finding ID    : 55005 Type          : FAILURE Status        : OPEN Priority      : CRITICAL Message       : sql dictionary health check: analyzetime for object new than               sysdate 12 on object TAB$ Failed Message       : damaged rowID is AAAAACAABAAAACVAAA - description: Object               SYS.SEQ$ is referenced

3)adrci中查看报告

ADRCI: Release 11.2.0.4.0 - Production on Wed Jul 3 09:52:43 2019copyright (c) 1982,2011,Oracle and/or its affiliates.  All rights reserved.ADR base = "/u01/app/oracle"adrci> show hm_runADR Home = /u01/app/oracle/diag/tnslsnr/prodb/Listener:*************************************************************************...**********************************************************HM RUN RECORD 130**********************************************************   RUN_ID                        56581   RUN_name                      Dict_Check   CHECK_name                    Dictionary Integrity Check   name_ID                       24   MODE                          0   START_TIME                    2019-07-03 09:58:40.350313 +08:00   RESUME_TIME                   <NulL>   END_TIME                      2019-07-03 09:58:56.425829 +08:00   MODIFIED_TIME                 2019-07-03 09:58:56.425829 +08:00   TIMEOUT                       0   FLAGS                         0   STATUS                        5   SRC_INCIDENT_ID               0   NUM_INCIDENTS                 0   ERR_NUMBER                    0   REPORT_file                   <NulL>adrci> create report hm_run Dict_Checkadrci> show report hm_run Dict_Check...adrci> show hm_run**********************************************************HM RUN RECORD 130**********************************************************   RUN_ID                        56581   RUN_name                      Dict_Check   CHECK_name                    Dictionary Integrity Check   name_ID                       24   MODE                          0   START_TIME                    2019-07-03 09:58:40.350313 +08:00   RESUME_TIME                   <NulL>   END_TIME                      2019-07-03 09:58:56.425829 +08:00   MODIFIED_TIME                 2019-07-03 09:59:40.845354 +08:00   TIMEOUT                       0   FLAGS                         0   STATUS                        5   SRC_INCIDENT_ID               0   NUM_INCIDENTS                 0   ERR_NUMBER                    0   REPORT_file                   /u01/app/oracle/diag/rdbms/PROD/PROD/hm/HMREPORT_Dict_Check.hm   已生成REPORT_file，xml格式。

其他：

每个类型的检查参数可能会不一样，如数据块完整性检查：

begin  DBMS_HM.RUN_CHECK(‘Data Block Integrity Check‘,‘f7_block_check‘,input_params => ‘BLC_DF_NUM=7;BLC_BL_NUM=100‘);end;/

具体参数：

SELECT a.*  FROM v$hm_check_param a,v$hm_check b WHERE a.check_ID = b.ID   AND b.name = ‘Data Block Integrity Check‘;

总结

以上是内存溢出为你收集整理的Oracle 11g Health Monitor Checks全部内容，希望文章能够帮你解决Oracle 11g Health Monitor Checks所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。