DBMS_REPAIR example


PURPOSE
This document provides an example of DBMS_REPAIR as introduced in Oracle 8i.
Oracle provides different methods for detecting and correcting data block
corruption - DBMS_REPAIR is one option.
WARNING: Any corruption that involves the loss of data requires analysis to
understand how that data fits into the overall database system. Depending on
the nature of the repair, you may lose data and logical inconsistencies can
be introduced; therefore you need to carefully weigh the gains and losses
associated with using DBMS_REPAIR.
SCOPE & APPLICATION
This article is intended to assist an experienced DBA working with an Oracle
Worldwide Support analyst only.  This article does not contain general
information regarding the DBMS_REPAIR package, rather it is designed to provide
sample code that can be customized by the user (with the assistance of
an Oracle support analyst) to address database corruption.  The
"Detecting and Repairing Data Block Corruption" Chapter of the Oracle8i
Administrator's  Guide should be read and risk assessment analyzed prior to
proceeding.
RELATED DOCUMENTS
Oracle 8i Administrator's Guide,  DBMS_REPAIR Chapter
Introduction
=============
Note: The DBMS_REPAIR package is used to work with corruption in the
transaction layer and the data layer only (software corrupt blocks).
Blocks with physical corruption (ex. fractured block) are marked as
the block is read into the buffer cache and DBMS_REPAIR ignores all
blocks marked corrupt.
The only block repair in the initial release of DBMS_REPAIR is to
*** mark the block software corrupt ***.
DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM must both be set to FALSE.
A backup of the file(s) with corruption should be made before using package.
Database Summary
===============
A corrupt block exists in table T1.
SQL> desc t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
COL1                                      NOT NULL NUMBER(38)
COL2                                               CHAR(512)
SQL> analyze table t1 validate structure;
analyze table t1 validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
---> Note: In the trace file produced from the ANALYZE, it can be determined
---        that the corrupt block contains 3 rows of data (nrows = 3).
---        The leading lines of the trace file follows:
Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_2835.trc
Oracle8 Enterprise Edition Release 8.1.5.0.0 - Beta
With the Partitioning option
*** 1998.12.16.15.53.02.000
*** SESSION ID:(7.6) 1998.12.16.15.53.02.000
kdbchk: row locked by non-existent transaction
table=0   slot=0
lockid=32   ktbbhitc=1
Block header dump:  0x01800003
Object id on Block? Y
seg/obj: 0xb6d  csc: 0x00.1cf5f  itc: 1  flg: -  typ: 1 - DATA
fsl: 0  fnx: 0x0 ver: 0x01
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   xid:  0x0002.011.00000121    uba: 0x008018fb.0345.0d  --U-    3  fsc
0x0000.0001cf60
data_block_dump
===============
tsiz: 0x7b8
hsiz: 0x18
pbl: 0x28088044
bdba: 0x01800003
flag=-----------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x19d
avsp=0x185
tosp=0x185
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x5ff
0x14:pri[1]     offs=0x3a6
0x16:pri[2]     offs=0x19d
block_row_dump:
[... remainder of file not included]
end_of_block_dump
DBMS_REPAIR.ADMIN_TABLES (repair and orphan key
================================================
ADMIN_TABLES provides administrative functions for repair and orphan key tables.
SQL> @adminCreate
SQL> connect sys/change_on_install
Connected.
SQL>
SQL> -- Repair Table
SQL>
SQL> declare
2  begin
3  -- Create repair table
4  dbms_repair.admin_tables (
5  --    table_name => 'REPAIR_TABLE',
6      table_type => dbms_repair.repair_table,
7      action => dbms_repair.create_action,
8      tablespace => 'USERS');          -- default TS of SYS if not specified
9  end;
10  /
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type
2  from dba_objects
3  where object_name like '%REPAIR_TABLE';
OWNER                 OBJECT_NAME                      OBJECT_TYPE
------------------------------------------------------------------
SYS                   DBA_REPAIR_TABLE                 VIEW
SYS                   REPAIR_TABLE                     TABLE
SQL>
SQL> -- Orphan Key Table
SQL>
SQL> declare
2  begin
3  -- Create orphan key table
4  dbms_repair.admin_tables (
5      table_type => dbms_repair.orphan_table,
6      action => dbms_repair.create_action,
7      tablespace => 'USERS');          -- default TS of SYS if not specified
8  end;
9  /
PL/SQL procedure successfully completed.
SQL> select owner, object_name, object_type
2  from dba_objects
3  where object_name like '%ORPHAN_KEY_TABLE';
OWNER                 OBJECT_NAME                      OBJECT_TYPE
------------------------------------------------------------------
SYS                   DBA_ORPHAN_KEY_TABLE             VIEW
SYS                   ORPHAN_KEY_TABLE                 TABLE
DBMS_REPAIR.CHECK_OBJECT
=========================
CHECK_OBJECT procedure checks the specified object and populates the repair
table with information about corruption and repair directive(s).  Validation
consists of block checking all blocks in the object.  All blocks previously
marked corrupt will be skipped.
Note: In the initial release of DBMS_REPAIR the only repair is to mark the
block as software corrupt.
SQL> @checkObject
SQL> set serveroutput on
SQL>
SQL> declare
2     rpr_count int;
3  begin
4     rpr_count := 0;
5  dbms_repair.check_object (
6     schema_name => 'SYSTEM',
7     object_name => 'T1',
8     repair_table_name => 'REPAIR_TABLE',
9     corrupt_count => rpr_count);
10     dbms_output.put_line('repair count: ' || to_char(rpr_count));
11  end;
12  /
repair count: 1
PL/SQL procedure successfully completed.
SQL> desc repair_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OBJECT_ID                                 NOT NULL NUMBER
TABLESPACE_ID                             NOT NULL NUMBER
RELATIVE_FILE_ID                          NOT NULL NUMBER
BLOCK_ID                                  NOT NULL NUMBER
CORRUPT_TYPE                              NOT NULL NUMBER
SCHEMA_NAME                               NOT NULL VARCHAR2(30)
OBJECT_NAME                               NOT NULL VARCHAR2(30)
BASEOBJECT_NAME                                    VARCHAR2(30)
PARTITION_NAME                                     VARCHAR2(30)
CORRUPT_DESCRIPTION                                VARCHAR2(2000)
REPAIR_DESCRIPTION                                 VARCHAR2(200)
MARKED_CORRUPT                            NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP                           NOT NULL DATE
FIX_TIMESTAMP                                      DATE
REFORMAT_TIMESTAMP                                 DATE
SQL> select object_name, block_id, corrupt_type, marked_corrupt,
2  corrupt_description, repair_description
3  from repair_table;
OBJECT_NAME                      BLOCK_ID CORRUPT_TYPE MARKED_COR
------------------------------ ---------- ------------ ----------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
REPAIR_DESCRIPTION
--------------------------------------------------------------------------------
T1                                      3            1 FALSE
kdbchk: row locked by non-existent transaction
table=0   slot=0
lockid=32   ktbbhitc=1
mark block software corrupt
Data Extraction
===============
The repair table indicates that block 3 of file 6 is corrupt - but remember
that this block has not yet been marked as corrupt, therefore now is the
time to extract any meaningful data.  After the block is marked corrupt,
the entire block must be skipped.
1. Determine the number of rows in the block from ALTER SYSTEM DUMP (nrows = 3).
2. Query the corrupt object and extract as much information as possible.
SQL> -- The following query can be used to salvage data from a corrupt block.
SQL> -- Creating a temporary table facilitates data insertion.
SQL> create table temp_t1 as
2  select * from system.t1
3  where dbms_rowid.rowid_block_number(rowid) = 3
4  and dbms_rowid.rowid_to_absolute_fno (rowid, 'SYSTEM','T1') = 6;
Table created.
SQL> select col1 from temp_t1;
COL1
----------
2
3
DBMS_REPAIR.FIX_CORRUPT_BLOCKS  (ORA-1578)
============================================
FIX_CORRUPT_BLOCKS procedure fixes the corrupt blocks in the specified objects
based on information in the repair table.  After the block has been marked as
corrupt,  an ORA-1578 results when a full table scan is performed.
SQL> declare
2     fix_count int;
3  begin
4     fix_count := 0;
5  dbms_repair.fix_corrupt_blocks (
6     schema_name => 'SYSTEM',
7     object_name => 'T1',
8     object_type => dbms_repair.table_object,
9     repair_table_name => 'REPAIR_TABLE',
10     fix_count => fix_count);
11     dbms_output.put_line('fix count: ' || to_char(fix_count));
12  end;
13  /
fix count: 1
PL/SQL procedure successfully completed.
SQL> select object_name, block_id, marked_corrupt
2  from repair_table;
OBJECT_NAME                      BLOCK_ID MARKED_COR
------------------------------ ---------- ----------
T1                                      3 TRUE
SQL> select * from system.t1;
select * from system.t1
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 3)
ORA-01110: data file 6: '/tmp/ts_corrupt.dbf'
DBMS_REPAIR.DUMP_ORPHAN_KEYS
==============================
DUMP_ORPHAN_KEYS reports on index entries that point to rows in corrupt data
blocks.
SQL> select index_name from dba_indexes
2  where table_name in (select distinct object_name from repair_table);
INDEX_NAME
------------------------------
T1_PK
SQL> @dumpOrphanKeys
SQL> set serveroutput on
SQL>
SQL> declare
2     key_count int;
3  begin
4     key_count := 0;
5  dbms_repair.dump_orphan_keys (
6     schema_name => 'SYSTEM',
7     object_name => 'T1_PK',
8     object_type => dbms_repair.index_object,
9     repair_table_name => 'REPAIR_TABLE',
10     orphan_table_name => 'ORPHAN_KEY_TABLE',
11     key_count => key_count);
12     dbms_output.put_line('orphan key count: ' || to_char(key_count));
13  end;
14  /
orphan key count: 3
PL/SQL procedure successfully completed.
SQL> desc orphan_key_table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME                               NOT NULL VARCHAR2(30)
INDEX_NAME                                NOT NULL VARCHAR2(30)
IPART_NAME                                         VARCHAR2(30)
INDEX_ID                                  NOT NULL NUMBER
TABLE_NAME                                NOT NULL VARCHAR2(30)
PART_NAME                                          VARCHAR2(30)
TABLE_ID                                  NOT NULL NUMBER
KEYROWID                                  NOT NULL ROWID
KEY                                       NOT NULL ROWID
DUMP_TIMESTAMP                            NOT NULL DATE
SQL> select index_name, count(*) from orphan_key_table
2  group by index_name;
INDEX_NAME                       COUNT(*)
------------------------------ ----------
T1_PK                                   3
Note: Index entry in the orphan key table implies that the index should be
rebuilt to guarantee the a table probe and an index probe return the same
result set.
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
===============================
SKIP_CORRUPT_BLOCKS enables/disables the skipping of corrupt blocks during
index and table scans of a specified object.
Note: If an index and table are out of sync, then a SET TRANSACTION READ ONLY
transaction may be inconsistent in situations where one query probes only
the index and then a subsequent query probes both the index and the table.
If the table block is marked corrupt, then the two queries will return
different results.
Suggestion: If SKIP_CORRUPT_BLOCKS is enabled, then rebuild any indexes
identified in the orphan key table (or all index associated with object
if DUMP_ORPHAN_KEYS was omitted).
SQL> @skipCorruptBlocks
SQL> declare
2  begin
3  dbms_repair.skip_corrupt_blocks (
4     schema_name => 'SYSTEM',
5     object_name => 'T1',
6     object_type => dbms_repair.table_object,
7     flags => dbms_repair.skip_flag);
8  end;
9  /
PL/SQL procedure successfully completed.
SQL> select table_name, skip_corrupt from dba_tables
2  where table_name = 'T1';
TABLE_NAME                     SKIP_COR
------------------------------ --------
T1                             ENABLED
SQL> -- rows in corrupt block skipped, no errors on full table scan
SQL> select * from system.t1;
COL1              COL2
--------------------------------------------------------------------------------
4                 dddd
5                 eeee
--> Notice the pk index has not yet been corrected.
SQL> insert into system.t1 values (1,'aaaa');
insert into system.t1 values (1,'aaaa')
*
SQL> select * from system.t1 where col1 = 1;
no rows selected
DBMS_REPAIR.REBUILD_FREELISTS
===============================
REBUILD_FREELISTS rebuilds freelists for the specified object.
SQL> declare
2  begin
3  dbms_repair.rebuild_freelists (
4     schema_name => 'SYSTEM',
5     object_name => 'T1',
6     object_type => dbms_repair.table_object);
7  end;
8  /
PL/SQL procedure successfully completed.
Rebuild Index
=============
Note:  Every index identified in the orphan key table should be rebuilt to
ensure consistent results.
SQL> alter index system.t1_pk rebuild online;
Index altered.
SQL> insert into system.t1 values (1, 'aaaa');
1 row created.
SQL> select * from system.t1;
COL1              COL2
--------------------------------------------------------------------------------
4                 dddd
5                 eeee
1                 aaaa
Note - The above insert statement was used to provide a simple example.
This is the perfect world - we know the data that was lost.  The temporary
table (temp_t1) should also be used to include all rows extracted from
the corrupt block.
Conclusion
==========
At this point the table T1 is available but data loss was incurred.  In general,
data loss must be seriously considered before using the DBMS_REPAIR package for
mining the index segment and/or table block dumps is very complicated and
logical inconsistencies may be introduced.  In the initial release, the only
repair affected by DBMS_REPAIR is to mark the block as software corrupt.
<<End of Article>

