9 Replies Latest reply: Jun 7, 2012 9:25 AM by Hemant K Chitale RSS

    why Delete Obsolete deleting control file backup

    896766
      I have following settings

      CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
      CONFIGURE CONTROLFILE AUTOBACKUP OFF;


      On Sunday, I do level 0 backup
      From Monday to Saturday, I do level 1 backup
      I also try to delete obsolete backup daily at the end of leve 0 and level 1 backup
      My question is why my control file backup was reported as obsolete and deleted each day ?? why no single copy/backup of control file was kept by RMAN?


      My scripts are:

      #Level 0 backup, Sunday only
      run {
      allocate channel d1 type disk;
      backup
      incremental level 0
      skip inaccessible
      tag backup_db_level_0
      filesperset 6
      format 'D:\RMANBackup\df_t%t_s%s_s%p.bkp'
      database;
      release channel d1;

      allocate channel d1 type disk;
      backup
      filesperset 20
      format 'D:\RMANBackup\al_t%t_s%s_s%p.arc'
      archivelog all;
      release channel d1;

      allocate channel d1 type disk;
      backup
      format 'D:\RMANBackup\ctr_t%t_s%s_p%p.ctl'
      current controlfile;
      release channel d1;

      allocate channel d1 type disk;
      report obsolete;
      delete noprompt force obsolete;
      release channel d1;
      }


      #Level 1 backup , Mon-Sat
      run {
      allocate channel d1 type disk;
      backup
      incremental level 1
      skip inaccessible
      tag backup_db_level_1
      filesperset 6
      format 'D:\RMANBackup\df_t%t_s%s_s%p.bkp'
      database; release channel d1;

      allocate channel d1 type disk;
      backup
      filesperset 20
      format 'D:\RMANBackup\al_t%t_s%s_s%p.arc'
      archivelog all;
      release channel d1;

      allocate channel d1 type disk;
      backup
      format 'D:\RMANBackup\ctr_t%t_s%s_p%p.ctl'
      current controlfile;
      release channel d1;

      allocate channel d1 type disk;
      report obsolete;
      delete noprompt force obsolete;
      release channel d1;
      }



      Anyone knows why or what's wrong ??
      Thanks
        • 1. Re: why Delete Obsolete deleting control file backup
          damorgan
          The first thing that is wrong is "CONFIGURE CONTROLFILE AUTOBACKUP OFF;"

          I stopped reading when I saw that. If you don't have a current backup of your control file you are water skiing without a life vest.

          Please read the FAQ and learn how to format your listing.
          • 2. Re: why Delete Obsolete deleting control file backup
            896766
            damorgan ,

            I don't understand your comment.

            control file AUTOBACKUP ON is recommended but it's an option.
            I choose to set it OFF and manually backup control file with "Backup current controlfile". The backup control files was generated and deleted later on in "Delete obsolete" command. I can see it in the log.

            And what's wrong with the format? I can see them generated fine..

            Edited by: user8638320 on Jun 5, 2012 3:47 PM

            Edited by: user8638320 on Jun 5, 2012 3:49 PM
            • 3. Re: why Delete Obsolete deleting control file backup
              Hemant K Chitale
              You have redundancy 1. Any 1 controlfile backup that captures information about the whole week's backups is adequate for recovery. The controlfile does capture information about backups. So, the latest controlfile on day 7 does have information about previous L1 backups. The earlier controlfile backups are obsolete.

              Hemant K Chitale
              • 4. Re: why Delete Obsolete deleting control file backup
                AJ
                The only problem with that as I can see is if the following happens (please correct me if I'm wrong Hemant):

                You alter the structure of the database (e.g. drop a tablespace) and then later that week you realize that you need to perform incomplete recovery to undo the drop tablespace command. You cannot do that with the current controlfile or with the backup controlfile if that controlfile backup is later than the alteration. You would need a controlfile backup from before the drop tablespace command.

                AJ
                • 5. Re: why Delete Obsolete deleting control file backup
                  Hemant K Chitale
                  Since CONTROLFILE AUTOBACKUP is OFF, the Full Backup also implicitly includes a control file backup in one of the backuppieces -- the one with the system datafile.
                  The "additional" controlfile backup created by a BACKUP CONTROLFILE is the one that is obsolete.

                  The OP could do a LIST BACKUP OF CONTROLFILE to identify the controlfile backups.


                  Hemant K Chitale
                  • 6. Re: why Delete Obsolete deleting control file backup
                    896766
                    Hemant K Chitale,

                    Thanks for the reply.

                    So my question now is.

                    With CONTROLFILE AUTOBACKUP is OFF
                    On Day 0 (Sunday), I have level 0 backup, which is equivalent to full backup, along with manual backcup of current controlfile.
                    On follwoing Day 1-6, I have level 1 backup also manual backup of current controlfile.
                    But I saw the manual backup of controlfiles on Day 1-6 all got deleted because of obsolete. Is that because the database did not any structure change?
                    If there is any structure change, like dropping a tablespace, on Day 3 for example, manual control file backup on Day 3 would be kept (not obsolete) until next level 0 backup or newer database structure change ?

                    Thanks,
                    • 7. Re: why Delete Obsolete deleting control file backup
                      damorgan
                      I am going to respectfully disagree.

                      Not with what Hemant has written because it is all correct but rather with the entire direction of what the OP is doing and the responses to it.

                      Let's say you take a backup at 2:00am.

                      Then at 8:00am you add a new tablepace

                      Then at 14:00 you have finished migrated tables into that new tablespace.

                      Then at 14:01 the database crashes.

                      Of what value is last night's control file backup?

                      Turn on autobackup and let Oracle manage things as it wishes ... just as with the change from rollback segments to undo ... rollback segments were the default too until 11gR2 ... Oracle will do a better job at protecting you than you will in almost every instance.

                      There is a lot more stored in a control file than just a history of backups.
                      • 8. Re: why Delete Obsolete deleting control file backup
                        AJ
                        Damorgan: well, last nights controlfile backup would be OK. The added tablespace after the backup would be added to the restored controlfile during the recovery phase.

                        But I totally agree with you, and I don't understand why Oracle haven't enabled controlfile autobackup on as default..
                        I cannot see any reason why anyone would not enable autobackup. Especially with versions prior to 10.2, not enabling it could really cause problems in a recovery scenario.

                        Example: you backup your database in nocatalog with
                         backup database plus archivelog delete input 
                        Ok, the controlfile backup is included in the backupset since datafile 1 is also backed up. The database server crash. Setup new server, install software and get all the backupsets of the database files onto the new server along with the backupset of the archived redo logs.
                        But since your controlfile autobackup was OFF, and therefore only included in the main datafiles backupset, you cannot (not easily) access the backupset containing the necessary archived redo logs needed to recover that backup.

                        Luckily this changed in the newer releases of Oracle with the CATALOG command enabling you to update the controlfile with "unknown" backupsets.
                        • 9. Re: why Delete Obsolete deleting control file backup
                          Hemant K Chitale
                          Take a look at the example(s) here :
                          http://hemantoracledba.blogspot.sg/2012/06/controlfile-autobackups-are-obsoleted.html


                          Hemant K Chitale