In this Document

Applies to:

Purpose

This document is intended to introduce various new features on Health Check introduced in Oracle Database 11g.

Scope

All DBAs and users concerned with Database Administration activities.

Details

11g Health monitor:
=============

11g Oracle has introduced a free "health monitor" using the dbms_hm package:

Automatic Health Monitoring proactively checks the health of the database and identifies any issues affecting the database.

Problems are detected even before users run into them and before they cause widespread damage.

The results of Automatic Health Monitoring are reflected in a simple meter (the Health Meter) designed to allow the DBA to quickly gauge the health of the database.

With the help of Automatic Health Monitoring, a DBA can get a comprehensive picture of the current health of the database as well as help on how to remedy the problems affecting the database.

Automatic Health Monitoring proactively detects problems early in their life-cycle and notifies the DBA of the user impact and recommended action to take.

Depending on the component or situation, Automatic Health Monitoring can either quarantine the offending component/resource or provide a fix to a problem.

Health Monitor checks (also known as checkers, health checks, or checks) examine various layers and components of the database. Health checks detect file corruptions, physical and logical block corruptions, undo and redo corruptions, data dictionary corruptions, and more. The health checks generate reports of their findings and, in many cases, recommendations for resolving problems. Health checks can be run in two ways:

Reactive --  The fault diagnosability infrastructure can run health checks automatically in response to a critical error.

Manual -- As a DBA, you can manually run health checks using either the DBMS_HM PL/SQL package or the Enterprise Manager interface. You can run checkers on a regular basis if desired, or Oracle Support may ask you to run a checker while working with you on a service request.

Health Monitor checks store findings, recommendations, and other information in the Automatic Diagnostic Repository (ADR).

Health checks can run in two modes:
-------------------------------------

DB-online mode means the check can be run while the database is open (that is, in OPEN mode or MOUNT mode).

DB-offline mode means the check can be run when the instance is available but the database itself is closed (that is, in NOMOUNT mode).

All the health checks can be run in DB-online mode. Only the Redo Integrity Check and the DB Structure Integrity Check can be used in DB-offline mode.


Health monitor runs the following checks:
---------------------------------------

* DB Structure Integrity Check :

   This check verifies the integrity of database files and reports failures if these files are
   inaccessible, corrupt or inconsistent. If the database is in mount or open mode, this check
   examines the log files and data files listed in the control file. If the database is in NOMOUNT
   mode, only the control file is checked.

* Data Block Integrity Check:

  This check detects disk image block corruptions such as checksum failures, head/tail
   mismatch, and logical inconsistencies within the block. Most corruptions can be repaired
   using Block Media Recovery. Corrupted block information is also captured in the
   V$DATABASE_BLOCK_CORRUPTION view. This check does not detect inter-block or
   inter-segment corruption.

* Redo Integrity Check:

  This check scans the contents of the redo log for accessibility and corruption, as well as the
  archive logs, if available. The Redo Integrity Check reports failures such as archive log or
  redo corruption.

* Undo Segment Integrity Check:

  This check finds logical undo corruptions. After locating an undo corruption, this check uses
  PMON and SMON to try to recover the corrupted transaction. If this recovery fails, then Health
  Monitor stores information about the corruption in V$CORRUPT_XID_LIST. Most undo
  corruptions can be resolved by forcing a commit.

* Transaction Integrity Check:

  This check is identical to the Undo Segment Integrity Check except that it checks only one
  specific transaction.

* Dictionary Integrity Check:

  This check examines the integrity of core dictionary objects, such as tab$ and col$.
  It performs the following operations:

    o  Verifies the contents of dictionary entries for each dictionary object.
    o  Performs a cross-row level check, which verifies that logical constraints on rows in the
        dictionary are enforced.
    o  Performs an object relationship check, which verifies that parent-child relationships
        between dictionary objects are enforced.

The Dictionary Integrity Check operates on the following dictionary objects:

tab$, clu$, fet$, uet$, seg$, undo$, ts$, file$, obj$, ind$, icol$, col$, user$, con$, cdef$, ccol$, bootstrap$, objauth$, ugroup$, tsq$, syn$, view$, typed_view$, superobj$, seq$, lob$, coltype$, subcoltype$, ntab$, refcon$, opqtype$, dependency$, access$, viewcon$, icoldep$, dual$, sysauth$, objpriv$, defrole$, and ecol$.

How to run Health Checks Manually
========================

We can run Health Monitor manually in following 2 ways

#  By using the DBMS_HM PL/SQL package

#  By using the Enterprise Manager interface, found on the Checkers subpage of the Advisor
    Central page

How to run Health Checks Using the DBMS_HM(DBMS_HM.RUN_CHECK) PL/SQL Package
----------------------------------------------------------------------------

    The DBMS_HM  has procedure called RUN_CHECK . To call RUN_CHECK,
    We need to supply the name of the check and a name for the run, as follows:

        BEGIN
            DBMS_HM.RUN_CHECK('Dictionary Integrity Check', 'my_run');
        END;

    To obtain a list of health check names, run the following query:

        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

    Most of health checks accept input parameters. You can view parameter names and       
    descriptions with the V$HM_CHECK_PARAM view. Some parameters are mandatory while   
    others are optional. If optional parameters are omitted, defaults are used. The following         query displays parameter information for all health checks:

        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;

    The Input parameters are passed in the input_params argument as name/value pairs       
     separated by semicolons (;). The following example illustrates how to pass the transaction     ID as a parameter to the Transaction Integrity Check:

        BEGIN
            DBMS_HM.RUN_CHECK (
            check_name => 'Transaction Integrity Check',
            run_name => 'my_run',
            input_params => 'TXN_ID=7.33.2');
        END;

How to Run Health Checks Using Enterprise Manager
------------------------------------------------

    Enterprise Manager provides an interface for running Health Monitor checkers.

    To run a Health Monitor Checker using Enterprise Manager:

        1.  On the Database Home page, in the Related Links section, click Advisor Central.
        2.  Click Checkers to view the Checkers subpage.
        3.  In the Checkers section, click the checker you want to run.
        4.  Enter values for input parameters or, for optional parameters, leave them blank to
             accept the defaults.
        5.  Click Run, confirm your parameters, and click Run again.


Please refer to the Oracle Documentation for more details:

http://docs.oracle.com/cd/B28359_01/server.111/b28310/diag007.htm