| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Urgent! ORA-01578 :ORACLE data block corrupted (file # 7,block # 82979)
This is a multi-part message in MIME format.
------=_NextPart_000_2521_48ec$4830
Content-Type: text/plain; format=flowed
Please go thro this document u will come to know how to solve the data block corruption.
>From: "Azhar Mahmood" <azhar_at_iiu.edu.my>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Urgent! ORA-01578 :ORACLE data block corrupted (file # 7,block #
>82979)
>Date: Thu, 03 Aug 2000 01:16:17 -0800
>
>Hi Listers
>
>While running a PL/SQL procedure, I encountered the following error :
>ORA-01578 :ORACLE data block corrupted (file # 7,block # 82979)
>ORA-01110 : data file 7: '/oradisk05/oradata/uia/indek01.dbf'
>ORA-06512 : at "UIA.GEN_GRAD3", line 485
>ORA-06512 : at line 16
>
>---
>from the alert_log file :
>Thu Aug 3 15:35:44 2000
> Current log# 8 seq# 5312 mem# 0: /oradisk03/oradata/uia/redo08.log
>LGWR: prodding the archiver
>Thread 1 advanced to log sequence 5313
> Current log# 9 seq# 5313 mem# 0: /oradisk03/oradata/uia/redo09.log
>Thu Aug 3 15:35:54 2000
>ARC0: received prod
>ARC0: media recovery disabled
>Thu Aug 3 15:36:05 2000
>LGWR: prodding the archiver
>Thread 1 advanced to log sequence 5314
> Current log# 10 seq# 5314 mem# 0: /oradisk03/oradata/uia/redo10.log
>Thu Aug 3 15:36:05 2000
>ARC0: received prod
>ARC0: media recovery disabled
>***
>Corrupt block relative dba: 0x01c14423 file=7. blocknum=82979.
>Bad header found during buffer read
>Data in bad block - type:6. format:2. rdba:0x01c14413
>last change scn:0x0000.0069cf55 seq:0x1 flg:0x02
>consistancy value in tail 0xcf550601
>check value in block header: 0x0, check value not calculated
>spare1:0x0, spare2:0x0, spare2:0x0
>
>----
>Please advise me what to do....
>-azhar
>
>
>
>--
>Author: Azhar Mahmood
> INET: azhar_at_iiu.edu.my
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
------=_NextPart_000_2521_48ec$4830
Content-Type: text/html; name="DATA BLOCK CORRUPTION.htm" Content-Transfer-Encoding: 8bit Content-Disposition: attachment; filename="DATA BLOCK CORRUPTION.htm" <HTML>
</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<PRE>
Oracle Worldwide Support
Bulletin
Bulletin : 108491.543
Author : EPITT
Category : RDBMS
Headline : DATA BLOCK CORRUPTION
Document ID: 108491.543
Title: Data Block Corruption
Author: EPITT
Department: U.S.A., RDBMS
Creation Date: 6 June 1995
Last Revision Date: 12 May 1997
Expiration Date 12 May 1998
Revision Number: 2
Distribution Code: External
Category: RDBMS
Product: Generic
Product Version: 6.x - 7.x
Platform: Generic
Information Type: Advisory
Impact: Medium
Abstract: This article discusses data block corruption and how
to resolve it.
Keywords: DATA BLOCK; CORRUPTED; ORA-1578
------------------------------------------------------------------------------
Data Block Corruption
INTRODUCTION
------------
This bulletin addresses the oracle data block corruption error (ora-1578) and
how to deal with it.
BACKGROUND
----------
ORA-1578 is generated when a corrupt data block is encountered.
The error should always provide you with the file number and the block number
where the corruption occurred. For the purposes of this bulletin, lets call
the file number returned with the error f and the block number b.
RESOLUTION
----------
If the corruption is the result of a bad disk, you can restore the datafile from backup to a good disk, startup mount, and rename the datafile to the new location before you recover.
2. If you do not run in archivelog mode, the first thing to do is to find out
what object is corrupted. The following query will tell you:
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
WHERE FILE_ID = f AND b BETWEEN BLOCK_ID AND
BLOCK_ID + BLOCKS - 1;
3. If the segment is an index, simply drop and recreate it.
4. If the segment is a table, it must be understood that the data within the
corrupt block is lost.
5. If you have a recent export dump file with the table in it, the easiest thing to do is to drop that table and import it. If you do not have a recent export dump file and you do not have a recent backup from which you can take an export, there are 3 other possible things to do in versions 7.1.x - 7.3.X. (There are only 2 possibilities before 7.1.x.)
6. There are events provided by oracle support that you can put in the init.ora to try to skip the corruption on export. This method will only work in a very limited number of cases. The limitations are too detailed for the
purposes of this bulletin.
7. If there is a unique single column index on the corrupted table, you can use that to select around the corrupt block and avoid touching it. If your index is not unique you will run into trouble if data in the corrupted block is duplicated.
In addition to the method below there is a plsql program that does this
in a bulletin titled 'PL/SQL PROGRAM TO GET DATA OUT OF A CORRUPT BLOCK'
by Ziyad Dahbour and Gita Kulandaiswamy. In versions 7.1.x - 7.3.x you
do not need to have an index, but can do a range scan with a rowid hint.
Pulling Data Out Of Tables Through Indexes
------------------------------------------
Suppose you have corrupted table emp with columns empno, ename and deptno.
Also suppose you have an index on the empno column.
Every row in a non-clustered table has a unique rowid pseudocolumn containing
its address. Indexes hold rowids for each row in a table, so you can
select from the table via the index and avoid the corrupt block by using the
rowid in a where clause.
ROWID:
The rowid is in hexidecimal and is 18 characters long: 8 characters of block
address, a period, 4 characters of row address, a period, and 4 characters
of file address.
If we issued SELECT EMPNO, ROWID FROM EMP WHERE EMPNO > 0; we would get
something like:
empno rowid
----- -----
100 00000003.0000.0006
101 00000003.0001.0006
102 00000003.0002.0006
103 00000003.0003.0006
.
.
.
500 00000004.0000.000A
501 00000004.0001.000A
.
.
.
755 0000001A.0005.000A
756 0000001A.000C.000A
Note that if the index were on a character column we would change the above
query to WHERE EMPNO > '';
Assume that you had:
01578, 00000, "ORACLE data block corrupted (file # 10, block # 4)
You can find all of the empnos for employees in the corrupted blocks with:
SELECT EMPNO, ROWID FROM EMP
WHERE EMPNO > 0
AND ROWID LIKE '00000004.%.000A';
EMPNO ROWID
----- -----
500 00000004.0000.000A
501 00000004.0001.000A
You could create a new table with the same definition as the emp table:
CREATE TABLE TEMP AS SELECT * FROM EMP WHERE 1 = 2;
and then insert into that table around the corruption:
INSERT INTO TEMP SELECT * FROM EMP WHERE EMPNO < 500;
INSERT INTO TEMP SELECT * FROM EMP WHERE EMPNO > 501;
Then drop the original table and rename temp. You then need to find out from
backups or paper records what information you have lost for empnos 500 and
501.
8. If you had many different rows in the corrupt block it might be easier
to issue the following:
CREATE TABLE EMPNOS AS
SELECT EMPNO FROM EMP
WHERE EMPNO > 0
AND ROWID NOT LIKE '00000004.%.000A';
This gives you all empnos which were not in the corrupt block. You would
then issue:
CREATE TABLE TEMP AS SELECT * FROM EMP WHERE 1 = 2;
INSERT INTO TEMP SELECT EMP.EMPNO, EMP.ENAME, EMP.DEPTNO
FROM EMP, EMPNOS
WHERE EMP.EMPNO > 0
AND EMP.EMPNO = EMPNOS.EMPNO;
9. If you do not have an index on the corrupted table and you are on versions
7.1.x - 7.3.x, you can still do a range scan to select around the corrupt
block and avoid touching it. You can do this by using the ROWID hint.
Although the ROWID hint is documented in version 7.0.x it does not work until
7.1.x. Before 7.1.x you are still doing a full table scan when you use the
ROWID hint.
Pulling Data Out Of Tables With The ROWID Hint Starting In 7.1
--------------------------------------------------------------
The scenario for pulling data out of tables with the ROWID hint starting
in 7.1.x is similar to the scenario for pulling data out of tables
through indexes described in item 6 above.
The syntax for using the ROWID hint is:
SELECT /*+ ROWID(EMP) */ column list
FROM EMP
WHERE ROWID BETWEEN 'low-rowid' and 'high-rowid';
Note that the where clause must have a ROWID range comparison.
Assume you have the same error with the same file and block number as before:
01578, 00000, "ORACLE data block corrupted (file # 10, block # 4)
The first ROWID in the corrupt block is 00000004.0000.000A
The last possible ROWID before this block is 00000003.FFFF.000A
The first ROWID after this block is 00000005.0000.000A
Create a temp table as before and insert into that table around the corruption:
CREATE TABLE TEMP AS SELECT * FROM EMP WHERE 1 = 2;
INSERT INTO TEMP SELECT /*+ ROWID(EMP) */ *
FROM EMP
WHERE ROWID BETWEEN '00000000.0000.0000'
AND '00000003.FFFF.000A'
INSERT INTO TEMP SELECT /*+ ROWID(EMP) */ *
FROM EMP
WHERE ROWID BETWEEN '00000005.0000.000A'
AND 'FFFFFFFF.FFFF.FFFF';
Then drop the original table, rename temp, and find out from backups or
paper records what information you have lost.
10. If a data dictionary table or index has a corrupt block you need to call
support to find out whether it can be reconstructed or not.
 |
 |