删除Oracle Undo表空间

近期处理了一次删除、重建Undo表空间的事情,有些细节还是值得记下来备忘。事情的起因是工程师需要将分布在不同ASM磁盘组里的Oracle数据库文件,迁移到新建的ASM磁盘组,操作过程中,错误的删除了Undo磁盘组的所有文件。
版本 11.2.0.4, RAC环境——测试、准生产环境;下文操作不保证在“生产环境”无害。

Step 1:

考虑新建Undo Tablespace,然后修改各个实例(Instance)的Undo_Tablespace 参数,使用新的Undo,然后删除老的Undo 表空间。

Step 2:

发现,此时无法新建表空间,报错Undo表空间的数据文件不可访问。

分析:我没有去细究原因,我想原因可能有两个,1是因为使用自动Undo管理,创建表空间的操作也需要获取Undo,所以报错??2是因为新建表空间,会触发类似checkpoint之类操作,有Active Tx还需要访问Undo??

Step 3:

 

修改undo_management=manual,再重试之前步骤

alter system set undo_management=manual sid=‘*’;

Step 4:

drop tablespace undotbs1 including contents and datafiles;

Step 5:

drop tablespace UNDOTBS2 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment ‘_SYSSMU1_3780397527$’ found, terminate

dropping tablespace

删除实例(Instance 2)对应的Undo表空间时出错了,ORA-01548 有Active 回滚段。我们可以查询dba_rollback_segs查看这个回滚段的状态。

Step 6:

drop rollback segment ‘_SYSSMU1_3780397527$’;

指定删除该回滚段,操作也报错,错误代码我不记得了——常规操作进行不下去了。

Step 7:

只能尝试一下 _offline_rollback_segments 或者 _corrupted_rollback_segments 之类的隐藏参数了。

Step 8:

生产参数文件(pfile),便于编辑参数配置

SQL> create pfile=‘?/dbs/init_temp.ora’ from spfile;

编辑$ORACLE_HOME/dbs/init_temp.ora文件,添加隐藏参数:

*._offline_rollback_segments=(‘_SYSSMU…’)

Step 9:

SQL> startup pfile=‘?/dbs/init_temp.ora'

使用修改过的参数文件启动数据库(想启动,先得关闭!)

Step 10:

这个时候,如果你直接去删除undo 表空间,效果跟之前一样,仍然是ora-01548错误,但是drop rollback segment 操作可以进行……

Step 11:

其他收尾工作,新建undo 表空间,改回使用spfile管理参数,修改undo 参数...

31 Comments

  1. Pingback: freight forwarder thailand

  2. Pingback: 1鍐嗭綖 鑹搧 mikihouse 銉熴偔銉忋偊銈筼ld navy 銈兗銉儔銉嶃偆銉撱兗

  3. Pingback: negozio on ine in vendita puma suede c assic puiijaxk uomo sneakers b ue f ower

  4. Pingback: camisa f uminense fc terceiro oficia mascu ina

  5. Pingback: buhh store 贸culos harry potter sem grau

  6. Pingback: baratas chamarras mezclilla de mujer color gris en ofertas

  7. Pingback: style design lab rakuten market store manic panic japan manic panic

  8. Pingback: echt heren adidas superstar zwart trainers maat 10

  9. Pingback: brinny 茅charpe chale femme cachemire chaud automne hiver grand plaid tissu glands foulard

  10. Pingback: details about mens columbia xco hiking fishing s s button front shirt small s plaid

  11. Pingback: adidas scarpe neo 8k grigio chiaro mec shopping online

  12. Pingback: open bar aden and anais office photo glassdoor

  13. Pingback: specialv忙rkt酶j austin healey

  14. Pingback: adidas zx 700 dames roze by9389 roze sneakers dames. 20 . adidas zx 700 wit

  15. Pingback: b臎啪eck茅 oble膷en铆 p谩nsk茅 craft brilliant windbreaker top 1903600 1851

  16. Pingback: babahu x1 el cepillo de dientes el茅ctrico ai es un 茅xito en

  17. Pingback: district made ladies perfect tri racerback tank top

  18. Pingback: bordeauxsk谩 doga pozemek

  19. Pingback: authentic gucci vintage old gucci wrist watch ladies women quartz gold black

  20. Pingback: nike sb aero cap pro trucker white black boardvillage

  21. Pingback: fuzz beanie baby

  22. Pingback: long evening dresses ever pretty

  23. Pingback: cyber week savings tory burch minnie travel ballet flat arabian

  24. Pingback: hugo boss boss mens double twill logo cap hats

  25. Pingback: adidas comfort sandals

  26. Pingback: adidas group had a solid first quarter at its adidas

  27. Pingback: mens air jordan clutch gym red white black lifestyle shoes

  28. Pingback: trading cards 1496 ty beanie babies 1st edition series 2 collectors cards factory sealed box 1999 buy it now only 18 on ebay trading cards beanie

  29. Pingback: nouveau style san marina cuir femme noir rock bottines low boots motards

  30. Pingback: genuine leather lace up gladiator sandals women vintage style peep toe flat heel bootie sandals designer summer shoes woman

  31. Pingback: january 2018 jordan release dates

Leave Comment