Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Urgent! ORA-01578 :ORACLE data block corrupted (file # 7,block # 82979)

Re: Urgent! ORA-01578 :ORACLE data block corrupted (file # 7,block # 82979)

From: ARUN K C <arun_k_c_at_hotmail.com>
Date: Thu, 03 Aug 2000 11:12:55 GMT
Message-Id: <10578.113704@fatcity.com>


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



Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

------=_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>

<HEAD>
<TITLE>
File 58865
</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                                                                      
----------                                                                      
   
  1. If you run in archivelog mode, the best resolution is to restore the file from backup and recover. This article does not cover the full details of this senario. You can find the name of the datafile with the corrupted block by querying dba_data_files or v$datafile and using the file number from the ora-1578. You will want to correct your hardware problem before you restore.

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 &gt; 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 &gt; '';                                                      
   
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 &gt; 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 &lt; 500;                      
     INSERT INTO TEMP SELECT * FROM EMP WHERE EMPNO &gt; 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 &gt; 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 &gt; 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.                    
   
  1. If a rollback segment is corrupted you should call Oracle Support. If a rollback segment has a status of "needs recovery", either the rollback segment itself is corrupted or another object with a transaction in the rollback segment is corrupted. If an object with a transaction in the rollback segment is corrupted, the solution is to drop that object.
  2. Often, an ora-1578 is the result of a hardware problem, especially if it occurs in conjunction with ora-600 [3339] in versions 6.x, 7.0.x and 7.1.x. Ora-600 [3339] has been removed from version 7.2.x and later. If you get an ora-600 [3398] or an ora-600 [3374], it means you have encountered a corruption in memory. Starting with 7.2.x you are more likely to get ora-600 [3374] than ora-600 [3398].
Received on Thu Aug 03 2000 - 06:12:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US