99%的人不知道，Oracle resetlogs强制开库需要推进SCN？

文章目录

• • 📣 1.故障现象
  • 📣 2.故障处理
  • 📣 3.恢复后确认
  • 📣 4.总结

有些时侯可能你的库处于非归档的模式下，而你的联机重做日志又currupted,你的数据文件不能完成完全的恢复，这里小编为大家介绍一个oracle的一个隐藏参数_allow_resetlogs_corruption，让数据库重生.

📣 1.故障现象

📣 2.故障处理

✨ 2.1 清理在线日志

SQL> set linesize 300
SQL> select group#,sequence#,archived,status from v$log;
    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1          7 NO  CURRENT
         3          6 NO  INACTIVE
         2          5 NO  INACTIVE

SQL> select group#,sequence#,archived,status from v$log;
    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1          7 NO  CURRENT
         3          0 NO  UNUSED
         2          0 NO  UNUSED

✨ 2.2 修改隐藏参数

SQL> show parameter _allow_resetlogs_corruption
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption          boolean     TRUE

✨ 2.3 控制文件备份

✨ 2.4 resetlogs开库

RMAN> recover database until cancel;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "cancel": expecting one of: "available, scn, sequence, time"
RMAN-01007: at line 1 column 24 file: standard input

发现以上不允许使用cancel，那么基于时间点恢复
recover database until time "to_date('2024-03-07 11:00:00','YYYY-MM-DD HH24:MI:SS')";

Starting recover at 08-MAR-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2024 00:03:59
RMAN-06555: datafile 1 must be restored from backup created before 07-MAR-24

继续恢复
RMAN> recover database;

Starting recover at 08-MAR-24
using channel ORA_DISK_1

starting media recovery

RMAN-08187: warning: media recovery until SCN 2187454 complete
Finished recover at 08-MAR-24

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
ORA-01092: ORACLE instance terminated. Disconnection forced
RMAN-03002: failure of sql statement command at 03/08/2024 00:11:38
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [], [], [], [], [], [], [], []
Process ID: 8041
Session ID: 35 Serial number: 38091

如果上述报出ora-00600
1.设置隐含参数_allow_error_simulation  = TRUE
alter system set "_allow_error_simulation"=true scope=spfile;

SQL> startup force
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size                  8895872 bytes
Variable Size             771751936 bytes
Database Buffers          402653184 bytes
Redo Buffers                7880704 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4193], [176], [183], [], [], [],
[], [], [], [], [], []
Process ID: 9078
Session ID: 33 Serial number: 29676


2.undo 4193、4194回滚段错误，
可以先undo手动管理undo_management，启动数据库open;

此时需要推进scn
alter system set undo_management = 'MANUAL' scope=spfile;
SQL> alter session set events '10015 trace name adjust_scn level 10';

3.重新启库
SQL> startup force;  
ORACLE instance started.

Total System Global Area 1191181696 bytes
Fixed Size                  8895872 bytes
Variable Size             771751936 bytes
Database Buffers          402653184 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.

✨ 2.5 重建UNDO

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

📣 3.恢复后确认

SQL>  set linesize 300
SQL> select group#,sequence#,archived,status from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1          4 NO  CURRENT
         2          2 NO  INACTIVE
         3          3 NO  INACTIVE

📣 4.总结