This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Sep 27, 2013 5:09 AM by EdStevens RSS

Monthly Backup Plan using Incremental Backups

user472167 Newbie
Currently Being Moderated

I would like to have a monthly backup plan as explained in the oracle documentation:

 

First Friday of the month: Full Backup (Incremental Level 0)

Monday, Tuesday, Wednesday, Thursday: Incremental Level 1

2nd, 3rd, 4th Friday: Cumulative Incremental Backups Level 1

 

As I understand is that the 4th cumulative incremental backup will contain all the changes from the full backup.

 

Is there any way so that the 4th friday backup will contain only the changes since the 3rd Friday and so on?

  • 1. Re: Monthly Backup Plan using Incremental Backups
    nagulan.selvakumar Journeyer
    Currently Being Moderated

    Hi,

     

    Your strategy will take long time for recover if your database is hit by disaster at around the 4th week as it should restore the files from beginning and also this will require a lot of space to accommodate your backup.

    You can use the below approach instead.

    Every Friday of the month: Full Backup (Incremental Level 0)

    Saturday through Thursday: Incremental Level 1

    Every Tuesday: Incremental Level 1 cumulative (if desired, which makes restoration easier if DB disaster would happen on wednesday or thursday).

     

    Also make sure, you have maintenance of RMAN backups like deleting obsolete on regular basis.

     

    Thank you!!

  • 2. Re: Monthly Backup Plan using Incremental Backups
    user472167 Newbie
    Currently Being Moderated

    I understand that if i have a disaster after the 4th Friday I have to restore the following backups:

    1.initial full backup

    2.All the Friday cumulative backups (max 4)

    3.All the daily incremental backups after the last Friday (max 4)

    4.All archive logs (the redo log will be archived and backup every 30 minutes)

     

    I have to do it this way though because the full database backup is very large and requires a long time to be transferred to the disaster recovery site.

     

    Is it possible to have a cumulative incremental backup of level 2, 3 or 4?

  • 3. Re: Monthly Backup Plan using Incremental Backups
    nagulan.selvakumar Journeyer
    Currently Being Moderated

    You don't need all the Friday cumulative backup but require only the last Friday cumulative backup as the backup is cumulative (backup all the changes made after level 0 backup). The constrain here is you need to preserve the level 0 backup for a months time.

    There are no level 2, 3 or 4 available in oracle backups.

     

    Apologies. There are level 2, 3 and 4 backups but they were all differential backups which doesn't differ from what level 1 backup does.

     

    Thank you!!

     

    Message was edited by: nagulan.selvakumar

  • 4. Re: Monthly Backup Plan using Incremental Backups
    user12075536123 Pro
    Currently Being Moderated

    I think the following backups

    ・First Friday of the month                          : Incremental Backups Level 0

    ・Monday,Tuesday,Wednesday,Thursday : Incremental Backups Level 2

    ・2nd, 3rd, 4th Friday                                  : Incremental Backups Level 1

  • 5. Re: Monthly Backup Plan using Incremental Backups
    EdStevens Guru
    Currently Being Moderated

    user12075536123 wrote:

     

    I think the following backups

    ・First Friday of the month                          : Incremental Backups Level 0

    ・Monday,Tuesday,Wednesday,Thursday : Incremental Backups Level 2

    ・2nd, 3rd, 4th Friday                                  : Incremental Backups Level 1

     

    Oracle has no "backup level 2".

     

    It has only level 0 and level 1

    Level 1 can be either cumulative or differential.

  • 6. Re: Monthly Backup Plan using Incremental Backups
    user12075536123 Pro
    Currently Being Moderated

    Thank you for advice.


    I understand Oracle has "backup level 0-4"

    I checked the document
    http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta007.htm#i78895


    INCREMENTAL LEVEL integer
    Copies only those data blocks that have changed since the last incremental integer backup, where integer is 0 or 1


    I understand Oracle has only level 0 and 1

     

    I tested
    ==================================================
    RMAN> backup incremental level 0 database;


    Starting backup at 18-SEP-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=64 instance=orcl device type=DISK
    channel ORA_DISK_1: starting incremental level 0 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 1 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 1 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 2 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 2 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 3 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 3 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 4 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 4 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 5 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of backup command at 09/18/2013 21:32:58
    RMAN-06011: invalid level specified: 5
    ==================================================


    why!!

  • 7. Re: Monthly Backup Plan using Incremental Backups
    EdStevens Guru
    Currently Being Moderated

    user12075536123 wrote:

     

    Thank you for advice.


    I understand Oracle has "backup level 0-4"

    I checked the document
    http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta007.htm#i78895


    INCREMENTAL LEVEL integer
    Copies only those data blocks that have changed since the last incremental integer backup, where integer is 0 or 1


    I understand Oracle has only level 0 and 1

     

    I tested
    ==================================================
    RMAN> backup incremental level 0 database;


    Starting backup at 18-SEP-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=64 instance=orcl device type=DISK
    channel ORA_DISK_1: starting incremental level 0 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 1 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 1 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 2 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 2 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 3 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 3 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 4 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting incremental level 4 datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    ・・・
    Finished backup at 18-SEP-13


    RMAN> backup incremental level 5 database;


    Starting backup at 18-SEP-13
    using channel ORA_DISK_1
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of backup command at 09/18/2013 21:32:58
    RMAN-06011: invalid level specified: 5
    ==================================================


    why!!

     

    Since there is no documentation of any level beyond 1, I can only guess that it is for backward compatibility.

  • 8. Re: Monthly Backup Plan using Incremental Backups
    user12075536123 Pro
    Currently Being Moderated

    > Since there is no documentation of any level beyond 1, I can only guess that it is for backward compatibility.

     

    Thank you for advice, I understood.

  • 9. Re: Monthly Backup Plan using Incremental Backups
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Whether you specify LEVEL 2 or 3 or 4,  all of them operate at LEVEL 1  only.  That has been the case since, at least, 10.2

     

    See Oracle Support Note "Does RMAN Oracle10g Db support Incremental Level 2 backups? (Doc ID 733535.1)"

     

    You should consider whether your Incremental Backups should be CUMULATIVE or DIFFERENTIAL.  Note that Incremental Backups are DIFFERENTIAL by default.

     

     

     

    Hemant K Chitale

  • 10. Re: Monthly Backup Plan using Incremental Backups
    user12075536123 Pro
    Currently Being Moderated

    Thank you for good advice, I understood about "level 2,3,4"

  • 11. Re: Monthly Backup Plan using Incremental Backups
    MahirM.Quluzade Guru
    Currently Being Moderated

    You  can check Incremental levels of  backup sets with

    select incremental_level  from v$backup_set;

  • 12. Re: Monthly Backup Plan using Incremental Backups
    user12075536123 Pro
    Currently Being Moderated

    I think that it is impossible because Oracle has not backup level 2,3,4.

     

    another monthly backup plan
    ・First Friday of the month                : backup incremental level 0 database;
    ・2nd, 3rd, 4th Friday                      : backup incremental level 1 database;
    ・Monday,Tuesday,Wednesday,Thursday : backup archivelog all

  • 13. Re: Monthly Backup Plan using Incremental Backups
    EdStevens Guru
    Currently Being Moderated

    user12075536123 wrote:

     

    I think that it is impossible because Oracle has not backup level 2,3,4.

     

    another monthly backup plan
    ・First Friday of the month                : backup incremental level 0 database;
    ・2nd, 3rd, 4th Friday                      : backup incremental level 1 database;
    ・Monday,Tuesday,Wednesday,Thursday : backup archivelog all

    And why are you not including archivelogs on Friday, Saturday, and Sunday? 

  • 14. Re: Monthly Backup Plan using Incremental Backups
    EdStevens Guru
    Currently Being Moderated

    user472167 wrote:

     

    I would like to have a monthly backup plan as explained in the oracle documentation:

     

    First Friday of the month: Full Backup (Incremental Level 0)

    Monday, Tuesday, Wednesday, Thursday: Incremental Level 1

    2nd, 3rd, 4th Friday: Cumulative Incremental Backups Level 1

     

    As I understand is that the 4th cumulative incremental backup will contain all the changes from the full backup.

     

    Is there any way so that the 4th friday backup will contain only the changes since the 3rd Friday and so on?

    After reviewing this thread some more, I feel compelled to ask why a monthly cycle?  What is the logic behind that vs the more usual weekly cycle?  This will almost certainly increase not only your disk requirements but also your expected time to recovery.  Even if you have a requirement to have a one month recovery window, that does not equate to a one-month (only one full backup per month) backup cycle.

     

    I'm not saying there are not necessarily legitimate reasons, but it is highly suspicious, and suggestive of not understanding how backups work and the role of the RETENTION PERIOD in managing backups.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points