环境说明

OS操作系统：WINDOWS 2012 64位

数据库版本：ORACLE 11.2.0.1

故障问题描述

客户反映数据库无法启动，报 ORA-01589：要打开数据库必须使用RESETLOGS 或 NORESETLOGS 选项。使用alter database open resetlogs 启动数据库报 ORA-01194：文件1需要更多恢复来保持一致性，ORA-01110：数据文件1：‘E:\APP\YING_LUN\ORADATA\ORCL\SYSTEM01.DBF’。

故障分析

1、在网上搜索相关文档，对于这样的问题需要进行控制文件恢复：

SQL> recover database using backup controlfile;

由于数据库在出故障之前没有开启归档日志，因为找不到需要的归档日志控制文件恢复失败。

2. 使用NORESETLOGS方式重建控制文件

目前数据库可以启动到 MOUNT 状态，可以备份控制文件创建脚本到TRACE文件中，获取控制文件创建脚本

SQL>alter database backup controlfile to trace;

==>在11G中可以通过v$diag_info视图查看备份的TRACE文件的位置

SQL> select value from v$diag_info where name='Default Trace File';

==>把TRACE 文件中的 NORESETLOGS 方式创建控制文件的SQL语句复制到 create.sql脚本

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 'E:\APP\YING_LIU\ORADATA\ORCL\REDO01.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 2 'E:\APP\YING_LIU\ORADATA\ORCL\REDO02.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 3 'E:\APP\YING_LIU\ORADATA\ORCL\REDO03.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 4 'E:\APP\YING_LIU\ORADATA\ORCL\REDO04.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 5 'E:\APP\YING_LIU\ORADATA\ORCL\REDO05.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 6 'E:\APP\YING_LIU\ORADATA\ORCL\REDO06.LOG' SIZE 500M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\SYSAUX01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\UNDOTBS01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\USERS01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\EXAMPLE01.DBF',

'E:\APP\TABLESPACE\MALASONG.DBF',

'E:\APP\TABLESPACE\WX_REDPACKET.DBF',

'D:\ORACLE\ORCL\SIGNIN_USER_TEST.DBF',

'E:\APP\TABLESPACE\EXAM_SYSTEM.DBF',

'E:\APP\TABLESPACE\EXAM_SYSTEM_TEST.DBF',

'E:\APP\TABLESPACE\ICLUB.DBF',

'E:\APP\TABLESPACE\PINBAO.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM3.DBF'

CHARACTER SET ZHS16GBK

;

==>执行create.sql 脚本重建控制文件

SQL> shutdown immediate;

SQL>@create.sql

==>执行create.sql 脚本时报错日志组5与日志文件 redo5.log不匹配。在网上搜索到的资料建议删除日志组相关内容或使用RESETLOGS方式创建控制文件，于是删除日志文件相关内容。

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

-- STANDBY LOGFILE

DATAFILE

'E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\SYSAUX01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\UNDOTBS01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\USERS01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\EXAMPLE01.DBF',

'E:\APP\TABLESPACE\MALASONG.DBF',

'E:\APP\TABLESPACE\WX_REDPACKET.DBF',

'D:\ORACLE\ORCL\SIGNIN_USER_TEST.DBF',

'E:\APP\TABLESPACE\EXAM_SYSTEM.DBF',

'E:\APP\TABLESPACE\EXAM_SYSTEM_TEST.DBF',

'E:\APP\TABLESPACE\ICLUB.DBF',

'E:\APP\TABLESPACE\PINBAO.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM3.DBF'

CHARACTER SET ZHS16GBK

;

==>执行修改后的 create.sql 报数据库名与SPFILE中的 DB_NAME不一致，经检查数据库名称与SPFILE

==>在的 DB_NAME是一致。于是决定使用 RESETLOGS 方式重建控制文件。

;

3、使用RESETLOGS方式重建控制文件：

==>把 RESETLOGS 方式创建控制文件的脚本复制到 create_2.sql 文件中

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 'E:\APP\YING_LIU\ORADATA\ORCL\REDO01.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 2 'E:\APP\YING_LIU\ORADATA\ORCL\REDO02.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 3 'E:\APP\YING_LIU\ORADATA\ORCL\REDO03.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 4 'E:\APP\YING_LIU\ORADATA\ORCL\REDO04.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 5 'E:\APP\YING_LIU\ORADATA\ORCL\REDO05.LOG' SIZE 500M BLOCKSIZE 512,

GROUP 6 'E:\APP\YING_LIU\ORADATA\ORCL\REDO06.LOG' SIZE 500M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\SYSAUX01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\UNDOTBS01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\USERS01.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\EXAMPLE01.DBF',

