Friday 28 May 2010

11g: Health Monitor (DBMS_HM)

DBMS_HS:

When Using DBMS_HS failure can be detected and logged automatically in reaction to the table creation failure. If there is a low use corrupt tablespace datafile, we may not notice the problem for some time.

The Health Monitor also allows us to perform the same integrity checks manually, rather than waiting for the reactive tests to take place. This may help you identify and fix problems before they are ever noticed by users.

Access to Health Monitor is available using the DBMS_HM package

The available checks are displayed using the V$HM_CHECK view

SQL> 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

6 rows selected.

Most of the health checks accept parameters, which are displayed using the V$HM_CHECK_PARAM view.

SET LINESIZE 120
COLUMN check_name FORMAT A30
COLUMN parameter_name FORMAT A15
COLUMN type FORMAT A15
COLUMN default_value FORMAT A15
COLUMN description FORMAT 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 TYPE DEFAULT_VALUE DESCRIPTION
------------------------------ --------------- --------------- --------------- -------------------
Data Block Integrity Check BLC_DF_NUM DBKH_PARAM_UB4 Block Data File number
Data Block Integrity Check BLC_BL_NUM DBKH_PARAM_UB4 Datablock 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 ofthe 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

7 rows selected.

The DBMS_HM.RUN_CHECK procedure is used to run a specific check with the appropriate parameters.

BEGIN
DBMS_HM.run_check (
check_name => 'DB Structure Integrity Check',
run_name => 'my_test_run');
END;
/

PL/SQL procedure successfully completed.

The findings of the health checks can be displayed using a variety of methods:

The DBMS_HM.GET_RUN_REPORT function returns a report of the findings in TEXT, HTML or XML format.

The findings are visible using the RMAN LIST FAILURE command, as seen previously.

The results can be queried directly from the V$HM_RUN, V$HM_FINDING, and V$HM_RECOMMENDATION views.

Enterprise Manager supports the Data Recovery Advisor and Health Monitor.

Automatic Diagnostic Repository Command Interpreter (ADRCI) utility. The example below shows the test output of the DBMS_HM.GET_RUN_REPORT function.

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512

SELECT DBMS_HM.get_run_report('MY_TEST_RUN') FROM dual;

DBMS_HM.GET_RUN_REPORT('MY_TEST_RUN')
------------------------------------------------------------------------------
Basic Run Information

Run Name : my_test_run
Run Id : 330
Check Name : DB Structure Integrity Check
Mode : MANUAL
Status : COMPLETED
Start Time : 2008-01-04 11:30:27.293105 +00:00
End Time : 2008-01-04 11:30:27.345898 +00:00
Error Encountered : 0
Source Incident Id : 0
Number of Incidents Created : 0
Input Paramters for the Run
Run Findings And Recommendations
Finding
Finding Name : Corrupt Datafile
Finding ID : 334
Type : FAILURE
Status : OPEN
Priority : HIGH
Message : Datafile 4: '/u01/app/oracle/oradata/DB11G/users01.dbf' is
corrupt
Message : Some objects in tablespace USERS might be unavailable

The ADR Command Interpreter (ADRCI) utility is initiated by issuing the "adrci" command on the command line.

$ adrci
ADRCI: Release 11.1.0.6.0 - Beta on Fri Jan 4 12:01:20 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ADR base = "/u01/app/oracle"

Health Monitor runs are displayed using the following command.

adrci> show hm_run

No comments:

Post a Comment