天萃荷净

用户现场服务器掉电关闭，导致Oracle数据库无法启动，alter日志文件报错ora 01190 01110

事故现场故障模拟：

一、模拟offline文件然后resetlogs操作

1.设置datafile 5数据文件offline

2.rman备份数据库

3.关闭原数据库，删除数据文件/当前日志和部分归档日志

4.执行不完全恢复，resetlogs打开数据库(如下面操作)

[oracle@xifenfei ora11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 15 07:36:59 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>recover database until cancel;

ORA-00279: change 868870 generated at 03/15/2012 03:32:11 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/oradata/archivelog/ora11g/1_29_777766629.dbf

ORA-00280: change 868870 for thread 1 is in sequence #29

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

FILE# ONLINE_STATUS TO_CHAR(CHANGE#,'999999999

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

5 OFFLINE 868810

SQL> alter database datafile 5 online;

alter database datafile 5 online

*

ERROR at line 1:

ORA-01190: control file or data file 5 is from before the last RESETLOGS

ORA-01110: data file 5: '/u01/oracle/oradata/ora11g/xifenfei01.dbf'

SQL> select file#,to_char(checkpoint_change#,'999999999999'),

2 to_char(last_change#,'999999999999') from v$datafile;

FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(LAST_CHANGE#,'9999

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

1 868874

2 868874

3 868874

4 868874

5 868810 868874

--可以看到offline的数据文件，没有因为resetlogs操作而改变

--CHECKPOINT_CHANGE#和RESETLOGS_CHANGE#信息

SQL> select file#,to_char(checkpoint_change#,'999999999999'),

2 to_char(RESETLOGS_CHANGE#,'999999999999')

3 from v$datafile_header;

FILE# TO_CHAR(CHECKPOINT_CHANGE# TO_CHAR(RESETLOGS_CHANGE#,

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

1 868874 868871

2 868874 868871

3 868874 868871

4 868874 868871

5 868810 787897

二、隐含参数设置

SQL> create pfile='/tmp/pfile' from spfile;

File created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

在pfile中增加

_allow_resetlogs_corruption=true

_allow_error_simulation=TRUE(10g及其以上版本需要)

三、打开数据库,online离线文件

SQL> startup pfile='/tmp/pfile' mount;

ORACLE instance started.

Total System Global Area 368263168 bytes

Fixed Size 1345016 bytes

Variable Size 293603848 bytes

Database Buffers 67108864 bytes

Redo Buffers 6205440 bytes

Database mounted.

--在mount状态下执行

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

Session altered.

--[一定要]在mount状态下执行online操作

SQL> alter database datafile 5 online;

Database altered.

SQL> recover database until cancel;

ORA-00279: change 868810 generated at 03/13/2012 22:19:37 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/oradata/archivelog/ora11g/1_27_777766629.dbf

ORA-00280: change 868810 for thread 1 is in sequence #27

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01190: control file or data file 1 is from before the last RESETLOGS

ORA-01110: data file 1: '/u01/oracle/oradata/ora11g/system01.dbf'

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

Database altered.

SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

no rows selected

-----------------------解决办法---------------------------

通过在参数文件spfile中增加Oracle数据库隐含参数“_allow_resetlogs_corruption"解决。

-----------------------温馨提示---------------------------

本文由大师惜分飞分享，Oracle研究中心www.oracleplus.net，转载请尽量保留本站网址。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之关机导致Oracle数据库启动失败,报错ora 01190 01110