测试环境模拟数据库宕机再启动出现当前联机日志损坏

[天上掉下个蓝妹妹]

SQL> conn / as sysdba
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 1 CURRENT
2 0 UNUSED
3 0 UNUSED
SQL> conn test/1
Connected.
--插入数据并提交
SQL> begin
2 for v in 1..100 loop
3 insert into test values(11,'qq');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
--插入数据不提交
SQL> begin
2 for v in 1..100 loop
3 insert into test values(22,'aa');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select count(*) from test;
COUNT(*)
----------
200
SQL> conn / as sysdba
Connected.
--不一致停库
SQL> shutdown abort;
ORACLE instance shut down.
oracle@DB:[/u01/app/oracle/oradata/orcl]ll redo*
-rw-rw----. 1 oracle oracle 52429312 Oct 21 16:16 redo01.log
-rw-rw-r--. 1 oracle oracle 52429312 Oct 21 16:16 redo02.log
-rw-rw-r--. 1 oracle oracle 52429312 Oct 21 16:16 redo03.log
--重命名当前日志
oracle@DB:[/u01/app/oracle/oradata/orcl]mv redo01.log redo01.log.bak
SQL> startup
ORACLE instance started.
Total System Global Area 221331456 bytes
Fixed Size 2251856 bytes
Variable Size 163578800 bytes
Database Buffers 50331648 bytes
Redo Buffers 5169152 bytes
Database mounted.
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-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> recover database until cancel;
ORA-00279: change 7532876 generated at 10/21/2016 16:00:04 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch4-bak/1_1_925768605.dbf
ORA-00280: change 7532876 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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'
ORA-01112: media recovery not started
--创建pfile
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
File created.
SQL> shutdown abort;
ORACLE instance shut down.
--加入隐含参数
oracle@DB:[/u01/app/oracle/oradata/orcl]cat /home/oracle/pfile.ora|grep _allow
_allow_resetlogs_corruption=TRUE
_allow_error_simulation=TRUE
SQL> startup pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 221331456 bytes
Fixed Size 2251856 bytes
Variable Size 163578800 bytes
Database Buffers 50331648 bytes
Redo Buffers 5169152 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [7532883], [0],
[7533126], [4194545], [], [], [], [], [], []
Process ID: 5001
Session ID: 1 Serial number: 5
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 221331456 bytes
Fixed Size 2251856 bytes
Variable Size 163578800 bytes
Database Buffers 50331648 bytes
Redo Buffers 5169152 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 7552886 generated at 10/21/2016 16:12:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch4-bak/1_2_925834283.dbf
ORA-00280: change 7552886 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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'
ORA-01112: media recovery not started
--推进SCN
SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5115
Session ID: 1 Serial number: 5

在无备份和归档情况下，数据库宕机再启动出现当前日志损坏，应该怎么恢复呀？

[阳泉酒家小当家]

你可以看一下： 无归档日志？无备份？windows32位平台？数据库异常宕机的恢复 http://blog.csdn.net/totoov5/article/details/8705567

[阳泉酒家小当家]

貌似得用bbed直接修改文件吧，但是感觉这样修改之后，数据的本身的准确性不能保证。 因为之前你是shutdown abort，可能有些事务正好出于活动状态，还没有提交或者回滚，所以，在系统再次启动时要做实例恢复，而因为你当前的日志损坏了，所以，没办法进行正常的实例恢复，所以就报错了。

[阳泉酒家小当家]

这种情况下，应该还可以使用网上提供的一些特殊工具，这些工具的作用是能解析文件，然后把表里能读的数据都读出来。 你看看，这个不知道对你这种情况有没有用： Oracle数据库ODU的几种恢复场景 http://database.51cto.com/art/201105/264727.htm

[惜分飞]

这种一般还不用odu，具体提供alert日志和http://www.xifenfei.com/oracle_recovery_check 检查的结果，我们分析下


odu，dul之类的工具，使用的机会很少，如果使用了，就是后果很严重

[天上掉下个蓝妹妹]

引用 4 楼 xifenfei 的回复:

这种一般还不用odu，具体提供alert日志和http://www.xifenfei.com/oracle_recovery_check 检查的结果，我们分析下 odu，dul之类的工具，使用的机会很少，如果使用了，就是后果很严重

我用冷备份恢复数据库，再重新进行模拟测试，测试期间alert log和oracle_recovery_check收集结果已发到您邮箱。

[天上掉下个蓝妹妹]

测试期间alert log和oracle_recovery_check收集结果我也放在云盘了，链接: https://pan.baidu.com/s/1dFuoSE1 密码: rqda