UNDO

How to Resolve SMON: about to recover undo segment xx SMON: mark undo segment xx as available

Repeated messages were found in the alert log after instance startup:
...
SMON: about to recover undo segment 32
SMON: mark undo segment 32 as available
SMON: about to recover undo segment 32
SMON: mark undo segment 32 as available
...

It looked like that SMON was rolling back the database with undo segment. But somehow, it met problems when recovering undo segment #32. The most annoying thing is the message looped itself and it seemed endless in the alert log. That make me very uncomfortable.

Let’s see the status of this rollback segment #32.
SQL> select status from dba_rollback_segs where segment_id = 32;

STATUS
----------------
PARTLY AVAILABLE

It means that the rollback segment #32 contains data for an in-doubt or recovered distributed transaction, or for yet to be recovered transactions.

Let’s solve the problem by the following two steps.
  1. Control over the management of rollback segments under AUM (Automatic Undo Management) mode in this session, just temporarily.
  2. SQL> alter session set "_smu_debug_mode"=4;
  3. Bring this undo segment #32 back ONLINE manually.
  4. SQL> alter rollback segment "_SYSSMU32$" online;
Done!

Leave a Reply

Your email address will not be published. Required fields are marked *