Oracle数据库REDO损坏ora-00333修复手札
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 union select'cp'||member||'/databak/logfile/'fromv$logfileunion select'cp'||name||'/databak/controlfile/'fromv$controlfile; 转到grid用户下,asmcmd,执行就可以了。时间嘛，那就得看你的数据文件大小和存储的IO速度了。 2.1修改隐含参数由于有了冷备，可以参数（如果没冷备，改这个参数，那再后面就没那个砖家来陪你玩了） SQL>createpfile=’/databak/pfile.ora’fromspfile; 修改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-01555causedbySQLstatementbelow(SQLID:4krwuz0ctqxdt,SCN:0x0db2.73a0c8cd): selectctime,mtime,stimefromobj$whereobj#=:1 Errorsinfile/oracle/app/oracle/diag/rdbms/oradb/oradb1/trace/jmrk1_ora_4608.trc: Errorsinfile/oracle/app/oracle/diag/rdbms/oradb/oradb1/trace/jmrk1_ora_4364.trc: ORA-00704:bootstrapprocessfailure ORA-00704:bootstrapprocessfailure ORA-00604:erroroccurredatrecursiveSQLlevel1 ORA-01555:snapshottooold:rollbacksegmentnumber20withname"_SYSSMU20_3214617278$"toosmall 这里一看，似乎和undo有关系，但看官莫急，仔细看一下 select ctime,stime from obj$ where obj# = :1这个语句好眼熟，不是初始化最早的语句么，那估计SCN有问题了。 SQL>Selectcurrent_scnfromv$database; ------------------------------------------------------------------- 0 为0.HOHO. 这里用两种方法来解决?： 1设置10046trace SQL>oradebugsetmypid Statementprocessed. SQL>oradebugEVENT10046TRACENAMECONTEXTFOREVER,LEVEL12 Statementprocessed. SQL>oradebugTRACEFILE_NAME 2 oradebugpoke推进scn 3设置隐含参数_minimum_giga_scn 我这里采用第三种： selectksppinm,ksppdescfromx$ksppiwhereksppinmlike'%giga%' KSPPINMKSPPDESC --------------------------------------------------------------------------- _minimum_giga_scnMinimumSCNtostartwithin2^30units selectto_char(checkpoint_change#,'99999999999999')fromv$database; TO_CHAR(CHECKPO--------------- 15060095276784 selectdistinct(to_char(checkpoint_change#,'99999999999999'))fromv$datafile_header; (TO_CHAR(CHECKP --------------- 15060095276784 SQL>select15060095276784/1024/1024/1024fromdual; 15060095276784/1024/1024/1024 ----------------------------- 14025.8067 修改pfile改修改： _minimum_giga_scn=14026 现次启动数据库，顺利open，但有一堆JOB报错，估计掉电时正在跑JOB.也就解释了为啥周日掉电也会把数据库redo 搞垮。 SQL> alter system setjob_queue_processes=0; 导出全部做一个逻辑导出咯。 expdpsystem/systemdirectory=fulldumpfile=dump_%u.dmplogfile=export.logfull=yparallel=4 导出时也报了一个错，说回滚段不可用，忘了把undo改成真正的undo了， SQL>createundotablespaceundotbs3datafile'+ordata(datafile)'size8G; 修改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吧。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |