Health Monitor

Health monitor runs diagnositic checks on various components of the database including files, memory, transaction interity, datameta and process uage.  The health checks have 2 categories:  1)  DB-Online and DB-offline.  These checkers generate reports of their findings and recommendations for fixing the problems. 

·         Reactive way:  Health monitor checks automatically in response to critical errors

·         Manual way:  DBA can run it manually via DBMS_HM PL/SQL or via Enterprise Manager interface

You can views via V$HM_RUN, DBMS_HM, ADRCI, or Enterprise Manager

  1  select name, offline_capable, description, internal_check

  2* from v$hm_check

NAME                             O DESCRIPTION                      I

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

HM Test Check                    Y Check for health monitor functio Y

                                   nality

DB Structure Integrity Check     Y Checks integrity of all database N

                                    files

CF Block Integrity Check         Y Checks integrity of a control fi N

                                   le block

Data Block Integrity Check       Y Checks integrity of a data file  N

                                   block

Redo Integrity Check             Y Checks integrity of redo log con N

                                   tent

Logical Block Check              N Checks logical content of a bloc Y

                                   k

Transaction Integrity Check      N Checks a transaction for corrupt N

                                   ions

Undo Segment Integrity Check     N Checks integrity of an undo segm N

                                   ent

No Mount CF Check                Y Checks control file in NOMOUNT m Y

                                   ode

Mount CF Check                   Y Checks control file in mount mod Y

                                   e

CF Member Check                  Y Checks a multiplexed copy of the Y

                                    control file

All Datafiles Check              Y Checks all datafiles in the data Y

                                   base

Single Datafile Check            Y Checks a data file               Y

Tablespace Check Check           Y Checks a tablespace              Y

Log Group Check                  Y Checks all members of a log grou Y

                                   p

Log Group Member Check           Y Checks a particular member of a  Y

                                   log group

Archived Log Check               Y Checks an archived log           Y

Redo Revalidation Check          Y Checks redo log content          Y

IO Revalidation Check            Y Checks file accessibility        Y

Block IO Revalidation Check      Y Checks file accessibility        Y

Txn Revalidation Check           N Revalidate corrupted transaction Y

Failure Simulation Check         Y Creates dummy failures           Y

Dictionary Integrity Check       N Checks dictionary integrity      N

ASM Mount Check                  Y Diagnose mount failure           Y

ASM Allocation Check             Y Diagnose allocation failure      N

ASM Disk Visibility Check        Y Diagnose add disk failure        Y

ASM File Busy Check              Y Diagnose file drop failure       Y

27 rows selected.

SQL> exec dbms_hm.run_check('Dictionary Integrity Check','DicoCheck',0,'TABLE_NAME=tab$');

PL/SQL procedure successfully completed.

SQL> set long 100000

SQL> select dbms_hm.get_run_report('DicoCheck') from dual;

DBMS_HM.GET_RUN_REPORT('DICOCHECK')

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

Basic Run Information

 Run Name                     : DicoCheck

 Run Id                       : 981

 Check Name                   : Dictionary Integrity Check

 Mode                         : MANUAL

 Status                       : COMPLETED

 Start Time                   : 2011-01-18 22:14:16.580684 -06:00

 End Time                     : 2011-01-18 22:14:16.685162 -06:00

 Error Encountered            : 0

 Source Incident Id           : 0

 Number of Incidents Created  : 0

Input Paramters for the Run

 TABLE_NAME=tab$

 CHECK_MASK=ALL

Run Findings And Recommendations

SQL> select name from v$hm_check where internal_check='N';

NAME

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

DB Structure Integrity Check

CF Block Integrity Check

Data Block Integrity Check

Redo Integrity Check

Transaction Integrity Check

Undo Segment Integrity Check

Dictionary Integrity Check

ASM Allocation Check

8 rows selected.

SQL> exec dbms_hm.run_check('Redo Integrity Check','my_run2');

PL/SQL procedure successfully completed.

SQL> SELECT c.name check_name, p.name parameter_name, p.type,

  2  p.default_value, p.description

  3  FROM v$hm_check_param p, v$hm_check c

  4  WHERE p.check_id = c.id and c.internal_check = 'N'

  5  ORDER BY c.name;

CHECK_NAME                     PARAMETER_NAME  TYPE            DEFAULT_VALUE   DESCRIPTION

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

ASM Allocation Check           ASM_DISK_GRP_NA DBKH_PARAM_TEXT                 ASM group name

                               ME

CF Block Integrity Check       CF_BL_NUM       DBKH_PARAM_UB4                  Control file block number

Data Block Integrity Check     BLC_DF_NUM      DBKH_PARAM_UB4                  File number

Data Block Integrity Check     BLC_BL_NUM      DBKH_PARAM_UB4                  Block number

Dictionary Integrity Check     CHECK_MASK      DBKH_PARAM_TEXT ALL             Check mask

Dictionary Integrity Check     TABLE_NAME      DBKH_PARAM_TEXT ALL_CORE_TABLES Table name

Redo Integrity Check           SCN_TEXT        DBKH_PARAM_TEXT 0               SCN of the latest good redo (if known)

Transaction Integrity Check    TXN_ID          DBKH_PARAM_TEXT                 Transaction ID

Undo Segment Integrity Check   USN_NUMBER      DBKH_PARAM_TEXT                 Undo segment number