Recovering Corrupted Data Blocks

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.

Control File Auto Backup Not copied to the Flashback Recovery Area (FRA)

During a migration exercise, a database was upgraded from 10.2.0.1 to 10.2.0.3,
the OS was also ported from Fedora core 4 32bits to Oracle Enterprise Linux 4 64 bits.

The original database didn’t have Flashback Recovery Area configured because of the previous limited disk space, on this new box, since the disk space is not anymore an issue, the FRA has been enabled.

After the migration and the completion of the initial manual backup, it was time to test the RMAN backups.
I was surprised to realize that the copy of the controlfile was not copied in the FRA Backup structure along with the backupset. (eventhough the controlfile autobackup was setup)

In order to troubleshoot, I issued the following RMAN command:

RMAN> backup current controlfile;

The controlfile has been copied to the following directory:
/opt/oracle/app/oracle/product/10.2.0/db_1/dbs/

Also, there was no indication of any wrong value when running the RMAN show all; statement.

So I did a quick research in metalink and I found the following solution:
Note:302153.1

By simply running the following RMAN command:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
The problem was solved.

While running a new RMAN backup, the controlfile is now being copied in the right directory:

Starting Control File and SPFILE Autobackup at 08-JUL-07
piece handle=/OraArch1/ORCL/autobackup/2007_07_08/o1_mf_s_627424692_3930vocb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-JUL-07