ORA-01196/ORA-01110特殊恢复案例一则

前不久,我们一套生产数据库的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