Home » Server Options » RAC & Failsafe » Undo Tablespace in RAC
Undo Tablespace in RAC [message #258850] Mon, 13 August 2007 11:55 Go to next message
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??
Re: Undo Tablespace in RAC [message #258858 is a reply to message #258850] Mon, 13 August 2007 12:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Undo Tablespace in RAC [message #258860 is a reply to message #258859] Mon, 13 August 2007 12:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
toshidas2000
Messages: 120
Registered: November 2005
Senior Member
34 hours and we do have seperate undo for both instance. First Instance undo panics..
Re: Undo Tablespace in RAC [message #258867 is a reply to message #258863] Mon, 13 August 2007 12:24 Go to previous messageGo to next message
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.
Re: Undo Tablespace in RAC [message #258874 is a reply to message #258867] Mon, 13 August 2007 12:32 Go to previous messageGo to next message
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
Re: Undo Tablespace in RAC [message #259939 is a reply to message #258874] Thu, 16 August 2007 21:38 Go to previous messageGo to next message
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.
Re: Undo Tablespace in RAC [message #264716 is a reply to message #258850] Tue, 04 September 2007 05:18 Go to previous messageGo to next message
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
Re: Undo Tablespace in RAC [message #264744 is a reply to message #264716] Tue, 04 September 2007 07:20 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: migrate 9i RAC to 10g (merged)
Next Topic: RAC option in v$database
Goto Forum:
  


Current Time: Thu Mar 28 06:05:34 CDT 2024