OraFAQ Forum: RAC & Failsafe » undo-tablespace-drop in 2 node cluster

Home » Server Options » RAC & Failsafe » undo-tablespace-drop in 2 node cluster (11g R1 RHEL 5.2)

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

undo-tablespace-drop in 2 node cluster [message #572371]

Tue, 11 December 2012 04:29

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 2651 times)

Report message to a moderator

Re: undo-tablespace-drop in 2 node cluster [message #572373 is a reply to message #572371]

Tue, 11 December 2012 04:37

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

Report message to a moderator

Re: undo-tablespace-drop in 2 node cluster [message #572375 is a reply to message #572373]

Tue, 11 December 2012 04:58

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;

Report message to a moderator

Re: undo-tablespace-drop in 2 node cluster [message #572379 is a reply to message #572375]

Tue, 11 December 2012 06:24

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

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.

Report message to a moderator

Re: undo-tablespace-drop in 2 node cluster [message #572424 is a reply to message #572421]

Wed, 12 December 2012 00:13

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

Quote:

you have used SCOPE=SPFILE. This should read SCOPE=BOTH.

Report message to a moderator

Re: undo-tablespace-drop in 2 node cluster [message #572425 is a reply to message #572424]

Wed, 12 December 2012 00:16

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

Report message to a moderator

Previous Topic:	Archive logs are missing in hot backup
Next Topic:	(ERROR) FAN - Fast Application Notification

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 14:38:59 CST 2025