Email: service@parnassusdata.com 7 x 24 online support!

ORA-1578 on Oracle Startup

ORA-1578 on Oracle Startup

An ORA-1578 on startup is usually bad news and relates to either a corrupt
rollback segment header, or a corrupt block being referenced during
bootstraping of the instance.

eg:
Database mounted.
ORA-01578: ORACLE data block corrupted (file # 11, block # 2)
ORA-01110: data file 1198: '/tmp/RPrbcor.dbf'
SVRMGR>
( Recovery does not fail if a corrupt block is encountered - the block is
skipped over and recovery continues. Warnings are written to the user
trace file.
eg:
Corrupt block dba: 0x20000003 file=8. blocknum=3. found during
media/instance recovery
on disk type:6. ver:1. dba: 0x2000ffff inc:0x00000001 seq:0x00000007
incseq:0x00010007
Reread of block=20000003 file=8. blocknum=3. found same corupted data
Actions:
1. Shutdown the instance (or you may get ORA-704/ORA-604/ORA-955 when you
next try to open the database)
eg: SHUTDOWN ABORT
2. Although it is possible to offline the affected file/s and double check
which object is involved it is better to first look at recovering the
corrupted file. This is only possible in ARCHIVELOG mode.
eg:
Take a SAFE copy of the existing problem file
Restore a good backup of the problem file
STARTUP MOUNT
ALTER DATABASE DATAFILE 'name_of_file' ONLINE;
RECOVER DATABASE


ALTER DATABASE OPEN;
3. If the ORA-1578 persists or the file cannot be restored then:
a. If this is a SYSTEM tablespace datafile you are in trouble.
Go to "Last Options"
b. If this is not a SYSTEM tablespace datafile you MAY be able to
continue as below.
4. If the ORA-1578 is on a rollback segment header then it is possible
that the header is only being accessed because Oracle is trying to
online the rollback segment. To check for this we can comment out all
of the rollback segments in the init.ora file and attempt to start the
database.
eg: Comment out the ROLLBACK_SEGMENTS=... clause
If you are using PUBLIC rollback segments then also set the init.ora
parameter TRANSACTIONS to a small number (about 20) and
TRANSACTIONS_PER_ROLLBACK_SEGMENT to the same number . Additionally
try to find one rollback segment which is known to be good and set
this in the ROLLBACK_SEGMENTS parameter. This is done to try to stop
Oracle needing to online any PUBLIC rollback segment when the database
opens. If there are no rollback segments you know to be good you can
try this step several times using different named rollback segments.
eg: TRANSACTIONS=20
TRANSACTIONS_PER_ROLLBACK_SEGMENT=20
ROLLBACK_SEGMENTS=(OK_RBS)
Now try to start the database:
eg:
SHUTDOWN ABORT
STARTUP
If the database opens go to step 6
5. If the above has not allowed you to open the database then the next
step is to attempt to offline the problem file:
eg:
SHUTDOWN ABORT
STARTUP MOUNT
ALTER DATABASE DATAFILE 'name_of_file' OFFLINE;
ALTER DATABASE OPEN;
If the "ALTER DATABASE DATAFILE ... OFFLINE" reports
"ORA-01145: offline immediate disallowed unless media recovery enabled"
go to "NOARCHIVELOG" below.
6. If the database opens check which object has the ORA-1578 error.
WARNING: On Oracle8 you need the file number from the accompanying
ORA-1110 error.
SELECT segment_type, owner, segment_name
FROM dba_extents
WHERE file_id=
AND  BETWEEN block_id and block_id+blocks-1
;
If SEGMENT_TYPE is ROLLBACK SEGMENT go to "Recovering Rollback Segments".
If OWNER is SYS more detailed investigation is required to determine
whether the problem object can be rebuilt.
For any other object see Note:28814.1 on how to handle block corruptions.

See "What are _OFFLINE_ROLLBACK_SEGMENTS")
5. Start the instance
6. If the database starts then the problem is almost certainly in one of
the listed rollback segments. Comment out the _OFFLINE_ROLLBACK_SEGMENTS
parameter NOW so you do not forget to do it later.
If the database still signals an ORA-1578 then add other rollback
segments to the _offline parameter or go to "Last Options".
7. Once the database is open you can query DBA_EXTENTS to check the exact
object:
WARNING: On Oracle8 you need the file number from the accompanying
ORA-1110 error.
SELECT segment_type, owner, segment_name
FROM dba_extents
WHERE file_id=
AND  BETWEEN block_id and block_id+blocks-1
;
Assuming this is a rollback segment and you are in NOARCHIVELOG
mode then you may want to try and salvage any data from the database
now as you cannot run the instance with an _OFFLINE_ROLLBACK_SEGMENT.
Go to "Last Options" for the only other options.
If this is an object other than a rollback segment the action to
take depends on the object type and owner.