rman differential vs cummulative backups

I am looking into setting up a backup scheme. Currently I want to backup to
disk, than from disk I will backup to tape.

From what I have been reading it seems that BACKUP INCREMENTAL LEVEL 1 DATABASE will take faster to do the backup and slower to restore as opposed to a BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE, which
will take longer to backup and faster to restore. Do I have that correct.

In addition, If I do my level 0 backup on a Sunday and M-F do my LEVEL 1 it sounds like I need to keep my recovery window to at least 7 days, is that correct?

Lastly, how do I recover can I recover from my DB. Can I do something like
this (see below) and this will bring me back to the most current transaction?


run {
allocate channel d1 type disk;
restore database;
recover database;
alter database open;
}

The last person was just doing full backups everyday and it was
eating up way too much disk, space so I am looking to cut down on the disk usuage.

Pardon my nievte, since I am not an offiicial DBA and have been gathering
all my informaiton from google.

Thanks in advance to all who answer this post.

your backup strategy depends on your database size, storage availability, and hard ware.

For big databases > 1TB level 0 and level 1 incremental (cumulative) backups make sense. You can also take incremental backup by updating the level 0 backup depends on the Oracle version.

Quote:

---

From what I have been reading it seems that BACKUP INCREMENTAL LEVEL 1 DATABASE will take faster to do the backup and slower to restore as opposed to a BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE, which
will take longer to backup and faster to restore. Do I have that correct.

---

I have not tested it but in theory, incremental backups the changes happened for one day where as incremental cumulative backups the changes once for few days so it is obvious that first one is faster and the second one is longer.

Quote:

---

run {
allocate channel d1 type disk;
restore database;
recover database;
alter database open;
}

---

The commands are correct but they are generic.

Thanks,

For updating level 0 backups you willl need to use Image copies.

Quote:

---

Originally Posted by vnktummala

your backup strategy depends on your database size, storage availability, and hard ware.

For big databases > 1TB level 0 and level 1 incremental (cumulative) backups make sense. You can also take incremental backup by updating the level 0 backup depends on the Oracle version.



I have not tested it but in theory, incremental backups the changes happened for one day where as incremental cumulative backups the changes once for few days so it is obvious that first one is faster and the second one is longer.



The commands are correct but they are generic.

Thanks,

---

I am not to sure what you mean by the commands being generic, can please
articulate a bit more or provide and example of how you would do a recovery.

Here is how I was planning to implement the level 0 backup. Also, the
cummulative or incremental would be the same way obviously replacing
the LEVEL 0 line of code.

crosscheck Backup;
crosscheck Archivelog All;
run {
SQL 'alter system switch logfile';

ALLOCATE CHANNEL t1 TYPE DISK;
BACKUP INCREMENTAL LEVEL 0 DATABASE;
backup archivelog all delete input;

}
delete noprompt obsolete;


I am not to sure what you mean by the commands being generic, can please
articulate a bit more or provide and example of how you would do a recovery.

What am I missing?

Thanks

First you need to create a file that will track changed blocks. Otherwise your incremental/differential backups will be much larger then they need to be.

Code:

---

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/oracle/oradata/sidname/block_change_tracking.dbf';

---

I think by generic, vnktummala meant that they are commands that anyone could use, however, your environment might require different options/commands. If you backup to the flash recovery area the commands will be very simple, but you need to make sure that the flash recovery is configured properly. Meaning that you have the right retention and enough space allocated both at the os level and in the database.

Also using a repository can be helpful. Think about retention, if you set the retention policy to 1 week, you will have one weeks worth of backups in the repository. If you set it longer, then "delete noprompt obsolete;" will leave additional backup on disk, probably beyond what you want. Just my 2 cents.

You will have different strategies for different days of a week.As mentioned earlier level 0 backup for large sized databases will be apprpopriate only on the weekends.For week days you will need to plan for incremntal backups.You might also want to enable Block change tracking if you are going with incremental backups.

