您好,登錄后才能下訂單哦!
接到電話,周日意外斷電,數(shù)據(jù)庫起不了,報(bào)REDO CRASH,ora-00333錯(cuò)誤。
相關(guān)環(huán)境如下:RAC oracle_11.2.0.3,無備份,開歸檔。
一到現(xiàn)場,既然對方?jīng)]有備份,那就做一個(gè)冷備份咯;oracle 11g嘛,把asm里datafile,logfile,controlfile(如果有必要spfile.ora)copy出來就可以了。拼的sql 如下:
select 'cp '||name ||' /databak/datafile/' fromv$datafile union
select 'cp '||member||' /databak/logfile/' from v$logfile union select 'cp '||name ||' /databak/controlfile/' fromv$controlfile;
轉(zhuǎn)到grid用戶下,asmcmd,執(zhí)行就可以了。時(shí)間嘛,那就得看你的數(shù)據(jù)文件大小和存儲的IO速度了。
由于有了冷備,可以參數(shù)(如果沒冷備,改這個(gè)參數(shù),那再后面就沒那個(gè)磚家來陪你玩了)
SQL>create pfile=’/databak/pfile.ora’ from spfile;
修改pfile.ora,增加如下部分
_allow_resetlogs_corruption=TRUE
*.undo_management='MANUAL'
*.rollback_segments='SYSTEM'
可能有人要問,你resetlogs,關(guān)undo啥事,但通常事情是這樣,redo出問題了,那就意味著有事務(wù)沒提交完,那undo必然不一致,所以干脆把undo也改了吧。
改完了,重啟咯
SQL>shutdown immediate;
SQL>startup mount pfile=’/databak/pfile.ora’
SQL>RECOVERDATABASE until cancel;
這時(shí)數(shù)據(jù)庫會提示你輸入下一個(gè)logseq,這里沒有,直接輸cancel;這里你會很高興的看到那個(gè)壞的redo被清掉了,可以歡呼了,起來了,但是世界是非富多彩地,馬上就把你的高興打破:
ORA-01555caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0db2.73a0c8cd): select ctime,mtime, stime from obj$ where obj# = :1 Errors in file/oracle/app/oracle/diag/rdbms/oradb/oradb1/trace/jmrk1_ora_4608.trc: Errors in file/oracle/app/oracle/diag/rdbms/oradb/oradb1/trace/jmrk1_ora_4364.trc: ORA-00704:bootstrap process failure ORA-00704:bootstrap process failure ORA-00604:error occurred at recursive SQL level 1 ORA-01555:snapshot too old: rollback segment number 20 with name"_SYSSMU20_3214617278$" too small
這里一看,似乎和undo有關(guān)系,但看官莫急,仔細(xì)看一下
select ctime,mtime, stime from obj$ where obj# = :1這個(gè)語句好眼熟,不是初始化最早的語句么,那估計(jì)SCN有問題了。
SQL>Selectcurrent_scn from v$database; ------------------------------------------------------------------- 0
為0.HOHO.
這里用兩種方法來解決啰:
1設(shè)置10046trace
SQL> oradebug setmypid
Statement processed.
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
Statement processed.
SQL> oradebug TRACEFILE_NAME
2 oradebugpoke推進(jìn)scn
3設(shè)置隱含參數(shù)_minimum_giga_scn
我這里采用第三種:
selectksppinm,ksppdesc from x$ksppi whereksppinm like '%giga%' KSPPINM KSPPDESC --------------------------------------------------------------------------- _minimum_giga_scn Minimum SCN to start with in 2^30units selectto_char(checkpoint_change#,'99999999999999') from v$database; TO_CHAR(CHECKPO--------------- 15060095276784 selectdistinct(to_char(checkpoint_change#,'99999999999999')) from v$datafile_header; (TO_CHAR(CHECKP --------------- 15060095276784 SQL> select15060095276784/1024/1024/1024 from dual; 15060095276784/1024/1024/1024 ----------------------------- 14025.8067
修改pfile改修改:
_minimum_giga_scn=14026
現(xiàn)次啟動數(shù)據(jù)庫,順利open,但有一堆JOB報(bào)錯(cuò),估計(jì)掉電時(shí)正在跑JOB.也就解釋了為啥周日掉電也會把數(shù)據(jù)庫redo 搞垮。
SQL> alter system setjob_queue_processes=0;
導(dǎo)出全部做一個(gè)邏輯導(dǎo)出咯。
expdp system/systemdirectory=full dumpfile=dump_%u.dmp logfile=export.log full=y parallel=4
導(dǎo)出時(shí)也報(bào)了一個(gè)錯(cuò),說回滾段不可用,忘了把undo改成真正的undo了,
SQL> create undotablespace undotbs3 datafile'+ordata(datafile)' size 8G;
修改pfile,改成成undotbs3;再次重啟,OK。
DB_Parameter _ALLOW_RESETLOGS_CORRUPTION
========================================
This documentation has been preparedavoiding the mention of the complex
structures from the code and to simply givean insight to the 'damage it could
cause'. The usage of this parameter leads to an in-consistent Database with no
other alternative but to rebuild thecomplete Database. This parameter could
be used when we realize that there are nostardard options available and are
convinced that the customer understands theimplications of using the Oracle's
secret parameter. The factors to be considered are ;--
1. Customer does not have a good backup.
2. A lot of time and money has beeninvested after the last good backup and
there is no possibility for reproduction of the lost data.
3. The customer has to be ready to exportthe full database and import it
back after creating a new one.
4. There is no 100% guarantee that by usingthis parameter the database would
come up.
5. Oracle does not support the databaseafter using this parameter for
recovery.
6. ALL OPTIONS including the ones mentionedin the action part of the error
message have been tried.
簡單點(diǎn)來說,就是_ALLOW_RESETLOGS_CORRUPTION這個(gè)參數(shù)沒有100%保證,你redo壞了能用他來OPEN庫的,并且了用了這個(gè)后不支持恢復(fù)了(rman),僅僅支持export.
沒啥好說的,有運(yùn)氣成份在里面,如果datafile block有環(huán)塊那就更麻煩了,如果壞了一片也就沒得完了,沒事還是不要玩掉電吧,把UPS電池時(shí)間弄長一點(diǎn),加個(gè)停電報(bào)警,省了一片心,再就是有空建個(gè)dataguard吧。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。