Level 1 Incremental Backup in Oracle 10g

Posted in: Technical Track

While reviewing some material in advance of my presentation at UKOUG Conference 2006, I found an interesting change in RMAN behavior in Oracle 10g.

The difference is in the way RMAN handles the case when an incremental level 1 backup is taken without an available level 0 backup. This probably won’t affect anyone much, but I found it interesting. And actually, there are scenarios in which it might cause issues.

Pre-10g behavior (also in effect in 10g by setting compatibility to < 10.0.0) produces a level 0 backup in case incremental level 1 is run without level 0.

Oracle 10g behavior is to take a level one backup assuming that a checkpoint SCN of the last level 0 backup is the datafile creation SCN.
It doesn’t look like a big deal but imagine the following scenario (I didn’t test it – just thinking logically):
Incremental backup strategy is “standard”.
Sunday – level 0 backup.
Monday-Saturday – level 1 cumulative.

Now, imagine that on Monday there is an issue and the backup is not available anymore — either it’s lost, or recovery with resetlogs has been done. The next incremental backup stores all the blocks, so we have a valid backup — no problem here.

However, the keyword in the backup strategy is cumulative. The next incremental level 1 cumulative backup still doesn’t find the level 0 backup and again copies every block in the database. This continues until the next level 0 backup is taken on Sunday. Assuming DBAs are always unlucky, the backup destination tapes will fill up before the incremental level 1 backup is run. Typical, isn’t it?

How can we avoid this? By taking the level 0 backup first time and not regular level 1.

Note that “differential” (when no cumulative keyword is specified) incremental backup won’t cause any issues, as it takes only those blocks changed since the last incremental backup.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

What does it take to be chief technology officer at a company of technology experts? Experience. Imagination. Passion. Alex Gorbachev has all three. He’s played a key role in taking the company global, having set up Pythian’s Asia Pacific operations. Today, the CTO office is an incubator of new services and technologies – a mini-startup inside Pythian. Most recently, Alex built a Big Data Engineering services team and established a Data Science practice. Highly sought after for his deep expertise and interest in emerging trends, Alex routinely speaks at industry events as a member of the OakTable.

7 Comments. Leave new

we do an incremental Level1 cumulative backup. However, everytime we add a datafile – we need need to do a Level0 to capture the change and then get back to Level1. Was wondering if there was a way around this in 9i (since there are time the dba’s forget to change it to Level0)

Alex Gorbachev
November 8, 2006 2:25 pm

Actually, that’s another good example.
If what I described is correct, than 9i RMAN should perform incremental level 0 backup instead of incremental level 1 in this case. Perhaps, I read it wrong? I’m referring to https://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup004.htm#sthref397


Personally, I’d suggest doing anything other than erroring out is a bug!

Backups are hard (and critical!) enough at the best of times without having to wonder whether your level 1 incremental is actually a level 1 incremental, a level 0 incremental or something in between. If a level 1 can’t find a prior level 0 or level 1 to ‘compare notes’ with, it should simply fail, in my view, not make things up as the mood takes it.

But maybe I’m just old-fashioned!

Alex Gorbachev
November 9, 2006 7:44 am

Second that! This is true for every environment I worked in last years.
On the other hand, I know there are shops which operate in such a way that backup failure is only noticed when recovery is required.

One can implement a check now to see is incremental level 0 is there but I would vote for failure by default. Anyway, since Oracle goes in the direction of self management and automation, I bet they won’t step back here either.


Alex , in our case RMAN does not seem to be doing a level0 instead of a level1(when we set the backup to level1 cumulative database, the new datafiles are not getting backed up), and we figured this out while cloning to preprod. so for now the solution has been remember to change it to level0 when new datafiles are added (quite cumbersome though)……is there something else that need to be done for this to happen automatically

Alex Gorbachev
November 9, 2006 11:32 am

vidya, that’s odd.
What does RMAN do then? Skips the datafile completely or fails?


it doesn’t fail – Level 1 complete successfully – when we clone the new datafile does not come over ………”odd” I agree ; I have not had the time to open a ticket with support ; probably I should (at this point I am not clear if this is expected bahavior or not)


Leave a Reply

Your email address will not be published. Required fields are marked *