实验环境:OEL 5.7 + Oracle 10.2.0.5
Tips:该参数仅在特殊恢复场景下使用,需要在专业Oracle工程师指导下进行操作。
1.隐藏参数说明
2.故障场景再现
3.非常规恢复
1.隐藏参数说明查询隐藏参数"_allow_resetlogs_corruption"及说明:
set linesize 333 col name for a35 col description for a66 col value for a30 SELECT i.ksppinm name, i.ksppdesc description, CV.ksppstvl VALUE FROM sys.x$ksppi i, sys.x$ksppcv CV WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV ('Instance') AND i.indx = CV.indx AND i.ksppinm LIKE '%&keyword%' ORDER BY 1; Enter value for keyword: allow_resetlog old 8: AND i.ksppinm LIKE '%&keyword%' new 8: AND i.ksppinm LIKE '%allow_resetlog%' NAME DESCRIPTION VALUE ----------------------------------- ------------------------------------------------------------------ ------------------------------ _allow_resetlogs_corruption allow resetlogs even if it will cause corruption FALSE通过这个隐藏参数非常规恢复的库,原则建议还是要重建库的。其实在alert日志中也会看到有这样的建议:
Wed Dec 26 00:00:41 CST 2018 alter database open resetlogs Wed Dec 26 00:00:41 CST 2018 RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. 2.故障场景再现模拟常规开库失败的场景:
SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE# ------------------ 10013731555 10014045643 10014045643 10014045643 10014045643 10014045643 10014045643 10014045643 10014045643 9 rows selected. SQL> alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '+ZHAOJINGYU/jy/datafile/system.256.839673875'说明:这个环境是模拟数据文件1丢失,最终从备份restore出来一个旧的文件,但由于种种原因,总之没有后续的归档去做recover,导致无法追平。
此时就可尝试使用_allow_resetlogs_corruption隐藏参数强制开库:
此时再去查询数据文件头的SCN已经一致:
SQL> select checkpoint_change# from v$datafile_header; CHECKPOINT_CHANGE# -------------------- 10014022016 10014022016 10014022016 10014022016 10014022016 10014022016 10014022016 10014022016 10014022016 9 rows selected.注意处理完毕后及时改回这个隐藏参数为false:
alter system set "_allow_resetlogs_corruption" = false scope=spfile;其他注意事项:如果开库遇到ORA-600 [2662]类错误,可以参考之前随笔:
最终通过推进SCN的手段来解决ORA-600 [2662]类问题。
其实这个场景其实可能会遇到各种问题,都属于非常规恢复范畴,后续我会计划去继续测试验证一些常见场景及解决方案。