Friday, April 22, 2011

Flash Recovery Area - Disable during recovery

Introduction

Flash Recovery Area (FRA) is one my favorite features in the Oracle Database. The FRA helps in automatic management of archive log files, RMAN backup pieces, control file autobackups and the flash back log files (if the FLASHBACK DATABASE is turned on). The FRA nicely creates all these files with well defined sub directory structure (based on dates) and also automatically deletes obsolete backup pieces (if the RMAN recovery window is set).

The other day, I was tasked with creating a clone of an Oracle 11g R1 (11.1.0.7) production database using the RMAN restore and recovery commands on the destination host. As usual, i made a copy of the production database's parameter file and edited it as per the new database (clone) settings. I did NOT remove any existing parameters though and retained all parameters including the FRA parameters viz. DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

Problem

Our RMAN restore was not successful as just before the restore started, Oracle strangely deleted two datafiles (as per the alertlog) and then RMAN first created two empty datafiles with the same name which were deleted and then started the actual restore. These two datafiles were created years ago on the production database and they were online datafiles. Nothing wrong with them.


**FROM THE ALERT LOG***

Thu Mar 24 10:49:24 2011
Deleted Oracle managed file +ORADATA/prod_myhost_db/datafile/fints_index2.1887.709799705
Deleted Oracle managed file +ORADATA/prod_myhost_db/datafile/fints_index2.1906.723204215

**FROM THE RMAN LOG**

creating datafile file number=1630 name=+ORADATA/prod_myhost_db/datafile/fints_index2.1887.709799705
creating datafile file number=1649 name=+ORADATA/prod_myhost_db/datafile/fints_index2.1906.723204215

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00030 to +ORADATA/prod_myhost_db/datafile/sales.1690.700575591
channel d1: restoring datafile 00063 to +ORADATA/prod_myhost_db/datafile/argus2_index2.263.700567185
channel d1: restoring datafile 00083 to +ORADATA/prod_myhost_db/datafile/bits_index2.1800.700576857
channel d1: restoring datafile 00112 to +ORADATA/prod_myhost_db/datafile/cbo_data2.1826.700576943
....
....
....

Solution

I got this working by disabling the FRA. I commented the two FRA parameters DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE in the destination database's init parameter file. Then I recreated the +ORADATA ASM diskgroup (formatted the ASM disks to remove data from the other files that got restored so far) and restarted the restore. This time the restore was successful.

Conclusion

Turn off the FRA (remove/comment the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE) on the destination database until the cloning process is over (restore and recovery). Once the new database is opened successfully, the FRA can be turned on again.
Related Posts Plugin for WordPress, Blogger...