Thanks I got all that planned out but what I want to confirm is the restore
part. If I want to restore my DB to the time it crashed. How does that work.

Can I just issue the command above:

restore database;
recover database;

What happens internally, does it re-apply every incremental back to the
latest level 0 or full backup? So again with my example, if I am on a weekly
schedule level 0 on SAT incr SUN-FRI do I have to keep my retention period
for 7 days?

I do something like this:

rman target / catalog rman/password@rman

run
{
restore DATABASE UNTIL TIME "to_date('2010-05-19:23:59:00','YYYY-MM-DD:HH24:MI:SS')";
RECOVER DATABASE UNTIL TIME "to_date('2010-05-20:19:00:00','YYYY-MM-DD:HH24:MI:SS')";
alter database open resetlogs;
}

This way, as long as I have the archivelogs and access to the backup files, I can recover to any point in time. This is done with an rman catalog so info like where the backupsets are located is housed there.

Make a small test instance and test backing up and recovering. Whenever I try something new, I always try it on my personal database first, then a test instance I made that is a dupicate of the database I'm going to be running the new thing on in prod. I take note of the time it takes for a successful run, any errors I get, what I did to resolve them, and how much disk space I am chewing up by running the new thing.

I never want to hear from management "Didn't you know that was going to happen?"

Jim

Jim, Thanks for the post. Rest assure I always test first before putting into
production.

Secondly, what you appear to be doing is a point in time recovery, I tested
that and it appears to work. I am a bit wary of that since if I want
to recover to the lastest point in time, I need to get the time down to correct minutue is that correct? Therefore I would prefer saying restore DB,
recover DB that should handle it for me.


If I have RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

I do a fullback up or level 0 backup 2 weeks ago and only do
incrementals since. Though the recovery says 7 days nothing will be
removed if I run "delete obsolete" because I need a full back or level 0 backup as a starting point to my recovery process. That is what I am
trying to proove.

I mean Oracle can't be that dumb to allow you to delete backups that it
still needs to do a full recovery of a database, or can it?

I will have my answer in a day or two but if somebody reads this before than
and concur with what I said I would feel much better since all this is new
to me.

On a closing note, comming from a SQL server background I never realized
the power and flexibility of Oracle, its quite over-whelming so I am trying to
concentrate on a little piece at a time.

Thanks to all who answer.

Quote:

---

Originally Posted by ridges54

I do something like this:

rman target / catalog rman/password@rman

run
{
restore DATABASE UNTIL TIME "to_date('2010-05-19:23:59:00','YYYY-MM-DD:HH24:MI:SS')";
RECOVER DATABASE UNTIL TIME "to_date('2010-05-20:19:00:00','YYYY-MM-DD:HH24:MI:SS')";
alter database open resetlogs;
}

This way, as long as I have the archivelogs and access to the backup files, I can recover to any point in time. This is done with an rman catalog so info like where the backupsets are located is housed there.

Make a small test instance and test backing up and recovering. Whenever I try something new, I always try it on my personal database first, then a test instance I made that is a dupicate of the database I'm going to be running the new thing on in prod. I take note of the time it takes for a successful run, any errors I get, what I did to resolve them, and how much disk space I am chewing up by running the new thing.

I never want to hear from management "Didn't you know that was going to happen?"

Jim

---

Jim,

If you insist on using point in time recovery you may want to do this, since
its one less step and also hides your password from people doing a ps -ef


cat point_in_time_recovery.rman
=========================

rman <
connect target sys/sys@rman
connect catalog rman/rman@rman

run
{
set until time "to_date('16-03-2009 17:52:07', 'dd-mm-yyyy hh24:mi:ss')";
restore database;
recover database;
SQL 'alter database open resetlogs';
}

EOF

Note: If your doing a regular restore DB and recover DB you don't need to
resetlogs, thats only needed for point int time.