All,
We had a problem with UNDOTBS a day back.(sequence of evets given below)
=====================================
1. Expired segments were around 8%
2. Tuned undo retention was around 8 hrs
3. System was taken down as scheduled for cold backup.
4. System was restarted and expired segments dropped below 1%
Observations at this point of time:
=========================
1. No active transactions:- no rows selected from v$transaction.
2. Tuned_undo_retention from v$undostat increasing exponentially
5. Went for a switch
6. Now the old UNDO TBS as well as the new one were filled up with unexpired extents. Both had 0.5% expired extents each.
7. Tuned_undo_retention from v$undostat was around 8000 hrs
8. ALTER SYSTEM SET undo_retention=43200 SCOPE=BOTH was issued.
9. UNDOTBS was switched back to old one
10. No of Expired extents started increasing
11. Tuned_undo_retention from v$undostat was around 11hrs in 2 hrs of time after step 8
I am trying to understand
1. What could have spiked up the value of tuned_undo_retention extremely high
2. Why the DB will not release unexpired undo extents even after a bounce and particularly where are no transactions.
Please provide your inputs.