Health Check

Oracle 11g provides a new tool to check the Health of the database .
The package is DBMS_HM. The database detects the issues , when it encounters them .
This can also be invoked manually . However , not all checks can be executed .

The following is the structure of the view v$hm_check


SQL> desc v$hm_check

ID               NUMBER
NAME             VARCHAR2(64)
NAME_NLS         VARCHAR2(1024)
CLSID            NUMBER
CLS_NAME         VARCHAR2(15)
FLAGS            NUMBER
INTERNAL_CHECK   VARCHAR2(1)
OFFLINE_CAPABLE  VARCHAR2(1)
DESCRIPTION      VARCHAR2(1024)
Only the checks with Internal Check <> 'Y' can be executed manually .
The results can be viewed thru ADRCI tool
These are the checks in v$hm_check .


SQL> col name format a35 wrapped
SQL> col description format a55 wrapped
SQL> Select name , description , internal_check from v$hm_check;


HM Test Check                       Check for health monitor functionality Y
DB Structure Integrity Check        Checks integrity of all database files N
CF Block Integrity Check            Checks integrity of a control file block N

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

Redo Integrity Check                Checks integrity of redo log content N

Logical Block Check                 Checks logical content of a block Y
Transaction Integrity Check         Checks a transaction for corruptions N
Undo Segment Integrity Check        Checks integrity of an undo segment N
No Mount CF Check                   Checks control file in NOMOUNT mode Y
Mount CF Check                      Checks control file in mount mode Y
CF Member Check                     Checks a multiplexed copy of the control Y
file
All Datafiles Check                 Checks all datafiles in the database Y
Single Datafile Check               Checks a data file Y
Tablespace Check Check              Checks a tablespace Y
Log Group Check                     Checks all members of a log group Y
Log Group Member Check              Checks a particular member of a log grou Y
p


.....
.....27 rows selected.


SQL> exec dbms_hm.run_check('Dictionary Integrity Check' , 'HM run');
PL/SQL procedure successfully completed.

View the results of the check in ADRCI.

D:\test>adrci
ADRCI: Release 11.2.0.1.0 - Production on Wed May 12 13:15:03 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "c:\app\oracle"
adrci>






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


HM RUN RECORD 9


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


RUN_ID                           21

RUN_NAME                         HM run

CHECK_NAME                       Dictionary Integrity Check

NAME_ID                          24

MODE                             0

START_TIME                       2010-05-12 13:09:22.671000 -04:00
RESUME_TIME                     
END_TIME                         2010-05-12 13:09:37.781000 -04:00
MODIFIED_TIME                    2010-05-12 13:09:37.781000 -04:00
TIMEOUT                          0
FLAGS                            0
STATUS                           5
SRC_INCIDENT_ID                  0
NUM_INCIDENTS                    0
ERR_NUMBER                       0
REPORT_FILE                      



As mentioned above , only the non-internal checks can be invoked manually .
As you can see , the following gives the error message , as this is a internal check .

SQL> exec dbms_hm.run_check('CF Member Check' , 'HM run');
BEGIN dbms_hm.run_check('CF Member Check' , 'HM run'); END;
*
ERROR at line 1:
ORA-51001: check [CF Member Check] not found in HM catalog
ORA-06512: at "SYS.DBMS_HM", line 191
ORA-06512: at line 1


Also , some of the checks are only for ASM instance .

SQL> exec dbms_hm.run_check('ASM Disk Visibility Check' , 'HM check asm run');
BEGIN dbms_hm.run_check('ASM Disk Visibility Check' , 'HM check asm run'); END;
*
ERROR at line 1:
ORA-51037: check [ASM Disk Visibility Check] can only be executed in ASM
instance
ORA-06512: at "SYS.DBMS_HM", line 191
ORA-06512: at line 1

Welcome any feedback.