rman differential vs cummulative backups
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: rman differential vs cummulative backups

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    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.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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.

    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.

    run {
    allocate channel d1 type disk;
    restore database;
    recover database;
    alter database open;
    }
    The commands are correct but they are generic.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Apr 2008
    Location
    Bangalore
    Posts
    96
    For updating level 0 backups you willl need to use Image copies.

  4. #4
    Join Date
    Jul 2006
    Posts
    195
    Quote Originally Posted by vnktummala View 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.



    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

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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.
    this space intentionally left blank

  6. #6
    Join Date
    Apr 2008
    Location
    Bangalore
    Posts
    96
    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.

  7. #7
    Join Date
    Jul 2006
    Posts
    195
    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?

  8. #8
    Join Date
    Jan 2007
    Location
    Dayton, OH
    Posts
    15
    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

  9. #9
    Join Date
    Jul 2006
    Posts
    195
    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.

  10. #10
    Join Date
    Jul 2006
    Posts
    195
    Quote Originally Posted by ridges54 View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width