Oracle DBMS_HM
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Performs health checks on the following table
ACCESS$ ICOL$ SUPEROBJ$
BOOTSTRAP$ ICOLDEP$ SYN$
CCOL$ IND$ SYSAUTH$
CDEF$ LOB$ TAB$
CLU$ NTAB$ TS$
COL$ OBJ$ TSQ$
COLTYPE$ OBJAUTH$ TYPED_VIEW$
CON$ OBJPRIV$ UET$
DEFROLE$ OPQTYPE$ UGROUP$
DEPENDENCY$ REFCON$ UNDO$
DUAL$ SEG$ USER$
ECOL$ SEQ$ VIEW$
FET$ SUBCOLTYPE$ VIEWCON$
FILE$    
AUTHID DEFINER
Dependencies
GV$HM_CHECK V$DIAG_HM_FINDING V$HM_CHECK
GV$HM_CHECK_PARAM V$DIAG_HM_INFO V$HM_CHECK_PARAM
GV$HM_FINDING V$DIAG_HM_MESSAGE V$HM_FINDING
GV$HM_INFO V$DIAG_HM_RECOMMENDATION V$HM_INFO
GV$HM_RECOMMENDATION V$DIAG_HM_RUN V$HM_RECOMMENDATION
GV$HM_RUN V$DIAG_VHM_RUN V$HM_RUN
V$DIAG_HM_FDG_SET    
Documented Partially: Only two of the 6 objects are documented.
Exceptions
Error Code Reason
ORA-00111 internal_error
First Available 11gR1
Security Model Owned by SYS with EXECUTE granted to the DBA role
Source {ORACLE_HOME}/rdbms/admin/dbmshm.sql
Subprograms
 
CREATE_OFFLINE_DICTIONARY
Creates LogMiner offline dictionary in ADR dbms_hm.create_offline_dictionary;
exec dbms_hm.create_offline_dictionary;

-- note that the directory {ORACLE_BASE}/diag/rdbms/<database_name>/<instance_name>/hm on my lab server the directory created is /app/oracle/diag/rdbms/orabase/orabase/hm.
 
CREATE_SCHEMA
Creates HM Schema in ADR dbms_hm.create_schema;
exec dbms_hm.create_schema;
 
DROP_SCHEMA
Drops HM Schema in ADR dbms_hm.drop_schema(force IN BOOLEAN := FALSE);
exec dbms_hm.drop_schema(TRUE);
 
GET_RUN_REPORT
Returns the report for the specified checker run dbms_hm.get_run_report(
run_name     IN VARCHAR2,
report_type  IN VARCHAR2 := 'TEXT',  -- 'XML', 'TEXT', 'HTML'
report_level IN VARCHAR2 := 'BASIC') -- 'BASIC', 'DETAIL'
RETURN CLOB;
desc gv$hm_run

SELECT name FROM gv$hm_run;

set long 1000000

spool c:\temp\uwhm.xml

SELECT dbms_hm.get_run_report('HM_RUN_1', 'XML', 'DETAIL') FROM dual;

spool off
 
RUN_CHECK
Runs the specified checker with the given arguments. The run's report will be maintained persistently in database. dbms_hm.run_check(check_name IN VARCHAR2,
checkname    IN VARCHAR2,
run_name     IN VARCHAR2 := NULL,
timeout      IN NUMBER   := NULL,
input_params IN VARCHAR2 := NULL);

Checkers
ASM Allocation Check
CF Block Integrity Check
DB Structure Integrity Check
Data Block Integrity Check
Dictionary Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check

Check names and their parameters can be accessed from the gv$hm_check and gv$gm_check_param.
Users can run all checks which are not internal in nature for example:

SELECT name
FROM gv$hm_check
WHERE internal_check = 'N';

which retrieves the list of checks that can be run manually by users.
SELECT name FROM gv$hm_check;

SELECT name FROM gv$hm_check
WHERE internal_check = 'N';

SELECT run_id FROM gv$hm_run;

exec dbms_hm.run_check('Dictionary Integrity Check', 'Run 1', 60);

col name format a12

SELECT run_id, name, check_name, run_mode, status, src_incident, num_incident, error_number
FROM gv$hm_run
ORDER  BY 1;

set linesize 121
col description format a40
col damage_description format a40

SELECT finding_id, status, type, description, damage_description
FROM gv$hm_finding
WHERE run_id = 221;

col name format a10
col repair_script format a60

SELECT name, type, rank, status, repair_script
FROM gv_$hm_recommendation
WHERE run_id = 21
AND fdg_id = 22;
 
RUN_DDE_ACTION
Runs a DDE (user) action for HM checks ... Appears to be for internal usage only dbms_hm.run_dde_action(
incident_id    IN NUMBER,
directory_name IN VARCHAR2,
check_name     IN VARCHAR2,
run_name       IN VARCHAR2,
timeout        IN NUMBER,
params         IN VARCHAR2)
RETURN BOOLEAN;
SELECT num_incident, check_name, name, timeout
FROM gv$hm_run;
 
Related Queries
DBMS_HM Related Queries set linesize 125
col check_name format a30
col parameter_name format a18
col type format a15
col default_value format a15
col description format a40

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;

Related Topics
ADRCI
DBMS_ADR
DBMS_IR
DBMS_SQLDIAG
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved