1 2 Previous Next 23 Replies Latest reply on Apr 28, 2019 4:39 PM by Dude!

    RMAN Backup Consistency with KEEP UNTIL

    Riaz.

      Dear All,

       

      We have 2-Node RAC (11.2.0.4) deployed on Windows OS. Our level 0 backup is created using UNTIL clause; our assumption is that the backup will be consistent and whenever restored, will not require ANY additional archive logs (other than the ones backed up as part of level 0 backup).

       

      But when we perform a restore/recover, it uses the archive logs that were not part of this self-contained backup. I am providing the restore/recover logs (disk groups and data files name renamed) and the way we concluded the log sequence/thread (using Doc ID 243760.1; the output is provided as well). As it can be observed, the restore/recover used following archive logs backups that were not part of the level 0 backup:

       

      1) "ora11gpr_arch_u_4rtrvhat_s_27803_p_1_t_1002423645"

      2) "ora11gpr_arch_u_4strvhbm_s_27804_p_1_t_1002423670"

      3)"ora11gpr_arch_u_4utrvob3_s_27806_p_1_t_1002430819"

       

      Now either my understanding (that backups created using KEEP UNTIL clause are consistent and self-contained and contain all archive logs to make this backup consistent, when restored) is wrong or I might be missing some steps while finding log sequence/thread.

       

      Can you please guide?

       

      Backup script (we are using Symantec Netbackup):

       

      @REM --------------------------------------------------------------------------- 
      @REM What kind of backup will we perform. 
      @REM --------------------------------------------------------------------------- 
      
      @if "%NB_ORA_FULL%" EQU "1" @set BACKUP_TYPE=INCREMENTAL Level=0 
      @if "%NB_ORA_INCR%" EQU "1" @set BACKUP_TYPE=INCREMENTAL Level=1 
      @if "%NB_ORA_CINC%" EQU "1" @set BACKUP_TYPE=INCREMENTAL Level=1 CUMULATIVE 
      @if NOT DEFINED BACKUP_TYPE @set BACKUP_TYPE=INCREMENTAL Level=0 
      
      echo RUN { 
      echo ALLOCATE CHANNEL ch00 
      echo TYPE 'SBT_TAPE'; 
      echo SEND 'NB_ORA_CLIENT=<VIP Name>,NB_ORA_SID=<Local SID>,NB_ORA_SERV=ded-nbu,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 CURRENT CONTROLFILE FORMAT 'ora11gpr_full_ctrl_u_%%u_s_%%s_p_%%p_t_%%t'; 
      echo RELEASE CHANNEL ch00; 
      echo }
      

       

      Find Log sequence/thread (using Doc ID 243760.1)

       

      SQL> ed
      Wrote file afiedt.buf
      
      
        1  select max(sequence#) from v$archived_log L, v$database D
        2              where L.resetlogs_change# = D.resetlogs_change# and
        3*             thread#=1
        4  /
      
      
      MAX(SEQUENCE#)
      --------------
              168364
      
      
      SQL> ed
      Wrote file afiedt.buf
      
      
        1  select max(sequence#) from v$archived_log L, v$database D
        2              where L.resetlogs_change# = D.resetlogs_change# and
        3*             thread#=2
        4  /
      
      
      MAX(SEQUENCE#)
      --------------
              166795
      
      
      SQL> ed
      Wrote file afiedt.buf
      
      
        1   select sequence#, thread#, first_change#, next_change#
        2               from v$archived_log L, v$database D
        3               where L.resetlogs_change# = D.resetlogs_change# and
        4*              sequence# in (168364,166795)
      SQL> /
      
      
       SEQUENCE#    THREAD# FIRST_CHANGE# NEXT_CHANGE#
      ---------- ---------- ------------- ------------
          166795          1    1.0454E+13   1.0454E+13
          166795          1    1.0454E+13   1.0454E+13
          166795          2    1.0454E+13   1.0454E+13
          168364          1    1.0454E+13   1.0454E+13
          166795          2    1.0454E+13   1.0454E+13
          168364          1    1.0454E+13   1.0454E+13
      
      
      6 rows selected.
      
      
      SQL> set numwidth 15
      SQL> /
      
      
            SEQUENCE#         THREAD#   FIRST_CHANGE#    NEXT_CHANGE#
      --------------- --------------- --------------- ---------------
               166795               1  10453997214377  10453997237945
               166795               1  10453997214377  10453997237945
               166795               2  10454408546377  10454408556774
               168364               1  10454408546421  10454408556762
               166795               2  10454408546377  10454408556774
               168364               1  10454408546421  10454408556762
      
      
      6 rows selected.
      
      
      SQL>  select sequence#, thread#, first_change#, next_change#
        2             from v$backup_redolog
        3             where sequence# in (168364,166795);
      
      
            SEQUENCE#         THREAD#   FIRST_CHANGE#    NEXT_CHANGE#
      --------------- --------------- --------------- ---------------
               166795               1  10453997214377  10453997237945
      
      
      SQL> spo off
      

       

      RMAN restore/recover log

       

      Recovery Manager11.2.0.4.0
      
      
      RMAN> 
      executing command: SET DBID
      
      
      RMAN> 
      Oracle instance started
      
      
      Total System Global Area    2572144640 bytes
      
      
      Fixed Size                     2284000 bytes
      Variable Size                822085152 bytes
      Database Buffers            1728053248 bytes
      Redo Buffers                  19722240 bytes
      
      
      RMAN> 2> 3> 4> 5> 6> 
      using target database control file instead of recovery catalog
      allocated channel: CH1
      channel CH1: SID=292 instance=ora11gpr1 device type=SBT_TAPE
      channel CH1: Veritas NetBackup for Oracle - Release 8.0 (165)
      
      
      sent command to channel: CH1
      
      
      Starting restore at 21-APR-19
      
      
      channel CH1: restoring control file
      channel CH1: restore complete, elapsed time: 00:00:37
      output file name=+DATA/ora11gpr/controlfile/current.366.1006174035
      output file name=+DATA/ora11gpr/controlfile/current.367.1006174035
      Finished restore at 21-APR-19
      released channel: CH1
      
      
      RMAN> 
      Oracle instance started
      database mounted
      
      
      Total System Global Area    2572144640 bytes
      
      
      Fixed Size                     2284000 bytes
      Variable Size                822085152 bytes
      Database Buffers            1728053248 bytes
      Redo Buffers                  19722240 bytes
      
      
      RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 
      executing command: SET until clause
      
      
      allocated channel: CH1
      channel CH1: SID=292 instance=ora11gpr1 device type=SBT_TAPE
      channel CH1: Veritas NetBackup for Oracle - Release 8.0 (165)
      
      
      sent command to channel: CH1
      
      
      RMAN-06908: WARNING: operation will not run in parallel on the allocated channels
      RMAN-06909: WARNING: parallelism require Enterprise Edition
      allocated channel: CH2
      channel CH2: SID=490 instance=ora11gpr1 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=587 instance=ora11gpr1 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=684 instance=ora11gpr1 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 21-APR-19
      Starting implicit crosscheck backup at 21-APR-19
      Finished implicit crosscheck backup at 21-APR-19
      
      
      Starting implicit crosscheck copy at 21-APR-19
      Finished implicit crosscheck copy at 21-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_21/thread_2_seq_1.378.1006174235
      File Name: +DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_2.370.1006174371
      
      channel CH1: starting datafile backup set restore
      channel CH1: specifying datafile(s) to restore from backup set
      channel CH1: restoring datafile 00001 to +DG_DATA/ora11gpr/datafile/system.303.885830093
      channel CH1: restoring datafile 00002 to +DG_DATA/ora11gpr/datafile/sysaux.314.885830093
      channel CH1: restoring datafile 00003 to +DG_DATA/ora11gpr/datafile/l1.283.931172447
      channel CH1: restoring datafile 00004 to +DG_DATA/ora11gpr/datafile/users.316.885830093
      channel CH1: restoring datafile 00005 to +DG_DATA/ora11gpr/datafile/l1.318.931172461
      channel CH1: restoring datafile 00006 to +DG_DATA/ora11gpr/datafile/int.339.897994789
      channel CH1: restoring datafile 00007 to +DG_DATA/ora11gpr/datafile/l1.341.890487447
      channel CH1: restoring datafile 00008 to +DG_DATA/ora11gpr/datafile/l1.342.890487455
      channel CH1: restoring datafile 00009 to +DG_DATA/ora11gpr/datafile/l1.343.890487463
      channel CH1: restoring datafile 00010 to +DG_DATA/ora11gpr/datafile/undotbs1_new.345.918823215
      channel CH1: restoring datafile 00011 to +DG_DATA/ora11gpr/datafile/undotbs2_new.346.918823281
      channel CH1: restoring datafile 00012 to +DG_DATA/ora11gpr/datafile/t1.315.937131005
      channel CH1: restoring datafile 00013 to +DG_DATA/ora11gpr/datafile/ora_ts.257.970477399
      channel CH1: restoring datafile 00014 to +DG_DATA/ora11gpr/datafile/ora_ts.256.970477409
      channel CH1: restoring datafile 00015 to +DG_DATA/ora11gpr/datafile/report_n1.359.1000555565
      channel CH1: restoring datafile 00016 to +DG_DATA/ora11gpr/datafile/ora_ts.258.985788479
      channel CH1: restoring datafile 00017 to +DG_DATA/ora11gpr/datafile/ora_ts.259.985788625
      channel CH1: restoring datafile 00018 to +DG_DATA/ora11gpr/datafile/ora_ts.260.985940497
      channel CH1: restoring datafile 00019 to +DG_DATA/ora11gpr/datafile/int.360.988186877
      channel CH1: restoring datafile 00020 to +DG_DATA/ora11gpr/datafile/l1.358.988186909
      channel CH1: restoring datafile 00021 to +DG_DATA/ora11gpr/datafile/ora_ts.261.988186939
      channel CH1: restoring datafile 00022 to +DG_DATA/ora11gpr/datafile/report_n2.357.991491379
      channel CH1: reading from backup piece ora11gpr_bk_u_4ktrva94_s_27796_p_1_t_1002416420
      channel CH1: piece handle=ora11gpr_bk_u_4ktrva94_s_27796_p_1_t_1002416420 tag=TAG20190309T010008
      channel CH1: restored backup piece 1
      channel CH1: restore complete, elapsed time: 00:44:15
      Finished restore at 21-APR-19
      
      
      
      
      
      
      Starting recover at 21-APR-19
      
      
      starting media recovery
      
      
      channel CH1: starting archived log restore to default destination
      channel CH1: restoring archived log
      archived log thread=1 sequence=166783
      channel CH1: restoring archived log
      archived log thread=2 sequence=165257
      channel CH1: restoring archived log
      archived log thread=2 sequence=165258
      channel CH1: restoring archived log
      archived log thread=1 sequence=166784
      channel CH1: restoring archived log
      archived log thread=1 sequence=166785
      channel CH1: restoring archived log
      archived log thread=1 sequence=166786
      channel CH1: restoring archived log
      archived log thread=2 sequence=165259
      channel CH1: restoring archived log
      archived log thread=1 sequence=166787
      channel CH1: restoring archived log
      archived log thread=2 sequence=165260
      channel CH1: restoring archived log
      archived log thread=2 sequence=165261
      channel CH1: restoring archived log
      archived log thread=2 sequence=165262
      channel CH1: restoring archived log
      archived log thread=2 sequence=165263
      channel CH1: restoring archived log
      archived log thread=1 sequence=166788
      channel CH1: restoring archived log
      archived log thread=2 sequence=165264
      channel CH1: restoring archived log
      archived log thread=2 sequence=165265
      channel CH1: reading from backup piece ora11gpr_arch_u_4rtrvhat_s_27803_p_1_t_1002423645
      channel CH1: piece handle=ora11gpr_arch_u_4rtrvhat_s_27803_p_1_t_1002423645 tag=TAG20190309T030019
      channel CH1: restored backup piece 1
      channel CH1: restore complete, elapsed time: 00:00:45
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166783.268.1006177605 thread=1 sequence=166783
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165257.271.1006177607 thread=2 sequence=165257
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165257.271.1006177607 RECID=298952 STAMP=1006177609
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165258.272.1006177607 thread=2 sequence=165258
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166783.268.1006177605 RECID=298949 STAMP=1006177607
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166784.274.1006177609 thread=1 sequence=166784
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166784.274.1006177609 RECID=298955 STAMP=1006177611
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166785.273.1006177607 thread=1 sequence=166785
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166785.273.1006177607 RECID=298951 STAMP=1006177609
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166786.277.1006177609 thread=1 sequence=166786
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165258.272.1006177607 RECID=298950 STAMP=1006177608
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165259.278.1006177611 thread=2 sequence=165259
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166786.277.1006177609 RECID=298958 STAMP=1006177613
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166787.269.1006177605 thread=1 sequence=166787
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165259.278.1006177611 RECID=298959 STAMP=1006177614
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165260.280.1006177613 thread=2 sequence=165260
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165261.281.1006177615 thread=2 sequence=165261
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165260.280.1006177613 RECID=298957 STAMP=1006177613
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165261.281.1006177615 RECID=298960 STAMP=1006177615
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165262.279.1006177611 thread=2 sequence=165262
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165262.279.1006177611 RECID=298956 STAMP=1006177612
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165263.270.1006177605 thread=2 sequence=165263
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166787.269.1006177605 RECID=298947 STAMP=1006177606
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166788.365.1006177605 thread=1 sequence=166788
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165263.270.1006177605 RECID=298948 STAMP=1006177606
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165264.275.1006177609 thread=2 sequence=165264
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165264.275.1006177609 RECID=298953 STAMP=1006177610
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165265.276.1006177609 thread=2 sequence=165265
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165265.276.1006177609 RECID=298954 STAMP=1006177610
      channel CH1: starting archived log restore to default destination
      channel CH1: restoring archived log
      archived log thread=2 sequence=165266
      channel CH1: restoring archived log
      archived log thread=1 sequence=166789
      channel CH1: restoring archived log
      archived log thread=1 sequence=166790
      channel CH1: reading from backup piece ora11gpr_arch_u_4strvhbm_s_27804_p_1_t_1002423670
      channel CH1: piece handle=ora11gpr_arch_u_4strvhbm_s_27804_p_1_t_1002423670 tag=TAG20190309T030019
      channel CH1: restored backup piece 1
      channel CH1: restore complete, elapsed time: 00:00:36
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165266.275.1006177669 thread=2 sequence=165266
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166788.365.1006177605 RECID=298961 STAMP=1006177616
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166789.270.1006177669 thread=1 sequence=166789
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166789.270.1006177669 RECID=298962 STAMP=1006177669
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166790.276.1006177669 thread=1 sequence=166790
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166790.276.1006177669 RECID=298964 STAMP=1006177670
      channel CH1: starting archived log restore to default destination
      channel CH1: restoring archived log
      archived log thread=1 sequence=166791
      channel CH1: restoring archived log
      archived log thread=2 sequence=165267
      channel CH1: restoring archived log
      archived log thread=1 sequence=166792
      channel CH1: restoring archived log
      archived log thread=2 sequence=165268
      channel CH1: restoring archived log
      archived log thread=2 sequence=165269
      channel CH1: restoring archived log
      archived log thread=2 sequence=165270
      channel CH1: restoring archived log
      archived log thread=1 sequence=166793
      channel CH1: restoring archived log
      archived log thread=1 sequence=166794
      channel CH1: reading from backup piece ora11gpr_arch_u_4utrvob3_s_27806_p_1_t_1002430819
      channel CH1: piece handle=ora11gpr_arch_u_4utrvob3_s_27806_p_1_t_1002430819 tag=TAG20190309T050019
      channel CH1: restored backup piece 1
      channel CH1: restore complete, elapsed time: 00:00:45
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166791.281.1006177705 thread=1 sequence=166791
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165266.275.1006177669 RECID=298963 STAMP=1006177670
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165267.365.1006177705 thread=2 sequence=165267
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166791.281.1006177705 RECID=298965 STAMP=1006177705
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166792.270.1006177705 thread=1 sequence=166792
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165267.365.1006177705 RECID=298970 STAMP=1006177709
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165268.279.1006177705 thread=2 sequence=165268
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165268.279.1006177705 RECID=298967 STAMP=1006177707
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165269.280.1006177705 thread=2 sequence=165269
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165269.280.1006177705 RECID=298966 STAMP=1006177706
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165270.269.1006177705 thread=2 sequence=165270
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166792.270.1006177705 RECID=298971 STAMP=1006177710
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166793.278.1006177707 thread=1 sequence=166793
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166793.278.1006177707 RECID=298969 STAMP=1006177708
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166794.276.1006177705 thread=1 sequence=166794
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_1_seq_166794.276.1006177705 RECID=298972 STAMP=1006177714
      channel default: deleting archived log(s)
      archived log file name=+DATA/ora11gpr/archivelog/2019_04_21/thread_2_seq_165270.269.1006177705 RECID=298968 STAMP=1006177707
      media recovery complete, elapsed time: 00:00:13
      Finished recover at 21-APR-19
      
      
      released channel: CH1
      
      
      released channel: CH2
      
      
      released channel: CH3
      
      
      released channel: CH4
      
      
      RMAN> 
      RMAN> 
      

       

      The output of backup (from where we are trying to restore/recover)

       

       

       

      Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr 15 01:00:05 2019
      
      
      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
      
      
      using target database control file instead of recovery catalog
      
      
      RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 
      allocated channel: ch00
      channel ch00: SID=245 instance=ora11gpr1 device type=SBT_TAPE
      
      
      sent command to channel: ch00
      
      
      Starting backup at 2019-04-15:01:00:10
      current log archived
      
      
      backup will be obsolete on date 2019-07-14:01:00:22
      archived logs required to recover from this backup will be backed up
      backup will be obsolete on date 2019-07-14:01:00:22
      archived logs required to recover from this backup will be backed up
      backup will be obsolete on date 2019-07-14:01:00:22
      archived logs required to recover from this backup will be backed up
      channel ch00: starting incremental level 0 datafile backup set
      channel ch00: specifying datafile(s) in backup set
      input datafile file number=00007 name=+DG_DATA/ora11gpr/datafile/ll.341.890487447
      input datafile file number=00008 name=+DG_DATA/ora11gpr/datafile/ll.342.890487455
      input datafile file number=00009 name=+DG_DATA/ora11gpr/datafile/ll.343.890487463
      input datafile file number=00006 name=+DG_DATA/ora11gpr/datafile/int.339.897994789
      input datafile file number=00018 name=+DG_DATA/ora11gpr/datafile/ora_ts.260.985940497
      input datafile file number=00013 name=+DG_DATA/ora11gpr/datafile/ora_ts.257.970477399
      input datafile file number=00002 name=+DG_DATA/ora11gpr/datafile/sysaux.314.885830093
      input datafile file number=00003 name=+DG_DATA/ora11gpr/datafile/ll.283.931172447
      input datafile file number=00005 name=+DG_DATA/ora11gpr/datafile/ll.318.931172461
      input datafile file number=00019 name=+DG_DATA/ora11gpr/datafile/int.360.988186877
      input datafile file number=00014 name=+DG_DATA/ora11gpr/datafile/ora_ts.256.970477409
      input datafile file number=00010 name=+DG_DATA/ora11gpr/datafile/undotbs1_new.345.918823215
      input datafile file number=00011 name=+DG_DATA/ora11gpr/datafile/undotbs2_new.346.918823281
      input datafile file number=00001 name=+DG_DATA/ora11gpr/datafile/system.303.885830093
      input datafile file number=00020 name=+DG_DATA/ora11gpr/datafile/ll.358.988186909
      input datafile file number=00016 name=+DG_DATA/ora11gpr/datafile/ora_ts.258.985788479
      input datafile file number=00017 name=+DG_DATA/ora11gpr/datafile/ora_ts.259.985788625
      input datafile file number=00021 name=+DG_DATA/ora11gpr/datafile/ora_ts.261.988186939
      input datafile file number=00012 name=+DG_DATA/ora11gpr/datafile/t1.315.937131005
      input datafile file number=00015 name=+DG_DATA/ora11gpr/datafile/report_n1.359.1000555565
      input datafile file number=00022 name=+DG_DATA/ora11gpr/datafile/report_n2.357.991491379
      input datafile file number=00004 name=+DG_DATA/ora11gpr/datafile/users.316.885830093
      channel ch00: starting piece 1 at 2019-04-15:01:00:22
      channel ch00: finished piece 1 at 2019-04-15:01:11:27
      piece handle=ora11gpr_bk_u_3ftv0s56_s_28783_p_1_t_1005613222 tag=TAG20190415T010011 comment=API Version 2.0,MMS Version 5.0.0.0
      channel ch00: backup set complete, elapsed time: 00:11:05
      channel ch00: starting incremental level 0 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: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
      channel ch00: starting incremental level 0 datafile backup set
      channel ch00: specifying datafile(s) in backup set
      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
      channel ch00: backup set complete, elapsed time: 00:00:25
      
      
      backup will be obsolete on date 2019-07-14:01:12:20
      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 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
      channel ch00: backup set complete, elapsed time: 00:00:25
      
      
      current log archived
      backup will be obsolete on date 2019-07-14:01:12:58
      archived logs required to recover from this backup will be backed up
      channel ch00: starting archived log backup set
      channel ch00: specifying archived log(s) in backup set
      input archived log thread=2 sequence=166795 RECID=298946 STAMP=1005613214
      input archived log thread=1 sequence=168365 RECID=298949 STAMP=1005613968
      input archived log thread=2 sequence=166796 RECID=298948 STAMP=1005613968
      channel ch00: starting piece 1 at 2019-04-15:01:12:58
      channel ch00: finished piece 1 at 2019-04-15:01:13:23
      piece handle=ora11gpr_bk_u_3jtv0ssq_s_28787_p_1_t_1005613978 tag=TAG20190415T010011 comment=API Version 2.0,MMS Version 5.0.0.0
      channel ch00: backup set complete, elapsed time: 00:00:25
      
      
      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
      
      
      released channel: ch00
      
      
      RMAN> 
      
      
      Recovery Manager complete.
      
        • 1. Re: RMAN Backup Consistency with KEEP UNTIL
          Dude!

          There is no such thing like a self-contained backup. A RMAN backup is either consistent or inconsistent and a recovery is either complete or incomplete. A consistent backup that does not require recovery using redo or archivelogs can only be accomplished during a backup while database that was shutdown properly and during mount mode. Any online backup is inconsistent.

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

            Thank you for your reply. As per https://docs.oracle.com/html/E10643_07/rcmsubcl011.htm:

             

            (KEEP option) Specifies the backup as an archival backup, which is a self-contained backup that is exempt from the configured retention policy.

            An archival backup is self-contained because is contains all files necessary to restore the backup and recover it to a consistent state. If the database is open during the backup, then RMAN automatically generates and backs up the archived redo log files needed to make the database backup consistent".

             

            It is really confusing.

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

              The KEEP option will override any default retention you have so that it will not be considered obsolete (other than when the KEEP time is up).

              It retains only the required archived redo logs necessary for you to recover that backup and a backup of the control file and spfile. That could be referred to as a "self-contained backup" because it has all the necessary parts to recover that backup.

               

              You never showed us the commands you used for recovery, so I'm guessing that you recovered "beyond the KEEP"  - and of course that would require RMAN to recover the data files using more archived redo logs than "keep backup" you had.

               

              AJ

              • 4. Re: RMAN Backup Consistency with KEEP UNTIL
                EdStevens

                Your understanding of the KEEP option is incorrect, as explained by AJ.  I will admit that the doc's use of the term 'self-contained' is misleading.

                 

                If you are trying to use the KEEP option just for your normal retention rules, I'd suggest you drop that idea and instead "CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;" (Or however many days you seems appropriate).  Then, in your backup scripts include the line 'delete noprompt obsolete'.

                 

                The only time I use the KEEP option is when I am taking a 'special' backup outside of my normal backup routine.  And when I do that, I also put the database in MOUNT mode, that the backup is truly consistent and self-contained.

                1 person found this helpful
                • 5. Re: RMAN Backup Consistency with KEEP UNTIL
                  Dude!

                  I was thinking about transportable tablespace when reading about self-contained, but you're right, the Oracle documentation consideres RMAN backups with the keep option self-contained archival backups.

                   

                  However, if you read carefully, it outlines:

                   

                  An archival backup is self-contained because is contains all files necessary to restore the backup and recover it to a consistent state.

                   

                  Recover to a consistent state however is not the same as the backup being consistent. A database of course must be consistent, otherwise it cannot be opened.

                   

                  See https://docs.oracle.com/cd/E25178_01/backup.1111/e10642/rcmcncpt.htm#BABIHBBE

                   

                  A consistent backup occurs when the database is in a consistent state. A database is in a consistent state after being shut down with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands. A consistent shutdown guarantees that all redo has been applied to the datafiles. If you mount the database and make a backup at this point, then you can restore the database backup later and open it without performing media recovery.

                   

                  This what I explained.

                   

                  Now either my understanding (that backups created using KEEP UNTIL clause are consistent and self-contained and contain all archive logs to make this backup consistent, when restored) is wrong or I might be missing some steps while finding log sequence/thread.

                  So perhaps your confusion is about "consistent backup" versus self-contained to recover to a consistent state.

                   

                  The KEEP option does not stand for consistent backups - the key for a consistent backup is that the database must be shutdown properly prior to the backup. RMAN with the KEEP option will however keep the data that will be required to restore and recover the database to a consistent state. What data RMAN finally uses or requires to accomplish the task is up to RMAN's own built-in logic. If you delete backups or archivloges outside RMAN, for exmple using the OS, you rrestore and recovery options can break.

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

                    Thank you for your feedback.

                     

                    You are right, I might be doing something wrong while concluding the log sequence/thread. Below is the script that I used:

                    RUN
                    {
                    set until sequence 166796 thread 1;
                    ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';
                    SEND 'NB_ORA_CLIENT=<VIP-Name>';
                    ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE';
                    SEND 'NB_ORA_CLIENT=<VIP-Name>';
                    ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE';
                    SEND 'NB_ORA_CLIENT=<VIP-Name>';
                    ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE';
                    SEND 'NB_ORA_CLIENT=<VIP-Name>';
                    restore database;
                    switch datafile all;
                    switch tempfile all;
                    recover database;
                    release channel CH1;
                    release channel CH2;
                    release channel CH3;
                    release channel CH4;
                    }
                    
                    • 7. Re: RMAN Backup Consistency with KEEP UNTIL
                      AJ

                      Riaz. skrev:

                       

                      Thank you for your feedback.

                       

                      You are right, I might be doing something wrong while concluding the log sequence/thread. Below is the script that I used:

                      1. RUN
                      2. {
                      3. setuntilsequence166796thread1;
                      4. ALLOCATECHANNELCH1TYPE'SBT_TAPE';
                      5. SEND'NB_ORA_CLIENT=<VIP-Name>';
                      6. ALLOCATECHANNELCH2TYPE'SBT_TAPE';
                      7. SEND'NB_ORA_CLIENT=<VIP-Name>';
                      8. ALLOCATECHANNELCH3TYPE'SBT_TAPE';
                      9. SEND'NB_ORA_CLIENT=<VIP-Name>';
                      10. ALLOCATECHANNELCH4TYPE'SBT_TAPE';
                      11. SEND'NB_ORA_CLIENT=<VIP-Name>';
                      12. restoredatabase;
                      13. switchdatafileall;
                      14. switchtempfileall;
                      15. recoverdatabase;
                      16. releasechannelCH1;
                      17. releasechannelCH2;
                      18. releasechannelCH3;
                      19. releasechannelCH4;
                      20. }

                      Yep.. 166796 from thread 1 is older than the KEEP backup it self, so the database files that was restored was not even from that backup.

                       

                      AJ

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

                        So how can we find the correct log sequence/thread?

                         

                        Regards,

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

                          Riaz. skrev:

                           

                          So how can we find the correct log sequence/thread?

                           

                          Regards,

                          Several ways, but since you have the backup log, you could look at which archived logs that were backed up after the database files.

                          Those are the ones needed to make the keep backup consistent. Since this is RAC (and you're using "set until sequence") you would use the

                          sequence from the thread having the lowest next_change (or else RMAN would have to look for a subsequent archived redo log beyond the highest next change).

                          So basically:

                           

                           

                          select sequence#, thread#, next_change#

                          from v$archived_log

                          where sequence# = 168365 and thread# = 1

                          union

                          select sequence#, thread#, next_change#

                          from v$archived_log

                          where sequence# = 166796 and thread# = 2

                           

                           

                          e.g : if 166796 from thread2 has the lowest next scn.

                          set until sequence 166797 thread 2;

                          restore..

                          recover..

                           

                          AJ

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

                            Wonderful; I think we are very close to the solution. Here are the steps I followed:

                             

                            RMAN Prompt:

                            set DB ID

                            startup force nomount

                            restore control file

                            execute commands to find out the log sequence/thread:

                            SQL> select max(sequence#) from v$archived_log L, v$database D
                              2  where L.resetlogs_change# = D.resetlogs_change# and
                              3  thread#=1
                              4  /
                            
                            
                            MAX(SEQUENCE#)
                            --------------
                                    168364
                            
                            
                            SQL>
                            SQL> select max(sequence#) from v$archived_log L, v$database D
                              2  where L.resetlogs_change# = D.resetlogs_change# and
                              3  thread#=2
                              4  /
                            
                            
                            MAX(SEQUENCE#)
                            --------------
                                    166795
                            
                            SQL> ed
                            Wrote file afiedt.buf
                            
                            
                              1  select sequence#, thread#, next_change#
                              2  from v$archived_log
                              3  where sequence# = 168364 and thread# = 1
                              4  union
                              5  select sequence#, thread#, next_change#
                              6  from v$archived_log
                              7* where sequence# = 166795 and thread# = 2
                            
                            SQL> /
                            
                            
                                  SEQUENCE#        THREAD#    NEXT_CHANGE#
                            --------------- --------------- ---------------
                                    166795              2  10454408556774
                                    168364              1  10454408556762
                            

                             

                            So we need to use "set until sequence 168365 thread 1" before initiating restore/recover?

                             

                            Regards,

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

                              Well, no, you are picking the latest archived redo log available with the two selects you are running at first. That would recover the database to almost current time.

                              My understanding was that you would want to recover using the archived redo logs that were backed up with the keep backup?

                               

                               

                              AJ

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

                                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.

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

                                  Ok, so if those archived redo logs belongs to the keep backup, then yes, that would be the way to use the set until.

                                   

                                  AJ

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

                                    Thank you; I will give it a try and will let you know the result.

                                    1 2 Previous Next