联机日志损坏如何恢复

1、如果是非当前日志而且归档（从v$log可以看出），可以使用 Alter database clear logfile group n 来创建一个新的日志文件；如果该日志还没有归档，则需要用 Alter database clear unarchived logfile group n

例如(手工vi状态是UNUSED且未归档的日志文件并破坏之)：

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0    5242880          1 NO UNUSED              9532226722 30-MAY-08
         2          1          0    5242880          1 NO UNUSED              9532226613 30-MAY-08
         3          1         33    5242880          1 NO CURRENT             9532313882 02-JUN-08

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         2         ONLINE /opt/oracle/db02/oradata/ORCL/redo02.log
         1         ONLINE /opt/oracle/db01/app/oracle/oradata/ORCL/redo01.log
         3         ONLINE /opt/oracle/db03/oradata/ORCL/redo03.log

SQL> !vi /opt/oracle/db02/oradata/ORCL/redo02.log --开始搞破坏，破坏的是非当前且未归档的日志
...
SQL> startup
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size                   731704 bytes
Variable Size             436207616 bytes
Database Buffers          104857600 bytes
Redo Buffers                1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/oracle/db02/oradata/ORCL/redo02.log'

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

SQL>

2、如果是当前日志损坏，一般不能clear，则可能意味着丢失数据。如果有备份，可以采用备份进行不完全恢复；如果没有备份，可能只能用 _allow_resetlogs_corruption=true 来进行强制恢复了，但是，这样的方法是不建议的，最好在有Oracle support的指导下进行。

如果不是 current and active 日志坏了仅是 inactive 坏了，则：
clear log;
startup mount;
alter database clear logfile '...';

否则做下面的步骤：（先备份您的数据库！！！！！）
_ALLOW_RESETLOGS_CORRUPTION=true
statup mount
recover database using backup controlfile until cancel;
cancel
alter database open resetlogs; --maybe error ,not serious
shutdown
去掉该参数_ALLOW_RESETLOGS_CORRUPTION
startup

例如(手工vi状态是current的日志文件并破坏之)：

最好先做一个物理的库的全备。

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          0    5242880          1 NO UNUSED              9532226722 30-MAY-08
         2          1          0    5242880          1 NO UNUSED              9532226613 30-MAY-08
         3          1         33    5242880          1 NO CURRENT             9532313882 02-JUN-08

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER
---------- ------- ------- ------------------------------------------------------------
         2         ONLINE /opt/oracle/db02/oradata/ORCL/redo02.log
         1         ONLINE /opt/oracle/db01/app/oracle/oradata/ORCL/redo01.log
         3         ONLINE /opt/oracle/db03/oradata/ORCL/redo03.log

SQL> !vi /opt/oracle/db03/oradata/ORCL/redo03.log --开始搞破坏，破坏的是当前且未归档的日志
...
SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size                   731704 bytes
Variable Size             436207616 bytes
Database Buffers          104857600 bytes
Redo Buffers                1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/db03/oradata/ORCL/redo03.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

SQL> show parameter spfile --确认是否通过spfile启动的，否则create spfile from pile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      ?/dbs/spfile@.ora

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; --隐含参数须打引号

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size                   731704 bytes
Variable Size             436207616 bytes
Database Buffers          104857600 bytes
Redo Buffers                1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/db03/oradata/ORCL/redo03.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 9532314069 generated at 06/02/2008 15:56:19 needed for thread 1
ORA-00289: suggestion : /opt/oracle/db01/app/oracle/product/9.2.0/dbs/arch1_33.dbf
ORA-00280: change 9532314069 for thread 1 is in sequence #33


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

ORA-00308: cannot open archived log '/opt/oracle/db01/app/oracle/product/9.2.0/dbs/arch1_33.dbf'
ORA-27037: unable to obtain file status
SVR4 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: '/opt/oracle/db01/app/oracle/oradata/ORCL/oradata/ORCL/system01.dbf'


SQL> alter database open resetlogs;

Database altered.

数据库被打开后，马上执行一个full export；后面把隐含参数改回后，重建库，并full import；最后建议执行一下 ANALYZE TABLE ... VALIDATE STRUCTURE CASCADE;

SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile; --open resetlogs后把隐含参数改回

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size                   731704 bytes
Variable Size             436207616 bytes
Database Buffers          104857600 bytes
Redo Buffers                1925120 bytes
Database mounted.
Database opened.
SQL>