ORA-01194错误恢复方法一

妖狐艹你老母 2022-10-02 07:55 530阅读 0赞

## 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

连接到:  
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'

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'

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>