解决 ORA-00305, ORA-00312以及ORA-01194, ORA-01110

[oracle@server1 bin]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 29 08:40:25 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 2.0417E+10 bytesFixed Size                  2240256 bytesVariable Size            1.5891E+10 bytesDatabase Buffers          4.4560E+9 bytesRedo Buffers               69280563 bytesDatabase mounted.ORA-00305: log 3 of thread 1 inconsistent; belongs to another databaseORA-00312: online log 3 thread 1: '+FDA1/dbservice1/redo03.log'

出现这个错误是因为在dbservice2上执行创建或变更重做日志的操作，但重做日志的地址却指向了dbservice1上的日志文件redo03.log，这个文件正好是dbservice1的当前重做日志文件。比如在dbservice2上执行了下面的语句

alter database drop logfile group 3; ALTER DATABASE ALTER LOGFILE THREAD 1GROUP 3 ('+FDA1/dbservice1/redo03.log') SIZE 52485760  reuse;

查看日志如下：

SQL> select * from v$log;    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC---------- ---------- ---------- ---------- ---------- ---------- ---STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME---------------- ------------- --------- ------------ ---------         1          1       2983  524857600        512          1 YESINACTIVE              85628220 29-DEC-12     85644142 29-DEC-12        2          1          0  524857600        512          1 YESUNUSED                       0                      0         3          1       2989  524857600        512          1 NOCURRENT               85717298 29-DEC-12   2.8147E+14

此时任何对该Log的操作基本都无效，drop redo logfile

SQL> alter database drop logfile group 3;alter database drop logfile group 3*ERROR at line 1:ORA-01623: log 3 is current log for instance dbservice1(thread 1) - cannot dropORA-00312: online log 3 thread 1: '+FDA1/dbservice1/redo03.log'

使用recover database恢复数据库:

SQL> recover database until cancelORA-00279: change 85717298 generated at 12/29/2012 00:26:41 needed for thread 1ORA-00289: suggestion : +FDA1ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'SERVICE1'ORA-00280: change 85717298 for thread 1 is in sequence #2989Specify log: {<RET>=suggested | filename | AUTO | CANCEL}ORA-00308: cannot open archived log '+FDA1'ORA-17503: ksfdopn:2 Failed to open file +FDA1ORA-15045: ASM file name '+FDA1' is not in reference formORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '+FDA1/dbservice1/system01.dbf'

使用resetlogs打开数据库：失败

SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '+DATA1/ooa3cm/system01.dbf'

尝试利用RMAN恢复archive log和系统文件：失败

RMAN> restore archivelog from logseq=2989 thread 1;Starting restore at 29-DEC-12using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of restore command at 12/29/2012 09:48:49RMAN-20242: specification does not match any archived log in the repositoryRMAN> recover database until logseq=2990 thread=1;Starting recover at 29-DEC-12using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4starting media recoveryunable to find archived logarchived log thread=1 sequence=2989Oracle Error:ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '+FDA1/dbservice1/system01.dbf'RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 12/29/2012 09:55:10RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2989 and starting SCN of 85717298

尝试使用语句将数据库redo log文件重命名，因为只是简单的修改配置、修改操作系统上的文件名，而没有把该重做日志修改为本机所用：打开数据库失败

SQL> ALTER DATABASE RENAME FILE '+FDA1/dbservice1/redo03.log' TO '+FDA1/dbservice1/redo03-1.log';Database altered.SQL>  alter database open  RESETLOGS; alter database open  RESETLOGS*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '+FDA1/dbservice1/system01.dbf'

清除日志文件内容，依然没用，没有把改日志文件修改为本机所用：修复数据库失败

