Health Checks Using the DBMS_HM PL/SQL Package in Oracle

Health checks is used to identified the file corruptions, physical and logical block corruptions, undo and redo corruptions, data dictionary corruptions, etc.
Oracle has built a package to monitor all this check with PLSQL Package DBMS_HM.

DBMS HM Package monitor following check
DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check
Most health checks accept input

Check DBMS HM performed which health Checkup

col name for a30
SELECT name FROM v$hm_check WHERE internal_check='N';
NAME
------------------------------------
DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check


Check the parameter name present in the Health checkup and used while running the health check with DBMS_HM Package

set line 200 pages 200
col check_name for a30
col parameter_name for a20
SELECT c.name check_name, p.name parameter_name
--, p.type,p.default_value, p.description
FROM v$hm_check_param p, v$hm_check c
WHERE p.check_id = c.id and c.internal_check = 'N'
ORDER BY c.name;

CHECK_NAME                     PARAMETER_NAME
------------------------------ --------------------
ASM Allocation Check           ASM_DISK_GRP_NAME
CF Block Integrity Check       CF_BL_NUM
Data Block Integrity Check     BLC_DF_NUM
Data Block Integrity Check     BLC_BL_NUM
Dictionary Integrity Check     CHECK_MASK
Dictionary Integrity Check     TABLE_NAME
Redo Integrity Check           SCN_TEXT
Transaction Integrity Check    TXN_ID
Undo Segment Integrity Check   USN_NUMBER

Run the DBMS_HM health check for Dictionary integrity check

Execute DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'report1');

-- Used parameter
exec DBMS_HM.RUN_CHECK(check_name => 'Transaction Integrity Check', run_name => 'report2', input_params => 'TXN_ID=7.33.2');

Check the health checkup report

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.GET_RUN_REPORT('REPORT2') FROM DUAL;

Views for DBMS HM for view status, errors and results

SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

SELECT type, description FROM v$hm_finding ;

Clear HM report

exec dbms_hm.drop_schema(FALSE);