Usage of UNDO tablespace constantly over 99% ?!

Thursday, February 12, 2009

We had strange situation with one of our OLTP databases and I've spent some time to find explanation for it but still no answer that satisfies me.
When I find acceptable answer or Metalink document about this problem I'll post info here just for reminder.


Info:
OS: SunOS
DB: Oracle 9.2.0.6.0

undo_management=AUTO
undo_retention=14400
undo-size = 2GB

Usage of our UNDOTBS1 tablespace raised to 100% and I've decided to free some undo space because ORA-01555 errors started to show up.



To free some space I've tried to find transactions that were using undo blocks and commit or rollback those transactions.

But when I queried:

select t.used_ublk, t.*
from v$transaction t
order by t.used_ublk desc;

there were transactions with just few blocks used - UNDOTBS1 tablespace was 2 GB large.

So to avoid ORA-01555 errors I've created another undo tablespace UNDOTBS2 and made that tbs as default.

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'/ora/oradata/undotbs02.dbf' SIZE 2048M REUSE AUTOEXTEND OFF;

alter system set undo_tablespace=UNDOTBS2 scope=both;



Then I could without pressure examine what happened.
I queried DBA_UNDO_EXTENTS and found out that there were lots of undo segments situated in UNDOTBS1 tablespace.

select segment_name, owner, tablespace_name, status
from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS

SYSTEM SYS SYSTEM ONLINE
_SYSSMU1$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU2$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU3$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU4$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU5$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU6$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU7$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU8$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU9$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU10$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU11$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU12$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU13$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU14$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU15$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU16$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU17$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU18$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU19$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU20$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU21$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU22$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU23$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU24$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU25$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU26$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU27$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU28$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU29$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU30$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU31$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU32$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU33$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU34$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU35$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU36$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU37$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU38$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU39$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU40$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU41$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU42$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU43$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU44$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU45$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU46$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU47$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU48$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU49$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU50$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU51$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU52$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU53$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU54$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU55$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU56$ PUBLIC UNDOTBS2 ONLINE
_SYSSMU57$ PUBLIC UNDOTBS2 ONLINE


Only offline rollback segments in UNDOTBS1 tablespace.

How come that this UNDOTBS1 tablespace was used 100% but when I tried to find transactions which were using that undo/rollback segments I couldn't find none.

I understand that there was transaction/transactions that eventually used whole UNDO tablespace, but they finished with their work - commit or rollback was executed.

This allocated UNDO space should be reused by another transactions - but insted of reusing UNDO space I got several:
ORA-01555 caused by SQL statement below (Query Duration=78 sec, SCN: 0x0004.f253ce76):


This was production database so I recreated UNDO tablespace as it was before. I've dropped UNDOTBS1 and created new one on the same location. Also made id as a default undo tablespace. After that dropped newly created UNDOTBS2.

I think that this was Oracle bug but still I am eager to find metalink document that confirms my thoughts.



Link on OTN forum: Click here!


Share/Bookmark

0 comments:

Post a Comment