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/systor ac.dbf'
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/systor
Interested !!
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
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.
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.
_offline_rollback_segment=
and restart your instance.
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/systor ac.dbf'
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/systor
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_rs0 1)
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
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
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
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.