undo表空間故障特殊恢復ORA

1、undo表空間故障特殊恢復（二）這個測試的是instanee recover （單實例里就是crash recovery）的恢復需要故障undo里 的數(shù)據(jù)，一般的情況instanee recover使用聯(lián)機日志文件的，當發(fā)生多版本更新的故障，也可需要回 滾段數(shù)據(jù)的。測試表SQL select coun t from tabtest;COUNT（1）17732SQL in sert into tabtest select * from tabtest where row num in sert into tabtest select * from tabtest where row num sh

2、utdow n abortORACLE例程已經(jīng)關(guān)閉。SQL quit從 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProductionWith the Partitio ning, OLAP, Data Mining and Real Application Testi ng optio ns 斷開 只有退出sqlplus環(huán)境，才能更改回滾段數(shù)據(jù)文件，刪除回滾數(shù)據(jù)文件，模擬回滾段丟失C:/Docume nts and Sett in gs/Adm ini stratorsqlplus /as sysdbaSQL*Pl

3、us: Release 10.2.0.4.0 - Production on 星期四 9 月 9 22:23:50 2010Copyright (c) 1982, 2007, Oracle. All Rights Reserved. 已連接到空閑例程。SQL startupORACLE例程已經(jīng)啟動。Total System Global Area 574619648 bytesFixed SizeVariable SizeDatabase BuffersRedo BuffersTotal System Global Area 574619648 bytesFixed SizeVariable

4、SizeDatabase BuffersRedo Buffers1297944 bytes234881512 bytes331350016 bytes7090176 bytes數(shù)據(jù)庫裝載完畢。ORA-01157:無法標識/鎖定數(shù)據(jù)文件2 -請參閱DBWR跟蹤文件ORA-01110:數(shù)據(jù)文件2: E:/0RACLE/PR0DUCT/10.2.0/0RADATA/TEST/UND0TBS13.DBF錯誤出現(xiàn)了，因為我已經(jīng)刪除文件“E:/0RACLE/PR0DUCT/10.2.0/0RADATA/TEST/UND0TBS13.DBF”SQL shutdow n abort;ORACLE例程已經(jīng)關(guān)閉。

5、這里startup force是模擬in sta nee recover時需要回滾段里的數(shù)據(jù)情況，為什么能模擬出來, 我也不清楚，我是經(jīng)過多次測試發(fā)現(xiàn)的，哪位高人知道原因，請指點？ ？SQL startup force;ORACLE例程已經(jīng)啟動。Total System Global Area 574619648 bytesFixed SizeVariable SizeDatabase BuffersTotal System Global Area 574619648 bytesFixed SizeVariable SizeDatabase Buffers1297944 bytes234881

6、512 bytes331350016 bytesRedo Buffers7090176 bytesRedo Buffers7090176 bytes數(shù)據(jù)庫裝載完畢。ORA-01157:無法標識/鎖定數(shù)據(jù)文件2 -請參閱DBWR跟蹤文件ORA-01110:數(shù)據(jù)文件2:E:/ORACLE/PRODUCT/10.2.0/ORADATA/TEST/UNDOTBS13.DBFSQL alter database datafile 2 offli ne drop;數(shù)據(jù)庫已更改。SQL alter database ope n;alter database ope n*第1行出現(xiàn)錯誤：ORA-01092:

7、ORACLE實例終止。強制斷開連接這里instanee recover需要回滾段里數(shù)據(jù)，但是回滾段丟失，所以實例出現(xiàn)異常直接關(guān)閉SQL startupORA-24324:未初始化服務(wù)句柄ORA-01041:內(nèi)部錯誤，hostdef擴展名不存在SQL quit從 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - ProduetionWith the Partitio ning, OLAP, Data Mining and Real Application Testi ng optio ns 斷開C:/Docume nts an

8、d Sett in gs/Adm ini stratorsqlplus /as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on 星期四 9 月 9 22:27:05 2010Copyright (c) 1982, 2007, Oracle. All Rights Reserved.已連接到空閑例程。SQL startupORACLE例程已經(jīng)啟動。Total System Global Area 574619648 bytesFixed Size1297944 bytesVariable Size234881512 bytesDatabase

9、 Buffers331350016 bytesRedo Buffers7090176 bytes數(shù)據(jù)庫裝載完畢。ORA-01092: ORACLE實例終止。強制斷開連接SQL在看看alertlog文件，錯誤信息如下：Errors in file e:/oracle/product/10.2.0/admi n/test/bdump/test_sm on_1828.trc:ORA-00604: error occurred at recursive SQL level 1ORA-00376: file 2 cannot be read at this timeORA-01110: data fil

