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