归档模式下redo丢失或损坏(active状态)



++++Session 1

SQL> create table t as select * from  dba_objects;
Table created.

SQL> create index t_idx_id on t(object_id);

Index created.

SQL> select count(1) from t;

  COUNT(1)
----------
     14039

SQL> delete from t where rownum<1001;

1000 rows deleted.

SQL> commit;

Commit complete.

++++Session 2

SQL>  alter system switch logfile;
System altered.

SQL> select * from v$log;

GROUP#  THREAD#  SEQUENCE#  BYTES  BLOCKSIZE MEMBERS ARC STATUS   FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------- ---------
  1      1       24     52428800    512  1   NO   CURRENT    1650411     29-DEC-15  2.8147E+14
  2      1       23     20971520    512  1   YES  ACTIVE  1650242     29-DEC-15  1650411    29-DEC-15
  3      1       22     52428800    512  1   YES  INACTIVE   1650239     29-DEC-15  1650242    29-DEC-15

SQL> col member format a50
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                  IS_
---------- ------- ------- -------------------------------------------
  1    ONLINE  /u01/app/oracle/oradata/orcl/redo01.log       NO
  2    ONLINE  /u01/app/oracle/oradata/orcl/redo02.log       NO
  3    ONLINE  /u01/app/oracle/oradata/orcl/redo03.log       NO
 
 
++++Session 3

[root@zw_test_26_75 ~]# dd if=/dev/null of=/u01/app/oracle/oradata/orcl/redo02.log bs=512 count=10
0+0 records in
0+0 records out
0 bytes (0 B) copied, 0.000120067 s, 0.0 kB/s

状态是active的，可以是arc，也可以是NO

++++Session 2
SQL&get; shutdown abort;
ORACLE instance shut down.
SQL&get;
SQL&get; startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              67110120 bytes
Database Buffers           96468992 bytes
Redo Buffers                2920448 bytes
Database mounted.

恢复失败，如果恢复成功直接可以open resetlogs;

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1

SQL> recover database until cancel;
ORA-00279: change 1628785 generated at 12/29/2015 14:57:25 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_97_896791384.dbf
ORA-00280: change 1628785 for thread 1 is in sequence #97

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_97_896791384.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-00308: cannot open archived log '/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_97_896791384.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 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: '/u01/app/oracle/oradata/orcl/system01.dbf'

SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

----pfile文件加入如下隐含参数：

*._allow_resetlogs_corruption=true
*._allow_error_simulation=true

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount pfile='/tmp/pfile.ora';
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              67110120 bytes
Database Buffers           96468992 bytes
Redo Buffers                2920448 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.