1.事情起因

接到電話，周日意外斷電，數據庫起不了，報REDO CRASH,ora-00333錯誤。

相關環境如下：RAC oracle_11.2.0.3，無備份，開歸檔。

2 處理

一到現場，既然對方沒有備份，那就做一個冷備份咯；oracle 11g嘛，把asm里datafile,logfile,controlfile(如果有必要spfile.ora)copy出來就可以了。拼的sql 如下：

select 'cp '||name ||' /databak/datafile/' fromv$datafile unionselect?'cp?'||member||'?/databak/logfile/'?from?v$logfile?union

select?'cp?'||name?||'?/databak/controlfile/'?fromv$controlfile;

轉到grid用戶下,asmcmd,執行就可以了。時間嘛，那就得看你的數據文件大小和存儲的IO速度了。

2.1修改隱含參數

由于有了冷備，可以參數(如果沒冷備，改這個參數，那再后面就沒那個磚家來陪你玩了)SQL>create?pfile=’/databak/pfile.ora’?from?spfile;

修改pfile.ora，增加如下部分

_allow_resetlogs_corruption=TRUE

*.undo_management='MANUAL'

*.rollback_segments='SYSTEM'

可能有人要問，你resetlogs，關undo啥事，但通常事情是這樣，redo出問題了，那就意味著有事務沒提交完，那undo必然不一致，所以干脆把undo也改了吧。

2.2 啟庫

改完了，重啟咯

SQL>shutdown immediate;

SQL>startup mount pfile=’/databak/pfile.ora’

SQL>RECOVERDATABASE until cancel;

這時數據庫會提示你輸入下一個logseq，這里沒有，直接輸cancel;這里你會很高興的看到那個壞的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有關系，但看官莫急，仔細看一下

select ctime,mtime, stime from obj$ where obj# = :1這個語句好眼熟，不是初始化最早的語句么，那估計SCN有問題了。SQL>Selectcurrent_scn?from?v$database;

-------------------------------------------------------------------

0

為0.HOHO.

這里用兩種方法來解決啰：

1設置10046traceSQL>?oradebug?setmypidStatement?processed.SQL>?oradebug?EVENT?10046?TRACE?NAME?CONTEXT?FOREVER,?LEVEL?12Statement?processed.SQL>?oradebug?TRACEFILE_NAME

2 oradebugpoke推進scn

3設置隱含參數_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

現次啟動數據庫，順利open，但有一堆JOB報錯，估計掉電時正在跑JOB.也就解釋了為啥周日掉電也會把數據庫redo 搞垮。

SQL> alter system setjob_queue_processes=0;

導出全部做一個邏輯導出咯。expdp?system/systemdirectory=full?dumpfile=dump_%u.dmp?logfile=export.log?full=y?parallel=4

導出時也報了一個錯，說回滾段不可用，忘了把undo改成真正的undo了，SQL>?create?undotablespace??undotbs3?datafile'+ordata(datafile)'?size?8G;

修改pfile，改成成undotbs3;再次重啟，OK。

2.3 MOS _ALLOW_RESETLOGS_CORRUPTION說明

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.

簡單點來說，就是_ALLOW_RESETLOGS_CORRUPTION這個參數沒有100%保證，你redo壞了能用他來OPEN庫的，并且了用了這個后不支持恢復了(rman)，僅僅支持export.

3總結：

沒啥好說的，有運氣成份在里面，如果datafile block有環塊那就更麻煩了，如果壞了一片也就沒得完了,沒事還是不要玩掉電吧，把UPS電池時間弄長一點，加個停電報警，省了一片心，再就是有空建個dataguard吧。

總結

以上是生活随笔為你收集整理的oracle00333,Oracle数据库REDO损坏ora-00333修复手札的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。