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'
qdyoungAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cadabraCommented:
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.
0
NaelCommented:
Interested !!
0
qdyoungAuthor Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

doronlaCommented:
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.
0
cadabraCommented:
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.
0
qdyoungAuthor Commented:
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'
0
cadabraCommented:
Try to perform a full export on your
database, it may report problems in objects other than the rollback
segment.

Take a look at this article:

http://www.fors.com/orasupp/rdbms/dba/28812_1.HTM

Good luck, and keep us posted
cadaba.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qdyoungAuthor Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.