Subject :
This article discusses what it means when a rollback segment needs
recovery and how to resolve it.

OVERVIEW
--------
This bulletin discusses why a rollback segment is the status of
"needs recovery", what the status means, and how to resolve it.

INTRODUCTION
------------
Rollback segments can be monitored through the data dictionary view,
dba_rollback_segments. There is a status column that describes what state
the rollback segment is currently in. Normal states are either online or offline.
Occasionally, the status of "needs recovery" will appear.
This is considered to be a corrupted rollback segment.

When a rollback segment is in this state, bringing the rollback segment
offline or online either through the alter rollback segment command or
removing it from the rollback segments parameter in the init.ora usually has no effect.

UNDERSTANDING
-------------
A rollback segment falls into this status of needs recovery whenever
Oracle tries to roll back an uncommitted transaction in its transaction
table and fails.

Here are some examples of why a transaction may need to rollback:
1-A user may do a dml transaction and decides to issue rollback
2-A shutdown abort occurs and the database needs to do an instance recovery
in which case, Oracle has to roll back all uncommitted transactions.

When a rollback of a transaction occurs, undo must be applied to the
data block the modified row/s are in. If for whatever reason, that data
block is unavailable, the undo cannot be applied. The result is a
'corrupted' rollback segment with the status of needs recovery.

What could be some reasons a datablock is unaccessible for undo?
1-If a tablespace or a datafile is offline or missing.
2-If the object the datablock belongs to is corrupted.
3-If the datablock that is corrupt is actually in the rollback segment
itself rather than the object.

HOW TO RESOLVE IT
-----------------
1-MAKE sure that all tablespaces are online and all datafiles are
online. This can be checked through dba_data_files under the
status column. For tablespaces, look in dba_tablespaces.

If that still does not resolve the problem then

2-PUT the following in the init.ora-
event = "10015 trace name context forever, level 10"

Setting this event will generate a trace file that will reveal the
necessary information about the transaction Oracle is trying to roll
back and mostimportantly, what object Oracle is trying to apply
the undo to.

3-TAKE the corrupted rollback segment out of the rollback_segments parameter in the init.ora

4-SHUTDOWN the database (if normal does not work, immediate, if that does
not work, abort) and bring it back up.

Note: An ora-1545 may be encountered, or other errors, that is ok.

5-CHECK in the directory that is specified by the user_dump_dest parameter
(in the init.ora or show parameter command) for a trace file that was
generated at startup time.

6-IN the trace file, there should be a message similiar to-
error recovery tx(#,#) object #.

TX(#,#) refers to transaction information.
The object # is the same as the object_id in sys.dba_objects.

7-USE the following query to find out what object Oracle is trying to
perform recovery on.
***************************************************
select owner, object_name, object_type, status
from dba_objects where object_id =