Can't drop UNDO TABLESPACE

Question

We have Oracle 11gR1 RAC two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following:

CREATE UNDO TABLESPACE UNDOTBS20140508  DATAFILE '+DATA/....../UNDOTBS20140508' SIZE 20G;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS20140508;
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES;

When trying to drop the UNDOTBSX02 it show the following error:

Error starting at line : 13 in command -
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES
Error report -
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 57
ORA-30013: undo tablespace 'UNDOTBSX02' is currently in use
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

I checked UNDOTBSX02 for any pending transaction and it is empty. So how to drop it?

Note : I am not Oracle DBA, but I have to do this task.

Answer 1

I tried the above suggestions, but did not work, such as select SQL did not get results and I still cannot drop the undo table space. The fundamental issue is that the old undo table space is still registered in Oracle as its undo, therefore we need to change it to the new undo table space.

Here are what I did and it worked:

1. create a new UNDO table space:

CREATE SMALLFILE UNDO TABLESPACE "UNDO" 
DATAFILE '+DATA/t2/datafile/undo_01.dbf' SIZE 10G 
REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 10G;

2. Switch the new UNDO to the "official" undo table space:

alter system set undo_tablespace='UNDO' scope=both;

3. drop the old undo table space:

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

hope this helps.

Answer 2

the error message mean the tablespace is in use, and since the undo tablespace is critical tablespace you need to shut it down first by using:

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 = 'UNDOTBSX02'
         ); 

then kill the SID for the undo tablespace by using

alter system kill session 'SID,serial'; --change the values of sid and serial by the ones  that being retrived from the previouse command

now you should be able to drop it.

Answer 3

You don't want to start dropping connections. How do you know what those connections are doing, and what might break if those connections are dropped.

You are better off waiting for sessions to start using the new tablespace. Eventually, the old undo tablespace will be not longer be in use. At that time you can drop it. If you need disk space you can try shrinking the undo tablespace, but that might not work. You can also get downtime to shutdown the database, bring up the database in restrict mode, then drop the old undo tablespace, that shutdown and startup the database unrestricted.

Answer 4

The answers provided by previous posters are all correct regarding the syntax to create a new UNDO tablespace and to switch system to the new UNDO tablespace.

The Oracle documentation states that the following statement will switch to the new UNDO tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = <name of new UNDO TS>;

e.g. for @Mohammad Jolani:

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS20140508;

However there are some restrictions to this switching which are documented as:

The switch operation does not wait for transactions in the old undo tablespace to commit. If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace. An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).

So according to A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. you would have to wait until Oracle has fully switched the current UNDO tablespace completely.

Try querying the status of the tablespace with :

select file#, ts#, status, bytes, substr(name, 0, 30) from v$datafile;

You might find that your old UNDO tablespace is still being used or in the PENDING OFFLINE state.

You could also try switching the UNDO tablespaces back and forth with:

ALTER SYSTEM SET UNDO_TABLESPACE = '';

An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment). If the parameter value for UNDO TABLESPACE is set to '' (two single quotes), then the current undo tablespace is switched out and the next available undo tablespace is switched in. Use this statement with care, because if there is no undo tablespace available, the SYSTEM rollback segment is used. This causes ORA-01552 errors to be issued for any attempts to write non-SYSTEM related undo to the SYSTEM rollback segment.

You can not force the switching unless you shut down your database. If Oracle thinks it still needs the old UNDO TS then so be it. Your system will already be writing to the new UNDO TS so there is no need to force anything unless you have special requirements.

Answer 5

Saw this link for solution. It worked for me!

Corresponding rollback segments need to be offline before you can drop the tablespace.

Also, restarting the database is required before new parameter values take effect.