ORA-01194错误恢复方法一

ORA-01194错误恢复方法一

ORA-01194: file 1 needs more recovery to be consistent

先说一下，我发现的这个错误的一个原因是冷备的时候，数据文件不一致造成的。

恢复方法如下：

1 如果归档模式，且在线日志和历史归档日志完好，可以使用recover database using backup controlfile until cancel;

优先使用在线日志尝试恢复。

2 使用_allow_resetlogs_corruption隐含参数强制打开数据库，exp全库数据，再imp。

3 用其他备份恢复，比如RMAN和exp出来的DMP文件。

现在介绍第一种方法。

C:Documents and SettingsAdministrator>sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on 星期二 1月 8 19:09:20 2008

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

连接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace;

数据库已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
数据库装载完毕。
数据库已经打开。

注意：在这一步，我把所有的数据文件由E盘拷贝到F:SUN目录；

这个时候的数据文件肯定是不一致的，往下看。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145750508 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "SUN" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 454
7 LOGFILE
8 GROUP 1 'F:SUNREDO01.LOG' SIZE 10M,
9 GROUP 2 'F:SUNREDO02.LOG' SIZE 10M,
10 GROUP 3 'F:SUNREDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'F:SUNSYSTEM01.DBF',
14 'F:SUNUNDOTBS01.DBF',
15 'F:SUNSYSAUX01.DBF',
16 'F:SUNUSERS01.DBF'
17 CHARACTER SET ZHS16GBK
18 ;

控制文件已创建

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01589: ??????????? RESETLOGS ? NORESETLOGS ??

SQL> alter session set nls_language=american;

Session altered.

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-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:SUNSYSTEM01.DBF'

SQL> set wrap off
SQL> set lin 300
SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ----------
1 ONLINE ONLINE 476049 08-JAN-08
2 ONLINE ONLINE 476049 08-JAN-08
3 ONLINE ONLINE 476049 08-JAN-08
4 ONLINE ONLINE 476049 08-JAN-08

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 476049 generated at 01/08/2008 19:13:19 needed for thread 1
ORA-00289: suggestion : F:ORACLEFLASH_RECOVERY_AREASUNARCHIVELOG2008_01_08O1_MF_1_18_%U_.ARC
ORA-00280: change 476049 for thread 1 is in sequence #18

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log 'F:ORACLEFLASH_RECOVERY_AREASUNARCHIVELOG2008_01_08O1_MF_1_18_%

U_.ARC'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

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: 'F:SUNSYSTEM01.DBF'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 476049 generated at 01/08/2008 19:13:19 needed for thread 1
ORA-00289: suggestion : F:ORACLEFLASH_RECOVERY_AREASUNARCHIVELOG2008_01_08O1_MF_1_18_%U_.ARC
ORA-00280: change 476049 for thread 1 is in sequence #18

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:oracleoradatasunsunredo01.log
ORA-00310: archived log contains sequence 17; sequence 18 required
ORA-00334: archived log: 'E:ORACLEORADATASUNSUNREDO01.LOG'

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: 'F:SUNSYSTEM01.DBF'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 476049 generated at 01/08/2008 19:13:19 needed for thread 1
ORA-00289: suggestion : F:ORACLEFLASH_RECOVERY_AREASUNARCHIVELOG2008_01_08O1_MF_1_18_%U_.ARC
ORA-00280: change 476049 for thread 1 is in sequence #18

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:oracleoradatasunsunredo02.log
Log applied.
Media recovery complete.
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>