Block corruption!



After a couple of weeks off my first day back was a little surprising. I got an ORA-1578 block corruption error for the first time in years. This was on our new system hosted in Azure, whether the fact this is on Azure and this has somehow caused the corruption in the way the VM etc is set up i don't know but it's certainly something to keep an eye on.

Anyway the error from the alert log was basically this (lots of other info removed for brevity)

ORA-01578: ORACLE data block corrupted (file # 1, block # 32533)

So a corruption in the system datafile (well i guess if you're going to corrupt at least pick the worst place to do it)

Let's check if anything else is reported as bad with a simple select (note this can be done in rman as readily as in sqlplus)

RMAN> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

using target database control file instead of recovery catalog
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO     CON_ID
---------- ---------- ---------- ------------------ --------- ----------
         1      32533          1                  0 FRACTURED          0

So now all we have to do is recover the bad block, while googling to confirm the syntax of this i discovered that there is a simple command to just fix any bad blocks - to avoid you having to type in file and block id's for everything which is nice - the command is as follows with output (again some removed to keep output small)

RMAN> RECOVER CORRUPTION LIST;

Starting recover at 18-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=382 device type=DISK
searching flashback logs for block images until SCN 4500224
finished flashback log search, restored 0 blocks

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00001

channel ORA_DISK_1: reading from backup piece /fra/DBNAME/backupset/2016_04_20/o1_mf_nnndf_TAG20160420T044941_ckgjojpp_.bkp
channel ORA_DISK_1: piece handle=/fra/DBNAME/backupset/2016_04_20/o1_mf_nnndf_TAG20160420T044941_ckgjojpp_.bkp tag=TAG20160420T044941
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:16:26

starting media recovery

archived log for thread 1 with sequence 52 is already on disk as file /fra/DBNAME/archivelog/2016_04_20/o1_mf_1_52_ckhqzqo7_.arc
etc
etc 
etc
media recovery complete, elapsed time: 00:11:28
Finished recover at 18-MAY-16

RMAN>

So all done - as easy as that - you can see initially it tried to get a block image from the flashback logs then gave up and used the archive logs. It took 11 mins to do all of this which did seem quite a long time but it got there in the end and it's much simpler (and less worrying) than doing a more complete file recovery.

Comments