11G Proactive Heath Checks

• Create a Failure

Before we can start identifying and repairing failures, we need to create one. Probably the easiest way to do this is to corrupt a datafile using the “echo” command. The following script navigates to the directory holding the datafiles, checks the current size of the users01.dbf file, echos nothing to it, then checks the file size once more.

$ cd /u01/app/oracle/oradata/DB11G
$ ls -l users01.dbf
-rw-r—– 1 oracle oinstall 57745408 Jan  3 11:42 users01.dbf
$ echo > users01.dbf
$ ls -l users01.dbf
-rw-r—– 1 oracle oinstall 1 Jan  3 13:26 users01.dbf

You can see the file size has been reduced to a single byte.

Next, we connect to the database and try to create a table in the USERS tablespace.

SQL> CREATE TABLE test_tab (id NUMBER) TABLESPACE USERS;
CREATE TABLE test_tab (id NUMBER) TABLESPACE USERS
*
ERROR at line 1:
ORA-01115: IO error reading block from file 4 (block # 3)
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/DB11G/users01.dbf’
ORA-27072: File I/O error
Additional information: 4
Additional information: 2

• Health Monitor (DBMS_HM)

In the previous example the failure was detected and logged automatically in reaction to the table creation failure. If this were a low use tablespace, we might not have noticed 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 or Enterprise Manager (see below). 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.

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

he 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 Parameters 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”
adrci>
Health Monitor runs are displayed using the following command.
adrci> show hm_run
The relevant run name is then used to produce a report using the following commands.
adrci> set homepath diag/rdbms/db11g/DB11G
adrci> create report hm_run my_test_run
adrci> show report hm_run my_test_run
<?xml version=”1.0″ encoding=”US-ASCII”?>
<HM-REPORT REPORT_ID=”my_test_run”>
<TITLE>HM Report: my_test_run</TITLE>
<RUN_INFO>
<CHECK_NAME>DB Structure Integrity Check</CHECK_NAME>
<RUN_ID>330</RUN_ID>
<RUN_NAME>my_test_run</RUN_NAME>
<RUN_MODE>MANUAL</RUN_MODE>
<RUN_STATUS>COMPLETED</RUN_STATUS>
<RUN_ERROR_NUM>0</RUN_ERROR_NUM>
<SOURCE_INCIDENT_ID>0</SOURCE_INCIDENT_ID>
<NUM_INCIDENTS_CREATED>0</NUM_INCIDENTS_CREATED>
<RUN_START_TIME>2008-01-04 11:30:27.293105 +00:00</RUN_START_TIME>
<RUN_END_TIME>2008-01-04 11:30:27.345898 +00:00</RUN_END_TIME>
</RUN_INFO>
<RUN_PARAMETERS/>
<RUN-FINDINGS>
<FINDING>
<FINDING_NAME>Corrupt Datafile</FINDING_NAME>
<FINDING_ID>334</FINDING_ID>
<FINDING_TYPE>FAILURE</FINDING_TYPE>
<FINDING_STATUS>OPEN</FINDING_STATUS>
<FINDING_PRIORITY>HIGH</FINDING_PRIORITY>
<FINDING_CHILD_COUNT>0</FINDING_CHILD_COUNT>
<FINDING_CREATION_TIME>2008-01-04 11:30:27.341374 +00:00</FINDING_CREATION_TIME>
<FINDING_MESSAGE>Datafile 4: ‘/u01/app/oracle/oradata/DB11G/users01.dbf’ is corrupt</FINDING_MESSAGE>
<FINDING_MESSAGE>Some objects in tablespace USERS might be unavailable</FINDING_MESSAGE>
</FINDING>
</RUN-FINDINGS>
</HM-REPORT>
adrci>