ORA-1578 on a Rollback Segment Header/Rollback Segment Block /REQUIRED Rollback Segment Block

~~~~~~~~~~~~~~
ORA-1578 on a Rollback Segment Header
~~~~~~~~~~~~~~
If there is an ORA-1578 on the rollback segment header Oracle cannot
know if there are any active transactions in that rollback segment.
What to do depends on whether we really do need the rollback segment
header. This scenario is the same as ORA-1578 on a rollback segment
block except that if segment header is needed there are more possible
side effects from the corruption.
If you are in ARCHIVELOG mode with a good backup then the best option
is to offline, restore and then recover the file and bring it back online.
This is only possible if the database is in ARCHIVELOG mode.
eg: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE;
Save the current copy of the file
Restore it at OS level from a good backup,
RECOVER DATAFILE 'name_of_file';
ALTER DATABASE DATAFILE 'name_of_file' ONLINE;
If the recovery has produced a good file then the rollback segment can
be recovered. Go to "Recovering Rollback Segments"
If the recovery is not possible (Eg: NOARCHIVELOG mode) or the same
ORA-1578 follow the steps in "ORA-1578 on a Rollback Segment Block" below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-1578 on a Rollback Segment Block
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If an ORA-1578 occurs on a rollback segment block there are 2 main
scenarios:
a. The corruption is in an undo chain that needs to be rolled back
(ie: the block is needed)
or
b. The corruption is in an undo block which is only being used
for consistent read and is not needed to roll out a transaction.
It is important to determine which of these applies as one can be tackled
by dropping the rollback segment but the other requires the ORA-1578 to
be resolved. In the first instance attempt to recover the file as this
is always the best option.
eg: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE;
Save the current copy of the file
Restore it at OS level from a good backup,
RECOVER DATAFILE 'name_of_file';
ALTER DATABASE DATAFILE 'name_of_file' ONLINE;
If recovery of the file is not possible then:
1. Note the File and Block where the ORA-1578 occurs.
WARNING: On Oracle8 you need the file number from the accompanying
ORA-1110 error.
2. Determine which rollback segment contains the corrupt block:
SELECT segment_type, segment_name
FROM dba_extents
WHERE file_id=
AND  BETWEEN block_id and block_id+blocks-1
;
3. Find the SEGMENT_ID (USN) and STATUS of this rollback segment:
SELECT segment_id, status
FROM dba_rollback_segs
WHERE segment_name=''
;
4. If the rollback segment is OFFLINE then it can probably be dropped as
it cannot contain any active transactions.
Go to "Dropping a Rollback Segment"
5. If the rollback segment is ONLINE then go to "Dropping a Rollback Segment"
NOTE: You MUST read the notes in that section before dropping the
rollback segment.
6. If the rollback segment is any other status you need to monitor
both the alert log for errors and check the rollback segment header for
active transactions.
Go to "Checking for active transactions" noting that:
For ACTIVE / NONE transactions try to get the users to commit
(as rolling back may require use of the corrupt block)
For in-doubt transactions it may be best to force commit them
(as rolling back may require use of the corrupt block)
For ACTIVE / DEAD transactions we have to wait to see if the
rollback of these needs the corrupt block.


If the rollout of a transaction hits a problem you will see an error in
the alert log. See the relevant section for the error in the alert log.
This will probably be
"Error 1578 encountered while recovering transaction (4, 1)."
as described next.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-1578 on a REQUIRED Rollback Segment Block
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For this scenario you typically will have an error of the form:
"ORACLE Instance XXXX (pid = 6) -
Error 1578 encountered while recovering transaction (4, 1)."
in the alert log. Note the lack of "on object ..." in this message.
Basically this shows that a block in a rollback segment which is
required to roll out a transaction has been corrupted:
1. Note the File and Block where the ORA-1578 occurs.
WARNING: On Oracle8 you need the file number from the accompanying
ORA-1110 error.
2. Offline, restore and then recover the file and bring it back online.
This is only possible if the database is in ARCHIVELOG mode.
eg: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE;
save the current copy of the file
Restore it at OS level from a good backup,