'E:\APP\TABLESPACE\MALASONG.DBF',

'E:\APP\TABLESPACE\WX_REDPACKET.DBF',

'D:\ORACLE\ORCL\SIGNIN_USER_TEST.DBF',

'E:\APP\TABLESPACE\EXAM_SYSTEM.DBF',

'E:\APP\TABLESPACE\EXAM_SYSTEM_TEST.DBF',

'E:\APP\TABLESPACE\ICLUB.DBF',

'E:\APP\TABLESPACE\PINBAO.DBF',

'E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM3.DBF'

CHARACTER SET ZHS16GBK

==>执行create_2.sql 脚本重建控制文件,启动数据库报 UNDO 段 _SYSSMU8_1682283174$快照过旧。

SQL> shutdown immediate;

SQL>@create_2.sql

SQL> alter database open resetlogs;

alter database open resetlogs

*

第 1 行出现错误:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 8 with name

"_SYSSMU8_1682283174$" too small

进程 ID: 8600

会话 ID: 576 序列号: 1

4、推高数据库SCN：

经向同时请教觉得通过推高 SCN跳过 UNDO的回滚操作。

SQL> startup open resetlogs;

ORACLE 例程已经启动。

Total System Global Area 3423965184 bytes

Fixed Size 2180544 bytes

Variable Size 2566916672 bytes

Database Buffers 838860800 bytes

Redo Buffers 16007168 bytes

ORA-01504: ???? 'RESETLOGS' ??? db_name 'ORCL' ???

==> 查看数据文件头的 SCAN

select name,checkpoint_change# from v$datafile_header;

NAME

--------------------------------------------------------------------------------

CHECKPOINT_CHANGE#

----------------------------

E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM01.DBF

10150360067303

.... ==>其他输出信息省略

==>重建控制文件，把数据库 SCN 推高一个亿，启动数据库仍然报错

SQL>@create_2.sql

SQL> alter session set events '10015 trace name adjust_scn level 1';

SQL> alter database open resetlogs;

alter database open resetlogs

*

第 1 行出现错误:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 8 with name

"_SYSSMU8_1682283174$" too small

进程 ID: 6420

会话 ID: 576 序列号: 1

==>最后把数据库 SCN 推高到 1亿亿还是报错

SQL>@create_2.sql

SQL> alter session set events '10015 trace name adjust_scn level 100000000';

SQL> alter database open resetlogs;

alter database open resetlogs

*

第 1 行出现错误:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 8 with name

"_SYSSMU8_1682283174$" too small

进程 ID: 6420

会话 ID: 576 序列号: 1

==>最后查了下，在 11.2.0.1 中这个方法无法推高 SCN，于是重建控制文件使用 oradebug来推高数据库 SCN,启动数据库时仍然报错，有可能是在 WINDOWS 64 位平台不支持 oradebug 推高SCN

SQL>@create_2.sql

SQL> select to_char('10150360067303','xxxxxxxxxxxxxxxxxxxxxx') from dual;

