ORA-00376: file xx cannot be read at this time, undo corruption

All of the actions below are performed after careful analysis, any act of below in business critical system may cause serious implications.

Issue : Undo lob corruption in one of the database.

Ironically, the DB is in no archive log mode and no backup exist.

Verify is it block corruption using db verify.

XXXXXX:TEST1:UAT $ dbv FILE='/u04/oradata/TEST1/datafile/undotbs1_01.dbf'

DBVERIFY: Release 11.2.0.2.0 - Production on Thu Feb 9 06:27:51 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u04/oradata/TEST1/datafile/undotbs1_01.dbf
Page 753840 is marked corrupt
Corrupt block relative dba: 0x00cb80b0 (file 3, block 753840)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x00cb7eb0
last change scn: 0x0012.d7026e0b seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6e0b0202
check value in block header: 0xd36f
computed block checksum: 0x0

Page 753841 is marked corrupt
Corrupt block relative dba: 0x00cb80b1 (file 3, block 753841)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x00cb7eb1
last change scn: 0x0012.d7026e23 seq: 0x4 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6e230204
check value in block header: 0x1818
computed block checksum: 0x0

Page 753842 is marked corrupt
Corrupt block relative dba: 0x00cb80b2 (file 3, block 753842)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x00cb7eb2
last change scn: 0x0012.d7026e66 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6e660202
check value in block header: 0xb796
computed block checksum: 0x0

Page 753843 is marked corrupt
Corrupt block relative dba: 0x00cb80b3 (file 3, block 753843)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x00cb7eb3
last change scn: 0x0012.d7026e8a seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6e8a0201
check value in block header: 0xa31
computed block checksum: 0x0

Page 753844 is marked corrupt
Corrupt block relative dba: 0x00cb80b4 (file 3, block 753844)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x00cb7eb4
last change scn: 0x0012.d7026ec2 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6ec20202
check value in block header: 0x2317
computed block checksum: 0x0

Page 753845 is marked corrupt
Corrupt block relative dba: 0x00cb80b5 (file 3, block 753845)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x00cb7eb5
last change scn: 0x0012.d7026ee8 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6ee80202
check value in block header: 0x66cc
computed block checksum: 0x0

Page 753846 is marked corrupt
Corrupt block relative dba: 0x00cb80b6 (file 3, block 753846)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x00cb7eb6
last change scn: 0x0012.d7026f05 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6f050203
check value in block header: 0x73e4
computed block checksum: 0x0

Page 753847 is marked corrupt
Corrupt block relative dba: 0x00cb80b7 (file 3, block 753847)
Bad header found during dbv:
Data in bad block:
type: 2 format: 2 rdba: 0x00cb7eb7
last change scn: 0x0012.d7026f44 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x6f440203
check value in block header: 0xff9b
computed block checksum: 0x0

DBVERIFY - Verification complete

Total Pages Examined         : 1441792
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1332343
Total Pages Processed (Seg)  : 85
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 109441
Total Pages Marked Corrupt   : 8
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3607284218 (18.3607284218)
XXXXXX:TEST1:UAT $

DB Verify tells us some of the blocks are corrupted.

If you database is up and running and you can run the following query.

select
segment_name,
status
from
dba_rollback_segs
where
tablespace_name='undotbs_corrupt'
and
status = ‘NEEDS RECOVERY’;

And then skip directly to modifying parameter file.

In this situation my database is crashed and not coming up.

Get the undo segment from system.dbf file by using unix strings

Note: ensure you have backup of system01.dbf just in case for additional safety.

The system01.dbf contains the metadata as we all know we just find the name of the undo segments associated with corrupted undo file. Note all undo segments names starts with _SYSSMU hence we grep with same.

$strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort –u

You will get a list of undo segments with names, add them in pfile as below.

