oracle-01194,ORA-01194

环境

redhat5.5

oracle 11g

开库报错

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: '/oradata/prod/system01.dbf'

SQL>  recover database until cancel;

ORA-00279: change 1513109 generated at 11/10/2013 13:08:31 needed for thread 1

ORA-00289: suggestion : /home/oracle/backup/arch1/1_3_831128809.dbf

ORA-00280: change 1513109 for thread 1 is in sequence #3

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

auto

ORA-00308: cannot open archived log

'/home/oracle/backup/arch1/1_3_831128809.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log

'/home/oracle/backup/arch1/1_3_831128809.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

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: '/oradata/prod/system01.dbf'

查看当前日志组

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC

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

STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#

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

NEXT_TIME

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

1          1          1   52428800        512          1 NO

INACTIVE               1512091 2013-11-10 13:06:49      1512763

2013-11-10 13:08:24

3          1          3   52428800        512          1 NO

CURRENT                1513109 2013-11-10 13:08:31   2.8147E+14

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC

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

STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE#

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

NEXT_TIME

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

2          1          2   52428800        512          1 NO

INACTIVE               1512763 2013-11-10 13:08:24      1513109

2013-11-10 13:08:31

查看日志组文件

SQL> select member from v$logfile;

MEMBER

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

/oradata/prod/redo01.log

/oradata/prod/redo02.log

/oradata/prod/redo03.log

恢复的时候直接给日志组文件

SQL> recover database until cancel;

ORA-00279: change 1513109 generated at 11/10/2013 13:08:31 needed for thread 1

ORA-00289: suggestion : /home/oracle/backup/arch1/1_3_831128809.dbf

ORA-00280: change 1513109 for thread 1 is in sequence #3

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

/oradata/prod/redo03.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

开库ok

SQL> alter database open resetlogs;

Database altered.