Oracle Full (not level 0) and incremental backup - how they support each other

 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