当无论怎样recover都出现下面错误的时候:
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 797381 generated at 05/17/2010 20:35:40 needed for thread 1
ORA-00289: suggestion : /oracle/oradata/archivelog/1_17_718660226.dbf
ORA-00280: change 797381 for thread 1 is in sequence #17
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/oradata/denver/redo01.log
ORA-00310: archived log contains sequence 14; sequence 17 required
ORA-00334: archived log: '/oracle/oradata/denver/redo01.log'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 8 needs more recovery to be consistent
ORA-01110: data file 8: '/oracle/oradata/bakdb/rmantbs01.dbf'
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1061158912 bytes
Fixed Size 2026128 bytes
Variable Size 348128624 bytes
Database Buffers 704643072 bytes
Redo Buffers 6361088 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
_allow_resetlogs_corruption(危险)
提示：Oracle的隐含参数只应该在测试环境或者在Oracle Support的支持下使用。
设置此参数之后，在数据库Open过程中，Oracle会跳过某些一致性检查，从而使数据库可能跳过不一致状态。
SQL> !oerr ora 01194
一次恢复DB的过程：试用隐含参数_allow_resetlogs_corruption和重建undo tablespace系统管理技术 2009-10-29 19:03:54 阅读247 评论0 字号：大中小
问题：
某个现场做数据库恢复的时侯有一个问题：DB恢复需要这个归档日志文件1_33160.dbf，但是在备份中没有。
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4207433305 generated at 04/23/2008 02:00:33 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/db02/app/oracle/oradata/ORCL/archive/1_33160.dbf
ORA-00280: change 4207433305 for thread 1 is in sequence #33160
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/opt/oracle/db02/app/oracle/oradata/ORCL/archive/1_33160.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/db02/app/oracle/oradata/ORCL/system01.dbf'
解决过程：
SunOS 5.8
login: oracle
Password:
Last login: Sun May 4 10:39:57 from 172.19.136.49
Sun Microsystems Inc. SunOS 5.8 Generic Patch February 2004
$ ls
db01 db02 db03 db04 lost+found
$ sqlplus '/as sysdba'
SQL*Plus: Release 9.2.0.4.0 - Production on Sun May 4 11:49:10 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/db02/app/oracle/oradata/ORCL/archive
Oldest online log sequence 33159
Next log sequence to archive 33161
Current log sequence 33161
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;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/db02/app/oracle/oradata/ORCL/system01.dbf'
resetlogs的字面意思是重置重做日志。在创建控制文件的时候如果不需要在线重做日志的话可以用resetlogs选项，第二种情况是在做不完全恢复后，数据文件和重做日志的内容不同步了，这个时候打开数据库一定要用resetlogs选项。resetlogs的时候数据库其实做了很多事，最主要的还是清空重做日志的内容，选定一个重做日志作为当前日志并将日志序列号重置为1，把resetlogs count和resetlogs scn写入控制文件、数据文件头部和重做日志的头部，当然还会做很多其它的事情。
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
尝试一下基于时间点的不完全恢复：
SQL> recover database using backup controlfile until time '2008-04-30 01:00:00';
ORA-00279: change 4207433305 generated at 04/23/2008 02:00:33 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/db02/app/oracle/oradata/ORCL/archive/1_33160.dbf
ORA-00280: change 4207433305 for thread 1 is in sequence #33160
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/opt/oracle/db02/app/oracle/oradata/ORCL/archive/1_33160.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/db02/app/oracle/oradata/ORCL/system01.dbf'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/db02/app/oracle/oradata/ORCL/system01.dbf'
准备使用隐含参数_allow_resetlogs_corruption强制启动DB：
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
提示：Oracle的隐含参数只应该在测试环境或者在Oracle Support的支持下使用。
设置此参数之后，在数据库Open过程中，Oracle会跳过某些一致性检查，从而使数据库可能跳过不一致状态。
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03114: not connected to ORACLE
SQL> conn /as sysdba
Connected to an idle instance.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
SQL> startup
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [53], [43], [], [], [], [],[]
以上强制启动后，产生Ora-600错误了，在预料之中。。。。
SQL> startup force;
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> startup mount
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
SQL> set pages 0 feedback off lines 132
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL>
SQL> !oerr ora 1092
01092, 00000, "ORACLE instance terminated. Disconnection forced"
// *Cause: The instance this process was connected to was terminated
// abnormally, probably via a shutdown abort. This process
// was forced to disconnect from the instance.
// *Action: Examine the alert log for more details. When the instance has been
// restarted, retry action.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03114: not connected to ORACLE
SQL> startup force;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup force;
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
由于和undo表空间不同步，那好重建undo tablespace：
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
SQL> show parameter undo
undo_management string AUTO
undo_retention integer 3600
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> alter system set undo_management=manual scope=spfile;
SQL> alter database open;
先改成不使用undo，可以启动DB，但没法新建对象：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
Database opened.
SQL>
SQL> conn wacos/oss
Connected.
SQL> select count(*) from tab;
2034
SQL> create table t_test (c1 date);
create table t_test (c1 date)
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'WACOS'
SQL> !oerr ora 1552
01552, 00000, "cannot use system rollback segment for non-system tablespace '%s'"
// *Cause: Tried to use the system rollback segment for operations involving
// non-system tablespace. If this is a clone database then this will
// happen when attempting any data modification outside of the system
// tablespace. Only the system rollback segment can be online in a
// clone database.
// *Action: Create one or more private/public segment(s), shutdown and then
// startup again. May need to modify the INIT.ORA parameter
// rollback_segments to acquire private rollback segment. If this is
// a clone database being used for tablspace point in time recovery
// then this operation is not allowed.
那就新建一个undo表空间，并弃用原先的：
SQL> create undo tablespace UNDOTBS1 datafile '/opt/oracle/db02/app/oracle/oradata/ORCL/undotbs01.dbf' size 3000m;
create undo tablespace UNDOTBS1 datafile '/opt/oracle/db02/app/oracle/oradata/ORCL/undotbs01.dbf' size 3000m
*
ERROR at line 1:
ORA-01543: tablespace 'UNDOTBS1' already exists
SQL> create undo tablespace UNDOTBS2 datafile '/opt/oracle/db02/app/oracle/oradata/ORCL/undotbs02.dbf' size 3000m;
SQL> show parameter undo
undo_management string MANUAL
undo_retention integer 3600
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> alter system set undo_management=auto scope=spfile;
SQL> alter system set undo_tablespace=UNDOTBS2 scope=spfile;
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> show user
USER is "WACOS"
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
Database opened.
SQL> conn wacos/oss
Connected.
SQL> create table t_test (c1 date);
SQL>
SQL> drop tablespace undotbs1;
SQL> create table t_test2 (c1 date);
SQL> select * from t_test;
SQL> insert into t_test values (sysdate);
SQL> commit;
SQL> select * from t_test;
04-MAY-08
现在没问题了，把隐含参数改回默认的：
SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 826248288 bytes
Fixed Size 732256 bytes
Variable Size 503316480 bytes
Database Buffers 318767104 bytes
Redo Buffers 3432448 bytes
Database mounted.
Database opened.
重启没问题了，问题解决，哈！
SQL> show parameter undo
undo_management string AUTO
undo_retention integer 3600
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS2
SQL> show parameter spfile
spfile string ?/dbs/spfile@.ora
SQL>
通常使用此方法Open数据库之后，应该立即通过导出、导入重建数据库。
--End--
使用未写入文档参数_ALLOW_RESETLOGS_CORRUPTION进行崩溃恢复经典作者：硕博网 文章来源：中华硕博网 点击数： 更新时间：2008-5-3中华硕博网(http://www.china-b.com/)全球500所高校指定报名中心--使用未写入文档参数_ALLOW_RESETLOGS_CORRUPTION进行崩溃恢复经典
什么情况可能使用该参数
有些时侯可能你的库处于非归档的模式下，而你的联机重做日志又currupted,你的数据文件不能完成完全的恢复。而这时当你试图打开数据库时，oracle提示你用resetlogs选项，当你使用该选项时oracle又不允许你使用该选项，总之你想打开数据库，可就是打不开。
1、最好做一个物理的库的全备
2、使用sqlplus 启动库至mount
sqlplus /nolog
sql>connect internal
sql>startup mount
3、确保所有的数据文件都处于"END BACKUP"状态
sql>set pages 0 feedback off lines 132
sql>spool alter_df.sql
sql>SELECT 'alter database datafile '||file_name||' END BACKUP;' from v$datafile;
sql>spool off
sql>@alter_df.sql
4、试着打开数据库
sql>alter database open;
如数据库成功打开，余下的都不需要做了，到此为止
5、如果你在打开时被要求进行恢复，使用"UNTIL CANCEL"这种进行恢复,然后再发出ALTER DATABASE OPEN RESETLOGS这个命令
sql>recover database until cancel;
sql>alter database open resetlogs;
6、如果数据库仍不能打开，把库down掉
sql>shutdown immediate
7、在init.ora中加入如下参数
_allow_resetlogs_corruption=TRUE
8、执行如下语句
sql>connect internal
sql>startup mount
sql>@alter_df.sql
sql>alter database open
使用未写入文档参数_ALLOW_RESETLOGS_CORRUPTION进行崩溃恢复_ALLOW_RESETLOGS_CORRUPTION进行崩溃恢复经典
9、如在alter database open时仍旧报错，使用until cancel恢复
sql>recover database until cancel;
sql>alter database open resetlogs;
10、经过"9",数据库一定被打开了，数据库被打开后，马上执行一个full export
11、down掉库,去掉_all_resetlogs_corrupt参数
12、重建库
13、import并完成恢复
14、建议执行一下ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;