OraFAQ Forum: Server Administration » Undo Tbs is not clearing/Flushing

Home » RDBMS Server » Server Administration » Undo Tbs is not clearing/Flushing (Oracle 11g R2, 2 node RAC on solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Undo Tbs is not clearing/Flushing [message #616607]

Thu, 19 June 2014 00:27

janakors
Messages: 232
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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

janakors
Messages: 232
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

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616612 is a reply to message #616610]

Thu, 19 June 2014 01:02

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

janakors
Messages: 232
Registered: September 2009

Senior Member

Following is checked at node2

Code: [Select all] [Show/ hide]

SQL> select * from v$transaction;
no rows selected
SQL>

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616658 is a reply to message #616656]

Thu, 19 June 2014 05:45

janakors
Messages: 232
Registered: September 2009

Senior Member

Code: [Select all] [Show/ hide]

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>

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616663 is a reply to message #616658]

Thu, 19 June 2014 05:55

janakors
Messages: 232
Registered: September 2009

Senior Member

i have also tried following but nothing found

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616671 is a reply to message #616607]

Thu, 19 June 2014 07:26

John Watson
Messages: 8977
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.

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616721 is a reply to message #616671]

Fri, 20 June 2014 00:21

janakors
Messages: 232
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

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616726 is a reply to message #616721]

Fri, 20 June 2014 00:54

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616732 is a reply to message #616721]

Fri, 20 June 2014 02:53

John Watson
Messages: 8977
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.

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616733 is a reply to message #616726]

Fri, 20 June 2014 02:56

janakors
Messages: 232
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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616734 is a reply to message #616733]

Fri, 20 June 2014 03:03

janakors
Messages: 232
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?

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616735 is a reply to message #616734]

Fri, 20 June 2014 03:05

John Watson
Messages: 8977
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?

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616736 is a reply to message #616735]

Fri, 20 June 2014 03:09

janakors
Messages: 232
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

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616737 is a reply to message #616736]

Fri, 20 June 2014 03:13

John Watson
Messages: 8977
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.

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616739 is a reply to message #616737]

Fri, 20 June 2014 03:22

janakors
Messages: 232
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

Report message to a moderator

Re: Undo Tbs is not clearing/Flushing [message #616746 is a reply to message #616739]

Fri, 20 June 2014 05:42

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 03 07:52:04 CDT 2025