误删除 logfile 的解决办法

描述下我的问题 oracle 处于归档模式，从未备份过

出现错误之前操作  ：

SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------------------
         3         ONLINE  /orastore/oracle/oradata/myora/redo03.log
         2 STALE   ONLINE  /orastore/oracle/oradata/myora/redo02.log
         1         ONLINE  /orastore/oracle/oradata/myora/redo01.log
         4         ONLINE  /orastore/oracle/oradata/myora/redo04.log
         4 INVALID ONLINE  /orastore/oracle/oradata/myora/redo04a.log

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

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 INACTIVE
         4 CURRENT
SQL> alter database drop logfile member '/orastore/oracle/oradata/myora/redo04a.log';
alter database drop logfile member '/orastore/oracle/oradata/myora/redo04a.log'
*
ERROR at line 1:
ORA-01609: log 4 is the current log for thread 1 - cannot drop members
ORA-00312: online log 4 thread 1: '/orastore/oracle/oradata/myora/redo04.log'
ORA-00312: online log 4 thread 1: '/orastore/oracle/oradata/myora/redo04a.log'

SQL> alter system switch logfile;

System altered.

SQL>  alter database drop logfile member '/orastore/oracle/oradata/myora/redo04a.log';

Database altered.

SQL> !     
cd[oracle@localhost admin]$ cd /orastore/oracle/oradata/myora/
[oracle@localhost myora]$ ll

-rw-r----- 1 oracle oinstall  52429312 08-23 00:58 redo01.log
-rw-r----- 1 oracle oinstall  52429312 08-23 01:53 redo02.log
-rw-r----- 1 oracle oinstall  52429312 08-23 00:29 redo03.log
-rw-r----- 1 oracle oinstall  52429312 08-23 01:46 redo04a.log
-rw-r----- 1 oracle oinstall  52429312 08-23 01:51 redo04.log

[oracle@localhost myora]$ rm -f redo04.log

[oracle@localhost myora]$ ll
-rw-r----- 1 oracle oinstall  52429312 08-23 00:58 redo01.log
-rw-r----- 1 oracle oinstall  52429312 08-23 01:53 redo02.log
-rw-r----- 1 oracle oinstall  52429312 08-23 00:29 redo03.log
-rw-r----- 1 oracle oinstall  52429312 08-23 01:46 redo04a.log

[oracle@localhost myora]$ cp redo04a.log redo04.log
[oracle@localhost myora]$ ll
总计 1170960
-rw-r----- 1 oracle oinstall   7061504 08-23 01:56 control01.ctl
-rw-r----- 1 oracle oinstall   7061504 08-23 01:56 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 08-23 01:56 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 08-23 01:56 example01.dbf
-rw-r----- 1 oracle oinstall  52429312 08-23 00:58 redo01.log
-rw-r----- 1 oracle oinstall  52429312 08-23 01:56 redo02.log
-rw-r----- 1 oracle oinstall  52429312 08-23 00:29 redo03.log
-rw-r----- 1 oracle oinstall  52429312 08-23 01:46 redo04a.log
-rw-r----- 1 oracle oinstall  52429312 08-23 01:56 redo04.log
-rw-r----- 1 oracle oinstall 251666432 08-23 01:56 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 08-23 01:56 system01.dbf
-rw-r----- 1 oracle oinstall  20979712 08-22 19:07 temp01.dbf
-rw-r----- 1 oracle oinstall  31465472 08-23 01:56 undotbs01.dbf
-rw-r----- 1 oracle oinstall   5251072 08-23 01:56 users01.dbf
[oracle@localhost myora]$ exit
exit

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  1219256 bytes
Variable Size              79693128 bytes
Database Buffers          247463936 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-00322: log 4 of thread 1 is not current copy
ORA-00312: online log 4 thread 1: '/orastore/oracle/oradata/myora/redo04.log'

 

解决办法：

alter database open read only;

Database altered.

 

select group#,sequence#,archived,status from v$log;

    GROUP#  SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
         1         14 YES INACTIVE
         2         16 NO  CURRENT
         3         13 YES INACTIVE
         4         15 YES INACTIVE

发现4是已经被归档的，非常兴奋

SQL> alter database drop logfile group 4;

Database altered.

直接就把group 4给干掉了

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  1219256 bytes
Variable Size              79693128 bytes
Database Buffers          247463936 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.

关掉再开启 成功解决问题

 

下面是我在网上找到的一些不同情况下的方法

 

oracle 不能正常启动，可以mount,

机器异常断电，oracle不能正常启动，可以mount，不能open。数据库未备份过。  
  错误码：  
  At   startup,   the   database   will   mount,   but   gives   the   following   errors   at   open:    
    ORA-00312:   "online   log   %s   thread   %s:   '%s'"    
            Cause:   This   message   reports   the   filename   for   details   of   another   message.    
          Action:   Other   messages   will   accompany   this   message.   See   the    
                          associated   messages   for   the   appropriate   action   to   take.    
    ORA-00322:   "log   %s   of   thread   %s   is   not   current   copy"    
            Cause:   Check   of   log   file   header   at   database   open   found   that   an   online   log      
                          appears   to   be   an   incorrectly   restored   backup.    
          Action:   Restore   correct   file   or   reset   logs.    
   
  解决方法描述：  
  首先检查ORACLE_HOME   和ORACLE_SID在startup参数中是否正确，如果不正确重新startup。  
   
  Recover   the   loss   of   an   inactive,   online   redo   log   group.    
   
  SQL>ALTER   DATABASE   CLEAR   LOGFILE   'filename';    
   
  If   there   is   more   than   one  LOGFILE  in   the   redo   log   group   then   you   must    
  specify   all   the   log   files.    
   
  For   example,   the   following   fails:    
   
  SVRMGR>   alter   database   clear  LOGFILE  '/vobs/oracle/dbs/log3.log';    
  alter   database   clear  LOGFILE   '/vobs/oracle/dbs/log3.log'    
  *    
  ORA-1514:   error   in   log   specification:   no   such   log    
  ORA-1517:   log   member:   '/vobs/oracle/dbs/log3.log    
   
  But,   specifying   all   the   log   files   in   the   group   works:    
  SVRMGR>   ALTER   DATABASE   CLEAR  LOGFILE('/vobs/oracle/dbs/t4.log','/vobs/oracle/;    
  Statement   processed.    
   
  介绍一个意外情况，如果上面的方法不成功，数据库还是不能open，采用recover，restore都不成功，说明LOGFILE可能已经损坏，数据库打开需要做一致性检查，所以不能正常打开。因为数据库未作过备份，为了尽可能地减少损失，采用打开数据库，然后立刻导出数据的方法。  
  oracle有一个不推荐的方法，可以使数据库在不进行一致性检查的方式下打开数据库。  
  做法：  
  1。为保险起见，将数据库的系统表空间，数据文件，控制文件均做一次备份：copy。  
  2。在初始化配置文件中（init.ora）加上：  
  _allow_resetlogs_corruption=true  
  _corrupted_rollback_segments=(将所有rollback_segments=(...)的内容加到这里）  
  将rollback_segments=(...)前加上#  
  3。startup   mount  
  recover   database   until   cancel；  
  cancel  
  alter   database   open   resetlogs;  
  此时数据库已经可以打开了，请立刻将数据export出来，然后重新安装oracle，重新建立database，再将数据import。  
  注意：这种方法属于数据库的不完全恢复，最后提交的sql可能会丢失，毕竟能够尽可能地减少损失，可以试试。  

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

转载于:http://blog.itpub.net/266281/viewspace-676061/