Oracle 健康监控器(Health Monitor)

健康监控器(Health Monitor)

ORACLE 11G 中引入了免费的数据库健康监控器，通过DBMS_HM程序包来完成。

检查项目：

1.DB结构完整性检查

2.数据块及完整性检查

3.中做日志完整性检查

4.Undo段完整性检查

5.事物完整性检查

6.数据字典完整性检查

SQL> select name,description from v$hm_check;

NAME                           DESCRIPTION

------------------------------ -------------------------------------------------

HM Test Check                  Check for HM Functionality

DB Structure Integrity Check   Checks integrity of all database files

Data Block Integrity Check     Checks integrity of a datafile block

Redo Integrity Check           Checks integrity of redo log content

Logical Block Check            Checks logical content of a block

Transaction Integrity Check    Checks a transaction for corruptions

Undo Segment Integrity Check   Checks integrity of an undo segment

All Control Files Check        Checks all control files in the database

CF Member Check                Checks a multiplexed copy of the control file

All Datafiles Check            Check for all datafiles in the database

Single Datafile Check          Checks a datafile

Log Group Check                Checks all members of a log group

Log Group Member Check         Checks a particular member of a log group

Archived Log Check             Checks an archived log

Redo Revalidation Check        Checks redo log content

IO Revalidation Check          Checks file accessability

Block IO Revalidation Check    Checks file accessability

Txn Revalidation Check         Revalidate corrupted txn

Failure Simulation Check       Creates dummy failures

Dictionary Integrity Check     Checks dictionary integrity

21 rows selected.

PROCEDURE RUN_CHECK

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 CHECK_NAME                     VARCHAR2                IN

 RUN_NAME                       VARCHAR2                IN     DEFAULT

 TIMEOUT                        NUMBER                  IN     DEFAULT

 INPUT_PARAMS                   VARCHAR2                IN     DEFAULT

.check_name:数据库检查名，这是一个强制参数必须被指定。可以在视图v$hm_check中查询到该参数的值。共21个值。

.run_name:可选参数，指定一个检查名。

.timeout:可选参数，可以设置老化时间。

.params:输入参数，用于控制检查的执行。可以在视图v$hm_check_param视图中查看。

以下是一个检查的例子：

SQL>exec dbms_hm.run_check('Dictionary Integrity Check','HM_TEST');

这个健康检查会存储报告到ADR数据库实例的home目录中。通过adrci的show hm_run命令可以显示这些信息：

adrci> show hm_run

.......

**********************************************************

HM RUN RECORD 2766

   RUN_ID                        55361

   RUN_NAME                      HM_RUN_55361

   CHECK_NAME                    DB Structure Integrity Check

   NAME_ID                       2

   MODE                          2

   START_TIME                    2015-09-28 08:58:30.714167 +08:00

   RESUME_TIME                   <NULL>

   END_TIME                      2015-09-28 08:58:30.742168 +08:00

   MODIFIED_TIME                 2015-09-28 08:58:30.742168 +08:00

   TIMEOUT                       0

   FLAGS                         0

   STATUS                        5

   SRC_INCIDENT_ID               0

   NUM_INCIDENTS                 0

   ERR_NUMBER                    0

   REPORT_FILE                   <NULL>

HM RUN RECORD 2767

   RUN_ID                        55381

   RUN_NAME                      HM_RUN_55381

   START_TIME                    2015-09-28 08:59:30.209094 +08:00

   END_TIME                      2015-09-28 08:59:30.241902 +08:00

   MODIFIED_TIME                 2015-09-28 08:59:30.241902 +08:00

adrci> show report hm_run testrun1

DIA-48614: HM run with name [testrun1] not found

网上其他人的例子：

Data Block Check

Multiple corrupted blocks

datafile 2 contains corrupt blocks

tablespace SYSAUX is unavailable

block 66578 in datafile 2 is corrupt

adrci>

上面的报告显示数据文件2上有坏块，该文件属于sysaux表空间。

所有的健康检查报告存储在v$hm_run视图中，你也可以通过dbms_hm包来获取报告：

SQL> /

NAME                             CHECK_NAME                       RUN_MODE STATUS

-------------------------------- -------------------------------- -------- -----------

HM_RUN_21                        DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_41                        DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_61                        DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_81                        DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_101                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_121                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_141                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_161                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_181                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_201                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_221                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_241                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_261                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_281                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_301                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_321                       DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_341                       DB Structure Integrity Check     REACTIVE COMPLETED

.....

HM_RUN_55701                     DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_55721                     DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_55741                     DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_55761                     DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_55781                     DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_55801                     DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_55821                     DB Structure Integrity Check     REACTIVE COMPLETED

HM_RUN_55841                     DB Structure Integrity Check     REACTIVE COMPLETED

my_run                           Dictionary Integrity Check       MANUAL   COMPLETED

HM_TEST                          Dictionary Integrity Check       MANUAL   COMPLETED

HM_RUN_1                         DB Structure Integrity Check     REACTIVE COMPLETED

my_run和HM_TEST就是我自己手动调用的。

可以查看刚刚检查的

SQL> set long 100000

SQL> set longchunksize 1000

SQL> set pagesize 1000

SQL> set linesize 512

SQL> select DBMS_HM.GET_RUN_REPORT('HM_TEST') FROM DUAL;

DBMS_HM.GET_RUN_REPORT('HM_TEST')

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Basic Run Information

 Run Name                     : HM_TEST

 Run Id                       : 55901

 Check Name                   : Dictionary Integrity Check

 Mode                         : MANUAL

 Status                       : COMPLETED

 Start Time                   : 2015-10-15 18:17:00.890101 +08:00

 End Time                     : 2015-10-15 18:17:01.349346 +08:00

 Error Encountered            : 0

 Source Incident Id           : 0

 Number of Incidents Created  : 0

Input Paramters for the Run

 TABLE_NAME=ALL_CORE_TABLES

 CHECK_MASK=ALL

Run Findings And Recommendations