Link to home
Start Free TrialLog in
Avatar of qdyoung
qdyoung

asked on

ORACLE data block corrupted

We got no backup. What should we do?

The database started up normally:

SQL*DBA: Release 7.2.2.4.0 - Production on Mon Dec  6 15:07:21 1999

Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.

Oracle7 Server Release 7.2.2.4.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.2.2.3.0 - Production

SQLDBA> Connected.
SQLDBA> ORACLE instance started.
Database mounted.
Database opened.
Total System Global Area      72836292 bytes
              Fixed Size         50612 bytes
           Variable Size      52797200 bytes
        Database Buffers      19660800 bytes
            Redo Buffers        327680 bytes
SQLDBA>
SQL*DBA complete.

Database "orac" warm started.

But users cannot login:

$ sqlplus scott/tiger

SQL*Plus: Release 3.2.2.0.0 - Production on Mon Dec  6 15:08:34 1999

Copyright (c) Oracle Corporation 1979, 1994.  All rights reserved.

ERROR: ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4829)
ORA-01110: data file 1: '/dbase/oracle7/dbs/systorac.dbf'
Avatar of cadabra
cadabra

There is a segment in the system tablespace which contains a corrupt block.

See if you can run this query
from sqldba or sqlplus
(as user system or sys):

SELECT SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_EXTENTS
WHERE FILE_ID = 1
AND 4829 BETWEEN BLOCK_ID AND
BLOCK_ID + BLOCKS - 1;

This should tell you which segment contains the corrupt block. If the segment is an index, you could drop and re-create it.

If it is not an index,
Post the results of the query, and we will try to can continue from there.

Good Luck,
cadabra.
Interested !!
Avatar of qdyoung

ASKER

It is a rollback segment:

SQLDBA> SELECT SEGMENT_NAME, SEGMENT_TYPE FROM DBA_EXTENTS
WHERE FILE_ID = 1
AND 4829 BETWEEN BLOCK_ID AND
BLOCK_ID + BLOCKS - 1;

SEGMENT_NAME   SEGMENT_TYPE    
----------------------------------------
POST_RS01      ROLLBACK        
1 row selected.

But I cannot drop then recreate it:

SQLDBA> ALTER ROLLBACK SEGMENT POST_RS01 OFFLINE;
ORA-01598: rollback segment 'POST_RS01' is not online
SQLDBA> DROP PUBLIC ROLLBACK SEGMENT POST_RS01;
ORA-01545: rollback segment 'POST_RS01' specified not available
SQLDBA> CREATE PUBLIC ROLLBACK SEGMENT POST_RS01 TABLESPACE SYSTEM STORAGE (INITIAL 8M NEXT 4M);
ORA-01535: rollback segment 'POST_RS01' already exists
Hey,

Try to shutdown the instance, remove the name of the bad rollback segment from the ROLLBACK_SEGMETNS parameter in the init.ora file, and restart the instance. The rollback segment should then be offline and droppable.

Another thing you can do is to create a lot more of rollback segments (in another TS then SYSTEM!) so maybe users will secceed to connect...

A third thing you can do is possible only if your DB is on NT. You can export the entire DB using the internal user... try it.

Good luck, man!
Hila send dash.
If the above does'nt work for you, try adding the following line in your init.ora file:

_offline_rollback_segment=(post_rs01)

and restart your instance.
Avatar of qdyoung

ASKER

Sorry for bother again.

Our DB is not on SCO OpenServer 5.0.4.
The rollback segmnet POST_RS01 was created without OFFLINE option. And it is no in ROLLBACK_SEGMETNS parameter.

There are many rollback segments on other tablespace. But users still cannot connect

SQLDBA> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                   STATUS          
------------------------------ ----------------
SYSTEM                         ONLINE          
POST_RS01                      NEEDS RECOVERY  
R01                            ONLINE        
R02                            ONLINE        
R03                            ONLINE        
R04                            ONLINE        
POST_RS02                      ONLINE          
POST_RS1                       ONLINE          
POST_RS                        ONLINE          
TMPRS1                         ONLINE          
TMPRS2                         ONLINE          
TMPRS3                         ONLINE        
TMPRS4                         ONLINE        
TMPRS5                         ONLINE        
TMPRS6                         ONLINE        
TMPRS7                         ONLINE        
TMPRS8                         ONLINE        
17 rows selected.
SQLDBA> connect scott/tiger
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 4829)
ORA-01110: data file 1: '/dbase/oracle7/dbs/systorac.dbf'
ASKER CERTIFIED SOLUTION
Avatar of cadabra
cadabra

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of qdyoung

ASKER

Thank you very much! The DB is now Ok.

Solution:
1. $sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID = 1 AND 4829 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
SEGMENT_NAME   SEGMENT_TYPE      
---------------------------------------
POST_RS01      ROLLBACK          
1 row selected.
SQLDBA> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                   STATUS          
------------------------------ ----------------
SYSTEM                         ONLINE          
POST_RS01                      NEEDS RECOVERY  
2. Adding the following line in initorac.ora file:
_offline_rollback_segments=(post_rs01)
3. $dbstart
4. $sqldba lmode=y
SQLDBA> connect internal;
SQLDBA> drop rollback segment post_rs01;
SQLDBA> CREATE PUBLIC ROLLBACK SEGMENT POST_RS01 TABLESPACE SYSTEM STORAGE (INITIAL 8M NEXT 4M);
5. Remove the added line in initorac.ora
6. $dbstart