OraFAQ Forum: RAC & Failsafe » Undo Tablespace in RAC

Home » Server Options » RAC & Failsafe » Undo Tablespace in RAC

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

Undo Tablespace in RAC [message #258850]

Mon, 13 August 2007 11:55

toshidas2000
Messages: 120
Registered: November 2005

Senior Member

I have a 2 node cluster. We have around 25GB of undo on both nodes. We always get SNAPSHOT too old. The first node undo tablespace always run out of sapce. Currently it is 35 GB.
Does select statements uses undo tablespace??

Report message to a moderator

Re: Undo Tablespace in RAC [message #258858 is a reply to message #258850]

Mon, 13 August 2007 12:04

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

No.But if table is lock or some DMLs are going on UNDO tablespace then in that case select goes to undo tablespace to retrive consistent copy of the query .

Quote:

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

http://download.oracle.com/docs/cd/B12037_01/server.101/b10739/undo.htm
Take a look for TUNING OPTIONS.
http://www.oracle.com/technology/oramag/oracle/05-jul/o45tuning.html

[Updated on: Mon, 13 August 2007 12:07]

Report message to a moderator

Re: Undo Tablespace in RAC [message #258859 is a reply to message #258858]

Mon, 13 August 2007 12:06

toshidas2000
Messages: 120
Registered: November 2005

Senior Member

If we shutdown the instance and bring it back, the undo should be 0% use, still it is 100% use and that is 35GB.

Report message to a moderator

Re: Undo Tablespace in RAC [message #258860 is a reply to message #258859]

Mon, 13 August 2007 12:07

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Whats the UNDO_RETENTION value?

And did you create 2 UNDOS for 2 nodes.

[Updated on: Mon, 13 August 2007 12:08]

Report message to a moderator

Re: Undo Tablespace in RAC [message #258863 is a reply to message #258860]

Mon, 13 August 2007 12:10

toshidas2000
Messages: 120
Registered: November 2005

Senior Member

34 hours and we do have seperate undo for both instance. First Instance undo panics..

Report message to a moderator

Re: Undo Tablespace in RAC [message #258867 is a reply to message #258863]

Mon, 13 August 2007 12:24

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

whats your ORACLE version post 4 decimals.

Try to switch the tablespaces between the nodes and then try your NODE1.

Report message to a moderator

Re: Undo Tablespace in RAC [message #258874 is a reply to message #258867]

Mon, 13 August 2007 12:32

toshidas2000
Messages: 120
Registered: November 2005

Senior Member

oracle version is 9.2.0.7.0 and how do I switch?? The undotablespace is mentioned in init.ora file

Report message to a moderator

Re: Undo Tablespace in RAC [message #259939 is a reply to message #258874]

Thu, 16 August 2007 21:38

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You should ensure that both instances are using automatic UNDO management. If you want to shrink your UNDO tablespace, you can perform the following:

create a new UNDO tablespace
switch the database to use the new UNDO tablespace (ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;)
drop the old UNDO tablespace

You can drop the UNDO tablespace as long as there aren't any active transaction using the UNDO tablespace.

Report message to a moderator

Re: Undo Tablespace in RAC [message #264716 is a reply to message #258850]

Tue, 04 September 2007 05:18

the_who
Messages: 5
Registered: December 2006
Location: Bandung

Junior Member

recreate new undo tablespace is the best thing you can do
because with new undo tablespace your problem with undo size
has finish...but..
i think with recreate undo tablespace you can't do flashback if one time you want your old data
all old historical saved in undo tablespace unless you have backup system....

is all i'm say true?
sory i'm beginner with this Smile

Report message to a moderator

Re: Undo Tablespace in RAC [message #264744 is a reply to message #264716]

Tue, 04 September 2007 07:20

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

Senior Member
Account Moderator

If you have a question, create a new topic don't use an old one.
Flashback is not in the scope of the original question.

Regards
Michel

Report message to a moderator

Previous Topic:	migrate 9i RAC to 10g (merged)
Next Topic:	RAC option in v$database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 20 12:07:12 CST 2025