SQL> alter database clear logfile group 3;Database altered.SQL> recover database until cancel;ORA-00283: recovery session canceled due to errorsORA-01610: recovery using the BACKUP CONTROLFILE option must be doneSQL> recover database until cancel using BACKUP CONTROLFILE;ORA-00279: change 85717298 generated at 12/29/2012 00:26:41 needed for thread 1ORA-00289: suggestion : +FDA1ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'DBSERVICE1'ORA-00280: change 85717298 for thread 1 is in sequence #2989Specify log: {<RET>=suggested | filename | AUTO | CANCEL}CANCELORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error belowORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '+FDA1/dbservice1/system01.dbf'ORA-01112: media recovery not startedSQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01194: file 1 needs more recovery to be consistentORA-01110: data file 1: '+FDA1/dbservice1/system01.dbf'

没办法，只能在initdbservice1.ora中添加一个允许强制打开参数

_allow_resetlogs_corruption=true

直接打开数据库失败，出现600错误，错误终于变了，有救了

SQL> startup pfile=/u01/oracle/product/11.2.0.3/dbs/initdbservice1.oraORACLE instance started.Total System Global Area 2.0417E+10 bytesFixed Size                  2240256 bytesVariable Size            1.5730E+10 bytesDatabase Buffers          4.6171E+9 bytesRedo Buffers               69280563 bytesDatabase mounted.ORA-01092: ORACLE instance terminated. Disconnection forcedORA-00600: internal error code, arguments: [kclchkblk_4], [0], [85742478], [0],[85737311], [], [], [], [], [], [], []Process ID: 27223Session ID: 6689 Serial number: 7

先mount数据库吧

SQL> startup mount pfile=/u01/oracle/product/11.2.0.3/dbs/initdbservice1.oraORACLE instance started.Total System Global Area 2.0417E+10 bytesFixed Size                  2240256 bytesVariable Size            1.5730E+10 bytesDatabase Buffers          4.6171E+9 bytesRedo Buffers               69280563 bytesDatabase mounted.

由于强制打开，原来的current 的 redo log已经不可用，虽然现在还标记current，但SCN、log file sequence信息都不一致，所以要Oracle的内部事件来调整SCN，redo log文件序列也会重新开始

SQL> alter session set events '10015 trace name adjust_scn level 10';Session altered.SQL> alter database open;Database altered.

数据库开启之后redo log信息如下，group3还是当前重做日志：

SQL> select * from v$log;    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC---------- ---------- ---------- ---------- ---------- ---------- ---STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME---------------- ------------- --------- ------------ ---------         1          1          1  52485760        512          1 YESINACTIVE              85717299 29-DEC-12     85737307 29-DEC-12         2          1          2  52485760        512          1 YESINACTIVE              85737307 29-DEC-12     85757313 29-DEC-12         3          1          3  52485760        512          1 NOCURRENT               85757313 29-DEC-12   2.8147E+14

赶紧重建第三个logfile吧:

SQL> alter database switch logfile;Database altered.SQL> alter database drop logfile group 3;Database altered.SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 ('+FDA1/dbservice1/redo03.log') SIZE 52485760  reuse;  2Database altered.SQL> archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     1Next log sequence to archive   4Current log sequence           4

至此结束，把_allow_resetlogs_corruption参数去除，重启数据库吧

 

附上最后出现600错误的trace log的错误信息：

Thread 1 checkpoint: logseq 1, block 2, scn 85717302    on-disk rba: logseq 1, block 3, scn 85717304  start recovery at logseq 1, block 3, scn 85717304*** 2012-12-29 10:13:58.892Recovery sets nab of thread 1 seq 1 to 3 with 8 zeroblks*** 2012-12-29 10:14:02.2392012-12-29 10:14:02.239444 : Validate domain 02012-12-29 10:14:02.241162 : Validated domain 0, flags = 0x0*** 2012-12-29 10:14:02.838Incident 583554 created, dump file: /u01/oracle/diag/rdbms/DBSERVICE1/incident/incdir_583554/DBSERVICE1_ora_27223_i583554.trcORA-00600: internal error code, arguments: [kclchkblk_4], [0], [85742478], [0], [85737311], [], [], [], [], [], [], []