10、e 2:E:/0RACLE/PR0DUCT/10.2.0/0RADATA/TEST/UND0TBS13.DBFThu Sep 09 22:27:17 2010Errors in file e:/oracle/product/10.2.0/admi n/test/udump/test_ora_3844.trc:ORA-00604:遞歸SQL級別1出現(xiàn)錯誤ORA-00376:此時無法讀取文件2ORA-01110:數(shù)據(jù)文件2:E:/ORACLE/PRODUCT/1020/ORADATA/TEST/UNDOTBS13.DBFError 604 happe ned duri ng db ope n, s

11、hutt ing dow n databaseUSER: term in ati ng in sta nee due to error 604Thu Sep 09 22:27:17 2010Errors in file e:/oracle/product/10.2.0/adm in /test/bdump/test_pm on _5952.trc:ORA-00604: error occurred at recursive SQL levelThu Sep 09 22:27:18 2010Errors in file e:/oracle/product/10.2.0/adm in /test/

12、bdump/test_reco_3988.trc:ORA-00604: error occurred at recursive SQL levelThu Sep 09 22:27:18 2010Errors in file e:/oracle/product/10.2.0/adm in /test/bdump/test_ckpt_5320.trc:ORA-00604: error occurred at recursive SQL levelThu Sep 09 22:27:18 2010Errors in file e:/oracle/product/10.2.0/adm in /test/

13、bdump/test_lgwr_1312.trc:ORA-00604: error occurred at recursive SQL levelThu Sep 09 22:27:18 2010Errors in file e:/oracle/product/10.2.0/admi n/test/bdump/test_mma n_4972.trc:ORA-00604: error occurred at recursive SQL levelThu Sep 09 22:27:18 2010Errors in file e:/oracle/product/10.2.0/admi n/test/b

14、dump/test_dbw0_4060.trc:ORA-00604: error occurred at recursive SQL levelThu Sep 09 22:27:18 2010Errors in file e:/oracle/product/10.2.0/adm in /test/bdump/test_psp0_1276.trc:ORA-00604: error occurred at recursive SQL levelIn sta nee termi nated by USER, pid = 3844ORA-1092 sig nailed duri ng: ALTER D

15、ATABASE OPEN.根據(jù)錯誤信息知道回滾段數(shù)據(jù)故障會丟失，如果有備份的話，直接recover datafile很簡單 就可以恢復，這里假設(shè)沒有備份，那該如何處理呢？現(xiàn)在因為回滾段數(shù)據(jù)文件的故障，導致數(shù)據(jù)庫無法打開，那我們想辦法讓 數(shù)據(jù)庫的檢查繞過或修復回滾段數(shù)據(jù)文件，數(shù)據(jù)庫open時，所有的數(shù)據(jù)文件都要online,如果不能online，那就要恢復或者 直接把數(shù)據(jù)文件drop掉。我們這里沒有備份不能恢復，那就drop掉故障的數(shù)據(jù)文件，然后讓數(shù)據(jù)可以打開，然后在重新創(chuàng)建新的 回滾段表空間，讓數(shù)據(jù)庫使用新的回滾段表空間，最后在把有故障的回滾段表空間刪除。這是解決問題的基本思路。操作步驟如下:

16、修改參數(shù)文件，添加隱含參數(shù)我這里只用了 “_corrupted_rollback_segments在文本參數(shù)文件inittest.ora的最后追加如下一條記錄*._corrupted_rollback_segme nts=( _SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSS MU15$,_SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$,_SY SSMU11$)SQL quit從 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produ

17、ctionWith the Partitio ning, OLAP, Data Mining and Real Application Testi ng optio ns 斷 開C:/Docume nts and Sett in gs/Adm ini stratorsqlplus /as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on 星期四 9 月 9 22:32:43 2010Copyright (c) 1982, 2007, Oracle. All Rights Reserved.已連接到空閑例程。SQL startup pfile=

18、E:/oracle/product/10.2.0/db_1/database/inittest.oraORACLE例程已經(jīng)啟動。Total System Global Area 574619648 bytesFixed Size1297944 bytesVariable Size234881512 bytesDatabase Buffers331350016 bytesRedo Buffers7090176 bytes數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開?，F(xiàn)在數(shù)據(jù)庫已經(jīng)打開，剩下就簡單多了，創(chuàng)建新的回滾段表空間，刪除故障回滾段表空間SQLSQL show parameter undoNAMETYPE

19、VALUEun do_ma nageme ntun do_rete nti onun do_tablespacestri ngAUTOin teger 900stri ngUNDOTBS1SQL create undo tablespace un dotbs4 datafile E:/ORACLE/PRODUCT/1O.2.O/ORADATA/ TEST/UNDOTBS41.DBF size 50m ;表空間已創(chuàng)建。SQL show parameter undoNAMETYPE VALUEun do_ma nageme ntstri ngAUTOun do_rete nti onin tege

