Oracle | DBMS

Oracle DBMS_HM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose

Performs health checks on the following data dictionary tables.

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 (2 of 6 subprograms are documented): Packages and Types Reference

Exceptions

Error Code	Reason
ORA-00111	internal_error

First Available 11.1

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to the DBA role

Source {ORACLE_HOME}/rdbms/admin/dbmshm.sql

Subprograms

CREATE_OFFLINE_DICTIONARY	DROP_SCHEMA	RUN_CHECK
CREATE_SCHEMA	GET_RUN_REPORT	RUN_DDE_ACTION

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 a 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.

And a big thank you to Earl Apex for a correction.

dbms_hm.run_check(

      check_name   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$hm_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 intended solely for internal use 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;

col check_name format a35

      

      SELECT run_id, num_incident, check_name, name, timeout

      FROM gv$hm_run

      WHERE start_time > SYSTIMESTAMP-95

      ORDER BY 1;

      

      SQL> DECLARE

        2   retVal BOOLEAN;

        3  BEGIN

        4    IF dbms_hm.run_dde_action(1, 'CTEMP', 'DB Structure Integrity Check', 'UWRUN', 10, NULL) THEN

        5      dbms_output.put_line('T');

        6    ELSE

        7      dbms_output.put_line('F');

        8    END IF;

        9  END;

       10  /

      T

      

      PL/SQL procedure successfully completed.

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; CHECK_NAME PARAMETER_NAME TYPE DEFAULT_VALUE DESCRIPTION ------------------------ -------------- --------------- --------------- ------------------ ASM Allocation Check ASM_DISK_GRP_NAME DBKH_PARAM_TEXT ASM group name CF Block Integrity Check CF_BL_NUM DBKH_PARAM_UB4 Control file block number Data Block Integrity Check BLC_DF_NUM DBKH_PARAM_UB4 File number Data Block Integrity Check BLC_BL_NUM DBKH_PARAM_UB4 Block number Dictionary Integrity Check CHECK_MASK DBKH_PARAM_TEXT ALL Check mask Dictionary Integrity Check TABLE_NAME DBKH_PARAM_TEXT ALL_CORE_TABLES Table name Redo Integrity Check SCN_TEXT DBKH_PARAM_TEXT 0 SCN of the latest good redo (if known) Transaction Integrity Check TXN_ID DBKH_PARAM_TEXT Transaction ID Undo Segment Integrity Check USN_NUMBER DBKH_PARAM_TEXT Undo segment number