Oracle隐藏参数_allow_resetlogs_corruption的使用实践

实验环境：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隐藏参数强制开库：

SQL> alter system set "_allow_resetlogs_corruption" = true scope=spfile;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database open resetlogs;

此时再去查询数据文件头的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;