20、r 900un do_tablespacestr ingUNDOTBS1SQL alter system set un do_tablespace=un dotbs4 scope=spfile;alter system set un do_tablespace=un dotbs4 scope=spfile*第1行出現(xiàn)錯誤：ORA-32001:已請求寫入SPFILE，但是在啟動時未指定SPFILESQL alter system set un do_tablespace=un dotbs4;系統(tǒng)已更改。SQLSQL shutdow n immediate數(shù)據(jù)庫已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫。ORACL

21、E例程已經(jīng)關(guān)閉。SQL startup pfile=E:/oracle/product/10.2.0/db_1/database/inittest.oraORACLE例程已經(jīng)啟動。Total System Global Area 574619648 bytesFixed Size1297944 bytesVariable Size234881512 bytesDatabase Buffers331350016 bytesRedo Buffers7090176 bytes數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開。檢查當前使用回滾段情況SQL select segme nt_n ame,status,tab

22、lespace_ name from dba_rollback_segs;SYSTEMSTATUS TABLESPACE_NAMESEGMENT_NAMESTATUS TABLESPACE_NAMESYSTEMSTATUS TABLESPACE_NAMESYSSMU1$NEEDS RECOVERYUNDOTBS1SYSSMU2$NEEDS RECOVERYUNDOTBS1.SYSSMU3$NEEDS RECOVERYUNDOTBS1.SYSSMU4$NEEDS RECOVERYUNDOTBS1SYSSMU5$NEEDS RECOVERYUNDOTBS1SYSSMU6$NEEDS RECOVER

23、YUNDOTBS1SYSSMU7$NEEDS RECOVERYUNDOTBS1SYSSMU8$NEEDS RECOVERYUNDOTBS1SYSSMU9$NEEDS RECOVERYUNDOTBS1SYSSMU10$NEEDS RECOVERYUNDOTBS1ONLINE SYSTEMSEGMENT_NAMESYSSMU11$ONLINEUNDOTBS4SYSSMU12$ONLINEUNDOTBS4SYSSMU13$ONLINEUNDOTBS4SYSSMU14$ONLINEUNDOTBS4SYSSMU15$ONLINEUNDOTBS4SYSSMU16$ONLINEUNDOTBS4SYSSMU1

24、7$ONLINEUNDOTBS4SYSSMU18$ONLINEUNDOTBS4SYSSMU19$ONLINEUNDOTBS4SYSSMU20$ONLINEUNDOTBS4已選擇21行。SQL select * from v$recover_file;FILE# ONLINE ONLINE_ERRORCHANGE#TIME2 OFFLINE OFFLINEFILE NOT FOUND08 OFFLINE OFFLINE1689870107-9 月-10FILE# ONLINE ONLINE_ERRORCHANGE#TIMESQL show parameter undoNAMETYPE VALUE

25、un do_ma nageme ntstri ng AUTOun do_rete nti onin teger900un do_tablespacestr ingUNDOTBS4SQL drop tablespace un dotbs4 in clud ing contents and datafiles;drop tablespace un dotbs4 in clud ing contents and datafiles*第1行出現(xiàn)錯誤：ORA-30013:還原表空間UNDOTBS4當前正在使用中SQLSQL shutdow n immediate;數(shù)據(jù)庫已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫。ORAC

26、LE例程已經(jīng)關(guān)閉。再用修改文本參數(shù)文件，添加隱含參數(shù)在文本參數(shù)文件inittest.ora的最后追加如下一條記錄*._corrupted_rollback_segme nts=( _SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU1$)啟動數(shù)據(jù)庫SQL startup pfile=E:/oracle/product/10.2.0/db_1/database/inittest.oraORACLE例程已經(jīng)啟動。Total System Global Ar

27、ea 574619648 bytesFixed SizeVariable SizeDatabase BuffersRedo BuffersTotal System Global Area 574619648 bytesFixed SizeVariable SizeDatabase BuffersRedo Buffers1297944 bytes234881512 bytes331350016 bytes7090176 bytes數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開。SQL drop tablespace un dotbs1 in clud ing contents and datafiles; 表空

28、間已刪除。SQL select segme nt_n ame,status,tablespace_ name from dba_rollback_segs;SEGMENT_NAMESTATUS TABLESPACE_NAMEONLINEONLINEONLINEONLINESYSTEM_SYSSMU11$_SYSSMU12$SYSTEMUNDOTBS4UNDOTBS4.SYSSMU13$ONLINEUNDOTBS4.SYSSMU14$ONLINEUNDOTBS4SYSSMU15$ONLINEUNDOTBS4SYSSMU16$ONLINEUNDOTBS4SYSSMU17$ONLINEUNDOTBS

