Refrence from Metelink Doc [ID 68013.1]
Checked for relevance on 12-SEP-2010
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 ***.
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
No comments:
Post a Comment