Oracle 11g Health Checks.

Oracle 11g now has a feature called health monitor which can be used 
to run diagnostic checks on the database.

Health check can be run in two ways.

1) Automatic - The fault diagnosable infrastructure runs the checks
               when a critical error is reported.

2) Manual - Can be done manually either through enterprise manager
            or through DBMS_HM PL/SQL package.

Types of Health Checks.

1) DB Structure Integrity Check - This check verifies the integrity
                                  of database files. It checks log
                                  files and datafiles listed in the
                                  control files.

2) Data Block Integrity Check - Checks the following :

                                a) Checksum failures
                                b) Head/Tail mismatch
                                c) Logical inconsistencies within 
                                   the block.

                                Does not check the following. 
     
                                a) Inter-block or Inter-segment
                                   corruption.

3) Redo Integrity check - Checks for corruption in online redo 
                          logs as well as archived redo logs.

4) Undo Segment Integrity Check - Finds logical undo corruptions.

5) Transaction Integrity Check - Checks integrity of transactions.

6) Dictionary Integrity Check - Checks integrity of core Data
                                Dictionary objects.


To obtain a list of health check names execute the following
query:

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.

To do a manual check suppose on Data dictionary 
execute the following.

SQL>  begin
      dbms_hm.run_check('Dictionary Integrity Check', 'test_check');
      end;

To list all the health checks that were run on the database
manually or automatically execute the following query.


SQL> select run_id, check_name, run_mode from v$hm_run;

    RUN_ID CHECK_NAME                       RUN_MODE
---------- -------------------------------- --------
        41 Data Block Integrity Check       MANUAL
        81 Dictionary Integrity Check       MANUAL
         1 DB Structure Integrity Check     REACTIVE

To obtain the findings of checks execute the query on
the respective RUN_ID number.

SQL> select type, description from v$hm_finding where run_id = 1;


TYPE
-------------
DESCRIPTION
-------------------------------------------------------------------
FAILURE
Control file needs media recovery

FAILURE
System datafile 1: '/u01/app/oracle/oradata/orcl/system01.dbf' needs
media recovery

FAILURE
One or more non-system datafiles need media recovery

TYPE
-------------
DESCRIPTION
----------------------------------------------------------------------

FAILURE
Datafile 2: '/u01/app/oracle/oradata/orcl/sysaux01.dbf' needs media
recovery

FAILURE
Datafile 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf' needs media
recovery

FAILURE
Datafile 4: '/u01/app/oracle/oradata/orcl/users01.dbf' needs media
recovery

TYPE
-------------
DESCRIPTION
-----------------------------------------------------------------------


6 rows selected.