删除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 参数...

159 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

  32. Pingback: details about adidas men mundial team turf cleats futsal black white shoes soccer spike 019228

  33. Pingback: balenciaga arena low legit check off 54 www.ceppe

  34. Pingback: ooarge femmes hppded jacket long kaki parka manteaux chaud manteau dhiver imperm茅able

  35. Pingback: court vantage vintage white mens

  36. Pingback: plus size yoga pants timeless contender capri

  37. Pingback: 褔邪褋褘 tissot. 泻褍锌懈褌褜 褌懈褋褋芯褌. tissot 褑械薪褘. tissot 泻褍锌懈褌褜. 屑邪谐邪蟹懈薪

  38. Pingback: 贸culos de sol gatinha lente transparente 贸culos de sol no

  39. Pingback: hornets finish road trip facing 1st place bucks

  40. Pingback: moscanueva 蟺慰蠀魏伪渭喂蟽慰 渭伪蠀蟻慰 渭蔚 蟿蟽蔚蟺蔚蟽 蟽蟿慰 蟽蟿畏胃慰蟽 l 蟺蟻慰蟽蠁慰蟻蔚蟽

  41. Pingback: tory burch patos studded thong sandal women

  42. Pingback: koszula check czerwona kratka

  43. Pingback: elomi energise underwire sports bra with j hook nude 42h model

  44. Pingback: chilli beans lan莽a cole莽茫o em parceria com o dj alok

  45. Pingback: n艖i 谩tmeneti kab谩t marikoo kuala marikoo s谩rga

  46. Pingback: t shirt guida t shirt moda uomo style

  47. Pingback: game mens emmitt smith navy blue alternate jersey nfl nike dallas cowboys 22 throwback personalized

  48. Pingback: sukienka orsay o艂贸wkowa bez r臋kaw贸w na sylwestra midi z dzianiny z dekoltem v

  49. Pingback: asos tall denim western a line shirt dress in indigo shopperboard

  50. Pingback: ccm tacks 9040 junior roller hockey skates

  51. Pingback: plecak m艂odzie偶owy coolpack unit diamond blue fioletowy

  52. Pingback: vintage cabelas moc toe gore tex botas 100

  53. Pingback: replacement lenses for oakley jupiter squared 2x pair combo

  54. Pingback: romantic milk dress cute fashion

  55. Pingback: dobreva womens unlined push up underwired support bra with lace at amazon womens clothing store

  56. Pingback: plateauschuhe billig kaufen

  57. Pingback: mens converse shorts cons military in olive

  58. Pingback: carabou extra long leg 36 quot mens trousers

  59. Pingback: il gufo grigio cappelli bambina corona in lana tricot con paillettes in linea

  60. Pingback: best resistol beaver western hat for sale in kerrville

  61. Pingback: nike sportswear huarache run se gs sneakers basse coral stardustrust pinklight

  62. Pingback: converse waterproof high tops for sale in sunnyvale

  63. Pingback: earthly concerns logo. bayou birkenstock logo

  64. Pingback: torba training id heathered tote. damskie trening

  65. Pingback: lost ink wide fit light grey platform heeled sandals

  66. Pingback: nike roshe grey red

  67. Pingback: mens running shoes sports sply 350 casual ultra boost retro walking

  68. Pingback: vestiti donna primavera autunnale lunghi fiori stampa moda mare vestito manica lunga v scollo spacco vita

  69. Pingback: womens chaps solid straight leg pants

  70. Pingback: looking for casual dresses see our summer dresses for women 2019 collection. check also summer kurti design collection for

  71. Pingback: Les 100 Ciels Sweter Lidia kaszmirowy zestawiaj z jeansami i dopasowanymi czarnymi spodniami modelka ma 173cm wzrostu i ma sobie rozmiar M 100% kaszmir 123534 JNGKWNM

  72. Pingback: bota ramarim cano curto bico fino salto grosso preta compre agora

  73. Pingback: knitting pattern for hat scarf mittens set

  74. Pingback: Drumohr Clothing for Men Spring - Summer 2019 Sweaters Model measures: chest 102 cm / height 188 cm / waist 93 cm merino wool Bluette 485119

  75. Pingback: womens trucker hats patagonia

  76. Pingback: kit 2 pulseiras e acess贸rios rel贸gio champion yot original

  77. Pingback: comprar converse blancas

  78. Pingback: blue jeans versace perfume masculino beleza e sa煤de jardim

  79. Pingback: jolie adidas fille t 35 vinted

  80. Pingback: ella bach茅 718 photos 36 reviews skin care service

  81. Pingback: taylor clip on sunglasses by mr foureyes angle shot optical glasses in black

  82. Pingback: botines al por mayor bucaramanga en mercado libre colombia

  83. Pingback: nmd nz 2019 nmd runner primeknit og black triple white nice

  84. Pingback: 銈炽偣銉併儱銉笺儬 銉兂銉戙兗銈?琚?銉欍儞銉?鐢枫伄瀛?琚淬儹銉炽儜銉笺偣 ilovebaby

  85. Pingback: nike air jordan i retro jeter

  86. Pingback: robe jackets for women

  87. Pingback: jordan nike mens cp3.ix gym red chllng red basketball shoe 10

  88. Pingback: funkytradition trendy hipster purple wayfarer sunglasses

  89. Pingback: tienda de ropa infantil fotos de stock paha l

  90. Pingback: conjunto pijama bride de sat茅n en blanco

  91. Pingback: champagne chiffon lace long prom dress. champagne evening dress prom24

  92. Pingback: l谩grima azul zafiro pendientes pendientes novia boda joyer铆a

  93. Pingback: vestido casual entallado cuello redondo estampado m茅xico

  94. Pingback: sacola de papel r铆gido tipo mickey com 14 x 8 x 19 cm 10 unidades

  95. Pingback: felix tenisky michael kors

  96. Pingback: flux power stretch tnf black the north face bewak

  97. Pingback: peep toe gucci cetim oliva original aer27

  98. Pingback: colar meia lua comprar em acess贸rios

  99. Pingback: best double stroller india

  100. Pingback: zara p贸l贸 rolling stones

  101. Pingback: french connection k茅k feh茅r csipkeruha 12

  102. Pingback: 蟽伪谓未维位喂伪 渭蠈未伪蟼 蟽蔚 渭喂伪 蟽蠁萎谓伪. 渭慰谓蟿苇蟻谓伪 蟽伪谓未维位喂伪 伪蟺蠈 蟿慰蠀蟼 christian

  103. Pingback: ten cate dames strings zwart 2 stuks

  104. Pingback: hockey womens john carlson white away authentic jersey 74 washington capitals 2018 stanley cup final champions

  105. Pingback: timberland moda italiana online timberland hombre custom classic shell boat zapatos

  106. Pingback: philippe coutinho photocall

  107. Pingback: speechless sequin dress

  108. Pingback: item 5 la blanca new blue womens size 14 threading along one piece swimsuit 119 703 la blanca new blue womens size 14 threading along one piece swimsuit

  109. Pingback: 45 pln koszulka legia ultras bia艂a koszulka legia ultras szara

  110. Pingback: narodowy wiatr torby torebki damskie znanych marek canvas tote casual pla偶a torby kobiet torba na zakupy

  111. Pingback: tw classic 99 statement fitted golf hat nike hats 57p8187 color white anthracite. nikecourt heritage 86 adjustable tennis

  112. Pingback: 卸械薪褋泻芯械 锌谢邪褌褜械 胁 锌芯谢 谐芯褉芯褏 锌褉芯写邪卸邪

  113. Pingback: boys ua freedom bfl t shirt 2 colors available

  114. Pingback: nike dallas cowboys authentic pro cut dez bryant men sz 48 thanksgiving jersey

  115. Pingback: black prom dresses mermaid shape with heavy beaded work keyhole back zipper puffy tulle trumpet evening

  116. Pingback: puma rebel logo leggings

  117. Pingback: nike air jordan 11 retro low easter

  118. Pingback: adidas kids astro trainers size 10 5.5 new boxed football

  119. Pingback: zapatos coronel tapioca marino zapatos sport hombre

  120. Pingback: girls unicorn graphic t shirt matalan

  121. Pingback: massimo dutti skirts at reasonable prices

  122. Pingback: anna field robe de cocktail dark blue robes de soir茅e site de vetement classe femme

  123. Pingback: the new balance furon 3.0 and visaro 2.0 will both be available at world soccer shop

  124. Pingback: lamberti 7002 kad谋n zincirli postac谋 莽anta vizon fiyatlar谋

  125. Pingback: new versace 3220 womens ladies cat eye full rim havana gold black frame demo lenses 52 16 140 eyeglasses eyeglass frame

  126. Pingback: 贸ticas carol abre primeira loja conceito novarejo

  127. Pingback: boys adidas t shirt in b26 birmingham for 6.00 for sale shpock

  128. Pingback: 泻褍锌懈褌褜. 胁芯谢械泄斜芯谢褜薪褘械 泻褉芯褋褋芯胁泻懈 卸械薪褋泻懈械 asics gel task mt 400

  129. Pingback: regatta puddle childrens all in one waterproof suit navy kids

  130. Pingback: womens adidas linear tights black womens clothing

  131. Pingback: sheath column v neck chiffon short sleeves knee length mother of the bride

  132. Pingback: 锌谢邪褌褜褟 谢械褌薪懈械 写谢懈薪薪褘械

  133. Pingback: sapatilhas puma de mulher coloridas comprar online

  134. Pingback: 啪lt茅 unisex tenisky converse chuck taylor all star zna膷ky converse

  135. Pingback: des verres de lunettes incolores qui filtrent la lumi猫re bleue est ce possible

  136. Pingback: spectacular sales for seafolly womens inka rib sweetheart tankini

  137. Pingback: the official adidas pogba predator kids 18 fg soccer cleats core black olive

  138. Pingback: men hugo boss gerald regular fit negro etiquetas men camisa talla 18 34

  139. Pingback: puma bape x disc blaze cooperation limited 3m reflective green camouflage mens and womens shoes

  140. Pingback: nurse ghost i will stab you shirt funny halloween tshirt clothing

  141. Pingback: vale by black veil brides t shirt gift tees unisex adult cool tee shirts maker cheap

  142. Pingback: fiona classic fra anonymous copenhagen k酶b online p氓

  143. Pingback: 2017 sexy one piece swimsuit swimwear women bathing suit swim vintage summer beach wear print bandage monokini swimsuit

  144. Pingback: samsung qe55q90r druh媒 produkt pod木a vlastn茅ho v媒beru v cene do 799 so z木avou 50

  145. Pingback: logitech silent multi device bluetooth mouse

  146. Pingback: boss bodywear logo crew sweatshirt by boss bodywear

  147. Pingback: under armour mens skysweeper wader hooded sweatshirt polyester

  148. Pingback: 芯斜褍胁褜 写谢褟 写械胁芯褔械泻 demar rozetka. 泻褍锌懈褌褜 芯斜褍胁褜 写谢褟 写械胁芯褔泻懈 demar 褋

  149. Pingback: 啪lt茅 lodi膷ky z br煤senej ko啪e a944 861

  150. Pingback: affordable jdy rose print high neck blouse

  151. Pingback: black and clear mules

  152. Pingback: stylish royal blue long sleeve homecoming dress

  153. Pingback: earbuds in ear headphones earphones noise isolating with powerful massive bass driver

  154. Pingback: blusa de frio casaco moletom time real madrid futebol novo r 89

  155. Pingback: 卸械薪褋泻懈械 泻褉芯褋褋芯胁泻懈 胁 褋褌懈谢械 fila disruptor 2 white purple 褎懈芯谢械褌芯胁褘械 懈薪褌械褉薪械褌 屑邪谐邪蟹懈薪

  156. Pingback: h samuel 9ct white gold engagement ring size n

  157. Pingback: second hand s茅zane bags joli closet

  158. Pingback: sidenkjol delicate nude

  159. Pingback: vipkid orange teaching t shirt vipkid us store

Leave Comment