29、4SYSSMU18$ONLINEUNDOTBS4SYSSMU19$ONLINEUNDOTBS4SYSSMU20$ONLINEUNDOTBS4已選擇11行。SQL shutdow n immediate; 數(shù)據(jù)庫已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫。ORACLE例程已經(jīng)關(guān)閉。SQL startup pfile=E:/oracle/product/10.2.0/db_1/database/inittest.oraORACLE例程已經(jīng)啟動。Total System Global Area 574619648 bytesFixed SizeVariable SizeDatabase BuffersRedo Buf

30、fersTotal System Global Area 574619648 bytesFixed SizeVariable SizeDatabase BuffersRedo Buffers1297944 bytes234881512 bytes331350016 bytes7090176 bytes數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開。查看如下都正常了吧SQL select segme nt_n ame,status,tablespace_ name from dba_rollback_segs;SEGMENT_NAMESTATUS TABLESPACE_NAMESYSTEMONLINESYSTE

31、M.SYSSMU11$ONLINEUNDOTBS4.SYSSMU12$ONLINEUNDOTBS4SYSSMU13$ONLINEUNDOTBS4SYSSMU14$ONLINEUNDOTBS4SYSSMU15$ONLINEUNDOTBS4SYSSMU16$ONLINEUNDOTBS4SYSSMU17$ONLINEUNDOTBS4SYSSMU18$ONLINEUNDOTBS4SYSSMU19$ONLINEUNDOTBS4SYSSMU20$ONLINEUNDOTBS4已選擇11行。SQL select coun t from tabtest;COUNT(1)21732SQL create spfil

32、e from pfile;文件已創(chuàng)建。SQL create spfile from pfile;文件已創(chuàng)建。SQL shutdow n immediate;數(shù)據(jù)庫已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫。ORACLE例程已經(jīng)關(guān)閉。SQL startupORACLE例程已經(jīng)啟動。Total System Global Area 574619648 bytesFixed Size1297944 bytesVariable Size234881512 bytesDatabase Buffers 331350016 bytesRedo Buffers數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開。SQL7090176 bytes已經(jīng)

33、恢復ok說明：其實在恢復數(shù)據(jù)庫后的善后工作很重要，不要留尾巴。做事要認真些，完美些，不要留下遺 憾。細節(jié)很重要善后處理：0.檢查數(shù)據(jù)庫、實例的狀態(tài)(v$instance,v$database),確認是否還有其他文件恢復 (v$recover_file)檢查是否有無效的對象定義檢查是否有失效的索引查看應(yīng)用腳本是否正常檢查系統(tǒng)是否有死鎖檢查系統(tǒng)的各資源是否正常查看系統(tǒng) log、alertlog、tracelog 等文件Oracle異常：ORA-01092: ORACLE實例終止。強制斷開連接一解決方案問題：SQL connect sys/password RAOASYS as sysdba已連接到

34、空閑例程。SQL startupORACLE 例程已經(jīng)啟動。Total System Global Area 109051904 bytesFixed Size1295272 bytesVariable Size92277848 bytesDatabase Buffers8388608 bytesRedo Buffers7090176 bytes數(shù)據(jù)庫裝載完畢。ORA-01092: ORACLE 實例終止。強制斷開連接 原因：從 10.2.1 升級到 10.2.4 后沒有執(zhí)行 catupgrd.sql 和 catalog.sql Solution：1.以 startup upgrade 打開數(shù)

35、據(jù)庫2以sysdba運行升級數(shù)據(jù)字典腳本和創(chuàng)建數(shù)據(jù)字典腳本D:oracleproduct10.2.0db_1RDBMSADMINcatupgrd.sql和D:oracleproduct10.2.0db_1RDBMSADMINcatalog.sql今天使用 Oracle 遇到了這個問題 ora-01033:oracle initialization or shutdown in progress,經(jīng)過分析研究終于解決了！ ！ 首先：問題的產(chǎn)生原因，出現(xiàn)這個錯誤是因為我將D:OracleappAdministratororadataorcl 下的一個文件刪除掉后出現(xiàn)的。錯誤原因：利用sql創(chuàng)建表空間時由于粗心大意把*.DBF,寫成了*.PDF，真是可 笑死了，于是我將目錄下的*.PDF文件刪除，當再次登陸ORCL數(shù)據(jù)庫的時候就報錯了： ORA-01033: ORACLE initialization or shutdown in progress分析：這個錯誤的原因應(yīng)該是Oracle在啟動后，用戶登錄時是要將方案中原有配置信息裝載進入，裝載過程中配置中有文件未找到，所以就報出錯誤。解決問題：運行cmdC:UsersAdministratorsqlplus /nologSQL*P