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>

11G – Backup Committed Undo? Why?

You already know what undo data is used for. When a transaction changes a block, the past image of the block is kept it the undo segments. The data is kept there even if the transaction is committed because some long running query that started before the block is changed can ask for the block that was changed and committed. This query should get the past image of the block—the pre-commit image, not the current one. Therefore undo data is kept undo segments even after the commit. The data is flushed out of the undo segment in course of time, to make room for the newly inserted undo data.

When the RMAN backup runs, it backs up all the data from the undo tablespace. But during recovery, the undo data related to committed transactions are no longer needed, since they are already in the redo log streams, or even in the datafiles (provided the dirty blocks have been cleaned out from buffer and written to the disk) and can be recovered from there. So, why bother backing up the committed undo data?

In Oracle Database 11g, RMAN does the smart thing: it bypasses backing up the committed undo data that is not required in recovery. The uncommitted undo data that is important for recovery is backed up as usual. This reduces the size and time of the backup (and the recovery as well).

In many databases, especially OLTP ones where the transaction are committed more frequently and the undo data stays longer in the undo segments, most of the undo data is actually committed. Thus RMAN has to backup only a few blocks from the undo tablespaces.

The best part is that you needn’t do anything to achieve this optimization; Oracle does it by itself.