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

大家都知道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, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>
SQL> startup mount;
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.

SQL>
SQL> alter database datafile '/u01/app/oracle/oradata/devdb/undotbs01.dbf' offline for drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter undo_tablespace
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

SQL> 
SQL> select a.name tbs_name,b.name file_name,bytes/1024/1024 mb
  2  from v$tablespace a,v$datafile b
  3  where a.ts#=b.ts# and 
  4        a.name like 'UNDO%';

TBS_NAME  FILE_NAME                                      MB
--------- --------------------------------------------- ----------
UNDOTBS1  /u01/app/oracle/oradata/devdb/undotbs01.dbf                          25
UNDOTBS1  /u01/app/oracle/oradata/devdb/undotbs02.dbf                       20480
UNDOTBS1  /u01/app/oracle/oradata/devdb/undotbs03.dbf                       10240

SQL >  create undo tablespace undotbs2 datafile
  2    '/u01/app/oracle/oradata/devdb/undo01.dbf' size 25m
  3    blocksize 8192
  4    extent management local autoallocate;

Tablespace created.

SQL> alter tablespace undotbs2 add datafile '/u01/app/oracle/oradata/devdb/undo02.dbf' size 20480m autoextend off;

Tablespace altered.

SQL> alter tablespace undotbs2 add datafile '/u01/app/oracle/oradata/devdb/undo03.dbf' size 10240m autoextend off;

Tablespace altered.

SQL> alter system set undo_tablespace='UNDOTBS2';

System altered.

接下来的任务是删除原来的undo表空间
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2_2232571081$' found, terminate dropping tablespace

SQL> 
SQL> alter rollback segment '_SYSSMU2_2232571081$' offline;
alter rollback segment '_SYSSMU2_2232571081$' offline
                       *
ERROR at line 1:
ORA-02245: invalid ROLLBACK SEGMENT name

SQL>
SQL> col segment_name for a40      
SQL> select segment_name,status from dba_rollback_segs;

