Dears,
This last week, I was asked to perform a backup and restore exercise.
This is completely a new db, not in my support scope.
Still did study the DB setup thoroughly and gave feedbacks and brought as much as inputs as I needed.
There was a immense pressure on me to execute this adhoc backup/restore exercise for the db successfully due to some so and so reasons, which I cant discuss.
Anyway, challenge upfront - step into an unknown waters (the customizations), but it is still it is oracle DB. The study I did , helped me to gain sufficient knowledge to carve out the plan for the restore/recovery exercise.
Objective: Refresh DEV with PROD data. The DB size was 14TB. This needs to be executed in a reasonable time frame. Last time the activity happened on Oct 2023, but no disclosure of the plan, but the only info given was restore took 7hrs.
I prepared the plan, and executed all prereqs, that night I was shared the credentials to login to the target DB where the restore is to be done.
On the 'D' day, I learnt the backups which are secured on the weekends "FULL" backup not incremental backup. Rest all days the backup admin is securing incremenal level 1.
So a FULL on sunday and all other days are an incremental level 1.
I was just blown away by this config, this is a managed backup, so no backup logs accessible before. My fault I didnt scrutinize the backup before. I kept hearing there is a incremental level 0 and level 1 but there is a mistake -- hrrrrrrrrr!
Ok, now the challenege ahead with 3TB worth of archive to recover from Full backup the PITR requested.
So I proceeded with the restore...
Attempt 1 - restore failed due to the "convert" strings in init param not supporting non-omf db restore.
Attempt 2 - Set new name for the datafiles by dynamically generating string using regexp_replace(name,'source','tgt',1,1) from v$datafile view and set them up in the restore command.
run
{
set newname for datafile 1 to '/tgt/file1.dbf';
set until time to_date('');
restore database;
}
while the restore was ongoing I broke the archive restore command into 9 parts thinking I have to
>> restore archives
>> recover archives
>>purge archives
This is when my stuck an option where rman already automated this routine...
recover database delete archivelog XXX G;
This does all what was mentioned above.
Restore finished in 3hrs for 11TB.
Now comes the main discussion point of this blog, when I kicked this recovery on.
To my surprise, the incremental level 1 was used and it was used properly; which made me wonder what could have happened?
I didn't collect evidence, but I will when I find opportunity..
1. Full backup doesn't store the incremental scn value in the dictionary which is its nature. So my assumption was they don't participate in incremental restore & recovery
This is where oracle turned smart, oracle after full backup was restored.
The incremental level 1 backup it used was actually taken on top of the last incremental level 1.So ideally what happened is,
Full backup restored df1 (scn-111), df2 (scn-123) etc..
incremental level 1 had changes captured from 99 and hence this incremental level 1 was used during this recovery exercise successfully. The archives left over were used as required to roll forward the DB.
Thanks
