A collection of knowledge and tips to Oracle technology problems focused on database management and architecture.

Thursday, January 27, 2011

ORACLE 11g HEALTH CHECK MONITOR

The Oracle Health Check Monitor (HM) facility is new with Oracle 11.1 database. It can run diagnostic checks that detect:

> file corruptions – 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.

> physical and logical block corruptions – 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.

> undo and redo corruptions -

For redo, HM 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.

For undo, HM 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 – identical to the Undo Segment Integrity Check except that it checks only one

specific transaction

> data dictionary corruptions – 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:

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

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;

Running HM Checker using Enterprise Manager:

1. On the Database Home page, in the Related Links section, click Advisor Central.

You can now view a report of a checker execution. The report contains findings, recommendations, and other information. You can view reports using Enterprise Manager, the ADRCI utility, or the DBMS_HM PL/SQL package. The following table indicates the report formats available with each viewing method.Report Viewing Method Report Formats AvailableEnterprise Manager HTMLDBMS_HM PL/SQL package HTML, XML, and textADRCI utility XML

To view run findings using Enterprise Manager

1. Access the Database Home page. 2. In the Related Links section, click Advisor Central. 3. Click Checkers to view the Checkers subpage. 4. Click the run name for the checker run that you want to view.

The Run Detail page appears, showing the findings for that checker run.

1. Click Runs to display the Runs subpage.

Enterprise Manager displays more information about the checker run.

1. Click View Report to view the report for the checker run.

The report is displayed in a new browser window.

Viewing Reports Using DBMS_HM

You can view Health Monitor checker reports with the DBMS_HM package function GET_RUN_REPORT. This function enables you to request HTML, XML, or text formatting. The default format is text, as shown in the following SQL*Plus example:

SET LONG 100000

SET LONGCHUNKSIZE 1000

SET PAGESIZE 1000

SET LINESIZE 512

SELECT DBMS_HM.GET_RUN_REPORT(‘HM_RUN_1061′) FROM DUAL;

DBMS_HM.GET_RUN_REPORT(‘HM_RUN_1061′)

———————————————————————–

Run Name : HM_RUN_1061

Run Id : 1061

Check Name : Data Block Integrity Check

Mode : REACTIVE

Status : COMPLETED

Start Time : 2011-01-12 22:11:02.032292 -07:00

End Time : 2011-01-12 22:11:20.835135 -07:00

Error Encountered : 0

Source Incident Id : 7418

Number of Incidents Created : 0

Input Paramters for the Run

BLC_DF_NUM=1

BLC_BL_NUM=64349

Run Findings And Recommendations

Finding

Finding Name : Media Block Corruption

Finding ID : 1065

Type : FAILURE

Status : OPEN

Priority : HIGH

Message : Block 64349 in datafile 1:

‘/ade/sfogel_emdb/oracle/dbs/t_db1.f’ is media corrupt

Message : Object BMRTEST1 owned by SYS might be unavailable

Finding

Finding Name : Media Block Corruption

Finding ID : 1071

Type : FAILURE

Status : OPEN

Priority : HIGH

Message : Block 64351 in datafile 1:

‘/ade/sfogel_emdb/oracle/dbs/t_db1.f’ is media corrupt

Message : Object BMRTEST2 owned by SYS might be unavailable

Viewing Reports Using the ADRCI Utility

You can create and view Health Monitor checker reports using the ADRCI utility.

To create and view a checker report using ADRCI

1. Ensure that operating system environment variables (such as ORACLE_HOME) are set properly, and then enter the following command at the operating system command prompt:

2. ADRCI

The utility starts and displays the following prompt:

adrci>>

Optionally, you can change the current ADR home. Use the SHOW HOMES command to list all ADR homes, and the SET HOMEPATH command to change the current ADR home. See Oracle Database Utilities for more information.

3. Enter the following command:

show hm_run

This command lists all the checker runs (stored in V$HM_RUN) registered in the ADR repository.

4. Locate the checker run for which you want to create a report and note the checker run name. The REPORT_FILE field contains a filename if a report already exists for this checker run. Otherwise, generate the report with the following command:

2 comments:

I have not used this used this utility earlier. The fact is that Oracle is so vast and there are so many hidden utilities that is yet to uncover. As I am a learner I have to figure out so many features that I have not even heard about. I will try this utility to check if there is error in my Oracle or not.

About Me

Sam J. DeFilippis is an Independent Consultant
and President/Founder of Decipher,LLC (www.decipherllc.com) who is also a very experienced and certified Sr. Oracle DBA (OCP,OCM) and Oracle Architect. Special skills and experience also include:
Remote DBA,
Data Architecture/Modeling,
Database Architecture, Infrastructure Architecture, Application Solutions Architecture, Database Administration, Data Warehouse and B.I. Architecture and Project Management.
Sam can be reached at sdefilip@gmail.com or 630-606-2576.