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.

Oracle OpenWorld, here I come

ORA-01555: snapshot too old when using flashback query

Last week, a fellow DBA needed help with the error ORA-01555 using the flashback query feature in oracle 10g. On his database, the automatic undo management feature was enabled.

He wanted to obtain a table’s data from 12 hours ago because of a user’s mistake.
The source of the issue was that the UNDO tablespace was inappropriately sized and that the UNDO_RETENTION parameter value was set to the default value of 900(15 minutes). UNDO_RETENTION Reference

Since the database is an “OLTP” system with hundreds of transactions per minutes, the undo tablespace did not have the needed data to flashback the table to the desired time.
The only solution was to restore the whole database to the last valid backup on another server and then export the desired information. The process took an afternoon, but could have taken 5 minutes if the proper settings where in place.

The ORA-01555 error can be caused when the UNDO_RETENTION parameter is improperly set. And the UNDO_RETENTION parameter is only honored if the current undo tablespace has enough space. ORA-01555 Using Automatic Undo Management – Causes and Solutions

Here is a script estimate_undo.txt to help you size the undo tablespace appropriately with the desired UNDO_RETENTION parameter value.

sys@ORCL> @estimate_undo

Current UNDO_RETENTION value
—————————————————————–
900

Specify the desired UNDO_RETENTION in seconds. Ex: 86400=24 hours
Enter value in seconds: 86400

Using 86400 seconds of UNDO_RETENTION

Megs needed for UNDO
——————–
9444

Current max Undo Size in MB
——————————
5000

UNDO Size in MB FILE_NAME
—————— ——————————————————
2500 /OraData1/oradata/orcl/undotbs01.dbf
2500 /OraData2/oradata/orcl/undotbs02.dbf

In this case, the undo tablespace needs to be enlarged by 4.5 GB.
The undo_retention parameter needs to be set to 86400.

Alter database datafile ‘/OraData1/oradata/orcl/undotbs01.dbf’ resize 4750m;
Alter database datafile ‘/OraData2/oradata/orcl/undotbs02.dbf’ resize 4750m;
Alter system set undo_retention = 86400 scope=both;

The Flashback operations could even be 100% guaranteed by specifying the RETENTION GUARANTEE clause of the undo tablespace. Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

The UNDO Advisor can also help with the undo retention settings and for the sizing of the UNDO tablespace. Chris Foot has more info about the Undo Advisor here; his article also explains how the ORA-01555 error maintains the read consistency.

No additional Oracle license is required for using the UNDO Advisor; but I recommend you read this Metalink Note.

In Oracle 11g, there is a new feature called “Flashback Data Archive” that captures historical data that can be retained for as long as the business demands.

Yuri van Buren has a good example of this new feature on his blog: Blogging about 11g – Part 3 – Flashback Data Archive a.k.a. Total Recall

More info on Flashback Data Archive:

Flashback Data Archive Whitepaper

Total Recall

References:

How To Size UNDO Tablespace For Automatic Undo Management

Recovering Table data Using the Flashback Table Feature

ORA-01555 “Snapshot too old” – Detailed Explanation