| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Drop UNDO tablespace
Sami,
Have you bounced the database since you created the new UNDO tablespace? It might clear out the old undo so that you can drop it.
Short of that, I would submit an SR to Oracle support and see what they have to say.
Tom
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sami Seerangan
Sent: Tuesday, June 06, 2006 9:46 AM
To: ORACLE-L
Subject: Drop UNDO tablespace
Env: Oracle 9.2.0.6, 3 Node RAC, Sun OS 2.9
We want to drop this undo tablespace UNDOTBS1 which is not part on any
instance.
But when we try to drop the tablespace it is throwing "ORA-01548:
active rollback segment '_SYSSMU1$' found, terminate dropping
tablespace". Is there a way to overcome this?
The datafile for UNDOTBS1 segments got corrupted and in "NEEDS RECOVERY" state for more than a month. We created new undo tablespace (UNDOTBS1_NEW) that time and UNDOTBS1 tablespace not required any more.
Alert_log
SMON: about to recover undo segment 1 SMON: mark undo segment 1 as needs recovery SMON: about to recover undo segment 2 SMON: mark undo segment 2 as needs recovery
ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u06/oradata/pwpb4/undo01.dbf'
SQL> show parameter undo
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_suppress_errors boolean FALSE undo_tablespace string UNDOTBS1_NEW
SQL>select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME ------------------------------ ---------------- ------------------------------ SYSTEM ONLINE SYSTEM _SYSSMU1$ NEEDS RECOVERY UNDOTBS1 _SYSSMU2$ NEEDS RECOVERY UNDOTBS1 _SYSSMU3$ NEEDS RECOVERY UNDOTBS1 _SYSSMU4$ NEEDS RECOVERY UNDOTBS1 _SYSSMU5$ NEEDS RECOVERY UNDOTBS1 _SYSSMU6$ NEEDS RECOVERY UNDOTBS1 _SYSSMU7$ NEEDS RECOVERY UNDOTBS1 _SYSSMU8$ NEEDS RECOVERY UNDOTBS1 _SYSSMU9$ NEEDS RECOVERY UNDOTBS1 _SYSSMU10$ NEEDS RECOVERY UNDOTBS1 _SYSSMU11$ ONLINE UNDOTBS2 _SYSSMU12$ ONLINE UNDOTBS2 _SYSSMU13$ ONLINE UNDOTBS2 _SYSSMU14$ ONLINE UNDOTBS2 _SYSSMU15$ ONLINE UNDOTBS2 _SYSSMU16$ ONLINE UNDOTBS2 _SYSSMU17$ ONLINE UNDOTBS2 _SYSSMU18$ ONLINE UNDOTBS2 _SYSSMU19$ ONLINE UNDOTBS2 _SYSSMU20$ ONLINE UNDOTBS2 _SYSSMU21$ ONLINE UNDOTBS3 _SYSSMU22$ ONLINE UNDOTBS3 _SYSSMU23$ ONLINE UNDOTBS3 _SYSSMU24$ ONLINE UNDOTBS3 _SYSSMU25$ ONLINE UNDOTBS3 _SYSSMU26$ ONLINE UNDOTBS3 _SYSSMU27$ ONLINE UNDOTBS3 _SYSSMU28$ ONLINE UNDOTBS3 _SYSSMU29$ ONLINE UNDOTBS3 _SYSSMU30$ ONLINE UNDOTBS3 _SYSSMU31$ ONLINE UNDOTBS1_NEW _SYSSMU32$ ONLINE UNDOTBS1_NEW _SYSSMU33$ ONLINE UNDOTBS1_NEW _SYSSMU34$ ONLINE UNDOTBS1_NEW _SYSSMU35$ ONLINE UNDOTBS1_NEW _SYSSMU36$ ONLINE UNDOTBS1_NEW _SYSSMU37$ ONLINE UNDOTBS1_NEW _SYSSMU38$ ONLINE UNDOTBS1_NEW _SYSSMU39$ ONLINE UNDOTBS1_NEW _SYSSMU40$ ONLINE UNDOTBS1_NEW
41 rows selected.
SQL> select file_name,TABLESPACE_NAME,status from dba_data_files
where TABLESPACE_NAME like 'UNDO%'
2 ;
FILE_NAME TABLESPACE_NAMESTATUS
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping tablespace
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 06 2006 - 08:50:29 CDT
 |
 |