oracle undo数据文件坏,oracle undo数据文件损坏故障处理案例

oracleundo数据文件损坏故障处理案例

大家都知道Oracle的数据都存储在数据文件中，undo表空间存储的数据是某些变更操作的前镜像以及一致性读数据，而且undo表空间是循环利用的，一段时间后会自动释放空间。近期遇到一个undo表空间的文件损坏的问题，导致数据库只能mount，不能open。由于没有备份，无法使用备份进行恢复，最后通过重建undo表空间，达到恢复的目的。这里把该故障的处理过程，拿出来分享一下。

故障现象：

odb1.test.com-PROD1> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 26 18:28:22 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 6680915968 bytes

Fixed Size                  2213936 bytes

Variable Size            4294969296 bytes

Database Buffers         2348810240 bytes

Redo Buffers               34922496 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01578: ORACLE data block corrupted (file # 3, block # 144)

ORA-01110: data file 3: '/u01/app/oracle/oradata/devdb/undotbs01.dbf'

Process ID: 13013

Session ID: 1057 Serial number: 3

查看alert日志

SMON: enabling cache recovery

Errors in file /u01/app/oracle/diag/rdbms/devdb/devdb/trace/devdb_ora_13013.trc  (incident=84178):

ORA-01578: ORACLE data block corrupted (file # 3, block # 144)

ORA-01110: data file 3: '/u01/app/oracle/oradata/devdb/undotbs01.dbf'

Incident details in: /u01/app/oracle/diag/rdbms/devdb/devdb/incident/incdir_84178/devdb_ora_13013_i84178.trc

Tue Dec 26 18:28:51 2016

Trace dumping is performing id=[cdmp_20160826182851]

Errors in file /u01/app/oracle/diag/rdbms/devdb/devdb/trace/devdb_ora_13013.trc:

ORA-01578: ORACLE data block corrupted (file # 3, block # 144)

ORA-01110: data file 3: '/u01/app/oracle/oradata/devdb/undotbs01.dbf'

Errors in file /u01/app/oracle/diag/rdbms/devdb/devdb/trace/devdb_ora_13013.trc:

ORA-01578: ORACLE data block corrupted (file # 3, block # 144)

ORA-01110: data file 3: '/u01/app/oracle/oradata/devdb/undotbs01.dbf'

Error 1578 happened during db open, shutting down database

USER (ospid: 13013): terminating the instance due to error 1578

Instance terminated by USER, pid = 13013

ORA-1092 signalled during: ALTER DATABASE OPEN...

opiodr aborting process unknown ospid (13013) as a result of ORA-1092

Tue Dec 26 18:28:54 2016

ORA-1092 : opitsk aborting process

查看错误代码

odb1.test.com-PROD1> oerr ora 01578

01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)"

// *Cause:  The data block indicated was corrupted, mostly due to software

//          errors.

// *Action: Try to restore the segment containing the block indicated. This

//          may involve dropping the segment and recreating it. If there

//          is a trace file, report the errors in it to your ORACLE

//          representative.

从上面的信息可以看出undo表空间的文件/u01/app/oracle/oradata/devdb/undotbs01.dbf有块损坏，导致数据库只能mount，不能open。

解决方法：

因没有备份，无法使用备份进行恢复。而undo表空间中没有业务数据，所以可以新建一个undo表空间，将现有undo表空间切换到新的undo表空间，再删除原来的undo空间，以达到恢复的目的。

新建一个undo表空间，并将数据库的undo表空间切换到新的undo表空间

odb1.test.com-PROD1> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 26 18:28:22 2016

Copyright (c) 1982, 2009,