1 2 Previous Next 23 Replies Latest reply on Apr 28, 2019 4:39 PM by Dude! Go to original post
      • 15. Re: RMAN Backup Consistency with KEEP UNTIL
        Dude!

        Riaz. wrote:

         

        Actually I am restoring to a different host than the backup host. So I assume after a controlfile restore (this controlfile is part of the backup), I will get the log sequence for that backup.

        The KEEP option exludes a backup from the retention policy. As I recall, if you restore a backup controlfile, RMAN will automatically perform a recatalog and attempts to find archivelogs according to the archiving destination and log format specified in the init parameter file. So it depends on what information you have copied to the new host or what data is available.

         

        If you perform a backup uisng the keep option while the database is not open, however, you can restore the database using the tag you specifed when making the backup with the keep option and then simply open the the database with resetlogs, since no recovery is required. Otherwise, when the backup was online, you need to specify SET UNTIL when recovering the database.

        • 16. Re: RMAN Backup Consistency with KEEP UNTIL
          Riaz.

          It seems the mystery is solved. From the backup script, specially due to below lines:

           

          echo SPFILE FORMAT 'spfile_%%d_DBID_%%I_Date_%%T_%%t'
          echo CURRENT CONTROLFILE FORMAT 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t';
          

           

          I thought the control file/SP file backups will always follow above format. Then I saw following lines in the backup log:

           

          including current control file in backup set  
          channel ch00: starting piece 1 at 2019-04-15:01:11:29  
          channel ch00: finished piece 1 at 2019-04-15:01:11:54  
          piece handle=ora11gpr_full_ctrl_u_3gtv0sq0_s_28784_p_1_t_1005613888 tag=TAG20190415T010022 comment=API Version 2.0,MMS Version 5.0.0.0  
          ......................................
          ......................................
          including current SPFILE in backup set  
          channel ch00: starting piece 1 at 2019-04-15:01:11:54  
          channel ch00: finished piece 1 at 2019-04-15:01:12:19  
          piece handle=spfile_ORA11GPR_DBID_1287758533_Date_20190415_1005613914 tag=TAG20190415T010022 comment=API Version 2.0,MMS Version 5.0.0.0  
          ......................................
          ......................................
          including current SPFILE in backup set  
          channel ch00: starting piece 1 at 2019-04-15:01:12:20  
          channel ch00: finished piece 1 at 2019-04-15:01:12:45  
          piece handle=ora11gpr_bk_u_3itv0srk_s_28786_p_1_t_1005613940 tag=TAG20190415T010011 comment=API Version 2.0,MMS Version 5.0.0.0  
          
          ......................................
          ......................................
          including current control file in backup set  
          channel ch00: starting piece 1 at 2019-04-15:01:13:25  
          channel ch00: finished piece 1 at 2019-04-15:01:13:50  
          piece handle=ora11gpr_bk_u_3ktv0stk_s_28788_p_1_t_1005614004 tag=TAG20190415T010011 comment=API Version 2.0,MMS Version 5.0.0.0  
          channel ch00: backup set complete, elapsed time: 00:00:25  
          Finished backup at 2019-04-15:01:13:50 
          

           

           

          So it seems that the backups of control file and spfile can be there in another backup set (or can have a different format, other than give one). Once I used the last control file backup piece (ora11gpr_bk_u_3ktv0stk_s_28788_p_1_t_1005614004) and tried recover/restore, It all went fine and it also used the backup piece I was expecting it to use:

           

           

          RMAN> RUN
          2> {
          3> ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';
          4> SEND 'NB_ORA_CLIENT=VIP-Name';
          5> restore controlfile from 'ora11gpr_bk_u_3ktv0stk_s_28788_p_1_t_1005614004';
          6> }
          
          
          allocated channel: CH1
          channel CH1: SID=4 device type=SBT_TAPE
          channel CH1: Veritas NetBackup for Oracle - Release 8.0 (165)
          
          
          sent command to channel: CH1
          
          
          Starting restore at 24-APR-19
          
          
          channel CH1: restoring control file
          channel CH1: restore complete, elapsed time: 00:00:38
          output file name=+DATA/ora11gpr/controlfile/current.365.1006434221
          output file name=+DATA/ora11gpr/controlfile/current.277.1006434221
          Finished restore at 24-APR-19
          released channel: CH1
          
          
          RMAN>
          
          
          RMAN> mount database;
          
          
          database mounted
          
          
          RMAN> RUN
          2> {
          3> set until sequence 166797 thread 2;
          4> ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';
          5> SEND 'NB_ORA_CLIENT=VIP-Name';
          6> ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE';
          7> SEND 'NB_ORA_CLIENT=VIP-Name';
          8> ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE';
          9> SEND 'NB_ORA_CLIENT=VIP-Name';
          10> ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE';
          11> SEND 'NB_ORA_CLIENT=VIP-Name';
          12> restore database;
          13> switch datafile all;
          14> switch tempfile all;
          15> recover database;
          16> release channel CH1;
          17> release channel CH2;
          18> release channel CH3;
          19> release channel CH4;
          20> }
          
          
          executing command: SET until clause
          
          
          allocated channel: CH1
          channel CH1: SID=4 device type=SBT_TAPE
          channel CH1: Veritas NetBackup for Oracle - Release 8.0 (165)
          
          
          sent command to channel: CH1
          
          
          allocated channel: CH2
          channel CH2: SID=199 device type=SBT_TAPE
          channel CH2: Veritas NetBackup for Oracle - Release 8.0 (165)
          
          
          sent command to channel: CH1
          sent command to channel: CH2
          
          
          allocated channel: CH3
          channel CH3: SID=292 device type=SBT_TAPE
          channel CH3: Veritas NetBackup for Oracle - Release 8.0 (165)
          
          
          sent command to channel: CH1
          sent command to channel: CH2
          sent command to channel: CH3
          
          
          allocated channel: CH4
          channel CH4: SID=392 device type=SBT_TAPE
          channel CH4: Veritas NetBackup for Oracle - Release 8.0 (165)
          
          
          sent command to channel: CH1
          sent command to channel: CH2
          sent command to channel: CH3
          sent command to channel: CH4
          
          
          Starting restore at 24-APR-19
          Starting implicit crosscheck backup at 24-APR-19
          Finished implicit crosscheck backup at 24-APR-19
          
          
          Starting implicit crosscheck copy at 24-APR-19
          Finished implicit crosscheck copy at 24-APR-19
          
          
          searching for all files in the recovery area
          cataloging files...
          cataloging done
          
          
          List of Cataloged Files
          =======================
          File Name: +DATA/ora11gpr/archivelog/2019_04_24/thread_2_seq_1.272.1006434427
          File Name: +DATA/ora11gpr/archivelog/2019_04_24/thread_1_seq_8.268.1006434465
          File Name: +DATA/ora11gpr/archivelog/2019_04_24/thread_1_seq_9.355.1006434467
          File Name: +DATA/ora11gpr/archivelog/2019_04_24/thread_1_seq_10.271.1006434549
          File Name: +DATA/ora11gpr/archivelog/2019_04_24/thread_2_seq_2.282.1006434551
          File Name: +DATA/ora11gpr/datafile/ll.400.1006439875
          File Name: +DATA/ora11gpr/datafile/ll.401.1006439881
          File Name: +DATA/ora11gpr/datafile/ll.402.1006439887
          File Name: +DATA/ora11gpr/datafile/il.403.1006439893
          File Name: +DATA/ora11gpr/datafile/ora_ts.291.1006439897
          File Name: +DATA/ora11gpr/datafile/ora_ts.346.1006439899
          File Name: +DATA/ora11gpr/datafile/sysaux.352.1006439901
          File Name: +DATA/ora11gpr/datafile/ll.288.1006439903
          File Name: +DATA/ora11gpr/datafile/ll.353.1006440303
          File Name: +DATA/ora11gpr/datafile/il.292.1006440553
          File Name: +DATA/ora11gpr/datafile/ora_ts.362.1006440675
          File Name: +DATA/ora11gpr/datafile/undotbs1_new.306.1006440679
          File Name: +DATA/ora11gpr/datafile/undotbs2_new.342.1006440715
          File Name: +DATA/ora11gpr/datafile/system.269.1006440885
          File Name: +DATA/ora11gpr/datafile/ll.351.1006440895
          File Name: +DATA/ora11gpr/datafile/ora_ts.303.1006440935
          File Name: +DATA/ora11gpr/datafile/ora_ts.298.1006440985
          File Name: +DATA/ora11gpr/datafile/ora_ts.278.1006440991
          File Name: +DATA/ora11gpr/datafile/llic.293.1006441001
          File Name: +DATA/ora11gpr/datafile/rpt_1.344.1006441031
          File Name: +DATA/ora11gpr/datafile/rpt_2.276.1006441033
          File Name: +DATA/ora11gpr/datafile/users.347.1006441037
          
          
          
          
          channel CH1: starting datafile backup set restore
          channel CH1: specifying datafile(s) to restore from backup set
          channel CH1: restoring datafile 00001 to +DATA/ora11gpr/datafile/system.303.885830093
          channel CH1: restoring datafile 00002 to +DATA/ora11gpr/datafile/sysaux.314.885830093
          channel CH1: restoring datafile 00003 to +DATA/ora11gpr/datafile/ll.283.931172447
          channel CH1: restoring datafile 00004 to +DATA/ora11gpr/datafile/users.316.885830093
          channel CH1: restoring datafile 00005 to +DATA/ora11gpr/datafile/ll.318.931172461
          channel CH1: restoring datafile 00006 to +DATA/ora11gpr/datafile/il.339.897994789
          channel CH1: restoring datafile 00007 to +DATA/ora11gpr/datafile/ll.341.890487447
          channel CH1: restoring datafile 00008 to +DATA/ora11gpr/datafile/ll.342.890487455
          channel CH1: restoring datafile 00009 to +DATA/ora11gpr/datafile/ll.343.890487463
          channel CH1: restoring datafile 00010 to +DATA/ora11gpr/datafile/undotbs1_new.345.918823215
          channel CH1: restoring datafile 00011 to +DATA/ora11gpr/datafile/undotbs2_new.346.918823281
          channel CH1: restoring datafile 00012 to +DATA/ora11gpr/datafile/lic.315.937131005
          channel CH1: restoring datafile 00013 to +DATA/ora11gpr/datafile/ora_ts.257.970477399
          channel CH1: restoring datafile 00014 to +DATA/ora11gpr/datafile/ora_ts.256.970477409
          channel CH1: restoring datafile 00015 to +DATA/ora11gpr/datafile/rpt_1.359.1000555565
          channel CH1: restoring datafile 00016 to +DATA/ora11gpr/datafile/ora_ts.258.985788479
          channel CH1: restoring datafile 00017 to +DATA/ora11gpr/datafile/ora_ts.259.985788625
          channel CH1: restoring datafile 00018 to +DATA/ora11gpr/datafile/ora_ts.260.985940497
          channel CH1: restoring datafile 00019 to +DATA/ora11gpr/datafile/il.360.988186877
          channel CH1: restoring datafile 00020 to +DATA/ora11gpr/datafile/ll.358.988186909
          channel CH1: restoring datafile 00021 to +DATA/ora11gpr/datafile/ora_ts.261.988186939
          channel CH1: restoring datafile 00022 to +DATA/ora11gpr/datafile/rpt_2.357.991491379
          channel CH1: reading from backup piece ora11gpr_bk_u_3ftv0s56_s_28783_p_1_t_1005613222
          channel CH1: piece handle=ora11gpr_bk_u_3ftv0s56_s_28783_p_1_t_1005613222 tag=TAG20190415T010011
          channel CH1: restored backup piece 1
          channel CH1: restore complete, elapsed time: 00:29:46
          Finished restore at 24-APR-19
          
          
          Starting recover at 24-APR-19
          
          
          starting media recovery
          
          
          channel CH1: starting archived log restore to default destination
          channel CH1: restoring archived log
          archived log thread=1 sequence=168365
          channel CH1: restoring archived log
          archived log thread=2 sequence=166796
          channel CH1: reading from backup piece ora11gpr_bk_u_3jtv0ssq_s_28787_p_1_t_1005613978
          channel CH1: piece handle=ora11gpr_bk_u_3jtv0ssq_s_28787_p_1_t_1005613978 tag=TAG20190415T010011
          channel CH1: restored backup piece 1
          channel CH1: restore complete, elapsed time: 00:00:45
          archived log file name=+DATA/ora11gpr/archivelog/2019_04_24/thread_1_seq_168365.347.1006443693 thread=1 sequence=168365
          archived log file name=+DATA/ora11gpr/archivelog/2019_04_24/thread_2_seq_166796.276.1006443693 thread=2 sequence=166796
          channel default: deleting archived log(s)
          archived log file name=+DATA/ora11gpr/archivelog/2019_04_24/thread_2_seq_166796.276.1006443693 RECID=298951 STAMP=1006443694
          channel default: deleting archived log(s)
          archived log file name=+DATA/ora11gpr/archivelog/2019_04_24/thread_1_seq_168365.347.1006443693 RECID=298952 STAMP=1006443694
          media recovery complete, elapsed time: 00:00:01
          Finished recover at 24-APR-19
          
          
          released channel: CH1
          
          
          released channel: CH2
          
          
          released channel: CH3
          
          
          released channel: CH4
          
          
          RMAN>
          

           

          So now my questions:

           

          (1) How it is possible for control file and spfile backups to have other formats than the ones given in backup command?

          (2) How to make sure ALL the control file and spfile backups ALWAYS follow the same format as like given in the backup command

          (3) My understanding was that there will be one backup of control file after executing the backup script, but it seems not be the case. Any idea why?

           

          Regards,

          • 17. Re: RMAN Backup Consistency with KEEP UNTIL
            AJ

            Show us the output of RMAN command:

            show controlfile autobackup format;

            show controlfile autobackup;

             

            3) Well, to me it seems that there is ?

             

            backup will be obsolete on date 2019-07-14:01:13:24 

            archived logs required to recover from this backup will be backed up 

            channel ch00: starting full datafile backup set 

            channel ch00: specifying datafile(s) in backup set 

            including current control file in backup set  <================================

            channel ch00: starting piece 1 at 2019-04-15:01:13:25 

            channel ch00: finished piece 1 at 2019-04-15:01:13:50 

            piece handle=ora11gpr_bk_u_3ktv0stk_s_28788_p_1_t_1005614004 tag=TAG20190415T010011 comment=API Version 2.0,MMS Version 5.0.0.0 

            channel ch00: backup set complete, elapsed time: 00:00:25 

            Finished backup at 2019-04-15:01:13:50

             

             

             

             

            AJ

            • 18. Re: RMAN Backup Consistency with KEEP UNTIL
              Riaz.

              Below is the output for commands from source database:

               

              RMAN> show controlfile autobackup format;
              
              using target database control file instead of recovery catalog
              RMAN configuration parameters for database with db_unique_name ORA11GPR are:
              CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
              CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
              
              
              RMAN> show controlfile autobackup;
              
              
              RMAN configuration parameters for database with db_unique_name ORA11GPR are:
              CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
              

               

              (3) Sorry for the confusion, I meant I was expecting there will be only one backup piece (in the format mentioned in script) but it is clear form the backup log that there were more than 1 control file backups were created (in different backup pieces). Consider below lines from backup log:

               

              including current control file in backup set  
              channel ch00: starting piece 1 at 2019-04-15:01:11:29  
              channel ch00: finished piece 1 at 2019-04-15:01:11:54  
              piece handle=ora11gpr_full_ctrl_u_3gtv0sq0_s_28784_p_1_t_1005613888 tag=TAG20190415T010022 comment=API Version 2.0,MMS Version 5.0.0.0  
              channel ch00: backup set complete, elapsed time: 00:00:25  
              ..............................................
              ..............................................
              ..............................................
              including current control file in backup set  
              channel ch00: starting piece 1 at 2019-04-15:01:13:25  
              channel ch00: finished piece 1 at 2019-04-15:01:13:50  
              piece handle=ora11gpr_bk_u_3ktv0stk_s_28788_p_1_t_1005614004 tag=TAG20190415T010011 comment=API Version 2.0,MMS Version 5.0.0.0  
              channel ch00: backup set complete, elapsed time: 00:00:25  
              Finished backup at 2019-04-15:01:13:50
              
              • 19. Re: RMAN Backup Consistency with KEEP UNTIL
                AJ

                If you want the same format for the controlfile autobackup you can just configure it to be the same. But you must have %F as well.

                 

                E.g

                 

                RMAN> configure controlfile autobackup format for device type sbt to 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t';
                
                
                RMAN-00571: ===========================================================
                RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                RMAN-00571: ===========================================================
                RMAN-03002: failure of configure command at 04/25/2019 12:14:42
                RMAN-06492: control file AUTOBACKUP format "ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t" must specify a "%F" format specifier
                
                
                RMAN> configure controlfile autobackup format for device type sbt to 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t_%F';
                
                
                new RMAN configuration parameters:
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t_%F';
                new RMAN configuration parameters are successfully stored
                
                
                RMAN> show controlfile autobackup format ;
                
                
                RMAN configuration parameters for database with db_unique_name TESTDB are:
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t_%F';
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
                

                 

                AJ

                • 20. Re: RMAN Backup Consistency with KEEP UNTIL
                  Riaz.

                  But autobackup is set to OFF. I wonder why two backups of controlfile are generated? One backup piece is following this format:

                   

                  echo DATABASE FORMAT 'ora11gpr_bk_u_%%u_s_%%s_p_%%p_t_%%t' 
                  

                   

                  The other follows this format:

                   

                  echo CURRENT CONTROLFILE FORMAT 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t';
                  

                   

                  I expect only one backup of controlfile with the below format:

                   

                  echo CURRENT CONTROLFILE FORMAT 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t';
                  

                   

                  Regards,

                  • 21. Re: RMAN Backup Consistency with KEEP UNTIL
                    AJ

                    The reason is because : whenever you backup datafile 1 (system), a controlfile will automatically be backed up. And in addition your backup script runs a separate backup command only for the current controlfile.

                    That is why.

                     

                     

                    AJ

                    1 person found this helpful
                    • 22. Re: RMAN Backup Consistency with KEEP UNTIL
                      Riaz.

                      Thank you; I learned something new (as autobackup is off, it will generate control file backup whenever we backup system tablespace).

                       

                      Our aim was to be able to create a self-contained backup that wouldn't need any additional archive outside of that backup for restore/recover within backup time. Now the explicit controlfile backup we are generating is not useful to achieve that objective. We want to have a custom name for control file backup (full backup controlfile format is different than the controlfile backup generated with archive logs backup). Do you think it will be helpful for us in achieving our objective if we backup the controlfile AFTER the full backup, in a different black? Something like:

                       

                      echo RUN {
                      echo ALLOCATE CHANNEL ch00
                      echo    TYPE 'SBT_TAPE';
                      echo SEND 'NB_ORA_CLIENT=<VIP-Name>,NB_ORA_SID=<SID>,NB_ORA_SERV=<Master Server>,NB_ORA_SCHED=Default-Application-Backup,NB_ORA_POLICY=<Policy Name>';
                      echo BACKUP
                      echo    %BACKUP_TYPE%
                      echo    KEEP UNTIL TIME 'SYSDATE+90'
                      echo    DATABASE FORMAT 'ora11gpr_bk_u_%%u_s_%%s_p_%%p_t_%%t'
                      echo SPFILE FORMAT 'spfile_%%d_DBID_%%I_Date_%%T_%%t';
                      
                      echo BACKUP
                      echo    KEEP UNTIL TIME 'SYSDATE+90'
                      echo CURRENT CONTROLFILE FORMAT 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t';
                      echo RELEASE CHANNEL ch00;}
                      

                       

                      Regards,

                      • 23. Re: RMAN Backup Consistency with KEEP UNTIL
                        Dude!

                        Oracle Database restore and recovery does not work like conventional filesystem backups. The controlfile holds RMAN metadata and information what datafiles need to be restored, but recovery only stops when no more recovery data is available.

                         

                        Keep in mind that archivelogs contain recovery information of multiple datafiles while the database was online. Some of the recovery information included will be incomplete. Your backup of archivlogs will contain data required to restore the database to a consistent state, but will also contain recovery information that relies on other archivelogs that are not included in the backup, including the online redologs.

                         

                        The same concept applies to a "self-contained" backup using the KEEP option, which will have all data required to recover the database to a consistent state, but you will have to tell RMAN when to stop applying redo information, i.e. not to apply recovery data that would require further archivelogs or online redo logs that are not included in your backup.

                         

                        If you wish to accomplish a RMAN backup that you can simply restore without having to worry about recovery, then you need to perform a consistent backup, meaning a cold backup. Again, as I mentioned before, a self-contained backup isn't necessarily a consistent backup, and hence will always result in an incomplete recovery, unless the online redo logs can be applied or you tell RMAN when recovery needs to stop.

                        1 2 Previous Next