Hi, I'm a junior DBA .
I have 1 problem.
I have create a new undo tablespace and set it to undo tablespace of instance
ora11> alter system set
undo_tablespace=undotbs2;
but we can not drop the original one:
ora11> drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1: ORA-01548: active rollback segment ‘_SYSSMU2_6654314$’ found, terminate dropping tablespace
how can solved this problem?
-------------------------------------------------------------------------------------------------------------------------------
For example:
1) find all active rollback segment in the undo tablespace to be dropped.
sys@anar> select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDOTBS1′
SEGMENT_NAME TABLESPACE_NAME STATUS
—————————— —————————— —————-
_SYSSMU10_820739558$ UNDOTBS1 OFFLINE
_SYSSMU9_2448906239$ UNDOTBS1 OFFLINE
_SYSSMU8_3066916762$ UNDOTBS1 OFFLINE
_SYSSMU7_892861194$ UNDOTBS1 OFFLINE
_SYSSMU6_1956589931$ UNDOTBS1 OFFLINE
_SYSSMU5_2919322705$ UNDOTBS1 OFFLINE
_SYSSMU4_3876247569$ UNDOTBS1 OFFLINE
_SYSSMU3_4245574747$ UNDOTBS1 OFFLINE
for eaxmple :_SYSSMU2_6654314$ UNDOTBS1 PARTLY AVAILABLE
2) set a parameter including all active rollback segments in init.ora file
_offline_rollback_segments=(_SYSSMU2_6654314$,…..)
3) shutdown database
4) Mount the database using pfile
sys@anar> startup mount pfile=’initanar.ora’
5) offline undo datafile for drop
sys@anar> alter database datafile ‘/app/oracle/oradata/anar/undotbs1.dbf’ offline drop;
6) open database
sys@anar> alter database open;
7)drop the undo segment
sys@anar> drop rollback segment “_SYSSMU2_6654314$”;
8)Add a new undo tablespace and set it as instance’s undo tablespace
sys@anar> alter system set undo_tablespace=undotbs2;
9) drop original undo tablespace
sql>Drop Tablespace undotbs1 including contents and datafiles;
10) remove the _offline_rollback_segments parameter fron pfile
参考:https://community.oracle.com/message/11232507#11232507
----------------------
(1) Take a Backup.
(2) Shutdown the DB
(2) Set UNDO_MANAGEMENT=MANUAL
(3) Set parameter OFFLINEROLLBACK_SEGMENT = (_SYSSMU1, _SYSSMU2...) etc for all the segments that were in the original UNDO tablespace. I checked v$rollname for the name of those active ones in the replacement UNDO TBS
(4) Open the DB
(5) Drop each RBS individually using 'DROP ROLLBACK SEGMENT "seg_name".
(6) Drop the UNDO tablespace.
(7) Shutdown the DB
(8) Comment out or remove the parameter OFFLINEROLLBACK_SEGMENT
(9) Set UNDO_MANAGEMENT back to AUTO
(10) Ensure the UNDO_TABLESPACE is set to your new UNDO TBS name
(11) Restart the DB
(12) Check the alert log for any errors - if all looks pukka then shutdown and take another backup. (You know it makes sense)
OFFLINEROLLBACK_SEGMENT is undocumented and support warn that its use should be restricted to those occasions when support ask you to use it. Be warned!
Edited by: Vazha_Mantua on 05-May-2009 06:14
Edited by: Vazha_Mantua on 05-May-2009 06:15
参考:https://community.oracle.com/message/3449574#3449574