SEGMENT_NAME                             STATUS
---------------------------------------- ------------------------------------------------------------
SYSTEM                                   ONLINE
_SYSSMU96_2626855311$                    OFFLINE
_SYSSMU95_2867914157$                    OFFLINE
_SYSSMU94_2165995008$                    OFFLINE
_SYSSMU93_2152957113$                    OFFLINE
_SYSSMU92_378936285$                     OFFLINE
_SYSSMU91_2887910803$                    OFFLINE
_SYSSMU90_1914376299$                    OFFLINE
_SYSSMU89_3881621438$                    OFFLINE
_SYSSMU88_2795609499$                    OFFLINE
_SYSSMU87_2407539561$                    OFFLINE
_SYSSMU86_692627908$                     OFFLINE
_SYSSMU85_856006933$                     OFFLINE
_SYSSMU84_3829382194$                    OFFLINE
_SYSSMU83_2565948183$                    OFFLINE
_SYSSMU82_3891756263$                    OFFLINE
_SYSSMU81_1952400690$                    OFFLINE
_SYSSMU80_2845645998$                    OFFLINE
_SYSSMU79_3808344055$                    OFFLINE
_SYSSMU78_410897117$                     OFFLINE
_SYSSMU29_4019643210$                    OFFLINE
_SYSSMU28_90219721$                      OFFLINE
_SYSSMU27_3088239721$                    OFFLINE
_SYSSMU116_3119609620$                   OFFLINE
_SYSSMU115_720301610$                    OFFLINE
_SYSSMU114_1114091232$                   OFFLINE
_SYSSMU113_1292246933$                   OFFLINE
_SYSSMU42_3016116591$                    OFFLINE
_SYSSMU53_2737708235$                    OFFLINE
_SYSSMU52_2324266352$                    OFFLINE
_SYSSMU51_222690711$                     OFFLINE
_SYSSMU50_1983357937$                    OFFLINE
_SYSSMU49_1088943503$                    OFFLINE
_SYSSMU48_2674714980$                    OFFLINE
_SYSSMU47_661180824$                     OFFLINE
_SYSSMU46_3208608042$                    OFFLINE
_SYSSMU45_454557363$                     OFFLINE
_SYSSMU44_3986437198$                    OFFLINE
_SYSSMU24_3631703957$                    OFFLINE
_SYSSMU23_1173087710$                    OFFLINE
_SYSSMU22_584541464$                     OFFLINE
_SYSSMU21_2292998292$                    OFFLINE
_SYSSMU25_3038758983$                    OFFLINE
_SYSSMU74_299654288$                     OFFLINE
_SYSSMU73_3176796502$                    OFFLINE
_SYSSMU19_3298206699$                    OFFLINE
_SYSSMU72_401746350$                     OFFLINE
_SYSSMU18_3359877885$                    OFFLINE
_SYSSMU41_2402883085$                    OFFLINE
_SYSSMU40_1522189554$                    OFFLINE
_SYSSMU39_2650049634$                    OFFLINE
_SYSSMU38_2701885968$                    OFFLINE
_SYSSMU17_968918022$                     OFFLINE
_SYSSMU26_1190614032$                    OFFLINE
_SYSSMU20_495695921$                     OFFLINE
_SYSSMU37_4110619942$                    OFFLINE
_SYSSMU36_3643017941$                    OFFLINE
_SYSSMU35_614315702$                     OFFLINE
_SYSSMU34_1969767758$                    OFFLINE
_SYSSMU33_2182944988$                    OFFLINE
_SYSSMU32_94718422$                      OFFLINE
_SYSSMU31_3942797337$                    OFFLINE
_SYSSMU71_3232828223$                    OFFLINE
_SYSSMU70_435663053$                     OFFLINE
_SYSSMU30_1048661479$                    OFFLINE
_SYSSMU76_445013758$                     OFFLINE
_SYSSMU75_1918435484$                    OFFLINE
_SYSSMU58_4222240783$                    OFFLINE
_SYSSMU57_1910590958$                    OFFLINE
_SYSSMU64_2780552157$                    OFFLINE
_SYSSMU16_3854717229$                    OFFLINE
_SYSSMU15_2116662486$                    OFFLINE
_SYSSMU14_2962806949$                    OFFLINE
_SYSSMU13_3749295798$                    OFFLINE
_SYSSMU12_3394647099$                    OFFLINE
_SYSSMU63_485997380$                     OFFLINE
_SYSSMU11_2947740095$                    NEEDS RECOVERY
_SYSSMU62_1492349711$                    OFFLINE
_SYSSMU61_626367163$                     OFFLINE
_SYSSMU60_2068943566$                    OFFLINE
_SYSSMU59_2657580527$                    OFFLINE
_SYSSMU112_2240178017$                   OFFLINE
_SYSSMU111_2453840524$                   OFFLINE
_SYSSMU110_652407272$                    OFFLINE
_SYSSMU109_3438362491$                   OFFLINE
_SYSSMU108_4276478778$                   OFFLINE
_SYSSMU107_2686253572$                   OFFLINE
_SYSSMU106_3680037555$                   OFFLINE
_SYSSMU105_3007023178$                   OFFLINE
_SYSSMU104_3120922276$                   OFFLINE
_SYSSMU103_538076310$                    OFFLINE
_SYSSMU102_3671989609$                   OFFLINE
_SYSSMU101_2743965288$                   OFFLINE
_SYSSMU100_1182363132$                   OFFLINE
_SYSSMU99_2272327060$                    OFFLINE
_SYSSMU98_1965528134$                    OFFLINE
_SYSSMU97_1780218387$                    OFFLINE
_SYSSMU56_3644558235$                    OFFLINE
_SYSSMU55_2671154422$                    OFFLINE
_SYSSMU54_2554729950$                    OFFLINE
_SYSSMU69_4011435110$                    OFFLINE
_SYSSMU68_3213539103$                    OFFLINE
_SYSSMU67_26050221$                      OFFLINE
_SYSSMU66_247782248$                     OFFLINE
_SYSSMU65_738935852$                     OFFLINE
_SYSSMU77_2247174000$                    OFFLINE
_SYSSMU43_3172529003$                    OFFLINE
_SYSSMU10_3550978943$                    NEEDS RECOVERY
_SYSSMU9_1424341975$                     NEEDS RECOVERY
_SYSSMU8_2012382730$                     NEEDS RECOVERY
_SYSSMU7_3286610060$                     NEEDS RECOVERY
_SYSSMU6_2443381498$                     NEEDS RECOVERY
_SYSSMU5_1527469038$                     NEEDS RECOVERY
_SYSSMU4_1152005954$                     NEEDS RECOVERY
_SYSSMU3_2097677531$                     NEEDS RECOVERY
_SYSSMU2_2232571081$                     NEEDS RECOVERY
_SYSSMU126_4033829179$                   ONLINE
_SYSSMU125_2942360563$                   ONLINE
_SYSSMU124_325088493$                    ONLINE
_SYSSMU123_2580600680$                   ONLINE
_SYSSMU122_3203705780$                   ONLINE
_SYSSMU121_1289592678$                   ONLINE
_SYSSMU120_3020831237$                   ONLINE
_SYSSMU119_67481394$                     ONLINE
_SYSSMU118_1121899644$                   ONLINE
_SYSSMU117_3200636936$                   ONLINE

