oracle未归档current redo丢失或损坏



oracle未归档current redo损坏的恢复

如下例子：
----Session 1
SQL> conn zw/zw
Connected.

SQL> select count(1) from t;

  COUNT(1)
----------
     14041

SQL> delete from t where rownum < 1001;
1000 rows deleted.

SQL> commit;
Commit complete.

 

---Session 2

SQL> select * from v$log;

GROUP#   THREAD#  SEQUENCE#  BYTES  BLOCKSIZE MEMBERS ARC STATUS  FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#      NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- --------
  1    1      13   52428800   512    1      NO  INACTIVE    1692121      29-DEC-15   1692124   29-DEC-15
  2    1      14   20971520   512    1      NO  INACTIVE    1692124      29-DEC-15   1692127   29-DEC-15
  3    1      15   52428800   512    1      NO  CURRENT    1692127      29-DEC-15   2.8147E+14
 
  
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/redo03.log bs=512 count=10
0+0 records in
0+0 records out
0 bytes (0 B) copied, 0.000112526 s, 0.0 kB/s

SQL> shutdown immediate
ORA-03113: end-of-file on communication channel
Process ID: 20455
Session ID: 145 Serial number: 3

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 12

SQL> recover database until cancel;
ORA-00279: change 1692127 generated at 12/29/2015 16:56:53 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0.4/db_1/dbs/arch1_15_899742732.dbf
ORA-00280: change 1692127 for thread 1 is in sequence #15

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_15_899742732.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_15_899742732.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> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

添加隐含参数
[oracle@zw_test_26_75 tmp]$ vi pfile.ora

*._allow_resetlogs_corruption=true
*._allow_error_simulation=true
~                             

SQL> startup mount pfile='/tmp/pfile.ora';
ORACLE instance started.

Total System Global Area  952020992 bytes
Fixed Size      2258960 bytes
Variable Size    314574832 bytes
Database Buffers   629145600 bytes
Redo Buffers      6041600 bytes
Database mounted.
SQL>

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1692134], [0],
[1692312], [4194432], [], [], [], [], [], []
Process ID: 20866
Session ID: 145 Serial number: 3

SQL> recover database until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

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

打开失败

++++ 使用10015 event手工推进scn
 
SQL>  startup mount pfile='/tmp/pfile.ora';
ORACLE instance started.

Total System Global Area  952020992 bytes
Fixed Size      2258960 bytes
Variable Size    314574832 bytes
Database Buffers   629145600 bytes
Redo Buffers      6041600 bytes
Database mounted.
SQL>

SQL>  alter session set events '10015 trace name adjust_scn level 1';

Session altered.

 
SQL> alter database open;
Database altered.

SQL> select * from v$log;

GROUP#   THREAD#  SEQUENCE#  BYTES    BLOCKSIZE   MEMBERS ARC STATUS    FIRST_CHANGE# FIRST_TIM NEXT_CHANGE#  NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- --------------
  1    1      1      52428800  512  1     NO  INACTIVE    1692128       29-DEC-15     1712135   29-DEC-15
  2    1      2      20971520  512  1     NO  CURRENT    1712135       29-DEC-15    2.8147E+14
  3    1      0      52428800  512  1     YES UNUSED   0     0
 
 

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>
System altered.

SQL>

----Session 1
SQL> conn zw/zw
Connected.

SQL> select count(1) from t;

  COUNT(1)
----------
     14041

SQL> delete from t where rownum < 1001;
1000 rows deleted.

SQL> commit;
Commit complete.

SQL> select count(1) from  t;

  COUNT(1)
----------
     13041
 
 
恢复后的

-----session 2

SQL> select count(1) from  t;

  COUNT(1)
----------
     13041