环境说明
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" NORESETLOGSARCHIVELOG
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" NORESETLOGSARCHIVELOG
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" RESETLOGSARCHIVELOG 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 openresetlogs;
alter database openresetlogs
*
第1⾏出现错误:
ORA-01092: ORACLE instance terminated.
Disconnection forced
ORA-00704: bootstrap process failure
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> startupopenresetlogs;
ORACLE例程已经启动。
Total System Global Area 3423965184 bytes
Fixed Size2180544 bytes
Variable Size2566916672 bytes
Database Buffers838860800 bytes
Redo Buffers16007168 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
发布评论