前不久，我们一套生产数据库的dataguard的备库出现实时应用过慢，数据库服务器IOWAIT高的情况，为了避免主库归档日志出现大量挤压，所以值班同事临时应急将主备库关系配置断掉，后续再进行DataGuard重构。除此之外，我们准备将备库打开进行一系列测试，试图探寻数据库服务器IOWAIT高的原因并解决DataGuard延迟的问题。
第二天，同事尝试打开备库，但是报了ORA-01196与错误
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'此错误因为备库还未将redo log与archive恢复至一致状态，数据库此时处于不一致状态是无法打开的。但是，备库所有的归档日志已经被删除，此时数据库无法进行一个完全恢复
SQL> set line 1000 pagesize 200
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;
NAME OPEN_MODE CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba MOUNTED 7669047 966955 7669046 PHYSICAL STANDBY
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
ALTER DATABASE ACTIVATE STANDBY DATABASE
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'此时，我准备重建控制文件，进行不完全恢复操作
QL> alter database backup controlfile to trace as '/home/ora11g/albert.ctl';
Database altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
albert.ctl文件内容：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA1459T" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo11.log',
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo12.log'
) SIZE 300M BLOCKSIZE 512,
GROUP 2 (
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo21.log',
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo22.log'
) SIZE 300M BLOCKSIZE 512,
GROUP 3 (
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo31.log',
'/oracle/redo_Travelskydba1/Travelskydba_DG/redo32.log'
) SIZE 300M BLOCKSIZE 512
-- STANDBY LOGFILE
-- GROUP 4 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo04.log' SIZE 300M BLOCKSIZE 512,
-- GROUP 5 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo05.log' SIZE 300M BLOCKSIZE 512
DATAFILE
'/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/sysaux01.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/undotbs01.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/users01.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/users02.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/users03.dbf',
'/oracle/oradata_Travelskydba1/Travelskydba_DG/users04.dbf'
CHARACTER SET AL32UTF8
;
[ora11g@ALBERT ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 28 12:36:00 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @albert.ctl
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2257720 bytes
Variable Size 1191185608 bytes
Database Buffers 2080374784 bytes
Redo Buffers 16527360 bytes
Control file created. --控制文件重建完毕
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs; --仍然无法打开数据库
alter database open resetlogs
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'修改数据库alter system set “_allow_resetlogs_corruption” =true scope=spfile;参数，再次进行不完全恢复（此操作会有严重丢失数据风险，生产系统请慎用）
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2257720 bytes
Variable Size 1191185608 bytes
Database Buffers 2080374784 bytes
Redo Buffers 16527360 bytes
SQL> alter system set "_allow_resetlogs_corruption" =true scope=spfile;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> @albert.ctl
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2257720 bytes
Variable Size 1191185608 bytes
Database Buffers 2080374784 bytes
Redo Buffers 16527360 bytes
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [7669055], [0],
[7996264], [4194545], [], [], [], [], [], []
Process ID: 12086
Session ID: 1705 Serial number: 3
此时出现ORA-00600 2262错误，此时距离成功已经不远，只需要推进SCN即可，
SQL> shutdown abort;
ORACLE instance shut down.
SQL> @albert.ctl
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2257720 bytes
Variable Size 1191185608 bytes
Database Buffers 2080374784 bytes
Redo Buffers 16527360 bytes
Control file created.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7779049 generated at 03/28/2020 12:54:12 needed for thread 1
ORA-00289: suggestion : /oracle/oradata_Travelskydba1/Travelskydba_DG/1_2_1036241636.dbf
ORA-00280: change 7779049 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'
ORA-01112: media recovery not started
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL> oradebug poke 0x06001AE70 4 8279047
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER: [06001AE70, 06001AE74) = 007E5407
SQL> alter database open resetlogs; --数据库成功打开
Database altered.
SQL> set line 1000 pagesize 200
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;
NAME OPEN_MODE CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba READ WRITE 8280898 8279049 8280903 PRIMARY