126 rows selected.

SQL> show parameter spfile
NAME                                 TYPE        VALUE
-----------------------------------  ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2/dbs/spfiledevdb.ora

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

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

上面标红的部分是有问题的，通过一个隐含参数将其屏蔽掉，就可以删除undotbs1表空间。
odb1.test.com-PROD1> vi  tmp.ora
devdb.__db_cache_size=2348810240
devdb.__java_pool_size=134217728
devdb.__large_pool_size=67108864
devdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
devdb.__pga_aggregate_target=2348810240
devdb.__sga_target=4362076160
devdb.__shared_io_pool_size=0
devdb.__shared_pool_size=1677721600
devdb.__streams_pool_size=67108864
*.audit_file_dest='/u01/app/oracle/admin/devdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/devdb/control01.ctl','/u01/app/oracle/oradata/devdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='devdb'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=devdbXDB)'
*.memory_target=6677331968
*.open_cursors=300
*.processes=1000
*.recyclebin='OFF'
*.remote_login_passwordfile='NONE'
*.undo_tablespace='UNDOTBS2'
*._corrupted_rollback_segments=(_SYSSMU11_2947740095$,_SYSSMU10_3550978943$,_SYSSMU9_1424341975$,_SYSSMU8_2012382730$,_SYSSMU7_3286610060$,_SYSSMU6_2443381498$,_SYSSMU5_1527469038$,_SYSSMU4_1152005954$,_SYSSMU3_2097677531$,_SYSSMU2_2232571081$)

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 pfile='/oracle/tmp.ora';
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.
Database opened.

SQL>
SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
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.
Database opened.


SQL> select a.name tbs_name,b.name file_name,bytes/1024/1024 mb
  2  from v$tablespace a,v$datafile b
  3  where a.ts#=b.ts# and 
  4        a.name like 'UNDO%';

TBS_NAME  FILE_NAME                                   MB
--------- --------------------------------------------- ----------
UNDOTBS2  /u01/app/oracle/oradata/devdb/undo01.dbf                          25
UNDOTBS2  /u01/app/oracle/oradata/devdb/undo02.dbf                       20480
UNDOTBS2  /u01/app/oracle/oradata/devdb/undo03.dbf                       10240                                                      
至此，undo表空间成功恢复，问题解决。