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';

Code:
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;

Code:
   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;


Code:
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.