Undo Block Corruption

Alert Log
========
Tue Jul 7 20:11:04 2009 #Database Crashed Time
Errors in file /opt/oracle/admin/LM_PROD/udump/lm_prod_ora_9948.trc:
ORA-01578: ORACLE data block corrupted (file # 92, block # 121)
ORA-01110: data file 92: '/wamu/lms/db/LM_PROD/d601/LM_PRODundotbs_01.dbf'
Tue Jul 7 20:11:25 2009
Error 1578 happened during db open, shutting down databaseScenario
Abnormal terminating of instance occurred in our production server on Tue Jul 7 20:11:04 2009 due to the Undo block corruption which occurred in the filesystem `/wamu/lms/db/LM_PROD/d601/LM_PRODundotbs_01.dbf ` . We were unable to open up the database. The database is running in archive log mode and the RMAN hot backup was taken at Tue Jul 7 00:30:03 PDT 2009. The current log sequence # 26300

Step 1

To do Media Recovery

SQL> recover databaseMedia recovery complete.SQL> alter database open;alter database open*ERROR at line 1:ORA-01092: ORACLE instance terminated. Disconnection forcedWe found there was a fractured block error on the Undo block while doing crash recovery. The block was fractured which means the head and tail portion of the block had issues, normally occurs due to missed writes from OS crash
Step 2
To find number of corrupt block

$ dbv file=/wamu/lms/db/LM_PROD/d601/LM_PRODundotbs_01.dbf blocksize=8192DBVERIFY: Release 10.2.0.3.0 - Production on Tue Jul 7 22:35:26 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.DBVERIFY - Verification starting : FILE = /wamu/lms/db/LM_PROD/d601/LM_PRODundotbs_01.dbfDBV-00200: Block, dba 385876089, already marked corruptedDBVERIFY - Verification completeTotal Pages Examined : 512000Total Pages Processed (Data) : 0Total Pages Failing (Data) : 0Total Pages Processed (Index): 0Total Pages Failing (Index): 0Total Pages Processed (Other): 512000Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 0Total Pages Marked Corrupt : 1Total Pages Influx : 0Highest block SCN : 2640445773 (34.2640445773)[LM_PROD: /wamu/lms/db/LM_PROD/d601]oracle@dbsu01r ]
Result
We found 1 block has been corrupted in the filesystem `/wamu/lms/db/LM_PROD/d601/LM_PRODundotbs_01.dbf ` .
Step 3

To list backup of datafile 92

$ rmanRecovery Manager: Release 10.2.0.3.0 - Production on Tue Jul 7 22:57:11 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN> connect target /connected to target database: LM_PROD (DBID=1610502646, not open)RMAN> list backup of datafile 92;using target database control file instead of recovery catalogList of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------60639 Incr 0 3.73G DISK 00:02:04 07-JUL-09 BP Key: 60639 Status: AVAILABLE Compressed: NO Tag: HOT_DB_BK_LEVEL0Piece Name: /wamu/lms/db/LM_PROD/orabkp/rman/hot_bkplvl0_LM_PROD_60692_1_691547487List of Datafiles in backup set 60639File LV Type Ckp SCN Ckp Time Name---- -- ---- ---------- --------- ----92 0 Incr 148661915022 07-JUL-09 /wamu/lms/db/LM_PROD/d601/LM_PRODundotbs_01.dbf
Step 4

To do RMAN Block recovery for corrupt Undo Blocks
RMAN> blockrecover datafile 92 block 121;Starting blockrecover at 07-JUL-09allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=542 devtype=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: sid=1100 devtype=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: sid=541 devtype=DISKallocated channel: ORA_DISK_4channel ORA_DISK_4: sid=1099 devtype=DISKchannel ORA_DISK_1: restoring block(s)channel ORA_DISK_1: specifying block(s) to restore from backup setrestoring blocks of datafile 00092channel ORA_DISK_1: reading from backup piece "/wamu/lms/db/LM_PROD/orabkp/rman/hot_bkplvl0_LM_PROD_60692_1_691547487"ORA-27037: unable to obtain file statusSVR4 Error: 2: No such file or directoryAdditional information: 3failover to previous backupRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of blockrecover command at 07/07/2009 22:59:24RMAN-06026: some targets not found - aborting restoreRMAN-06023: no backup or copy of datafile 92 found to restore
Block recovery failed indicating no backup of datafile 92 found.



