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
