Last Week, I experienced a kernel crash using Oracle 10.2.0.3 on my Oracle Enterprise Linux 4 sandbox server. There was not a single reaction from the server, I had to reboot the box by pressing the reset button.
I happened to have a backup of this sandbox database, which gave me the opportunity to perform a media recovery.
The situation was that the undo Tablespace had a corrupt block.
Here is the alert log content:
Mon Aug 20 08:54:44 2007
SMON: enabling cache recovery
Mon Aug 20 08:54:45 2007
Errors in file /opt/oracle/app/oracle/admin/orcl/udump/orcl_ora_6733.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 9)
ORA-01110: data file 2: ‘/OraData2/oradata/orcl/undotbs01.dbf’
Mon Aug 20 08:54:45 2007
Error 1578 happened during db open, shutting down database
USER: terminating instance due to error 1578
Instance terminated by USER, pid = 6733
ORA-1092 signalled during: ALTER DATABASE OPEN…
Just when I was about to restore the whole datafile, I remembered a nice Oracle 10g feature called Block-level media recovery.
Note that this is a Enterprise Edition feature.
I had a huge load of tasks to do that particular morning and I was very happy with the time saved recovering that datafile.
Here is the RMAN output session:
RMAN> blockrecover datafile 2 block 9;
Starting blockrecover at 20-AUG-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=154 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00002
channel ORA_DISK_1: reading from backup piece /OraArch1/ORCL/backupset/2007_08_19/o1_mf_nnndf_BACKUP_ORCL.TO_0_3dl0ygb3_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/OraArch1/ORCL/backupset/2007_08_19/o1_mf_nnndf_BACKUP_ORCL.TO_0_3dl0ygb3_.bkp tag=BACKUP_ORCL.TO_081907110003
channel ORA_DISK_1: block restore complete, elapsed time: 00:03:56
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 20-AUG-07
RMAN> backup validate datafile 2;
Starting backup at 20-AUG-07
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/OraData2/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
Finished backup at 20-AUG-07
It was as easy as this ! The database is now up & running, with no further issue in this matter.