Setting an Oracle event:The structure of the trace syntax

PURPOSE
-------
The purpose of this article is to explain briefly the structure of the syntax to
event-based trace generation.
Setting an event: The structure of the trace syntax
---------------------------------------------------
@ A comprehensive/full overview of the event syntax can be found in:
@ Note:9331.1 - Full Event Syntax (from ksdp.c)
@ Note:45217.1 - Summary Event Syntax for WWCS
0. "Setting an Event" - Abstract definition:
============================================
"Setting an event" means to tell oracle to generate information in form of a
so called trace file in the context of the event.
1. Event Classes to be traced:
==============================
There are 4 Classes of traceable events:
Class 1 "Dump something": Traces are generated upon so called unconditioned,
immediate, events. This is the case when oracle data has
to be dumped like, e.g., the headers of all redolog files
or the contents of the controlfile. These events can not
be set in the init<SID>.ora.
Class 2 "Trap on Error" : Setting this class of (error-) events cause oracle to
generate a so called errorstack everytime the event happens.
Class 3 "Change execution path" : Setting such an event will cause oracle to
change the execution path for some specific code segment.
For example, setting event "10269" prevents SMON from doing
free space coalescing.
Class 4 "Trace something": Events from this class are set to obtain traces that are
used for, e.g., sql tuning. A common event is "10046" which
will cause oracle to trace  the sql access path on each
sql-statement.
II. Event based trace generation syntax - Overview and examples:
================================================================
1. Session:         alter session set events '10181 trace name context forever, level 1000';
2. init<sid>.ora:   event="10181 trace name context forever, level 1000";
-------------------------------------------------------------------------------------------
| TRACE      |                        TRACE SYNTAX                                          |
| CLASS      |                                                                              |
|-------------------------------------------------------------------------------------------|
|            | <event name> |                     <action>                                  |
|-------------------------------------------------------------------------------------------|
|            |              | <action key word> | "name" | <trace name> | <trace qualifier> |
-------------------------------------------------------------------------------------------|
|            |              |                   |        |              |                   |
|            |  immediate   |   trace           | "name" | blockdump    |    level 67110390 |
|            |  immediate   |   trace           | "name" | redohdr      |    level 10       |
|            |  immediate   |   trace           | "name" | file_hdrs    |    level 10       |
| "Dump      |  immediate   |   trace           | "name" | controlf     |    level 10       |
| Something" |  immediate   |   trace           | "name" | systemstate  |    level 10       |
|            |              |                   |        |              |                   |
|-------------------------------------------------------------------------------------------
|            |              |                   |        |              |                   |
|            |        942   |   trace           | "name" | errorstack   |    forever        |
|            |        942   |   trace           | "name" | errorstack   |    off            |
| "Trap      |         60   |   trace           | "name" | errorstack   |    level 1        |
| on         |       6501   |   trace           | "name" | processstate |    level 10       |
| Error"     |       4030   |   trace           | "name" | heapdump     |    level 2        |
|            |              |                   |        |              |                   |
|-------------------------------------------------------------------------------------------
|            |              |                   |        |              |                   |
| "Change    |      10269   |   trace           | "name" | context      | forever, level 10 |
| Execution  |              |                   |        |              |                   |
| path"      |              |                   |        |              |                   |
|            |              |                   |        |              |                   |
|-------------------------------------------------------------------------------------------
|            |              |                   |        |              |                   |
|            |      10046   |   trace           | "name" | context      | forever, level 12 |
| "Trace     |      10046   |   trace           | "name" | context      | off               |
| something" |              |                   |        |              |                   |
|            |              |                   |        |              |                   |
-------------------------------------------------------------------------------------------
III: Trace syntax: Annotations
===============================
0. There are tools like oradebug that allow for setting an event in another
session; this is useful, e.g., for tracing the export utility.
@Setting Events from Oracle Tools <Note:45219.1">
@For a list of common ACTIONS see <Event:List>
@For COMMON numeric events see    <event:Numeric>
1. The general syntax of setting an event is:  <event name>  <action>
<action> consists of three parts:           <action key word> <trace name> <trace qualifier>
@<action key word> can be either "trace", "crash", or "debug".
@ See <Note:9331.1">
<event name> is either "immediate", by this indicating an unconditioned event
or an event name given as a symbolic number from the system event name table.
An unconditioned event (keyword "immediate") cannot be set in the parameter file.
<trace qualifier> "forever" means: Activate a trace whenever this event occurs.
<trace name> "context" is a special trace name and pertains only to events set up
to either trace a diagnostic event or to change the behaviour of the oracle
code execution path. It cannot be used in conjunction with errorstack- ("errorstack")
or dump-generating ("immediate") events.
2. There are exactly 2 types of events, session-events and process-events.
Process-events are initialized in the parameter file, session-events
are initialized with the "alter session..." or "alter system ..."command.
When checking for posted events, the oracle server first checks for session events
then for process-events.
RELATED DOCUMENTS
-----------------
@     Event Syntax for most common forms of event setting <Note:45217.1>
@     The FULL Event syntax <Note:9331.1>
@     Setting Events from Oracle Tools <Note:45219.1>
@     List of common ACTIONS <Event:List>
@     COMMON numeric events <Event:Numeric>

