Drop UNDO tablespace
- From: "Sami Seerangan" <dba.orcl@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 Jun 2006 09:45:34 -0400
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
=======
Tue Jun 6 13:22:40 2006
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
.
.
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
Tue Jun 6 13:22:41 2006
Errors in file /opt/oracle/admin/pwpb4/bdump/pwpb41_smon_26754.trc:
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_NAME STATUS
----------------------------------------
------------------------------ ---------
/u06/oradata/pwpb4/undo01.dbf UNDOTBS1
AVAILABLE
/u06/oradata/pwpb4/undo02.dbf UNDOTBS2
AVAILABLE
/u06/oradata/pwpb4/undo03.dbf UNDOTBS3
AVAILABLE
/u06/oradata/pwpb4/undo01_new.dbf UNDOTBS1_NEW
AVAILABLE
SQL> alter tablespace UNDOTBS1 offline immediate;
Tablespace altered.
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate
dropping tablespace
--
//www.freelists.org/webpage/oracle-l
Other related posts:
