Home » RDBMS Server » Server Administration » Undo Tbs is not clearing/Flushing (Oracle 11g R2, 2 node RAC on solaris 10)
Undo Tbs is not clearing/Flushing [message #616607] Thu, 19 June 2014 00:27 Go to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
hi,
i am having 11.2.0.2 with 2 x nodes RAC on solaris 10. Undo tablespace2 which is assosiated with 2nd node (not primary node) is not getting cleared or fllushed. undo retention is 900 sec but in past 2 weeks from now i have increase (i had to) by 20gb, so i don't have any idea why it it is not getting cleared and very soon my disk group be out of space. current size of this undo tablespace is 28 gb

kindly guide

Regards

[Updated on: Thu, 19 June 2014 00:34] by Moderator

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616609 is a reply to message #616607] Thu, 19 June 2014 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post evidence: post the query and result that makes you feel the tablespace is not "getting clear".
Maybe it is not "getting clear" because a transaction is still going on.

[Updated on: Thu, 19 June 2014 00:37]

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616610 is a reply to message #616609] Thu, 19 June 2014 00:40 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
i use EM to see its size daily and on daily basis it is getting increased. This database host an application for hospital mgmt system.
Regards
Re: Undo Tbs is not clearing/Flushing [message #616612 is a reply to message #616610] Thu, 19 June 2014 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Most likely my first sentence is the correct one: something prevent the undo tablespace to reuse the extents.
Query v$transacrion.

[Updated on: Thu, 19 June 2014 01:02]

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616656 is a reply to message #616612] Thu, 19 June 2014 05:35 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
Following is checked at node2
SQL> select * from v$transaction;
no rows selected
SQL>
Re: Undo Tbs is not clearing/Flushing [message #616658 is a reply to message #616656] Thu, 19 June 2014 05:45 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
SQL>select t1.sid,t1.username,t2.xidusn,t2.used_urec,t2.used_ublk from v$session t1,v$transaction t2 where t1.saddr = t2.ses_addr;

    SID  USERNAME                      XIDUSN  USED_UREC  USED_UBLK
------- ----------------------------- ------- ---------- ----------
   1351 USER01                             20          1          1

SQL>
Re: Undo Tbs is not clearing/Flushing [message #616663 is a reply to message #616658] Thu, 19 June 2014 05:55 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
i have also tried following but nothing found
SQL> SELECT COUNT(*)
  2    FROM v$transaction t, v$session s, v$mystat m
  3   WHERE t.ses_addr = s.saddr
  4     AND s.sid = m.sid
  5     AND ROWNUM = 1;

  COUNT(*)
----------
         0
Re: Undo Tbs is not clearing/Flushing [message #616671 is a reply to message #616607] Thu, 19 June 2014 07:26 Go to previous messageGo to next message
John Watson
Messages: 4592
Registered: January 2010
Location: Global Village
Senior Member
janakors wrote on Thu, 19 June 2014 06:27
hi,
i am having 11.2.0.2 with 2 x nodes RAC on solaris 10. Undo tablespace2 which is assosiated with 2nd node (not primary node) is not getting cleared or fllushed. undo retention is 900 sec but in past 2 weeks from now i have increase (i had to) by 20gb, so i don't have any idea why it it is not getting cleared and very soon my disk group be out of space. current size of this undo tablespace is 28 gb

kindly guide

Regards
If I understand you correctly, you are increasing the size of the datafile(s) manually. Why? Are you getting any errors? For an undo tablespace to have no free space is normal, and not necessarily any cause for concern.
Re: Undo Tbs is not clearing/Flushing [message #616721 is a reply to message #616671] Fri, 20 June 2014 00:21 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
well, you are right but if undo tablespace have no space left, what u think what will happen, it wont allow any DML as it cant undo if necessary .no free extent. please comment

Regards
Re: Undo Tbs is not clearing/Flushing [message #616726 is a reply to message #616721] Fri, 20 June 2014 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No space is reused, I was misled by your sentence " it is getting increased".
Query DBA_UNDO_EXTENTS to know the space usage in undo tablespace.

Re: Undo Tbs is not clearing/Flushing [message #616732 is a reply to message #616721] Fri, 20 June 2014 02:53 Go to previous messageGo to next message
John Watson
Messages: 4592
Registered: January 2010
Location: Global Village
Senior Member
janakors wrote on Fri, 20 June 2014 06:21
well, you are right but if undo tablespace have no space left, what u think what will happen, it wont allow any DML as it cant undo if necessary .no free extent. please comment

Regards
Trust me. I'm a DBA.
Re: Undo Tbs is not clearing/Flushing [message #616733 is a reply to message #616726] Fri, 20 June 2014 02:56 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
well after browsing the net i found something here https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:676925300346249247
and it says that if no space left so it will reuse even those extents as well whose retention time is still left.
Now look at following at my DB
SQL> select status, count(1)from dba_undo_extents group by status;

STATUS      COUNT(1)
--------- ----------
ACTIVE             1
EXPIRED          914
UNEXPIRED        383


here 383 are expired so why not this space is reclaimed? does because i am not hitting 100% of undo tablespace , if yes so then i dont need
to worry about the space usage of undo tablespace?

please comments

Reagrds
Re: Undo Tbs is not clearing/Flushing [message #616734 is a reply to message #616733] Fri, 20 June 2014 03:03 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
Quote:

SQL> select status, count(1)from dba_undo_extents group by status;

STATUS COUNT(1)
--------- ----------
ACTIVE 1
EXPIRED 914
UNEXPIRED 383


What accounts for the contents being so full if so much is not active or expired?
Re: Undo Tbs is not clearing/Flushing [message #616735 is a reply to message #616734] Fri, 20 June 2014 03:05 Go to previous messageGo to next message
John Watson
Messages: 4592
Registered: January 2010
Location: Global Village
Senior Member
Does your tea cup get any smaller when the tea gets cold? Or even when you drink it?
Re: Undo Tbs is not clearing/Flushing [message #616736 is a reply to message #616735] Fri, 20 June 2014 03:09 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
well said john, but what if i need to put more tea in the same cup and this cup is full of cold tea?

Regards
Re: Undo Tbs is not clearing/Flushing [message #616737 is a reply to message #616736] Fri, 20 June 2014 03:13 Go to previous messageGo to next message
John Watson
Messages: 4592
Registered: January 2010
Location: Global Village
Senior Member
How far can this analogy be pushed.....
It is more a case of putting the cup in the microwave, and warming up the cold tea to make it drinkable.

Seriously, for the undo tablespace to be full is a natural state. Uncle Oracle will re-use the space within the undo segments as necessary.
Re: Undo Tbs is not clearing/Flushing [message #616739 is a reply to message #616737] Fri, 20 June 2014 03:22 Go to previous messageGo to next message
janakors
Messages: 177
Registered: September 2009
Senior Member
ok thanks ....what i understand is if undo tablespce hit 100 % and there are a lot of expired extents so they will be re-used and even (if retention is not guaranteed) unexpired extents will be re-used. correct or not, final reply please

thank You very much for your time and everyone else

Regards
Re: Undo Tbs is not clearing/Flushing [message #616746 is a reply to message #616739] Fri, 20 June 2014 05:42 Go to previous message
Michel Cadot
Messages: 59205
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is correct and V$UNDOSTAT will tell you what happened about undo during the instance life.

Previous Topic: spfile error
Next Topic: Duplicate permissions entries and can't create new ones
Goto Forum:
  


Current Time: Wed Sep 24 00:25:55 CDT 2014

Total time taken to generate the page: 0.07711 seconds