Data Block Cache Header Format Changes (Oracle8 Physical layout)

Oracle8 has introduced a change with the data block cache header format.  The
basic idea is that incarnation and sequence numbers stored in the cache header
have been replaced with an SCN number and sequence number.  The size of the
cache header has remained 20 bytes.  The size of the block trailer is still 4
bytes.  Only the format of the cache header and the trailer has changed.
Oracle7 Implementation
~~~~~~~~~~~~~~~~~~~~~~
The current Oracle7 implementation stores the incarnation and sequence number
in the cache header of each data block to determine the current version of the
block.  A compressed version of the incarnation/sequence is maintained at the
end of the data block.  The incarnation and sequence numbers are each 4 byte
values.  The low-order 2 bytes of each value are stored as the last 4 bytes of
the data block.  This information is used to detect media corruption when
reading the block for normal operations or during recovery, or when validating
the block.  It allows for a consistency check to ensure the top of the block
is in sync with the bottom of the block.
See [NOTE:33242.1] for more details on the Oracle7 format.
Oracle8 Implementation
~~~~~~~~~~~~~~~~~~~~~~
The basic idea is to store the current redo generating SCN in the cache header
when making a change to a block.  Since multiple changes to a block can be
made at the same SCN, a sequence number is also stored in the cache header to
differentiate between different changes at the same SCN.  The sequence number
is increased each time a change is made at the same SCN.  The sequence number
is reset to 1 when making a change at a higher SCN than the SCN currently in
the block.
Oracle8 data block layout
-------------------------
|   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |10 |11 |12 |13 |14 |15 |
+-------+-------+---------------+---------------+---------------+
|Typ|Fmt|Filler |     RDBA      |    SCNBase    |SCNWrap|Seq|Flg|
|-------+-------+---------------+---------------+---------------|
|ChkVal |Filler | <------                                       |
|-------+-------+                                               |
:                       Body of                                 :
:                        Data Block                             :
:                                                               :
|                                               +---------------|
|                                    ---------> |     Tail      |
+---------------------------------------------------------------+
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | A | B | C | D | E | F |
|   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |   |
The bytes in the header are defined as:
Typ     Block Type (defined in k.h).
Fmt     Block format.  In Oracle8 this is 2, converted on-the-fly.
Filler  Not used currently.
RDBA    Relative database address of the block.
SCNBase SCN Base
SCNWrap SCN Wrap
Seq     Sequence number.  Incremented for every change made to the
block at the same SCN.
Flg     Flag (defined in kcbh.h)
ChkVal  Optional check value for the block.  Set so that an xor of all
the ub2 values in the block equals zero.  This may require the
value to be zero (unlike Oracle7).  ChkVal will be verified
only if KCBHFCKV is set in the Flg field.
- always for file header blocks
- if the init.ora <parameter:DB_BLOCK_CHECKSUM> is set.
Tail    Consistency information used to verify the beginning and the
end of the block are of the same version.  Lower order 2 bytes
of SCNBase, plus block Type, plus SCN Seq number.
Migration to the New Format
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Data block cache headers will be upgraded to the new Oracle8 format on-the-fly
when the block is read into the cache.  Oracle8 will do this automatically for
every block that is read into the cache during normal operation. The on-disk
copy is not updated unless the block becomes dirty.
Soft Corrupt in Oracle8
~~~~~~~~~~~~~~~~~~~~~~~
Soft corrupt blocks in Oracle8 have:
Seq# is 0xff
flg  is 0x00

沪ICP备14014813号

沪公网安备 31010802001379号