28 January, 2007

Using Partial Recoveries to test Backups

When testing new backup technologies on "fairly large" databases [see my previous post http://hemantoracledba.blogspot.com/2007/01/deleting-data-doesnt-reduce-size-of.html for a definition of "fairly large"] sometimes you can contrive to do Partial Recoveries to test Restoration and Recovery of Backups. This has become necessary, for instance, in one case where the vendors' [multiple vendors involved] Snap* based backup method while seemingly restoring the whole database wouldn't be seen as consistent datafiles by Oracle whenever I issued RECOVER commands. While the SnapShot and SnapRestore wasn't taking very long, when I wanted to "prove" the Oracle BEGIN/END BACKUP scripts and the RECOVER scripts that I was using, I had to use "cp" as a Restoration method. This allowed me to prove that when I used "cp" to backup and restore the datafiles, there were no errors in the RECOVER command. Unfortunately, the database being "fairly large", I would have been spending far many more hours in the "cp" then in the actual BEGIN/END BACKUP and RECOVER commands !. So I simulated transactions and did a "cp" based restoration of only the SYSTEM and UNDO tablespaces. That way I could "copy" the "whole" database much faster.
Essentially what I did was
a) BEGIN BACKUP for all tablespaces
b) copy out SYSTEM and UNDOTBS
c) END BACKUP
d) Restore SYSTEM and UNDOTBS to the alternate location
e) CREATE CONTROLFILE with only SYSTEM and UNDOTBS
f) RECOVER only SYSTEM and UNDOTBS

So I was using a TSPITR method to test backup and restoration.

I could prove that when the vendors technologies were being used, I was getting inconsistent database files [database files seemed to be of a point-in-time _before_ the BEGIN BACKUP -- ie, the Snap* mechanism was copying the files __before__ the BEGIN BACKUP had been run]. This WAS resolved by the vendors finally.

No comments: