undo表空間故障特殊恢復(fù)ORA

1、undo表空間故障特殊恢復(fù)（二）這個(gè)測(cè)試的是instanee recover （單實(shí)例里就是crash recovery）的恢復(fù)需要故障undo里 的數(shù)據(jù)，一般的情況instanee recover使用聯(lián)機(jī)日志文件的，當(dāng)發(fā)生多版本更新的故障，也可需要回 滾段數(shù)據(jù)的。測(cè)試表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 斷開(kāi) 只有退出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)啟動(dò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ù)庫(kù)裝載完畢。ORA-01157:無(wú)法標(biāo)識(shí)/鎖定數(shù)據(jù)文件2 -請(qǐng)參閱DBWR跟蹤文件ORA-01110:數(shù)據(jù)文件2: E:/0RACLE/PR0DUCT/10.2.0/0RADATA/TEST/UND0TBS13.DBF錯(cuò)誤出現(xiàn)了，因?yà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í)需要回滾段里的數(shù)據(jù)情況，為什么能模擬出來(lái), 我也不清楚，我是經(jīng)過(guò)多次測(cè)試發(fā)現(xiàn)的，哪位高人知道原因，請(qǐng)指點(diǎn)？ ？SQL startup force;ORACLE例程已經(jīng)啟動(dò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ù)庫(kù)裝載完畢。ORA-01157:無(wú)法標(biāo)識(shí)/鎖定數(shù)據(jù)文件2 -請(qǐng)參閱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ù)庫(kù)已更改。SQL alter database ope n;alter database ope n*第1行出現(xiàn)錯(cuò)誤：ORA-01092:

7、ORACLE實(shí)例終止。強(qiáng)制斷開(kāi)連接這里instanee recover需要回滾段里數(shù)據(jù)，但是回滾段丟失，所以實(shí)例出現(xiàn)異常直接關(guān)閉SQL startupORA-24324:未初始化服務(wù)句柄ORA-01041:內(nèi)部錯(cuò)誤，hostdef擴(kuò)展名不存在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 斷開(kāi)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)啟動(dòng)。Total System Global Area 574619648 bytesFixed Size1297944 bytesVariable Size234881512 bytesDatabase

9、 Buffers331350016 bytesRedo Buffers7090176 bytes數(shù)據(jù)庫(kù)裝載完畢。ORA-01092: ORACLE實(shí)例終止。強(qiáng)制斷開(kāi)連接SQL在看看alertlog文件，錯(cuò)誤信息如下：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級(jí)別1出現(xiàn)錯(cuò)誤ORA-00376:此時(shí)無(wú)法讀取文件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ù)錯(cuò)誤信息知道回滾段數(shù)據(jù)故障會(huì)丟失，如果有備份的話，直接recover datafile很簡(jiǎn)單 就可以恢復(fù)，這里假設(shè)沒(méi)有備份，那該如何處理呢？現(xiàn)在因?yàn)榛貪L段數(shù)據(jù)文件的故障，導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法打開(kāi)，那我們想辦法讓 數(shù)據(jù)庫(kù)的檢查繞過(guò)或修復(fù)回滾段數(shù)據(jù)文件，數(shù)據(jù)庫(kù)open時(shí)，所有的數(shù)據(jù)文件都要online,如果不能online，那就要恢復(fù)或者 直接把數(shù)據(jù)文件drop掉。我們這里沒(méi)有備份不能恢復(fù)，那就drop掉故障的數(shù)據(jù)文件，然后讓數(shù)據(jù)可以打開(kāi)，然后在重新創(chuàng)建新的 回滾段表空間，讓數(shù)據(jù)庫(kù)使用新的回滾段表空間，最后在把有故障的回滾段表空間刪除。這是解決問(wèn)題的基本思路。操作步驟如下:

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 斷 開(kāi)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)啟動(dòng)。Total System Global Area 574619648 bytesFixed Size1297944 bytesVariable Size234881512 bytesDatabase Buffers331350016 bytesRedo Buffers7090176 bytes數(shù)據(jù)庫(kù)裝載完畢。數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)?，F(xiàn)在數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)，剩下就簡(jiǎn)單多了，創(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)錯(cuò)誤：ORA-32001:已請(qǐng)求寫(xiě)入SPFILE，但是在啟動(dòng)時(shí)未指定SPFILESQL alter system set un do_tablespace=un dotbs4;系統(tǒng)已更改。SQLSQL shutdow n immediate數(shù)據(jù)庫(kù)已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫(kù)。ORACL

21、E例程已經(jīng)關(guān)閉。SQL startup pfile=E:/oracle/product/10.2.0/db_1/database/inittest.oraORACLE例程已經(jīng)啟動(dòng)。Total System Global Area 574619648 bytesFixed Size1297944 bytesVariable Size234881512 bytesDatabase Buffers331350016 bytesRedo Buffers7090176 bytes數(shù)據(jù)庫(kù)裝載完畢。數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。檢查當(dā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)錯(cuò)誤：ORA-30013:還原表空間UNDOTBS4當(dāng)前正在使用中SQLSQL shutdow n immediate;數(shù)據(jù)庫(kù)已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫(kù)。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$)啟動(dòng)數(shù)據(jù)庫(kù)SQL startup pfile=E:/oracle/product/10.2.0/db_1/database/inittest.oraORACLE例程已經(jīng)啟動(dò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ù)庫(kù)裝載完畢。數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。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ù)庫(kù)已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫(kù)。ORACLE例程已經(jīng)關(guān)閉。SQL startup pfile=E:/oracle/product/10.2.0/db_1/database/inittest.oraORACLE例程已經(jīng)啟動(dò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ù)庫(kù)裝載完畢。數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。查看如下都正常了吧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ù)庫(kù)已經(jīng)關(guān)閉。已經(jīng)卸載數(shù)據(jù)庫(kù)。ORACLE例程已經(jīng)關(guān)閉。SQL startupORACLE例程已經(jīng)啟動(dòng)。Total System Global Area 574619648 bytesFixed Size1297944 bytesVariable Size234881512 bytesDatabase Buffers 331350016 bytesRedo Buffers數(shù)據(jù)庫(kù)裝載完畢。數(shù)據(jù)庫(kù)已經(jīng)打開(kāi)。SQL7090176 bytes已經(jīng)

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

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

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