Home » Server Options » RAC & Failsafe » undo-tablespace-drop in 2 node cluster (11g R1 RHEL 5.2)
icon5.gif  undo-tablespace-drop in 2 node cluster [message #572371] Tue, 11 December 2012 04:29 Go to next message
arifulla04@gmail.com
Messages: 10
Registered: September 2012
Location: Bangalore
Junior Member
Hello

I am having issue with undo-tablespace of one instance in 2 node RAC,

please can any body tell me steps to create new-undotablespace in 1 node and drop old undotablespace.

its production database
  • Attachment: rac1.txt
    (Size: 1.44KB, Downloaded 92 times)
Re: undo-tablespace-drop in 2 node cluster [message #572373 is a reply to message #572371] Tue, 11 December 2012 04:37 Go to previous messageGo to next message
John Watson
Messages: 4812
Registered: January 2010
Location: Global Village
Senior Member
Creating and dropping an undo tablespace is the same in a RAC as it is single instance.
What is the "issue" you are having?
Re: undo-tablespace-drop in 2 node cluster [message #572375 is a reply to message #572373] Tue, 11 December 2012 04:58 Go to previous messageGo to next message
arifulla04@gmail.com
Messages: 10
Registered: September 2012
Location: Bangalore
Junior Member
Hi Watson , Thanks for reply
I understand the steps, but i am confused with undo_management that i cant find in RAC instance-pfile, can u please check

the steps below so that i can perform the operation and please suggest any changes required.

SQL> CREATE undo tablespace UNDOTBS3 datafile '+DM_DATA/rac1/datafile/undotbs3.dbf' SIZE 5G;

SQL> ALTER system SET undo_tablespace=UNDOTBS3 sid=rac1 scope=spfile;

SQL> show parameter undo_tablespace

Check the all segment of old undo tablespace to be offline

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

If any one the above segment is online then change it status to offline by using below command .

SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

Drop the old tablespace.

SQL> DROP tablespace UNDOTBS1 including contents and datafiles;
Re: undo-tablespace-drop in 2 node cluster [message #572379 is a reply to message #572375] Tue, 11 December 2012 06:24 Go to previous messageGo to next message
John Watson
Messages: 4812
Registered: January 2010
Location: Global Village
Senior Member
I still do not know what "issue" you have.

The routine you give will not work, because you have used SCOPE=SPFILE. This should read SCOPE=BOTH.

When you use copy/paste from SQL*Plus, you must enclose the text within [code] tags, as described here How to use [code] tags and make your code easier to read

[Updated on: Tue, 11 December 2012 06:24]

Report message to a moderator

Re: undo-tablespace-drop in 2 node cluster [message #572421 is a reply to message #572379] Tue, 11 December 2012 22:36 Go to previous messageGo to next message
arifulla04@gmail.com
Messages: 10
Registered: September 2012
Location: Bangalore
Junior Member
Thanks Watson

my issue is i accidentally deleted one datafile of undo-tablespace at OS -level, in one node of RAC,
I want to delete old undo-tablespace and create new undo-tablespace, please tell me steps to create
new undotablespace in one node of RAC.
Re: undo-tablespace-drop in 2 node cluster [message #572424 is a reply to message #572421] Wed, 12 December 2012 00:13 Go to previous messageGo to next message
John Watson
Messages: 4812
Registered: January 2010
Location: Global Village
Senior Member
Quote:
you have used SCOPE=SPFILE. This should read SCOPE=BOTH.
Re: undo-tablespace-drop in 2 node cluster [message #572425 is a reply to message #572424] Wed, 12 December 2012 00:16 Go to previous message
arifulla04@gmail.com
Messages: 10
Registered: September 2012
Location: Bangalore
Junior Member
Thanks Watson
I will use scope=both rest all same as i have updated the steps previously.

please confirm because its a production database
Previous Topic: Archive logs are missing in hot backup
Next Topic: (ERROR) FAN - Fast Application Notification
Goto Forum:
  


Current Time: Fri Nov 28 15:40:38 CST 2014

Total time taken to generate the page: 0.12256 seconds