TO_CHAR('10650360067303

-----------------------

9afbaedb4e7

SQL>oradebug setmypid

SQL>oradebug dumpvar sga kcsgscn_

SQL>oradebug poke 0x0082237B0 8 0x000012539f0dcce7

SQL>alter database open resetlogs;

alter database open resetlogs

*

第 1 行出现错误:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 8 with name

"_SYSSMU8_1682283174$" too small

进程 ID: 6420

会话 ID: 576 序列号: 1

5、跳过有问题的UNDO 段，强制打开数据库：

SQL>

alter system set "_allow_resetlogs_corruption"=true scope=spfile; ==>允许跳过损坏的UNDO段

alter system set "_corrupted_rollback_segments"='_SYSSMU8$' scope=spfile; ==>把有问题的UNDO段标记为坏的 UDNO 段。

==>启动数据库

SQL>alter database open resetlogs;

==>创建信息的 UNDO 表空间，并修改数据库的默认UNDO 表空间为新的UNDO 表空间

SQL>

create undo tablespace UNDO02 datafile 'E:\app\ying_liu\oradata\ORCL\undo02_01.dbf' size 2G autoextend on;

alter system set undo_tablespace=UNDO02 scope=both;

==>删除旧的UNDO 表空间

alter tablespace undotbs1 offline normal;

drop tablespace UNDOTBS1 including contents and datafiles;

==>把SPFILE中的隐藏参数恢复默认值，重启数据库

SQL>

alter system reset "_allow_resetlogs_corruption" scope=spfile;

alter system reset "_corrupted_rollback_segments" scope=spfile;

SQL> startup;

至此故障解决，数据库正常启动。

5、为了找到控制文件出问题的原因分析数据库告警日志：

==> 2016年 11月15日 16:06:07 启动数据库报无法找到 REDO LOG 的错误，可能是redo log 损坏了，

==> 也可是被删除了，这也是使用 NORESETLOGS 创建控制文件失败的原因

Tue Nov 15 16:06:07 2016

Starting ORACLE instance (normal)

.... ==>省略部分日志信息

Successful mount of redo thread 1, with mount id 1455795184

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: alter database mount exclusive

alter database open

Beginning crash recovery of 1 threads

parallel recovery started with 7 processes

Started redo scan

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_ora_11860.trc:

ORA-00313: ??????? 5 (???? 1) ???

ORA-00312: ???? 5 ?? 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO05.LOG'

ORA-27041: ??????

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Aborting crash recovery due to error 313

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_ora_11860.trc:

ORA-00313: ??????? 5 (???? 1) ???

ORA-00312: ???? 5 ?? 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO05.LOG'

ORA-27041: ??????

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_ora_11860.trc:

ORA-00313: ??????? 5 (???? 1) ???

ORA-00312: ???? 5 ?? 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO05.LOG'

ORA-27041: ??????

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

ORA-313 signalled during: alter database open...

Tue Nov 15 16:06:13 2016

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_m000_16324.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO01.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_m000_16324.trc:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO02.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_m000_16324.trc:

ORA-00313: open failed for members of log group 3 of thread 1

ORA-00312: online log 3 thread 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO03.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_m000_16324.trc:

ORA-00313: open failed for members of log group 4 of thread 1

ORA-00312: online log 4 thread 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO04.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_m000_16324.trc:

ORA-00313: open failed for members of log group 5 of thread 1

ORA-00312: online log 5 thread 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO05.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_m000_16324.trc:

ORA-00313: open failed for members of log group 6 of thread 1

ORA-00312: online log 6 thread 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO06.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Checker run found 12 new persistent data failures

Tue Nov 15 16:07:18 2016

==>在 2016/11/15 16:54:59 执行了不完全恢复，但因为没有开启归档日志，找不到需要的归档日志

==>恢复失败

Tue Nov 15 16:54:59 2016

ALTER DATABASE RECOVER database until time '2011-10-01 00:00:00'

Media Recovery Start

started logmerger process

Parallel Media Recovery started with 8 slaves

Tue Nov 15 16:55:00 2016

Media Recovery failed with error 19907

Slave exiting with ORA-283 exception

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_pr00_560.trc:

ORA-00283: recovery session canceled due to errors

ORA-19907: recovery time or SCN does not belong to recovered incarnation

ORA-283 signalled during: ALTER DATABASE RECOVER database until time '2011-10-01 00:00:00' .

Tue Nov 15 16:55:11 2016

ALTER DATABASE RECOVER database until time '2016-10-01 00:00:00'

Media Recovery Start

started logmerger process

Parallel Media Recovery started with 8 slaves

Media Recovery Not Required

Tue Nov 15 16:55:11 2016

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_pr00_5260.trc:

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: 'E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM01.DBF'

Slave exiting with ORA-1547 exception

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_pr00_5260.trc:

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: 'E:\APP\YING_LIU\ORADATA\ORCL\SYSTEM01.DBF'

ORA-10879 signalled during: ALTER DATABASE RECOVER database until time '2016-10-01 00:00:00' ...

Tue Nov 15 16:55:23 2016

alter database open resetlogs

ORA-1194 signalled during: alter database open resetlogs...

Tue Nov 15 16:55:43 2016

alter database clear unarchived logfile group 5

ORA-1624 signalled during: alter database clear unarchived logfile group 5...

Tue Nov 15 16:55:43 2016

Errors in file e:\app\ying_liu\diag\rdbms\orcl\orcl\trace\orcl_m000_932.trc:

ORA-00313: open failed for members of log group 5 of thread 1

ORA-00312: online log 5 thread 1: 'E:\APP\YING_LIU\ORADATA\ORCL\REDO05.LOG'

ORA-27041: unable to open file

OSD-04002: 无法打开文件

O/S-Error: (OS 2) 系统找不到指定的文件。

Checker run found 13 new persistent data failures

==> 2016/11/15 17:03:07 手动执行了控制文件恢复操作，但因为没有对应的归档日志失败（没开归档）

==> 这个操作直接导致了控制文件与数据文件头 SCN不一致，以至于后续启动数据库时都要求恢复控制文

==>文件。

Tue Nov 15 17:03:07 2016

ALTER DATABASE RECOVER database using backup controlfile

Media Recovery Start

started logmerger process

Tue Nov 15 17:03:07 2016

WARNING! Recovering data file 1 from a fuzzy file. If not the current file