*._offline_rollback_segments=(_SYSSMU1_1378091378,
_SYSSMU10_2643669275,
_SYSSMU11_826597511,
_SYSSMU12_22461,
_SYSSMU12_2246107276,
_SYSSMU13_3422565370,
_SYSSMU14_62229858,
_SYSSMU15_1024915312,
_SYSSMU16_2988233509,
_SYSSMU17_3863972028,
_SYSSMU18_2623488503,
_SYSSMU19_4002625085,
_SYSSMU2_218893415,
_SYSSMU20_3857048045,
_SYSSMU21_52627125,
_SYSSMU22_4233552383,
_SYSSMU23_1018160074,
_SYSSMU24_3277943992,
_SYSSMU25_1953391561,
_SYSSMU26_686432436,
_SYSSMU27_2952114602,
_SYSSMU28_2753593865,
_SYSSMU29_3172680245,
_SYSSMU3_4109074657,
_SYSSMU30_1816038839,
_SYSSMU31_557968885,
_SYSSMU32_59699221,
_SYSSMU33_476091882,
_SYSSMU34_2983566433,
_SYSSMU35_3303307547,
_SYSSMU36_1096963070,
_SYSSMU37_2157735066,
_SYSSMU38_1985749567,
_SYSSMU39_2398639442,
_SYSSMU4_2498249540,
_SYSSMU40_3836713719,
_SYSSMU41_461384283,
_SYSSMU42_3456463656,
_SYSSMU43_3501537783,
_SYSSMU44_577485822,
_SYSSMU45_2593925016,
_SYSSMU46_1920638535,
_SYSSMU47_3798781595,
_SYSSMU48_3933969892,
_SYSSMU49_649767891,
_SYSSMU5_3694696749,
_SYSSMU50_2060600321,
_SYSSMU51_3133692975,
_SYSSMU52_3856128489,
_SYSSMU53_4021032957,
_SYSSMU54_1506493404,
_SYSSMU55_1329844642,
_SYSSMU56_180609774,
_SYSSMU57_2744292633,
_SYSSMU58_1824611347,
_SYSSMU59_3098138300,
_SYSSMU6_2879209801,
_SYSSMU60_3475426220,
_SYSSMU61_346629303,
_SYSSMU62_1343668862,
_SYSSMU63_3456167468,
_SYSSMU64_950841864,
_SYSSMU65_200150191,
_SYSSMU66_4106954499,
_SYSSMU67_4187344329,
_SYSSMU68_3741556969,
_SYSSMU69_4268544737,
_SYSSMU7_2236703861,
_SYSSMU70_3626470476,
_SYSSMU71_2742853818,
_SYSSMU72_3622921546,
_SYSSMU73_838674252,
_SYSSMU74_113367856,
_SYSSMU75_3364932326,
_SYSSMU76_306382332,
_SYSSMU77_2492597811,
_SYSSMU78_2845006942,
_SYSSMU79_3897019929,
_SYSSMU8_3597593650 ,
_SYSSMU80_3108059925,
_SYSSMU81_3846280772,
_SYSSMU82_1376842314,
_SYSSMU83_4228099315,
_SYSSMU84_987292088,
_SYSSMU85_3186478228,
_SYSSMU9_858901864)

Also add the _allow_resetlogs_corruption=true --> as db in no archive log mode

Startup the database in mount state

SQL> startup mount

ORACLE instance started.

SQL recover database until cancel; --> Recover no possibility --> Recover will fail no archives etc etc

SQL> Alter Database open resetlogs;  or just alter database open

Throws error = ora-2662 (SCN Bump)  As SCN of some files are ahead, we need bump the scn by couple of restarts.

SQL> shut abort

SQL> startup mount

SQL> alter session set event '10215 context scn level 10; --> may require bounce multiple times

SQL> Alter Database open resetlogs;  or just alter database open

Total System Global Area 1253031936 bytes
Fixed Size                  2157904 bytes
Variable Size             536875696 bytes
Database Buffers          704643072 bytes
Redo Buffers                9355264 bytes
Database mounted.
SQL> alter database open;

Database altered.

DB will open but will throw ORA_4194 as I have not created any rollback segments, and my undo_management is manual.

Back to Basics:- before to 8i we have to specify rollback segments(undo) manually in parameters file with parameter called “rollback_segments” so that the instance will create this segment names in system.dbf file, but now its undo automatic a separate undo tablespace will be used for the same.

But due to the undo datafile corruption we are switching back to manual mode and then trying to bypass the undo corruption.

Create some manual rollback segments and then start again

SQL> create rollback segment r01;

Rollback segment created.

SQL> create undo tablespace undotbs2 datafile '/u04/oradata/TEST1/datafile/undotbs2_02.dbf' size 500M ;

Tablespace created.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

Shutdown and revert back to AUTO undo mode

SQL> shut immediate

Change UNDO_MANAGMENT =AUTO in pfile

Chnage UNDO_TABLESPACE=UNDOTBS2 in pfile

SQL> startup mount
ORACLE instance started.

Total System Global Area 1253031936 bytes
Fixed Size                  2157904 bytes
Variable Size             553652912 bytes
Database Buffers          687865856 bytes
Redo Buffers                9355264 bytes
Database mounted.
SQL> alter database open;

Database altered.

Test if everything is intact
~~~~~~~~~~~~~~~~~~
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)
----------
52912

SQL> drop table t1;

Table dropped.

Hope this helps

-Thanks

Geek DBA