oracle ora1578,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.