ORA-00333: redo log read error block 8194

重启服务器后，数据无法启动，日志介质损坏

1：数据库启动报错ORA-00333


SQL> select status from v$instance;

STATUS

------------

MOUNTED


SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00333: redo log read error block 8194 count 8192

SQL> select group#,sequence#,archived,status from v$log;

 

    GROUP#  SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

                 1     117247 NO  CURRENT

                 3     117246 NO  ACTIVE

                 2     117245 NO  INACTIVE


SQL> select group#,member from v$logfile;

GROUP# MEMBER

------ --------------------------------------------------

     3 /home/oracle/app/oracle/datafile/orcl/redo03.log

     2 /home/oracle/app/oracle/datafile/orcl/redo02.log

     1 /home/oracle/app/oracle/datafile/orcl/redo01.log


查看alert日志，group 1日志文件文件损坏：

ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/datafile/orcl/redo01.log'

ORA-27072: File I/O error

Additional information: 4

Additional information: 8194

Additional information: 1084416

可以看到损坏的是当前的联机日志。

但是这个数据库没有备份，没有开归档，只能使用非常规恢复！

设置参数“_allow_resetlogs_corruption” 并重启数据库导mount


查看隐含参数

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

  from x$ksppi a, x$ksppcv b

 where a.indx = b.indx and a.ksppinm like   '_allow%';

_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption

可以看到这个参数是 数据丢失情况下允许resetlogs 。修改参数默认值：


SQL> Alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

 关闭数据库：

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

启动数据库导mount状态：

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 4175568896 bytes

Fixed Size                                  2234960 bytes

Variable Size                         1644168624 bytes

Database Buffers                2516582400 bytes

Redo Buffers                           12582912 bytes

Database mounted.

执行 recover database until cancel; 


同时查看数据文件：

SQL> col checkpoint_change# for 9999999999999999

SQL> select file#,checkpoint_change# from v$datafile;

 

     FILE# CHECKPOINT_CHANGE#

---------- ------------------

                 1     14462303120211

                 2     14462303120211

                 3     14462303120211

                 4     14462303120211

                 5     14462303120211

                 6     14462303120211

                 7     14462303120211

可以看到数据库的SCN是一致的。

执行 recover database until cancel命令：


SQL> recover database until cancel;

ORA-00279: change 14462303120211 generated at 09/17/2015 22:02:15 needed for thread 1

ORA-00289: suggestion : /home/oracle/app/oracle/archive/orcl/1_117246_814995340.dbf

ORA-00280: change 14462303120211 for thread 1 is in sequence #117246

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

CANCEL  ------->选择cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below  这里警告：recover成功但是OPEN RESETLOGS会报错

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/home/oracle/app/oracle/datafile/orcl/system01.dbf'

 

 

ORA-01112: media recovery not started

SQL> alter database open resetlogs;  执行open resetlogs果然报错

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2_111974964$" too small

Process ID: 28092

Session ID: 1522 Serial number: 3

创建pfile文件并启动数据库


创建pfile文件，可以看到_allow_resetlogs_corruption参数为true

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

File created.

关闭数据库：

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile='/home/oracle/pfile.ora';

ORACLE instance started.

 

Total System Global Area 4175568896 bytes

Fixed Size                                  2234960 bytes

Variable Size                         1644168624 bytes

Database Buffers                2516582400 bytes

Redo Buffers                           12582912 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 30240

Session ID: 1522 Serial number: 3

SQL> select status from v$instance;

 

STATUS

------------

OPEN