Can not drop UNDOSTBS
来源:互联网 发布:人工智能接口 编辑:程序博客网 时间:2024/06/07 19:18
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
- Can not drop UNDOSTBS
- ORA-12919: Can not drop the default permanent tablespace
- 053-23 How can you reverse the effects of an ALTER DISKGROUP … DROP DISK command if it has not yet c
- 23 How can you reverse the effects of an ALTER DISKGROUP … DROP DISK command if it has not yet compl
- checkbox can not unchecked
- can not find -lXXX
- Can not be copied
- adduser can not find
- can not move gridview
- can't/cannot/can not 区别
- drop
- var can not be resolved
- ResultSet can not re-read
- Fuzzy can not go on
- SUN workstation can not boot
- can not find interface declaration
- Can not issue data manipulation......
- The import ....... can not resolved
- BZOJ3398 [Usaco2009 Feb]Bullcow 牡牛和牝牛
- MySQL双主一致性架构优化
- HDU
- 编程范式13听课笔记——缓冲区溢出和C++接受可变参数
- BottomTabBar的使用
- Can not drop UNDOSTBS
- BZOJ1054 移动玩具 BFS+hash
- angularjs购物车全选反选
- 取消chrome浏览器下input和textarea的默认样式
- label的作用是什么?是怎么用的?
- 如何一句话惹毛程序员
- Recvclerview上拉加载
- 阿里云搭建基于PPTP的VPN(Windows Server 2008)
- NPOI 2.0 使用教程