ORACLE RAC下丢失online logfile的恢复（inactive，active，current）

测试环境：
系统：LINUX-64
数据库：10.2.0.1
INSTANCE：RACDB1，RACDB2     
二节点的RAC，使用ASM。

一：当onlinelog是inactive，解决方法如下

（1）关闭数据库，删除inactive状态 logfile。启动时报错如下。

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
查看alert日志：
Errors in file /u01/app/oracle/admin/RACDB/bdump/racdb1_lgwr_16387.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+RAC_DISK/racdb/onlinelog/group_1.257.794232617'
ORA-17503: ksfdopn:2 Failed to open file +RAC_DISK/racdb/onlinelog/group_1.257.794232617
ORA-15012: ASM file '+RAC_DISK/racdb/onlinelog/group_1.257.794232617' does not exist
Thu Sep 27 09:46:33 2012
Errors in file /u01/app/oracle/admin/RACDB/bdump/racdb1_lgwr_16387.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+RAC_DISK/racdb/onlinelog/group_1.257.794232617'
ORA-17503: ksfdopn:2 Failed to open file +RAC_DISK/racdb/onlinelog/group_1.257.794232617
ORA-15012: ASM file '+RAC_DISK/racdb/onlinelog/group_1.257.794232617' does not exist

（2）确认日志的状态：

SQL> select thread#,group#,status from v$log;

   THREAD#     GROUP# STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         1          2 INACTIVE
         1          5 CURRENT
         2          4 INACTIVE
         2          3 CURRENT
确定GROUP1为inactive；

（3）尝试恢复

现在RACDB1为mount，RACDB2为open。

RACDB1>startup mount

RACDB1>alter database clear logfile group 1;
alter database clear logfile group 1
*
ERROR at line 1:
ORA-01138: database must either be open in this instance or not at all

ORA-01138错误的解释：
RACDB1>!oerr ora 01138
01138, 00000, "database must either be open in this instance or not at all"
// *Cause:  The requested operation can not be done when the database is
//          mounted but not open in this instance, and another instance has
//          the database open.
// *Action: Execute the operation in an open instance, open the datbase in
//          this instance, or close the database in the other instances.

根据这个提示，在另个instance执行。
RACDB2>alter database clear logfile group 1;

Database altered.

注意：clear相当于DROP，然后再ADD。
其实也可先drop，再add，参考：
http://space.itpub.net/26655292/viewspace-745154

（4）打开数据库

RACDB1>alter database open;

Database altered.
此时，数据无丢失。

 

二：当onlinelog是active或current，解决方法如下

（1）关闭数据库，删除current状态 logfile。启动时报错如下。

RACDB1>startup
ORACLE instance started.

Total System Global Area  184549376 bytes
Fixed Size                  2019448 bytes
Variable Size             159387528 bytes
Database Buffers           20971520 bytes
Redo Buffers                2170880 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
查看alert，如下：
Errors in file /u01/app/oracle/admin/RACDB/bdump/racdb1_lgwr_24317.trc:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1: '+RAC_DISK/racdb/onlinelog/group_5.276.795086553'
ORA-17503: ksfdopn:2 Failed to open file +RAC_DISK/racdb/onlinelog/group_5.276.795086553
ORA-15012: ASM file '+RAC_DISK/racdb/onlinelog/group_5.276.795086553' does not exist
ORA-00312: online log 5 thread 1: '+RAC_DISK/racdb/onlinelog/group_5.269.795086545'
ORA-17503: ksfdopn:2 Failed to open file +RAC_DISK/racdb/onlinelog/group_5.269.795086545
ORA-15012: ASM file '+RAC_DISK/racdb/onlinelog/group_5.269.795086545' does not exist

（2）确认状态
RACDB2>select thread#,group#,status from v$log;

   THREAD#     GROUP# STATUS
---------- ---------- ----------------
         1          1 UNUSED
         1          2 INACTIVE
         2          3 CURRENT
         2          4 INACTIVE
         1          5 CURRENT
查看group5是current状态。

（3）尝试恢复

RACDB2>alter database clear logfile group 5;
alter database clear logfile group 5
*
ERROR at line 1:
ORA-00350: log 5 of instance RACDB1 (thread 1) needs to be archived
ORA-00312: online log 5 thread 1:
'+RAC_DISK/racdb/onlinelog/group_5.269.795086545'
ORA-00312: online log 5 thread 1:
'+RAC_DISK/racdb/onlinelog/group_5.276.795086553'

RACDB2>alter database clear unarchived logfile group 5;
alter database clear unarchived logfile group 5
*
ERROR at line 1:
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1:
'+RAC_DISK/racdb/onlinelog/group_5.276.795086553'
ORA-17503: ksfdopn:2 Failed to open file
+RAC_DISK/racdb/onlinelog/group_5.276.795086553
ORA-15012: ASM file '+RAC_DISK/racdb/onlinelog/group_5.276.795086553' does not
exist
ORA-00312: online log 5 thread 1:
'+RAC_DISK/racdb/onlinelog/group_5.269.795086545'
ORA-17503: ksfdopn:2 Failed to open file
+RAC_DISK/racdb/onlinelog/group_5.269.795086545
ORA-15012: ASM file '+RAC_DISK/racdb/onlinelog/group_5.269.795086545' does not
exist

（4）解决current,active联机日志。需要关闭所有实例。

RACDB1>startup mount

RACDB1>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RACDB1>recover database until cancel;
Media recovery complete.
RACDB1>alter database open resetlogs;

Database altered.

RACDB1>alter system switch logfile;

System altered.

RACDB2>startup

总结：active和current的恢复过程一样，只不过active状态表明已经归档，丢失后不会游数据丢失。而current可能会丢失数据。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26655292/viewspace-745157/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26655292/viewspace-745157/