DB Health Check:: 11gR2 New Feature

A relatively unknown new feature with 11g is the Health Monitor Checks.

This feature has 27 checks out of which 6 of them are not internal. The internal checks are managed by the RDBMS software
The non internal checks can be used by DBAs to check and fix any problems.
the non-internal checks are

DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check

Some of these checks needed Oracle Support guidence before like
Data Dictionary Consistency checks ID 456468.1. It is very useful to make it part of the software package now.


DBA should make this part of their checks every few months. Some of these checks are resource intensive and it is recommended to run during low load periods.

To run a check use

BEGIN
DBMS_HM.RUN_CHECK('Dictionary Integrity Check', to_char(sysdate,'DDMONYY')||'_dict_check');
END;
/


BEGIN
DBMS_HM.RUN_CHECK('DB Structure Integrity Check', to_char(sysdate,'DDMONYY')||'_dbstruct');
END;
/

These can also be added to the scheduler.

ADRCI can be used then to view the reports.

adrci> show hm_run

ADR Home = /apps/oracle/diag/rdbms/XXXX/XXXX4:
*************************************************************************

**********************************************************
HM RUN RECORD 1
**********************************************************
RUN_ID 1
RUN_NAME 01SEP11_check
CHECK_NAME Dictionary Integrity Check
NAME_ID 24
MODE 0
START_TIME 2011-09-01 09:25:46.844074 +01:00
RESUME_TIME
END_TIME 2011-09-01 09:35:15.977181 +01:00
MODIFIED_TIME 2011-09-01 09:37:45.385591 +01:00
TIMEOUT 0
FLAGS 0
STATUS 5
SRC_INCIDENT_ID 0
NUM_INCIDENTS 0
ERR_NUMBER 0
REPORT_FILE /apps/oracle/diag/rdbms/XXXX/XXXX4/hm/HMREPORT_01SEP11_check.hm
1 rows fetched

adrci> show report hm_run 01SEP11_check


This then should return the errors if any as

Dictionary Inconsistency
6386
FAILURE
OPEN
CRITICAL
0
2011-09-01 09:35:11.868297 +01:00
SQL dictionary health check: syn$.owner fk 95 on object SYN$ failed
Damaged rowid is AAAAA9AABAAAC5BAER - description: Synonymn ORDDCM_DOC_REGS is referenced