Step 5

To Bring Datafile 92 offline

$ sqlplus "/ as sysdba"SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 7 22:56:39 2009Copyright (c) 1982, 2006, Oracle. All Rights Reserved.Connected to an idle instance.SQL> startup mountORACLE instance started.Total System Global Area 6878658560 bytesFixed Size 2138752 bytesVariable Size 4060175744 bytesDatabase Buffers 2751463424 bytesRedo Buffers 64880640 bytesDatabase mounted.SQL> alter database datafile 92 offline;Database altered.
Step 6

To Rename Undo Datafile
$ mv LM_PRODundotbs_01.dbf LM_PRODundotbs_01.dbf_old[LM_PROD: /wamu/lms/db/LM_PROD/d601]oracle@dbsu01r ]$ ls -ltrtotal 8806496drwxr-xr-x 2 oracle dba 96 Aug 2 2007 lost+found-rw-r----- 1 oracle dba 4194312192 Jul 7 20:47 LM_PRODundotbs_01.dbf_old[LM_PROD: /wamu/lms/db/LM_PROD/d601]oracle@dbsu01r ]
We have restored datafile 92 from the RMAN Backup on Tue Jul 7 00:30:03 PDT 2009
Step 7

To restore Datafile 92
RMAN> restore datafile 92;Finished restore at 07-JUL-09
$ ls -ltrtotal 8806496drwxr-xr-x 2 oracle dba 96 Aug 2 2007 lost+found-rw-r----- 1 oracle dba 4194312192 Jul 7 20:47 LM_PRODundotbs_01.dbf_old-rw-r----- 1 oracle dba 4194312192 Jul 7 23:02 LM_PRODundotbs_01.dbf[LM_PROD: /wamu/lms/db/LM_PROD/d601]
Step 8

To recover Datafile 92
SQL> recover datafile 92;SQL>Alter database open ;

SQL>Alter database datafile 92 online ;

SQL>archive log list

SQL>Alter system switch logfile;

SQL>archive log list
Database log mode Archive ModeAutomatic archival EnabledArchive destination /wamu/lms/db/LM_PROD/d802/archOldest online log sequence 26303Next log sequence to archive 26305Current log sequence 26305

SQL> select * from v$logfile;SQL> select * from v$log;SQL> select * from v$recover_file;SQL> select checkpoint_change# from v$datafile_header where file#=92;CHECKPOINT_CHANGE#------------------178822645DBVERIFY came out with no corrupt blocks

$ dbv file=/wamu/lms/db/LM_PROD/d601/LM_PRODundotbs_01.dbf blocksize=8192DBVERIFY: Release 10.2.0.3.0 - Production on Tue Jul 7 22:35:26 2009Copyright (c) 1982, 2005, Oracle. All rights reserved.DBVERIFY - Verification starting : FILE = /wamu/lms/db/LM_PROD/d601/LM_PRODundotbs_01.dbfDBV-00200: Block, dba 385876089, already marked corruptedDBVERIFY - Verification completeTotal Pages Examined : 512000Total Pages Processed (Data) : 0Total Pages Failing (Data) : 0Total Pages Processed (Index): 0Total Pages Failing (Index): 0Total Pages Processed (Other): 512000Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 0Total Pages Marked Corrupt : 0
Total Pages Influx : 0Highest block SCN : 2640445773 (34.2640445773)[LM_PROD: /wamu/lms/db/LM_PROD/d601]oracle@dbsu01r ]