How to create undo tablespace in oracle RAC

1) Create Undo tablespace UNDOTBS01

SQL> create undo tablespace UNDOTBS01 DATAFILE '+DATAGROUP' SIZE 4g;
Tablespace created.
SQL> @asm
NAME TOTAL_GB FREE_GB
------------------------------ ---------- ----------
DATAGROUP 249.995117 11.46875
IDXGROUP 149.99707 7.55761719

2) Create Undo tablespace UNDOTBS02

SQL> create undo tablespace UNDOTBS02 DATAFILE '+DATAGROUP' SIZE 2G;
Tablespace created.

3) Assign the Newly created UNDOTBS01 tablespace to node1

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS01 SCOPE=BOTH SID='test1';
System altered.

3) Assign the Newly created UNDOTBS02 tablespace to node2

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS02 SCOPE=BOTH SID='test2';
System altered.

SQL>

SQL> show parameter undo

NAME TYPE VALUE
———————————— –
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string UNDOTBS02
SQL>

To drop tablespace UNDOTBS1(old Undo) see its segment status

SQL> SELECT a.name,b.status , d.username , d.sid , d.serial#
 FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
 WHERE a.usn = b.usn
 AND a.usn = c.xidusn
 AND c.ses_addr = d.saddr
 AND a.name IN (
 SELECT segment_name
 FROM dba_segments
 WHERE tablespace_name = 'UNDOTBS1'
 );
NAME STATUS USERNA SID SERIAL#
------------------------------ ---------- ---------
_SYSSMU82$ PENDING OFFLINE SCOTT 10 16007
_SYSSMU241$ PENDING OFFLINE SCOTT 17 30587
_SYSSMU34$ PENDING OFFLINE SCOTT 31 22303
_SYSSMU9$ PENDING OFFLINE SCOTT 33 10379
_SYSSMU22$ PENDING OFFLINE SCOTT 42 1131
_SYSSMU105$ PENDING OFFLINE SCOTT 45 58502
_SYSSMU234$ PENDING OFFLINE SCOTT 46 44824

wait for some time and execute again

QL> SELECT a.name,b.status , d.username , d.sid , d.serial#
 2 FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
 3 WHERE a.usn = b.usn
 4 AND a.usn = c.xidusn
 5 AND c.ses_addr = d.saddr
 6 AND a.name IN (
 7 SELECT segment_name
 8 FROM dba_segments
 9 WHERE tablespace_name = 'UNDOTBS1'
 10 );
SQL> 0 rows
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS1' and status='ONLINE';
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ ------------------------------ -----------------------
PUBLIC _SYSSMU103$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU198$ UNDOTBS1 ONLINE

After sometime executed the same query

 

SQL> /

no rows selected

SQL>  select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS1' and status='ONLINE'
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
And same for UNDOTBS2.
Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Database administration. Bookmark the permalink.

3 Responses to How to create undo tablespace in oracle RAC

  1. Venu says:

    Hi Sher khan
    Thanks for such a nice article. I was able to recreate undo tablespaces without any issue following the steps.
    Regards
    Venu

  2. Roman says:

    perfect

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s