1 2 Previous Next 23 Replies Latest reply on Jul 5, 2018 2:56 PM by Peter_L_

    Backupsets of archived log weren't obsoleted and deleted

    Peter_L_

      Hi,

      Since applying 12.2.0.1.180417 archivelog backupsets were not obsoleted and due this they were not deleted by “delete obsolete”.

      Patch was applied on 3th May.

      If you “list backup by file;” you see many archivelog backups, which should be obsolete and deleted (it’s June and we have a recover window of 5 days) and 2 database backup files from this 3th May.

      Do you have an idea? I’ve already opened a SR, but maybe there is a faster solution.

      Because of this behaivior is occured on all our RACs which were patched in May/18, I think it’s a bug.

       

      Cheers, Peter

        • 1. Re: Backupsets of archived log weren't obsoleted and deleted
          Ian Baugaard

          Hi Peter,

           

          Out of curiosity, are you using a recovery catalog in this environment? Has the recovery catalog been patched as well and upgraded using the UPGRADE CATALOG command?

           

          Regards

           

          Ian

          • 2. Re: Backupsets of archived log weren't obsoleted and deleted
            Joerg.Sobottka

            Can you make a show all; at rman and post the output?

            Does Report obsolete shows entries? So are they not deleted or are they not set obsolete?

            • 3. Re: Backupsets of archived log weren't obsoleted and deleted
              Peter_L_

              Hi Ian,

              the RMAN was patched with RU Apr. 18 and catalog  was upgraded, too.

              Cheers Peter

              • 4. Re: Backupsets of archived log weren't obsoleted and deleted
                Peter_L_

                CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;

                CONFIGURE BACKUP OPTIMIZATION ON;

                CONFIGURE DEFAULT DEVICE TYPE TO DISK;

                CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/shares/backup/DB*****/AB_%F_DB*****.CTL';

                CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

                CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

                CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

                CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/mnt/shares/backup/DB*****/BS_DB*****_%d_%U' MAXPIECESIZE 4 G;

                CONFIGURE MAXSETSIZE TO UNLIMITED;

                CONFIGURE ENCRYPTION FOR DATABASE OFF;

                CONFIGURE ENCRYPTION ALGORITHM 'AES128';

                CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

                CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

                CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

                CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/mnt/shares/backup/DB*****/SPFILES_PFILES_CONTROLFILE/SS_DB*****.CTL';

                 

                Report obsolete shows entries, but NO backupsets with archived redo logs. Since patching archived redo log backups were not removed..

                 

                Cheers Peter

                • 5. Re: Backupsets of archived log weren't obsoleted and deleted
                  Ian Baugaard

                  Hi Peter,

                   

                  You have recovery window set, could you please summarise your backup strategy and also provide an indication of the status of the backup completion over the past week?

                   

                  COL input_bytes_per_sec_display          FOR a10          HEAD THRUPUT_IN
                  COL output_bytes_per_sec_display          FOR a11          HEAD THRUPUT_OUT
                  COL time_taken_display                    FOR a10          HEAD TIME_TAKEN
                  COL status                              FOR a20
                  SELECT
                       start_time,
                       end_time,
                       status,
                       input_type,
                       LPAD( input_bytes_per_sec_display, 10, ' ' ) input_bytes_per_sec_display,
                       LPAD( output_bytes_per_sec_display, 11, ' ' ) output_bytes_per_sec_display,
                       LPAD( time_taken_display, 10, ' ' ) time_taken_display
                  FROM v$rman_backup_job_details
                  WHERE end_time >= TRUNC( sysdate - 7 )
                  ORDER BY start_time
                  /

                   

                  Regards

                   

                  Ian

                  • 6. Re: Backupsets of archived log weren't obsoleted and deleted
                    Peter_L_
                    START_TI END_TIME STATUS           INPUT_TYPETHRUPUT_IN THRUPUT_OUT TIME_TAKEN

                    -------- -------- -------------------- ------------- ---------- ----------- ----------

                    11.06.18 11.06.18 COMPLETED        DB INCR      228.06M  23.92M   00:07:19
                    12.06.18 12.06.18 COMPLETED        DB INCR      209.64M  24.09M   00:08:14
                    13.06.18 13.06.18 COMPLETED        DB INCR      184.05M  27.32M   00:07:02
                    14.06.18 14.06.18 COMPLETED        DB INCR      166.09M  27.34M   00:06:37
                    15.06.18 15.06.18 COMPLETED        DB INCR      206.68M   9.44M   00:04:40
                    15.06.18 15.06.18 COMPLETED        DB INCR      169.80M 125.80M   00:06:24
                    16.06.18 16.06.18 COMPLETED        DB INCR      171.64M  30.62M   00:06:33
                    17.06.18 17.06.18 COMPLETED        DB INCR      175.03M  32.39M   00:06:41
                    • 7. Re: Backupsets of archived log weren't obsoleted and deleted
                      Joerg.Sobottka

                      You only have incremental backups and you have backup optimization on.

                      So to allow restore and recover the database needs to keep the archive logs, I think.

                      First step should be to set backup optimization off as this often has ugly side effects

                      1 person found this helpful
                      • 8. Re: Backupsets of archived log weren't obsoleted and deleted
                        Ian Baugaard

                        Hi Joerg,

                         

                        I think the second backup of the 15th is a full (An incremental level 0). You could discern between a full and incremental much easier in 10g but it was subsequently changed for some reason.

                         

                        @Wicaz, please confirm whether or not the second backup on the 15th is indeed a full?

                         

                        Bear in mind that a recovery window of 5 days, as of today, will need to go back to the full completed prior to the 15th (Assuming that it is) so that you can roll-forward to the sysdate-5 mark (If you guys can make sense of what I'm trying to say). So assuming that your last full backup was taken on 08/06/2018, any backup older than that will be eligible for deletion.

                         

                        Final thought, could you also please post your backup scripts?

                         

                        Ian

                        • 9. Re: Backupsets of archived log weren't obsoleted and deleted
                          Joerg.Sobottka

                          Ian Baugaard wrote:

                           

                          Hi Joerg,

                           

                          I think the second backup of the 15th is a full (An incremental level 0). You could discern between a full and incremental much easier in 10g but it was subsequently changed for some reason.

                           

                          @Wicaz, please confirm whether or not the second backup on the 15th is indeed a full?

                           

                          Bear in mind that a recovery window of 5 days, as of today, will need to go back to the full completed prior to the 15th (Assuming that it is) so that you can roll-forward to the sysdate-5 mark (If you guys can make sense of what I'm trying to say). So assuming that your last full backup was taken on 08/06/2018, any backup older than that will be eligible for deletion.

                           

                          Final thought, could you also please post your backup scripts?

                           

                          Ian

                          Yes, it could be, because of the size, BUT as there is BACKUP OPTIMIZATION on, it could also be something else...

                          So this is not enough - if there is a recovery window of 5 days and the last Level 0 backup is from 07. or 08. of June, the database needs the archivelogs since then as you've stated right.

                           

                          By the way - this select may help to identify Level 0/1 backups:

                           

                          SELECT TO_CHAR (completion_time, 'YYYY-MON-DD') completion_time

                                  , TYPE

                                  , ROUND (SUM (bytes) / 1048576)          MB

                                  , ROUND (SUM (elapsed_seconds) / 60)     BCPTIME

                               FROM (SELECT CASE

                                               WHEN s.backup_type = 'L' THEN 'Archive Log'

                          --                    WHEN s.controlfile_included = 'YES' THEN 'Control File'

                                            WHEN s.backup_type = 'D' THEN 'Full (Level ' || NVL (s.incremental_level, 0) || ')'

                                               WHEN s.backup_type = 'I' THEN 'Incremental (Level ' || s.incremental_level || ')'

                                               ELSE s.backup_type

                                            END

                                               TYPE

                                          , TRUNC (s.completion_time) completion_time

                                          , p.tag

                                          , p.bytes

                                          , s.elapsed_seconds

                                       FROM v$backup_piece p, v$backup_set s

                                      WHERE status = 'A' AND p.recid = s.recid

                                     UNION ALL

                                     SELECT 'Datafile Copy' TYPE, TRUNC (completion_time), tag, output_bytes, 0 elapsed_seconds FROM v$backup_copy_details)

                          GROUP BY tag, TO_CHAR (completion_time, 'YYYY-MON-DD'), TYPE

                          ORDER BY 1 ASC, 2, 3;

                          • 10. Re: Backupsets of archived log weren't obsoleted and deleted
                            Peter_L_

                            Hi Joerg,

                            great script, thank you for sharing!

                            Our backup strategie is:

                            Every friday "incremental level 0 database plus archivelog"

                            other days "incremental level 1 database plus archivelog". The backup is managed and reported by cloud control.

                            As patching with RU 12.2.0.1.180417 all works fine. On regulary intervall I perform an complete restore test on an auxiliary instance (last week for instance).

                             

                            Here is the script output:

                             

                            COMPLETION_TIME TYPE                                                           MBBCPTIME

                            -------------------- ----------------------------------------------------------- ---------- ----------

                            2018-JUN-08     Archive Log                                                  7217     1
                            2018-JUN-09     Archive Log                                                  7336     1
                            2018-JUN-10     Archive Log                                                  4687     1
                            2018-JUN-12     Control File                                                 7525     0
                            2018-JUN-13     Control File                                                 3254     0
                            2018-JUN-13     Incemental (Level 1)                                          434     0
                            2018-JUN-14     Control File                                                 3722     0
                            2018-JUN-14     Incemental (Level 1)                                         4396     0
                            2018-JUN-15     Archive Log                                                    63     1
                            2018-JUN-15     Control File                                                  357     0
                            2018-JUN-15     Control File                                                  681     0

                             

                            COMPLETION_TIME TYPE                                                           MBBCPTIME

                            -------------------- ----------------------------------------------------------- ---------- ----------

                            2018-JUN-15     Incemental (Level 0)                                         3086     2
                            2018-JUN-15     Incemental (Level 0)                                        12407     5
                            2018-JUN-15     Incemental (Level 1)                                         3402     0
                            2018-JUN-15     Incemental (Level 1)                                        34863     6
                            2018-JUN-16     Archive Log                                                   250     1
                            2018-JUN-16     Control File                                                  141     0
                            2018-JUN-16     Incemental (Level 1)                                         3147     2
                            2018-JUN-16     Incemental (Level 1)                                         8100     5
                            2018-JUN-17     Archive Log                                                   195     2
                            2018-JUN-17     Control File                                                  644     0
                            2018-JUN-17     Incemental (Level 1)                                         2203     1

                             

                            COMPLETION_TIME TYPE                                                           MBBCPTIME

                            -------------------- ----------------------------------------------------------- ---------- ----------

                            2018-JUN-17     Incemental (Level 1)                                         8840     5

                             

                            Regarding "BACKUP OPTIMIZATION on" what kind of trouble makes this setting?

                             

                            Thank you, Peter

                            • 11. Re: Backupsets of archived log weren't obsoleted and deleted
                              Peter_L_

                              Hi Ian,

                              early I wrote my own scripts.

                              Now with 12.2. I let manage the backups by Em Cloud Control:

                               

                              Here are the scripts geneterated by EMCC:

                              -- Full Backup

                              backup incremental level 0 cumulative device type disk tag '%TAG' database;

                              backup device type disk tag '%TAG' archivelog all not backed up delete all input;

                              run {

                              allocate channel oem_backup_disk1 type disk format '/mnt/shares/backup/DBTEST/%d_%U' maxpiecesize 1000 G;

                              backup tag '%TAG' current controlfile;

                              release channel oem_backup_disk1;

                              }

                              allocate channel for maintenance type disk;

                              delete noprompt obsolete device type disk;

                              release channel;

                              ";

                               

                              -- Diff Backup

                              backup incremental level 1 device type disk tag '%TAG' database;

                              backup device type disk tag '%TAG' archivelog all not backed up delete all input;

                              run {

                              allocate channel oem_backup_disk1 type disk format '/mnt/shares/backup/DBTEST/%d_%U' maxpiecesize 1000 G;

                              backup tag '%TAG' current controlfile;

                              release channel oem_backup_disk1;

                              }

                              allocate channel for maintenance type disk;

                              delete noprompt obsolete device type disk;

                              release channel;

                              ";

                               

                              Peter

                              • 12. Re: Backupsets of archived log weren't obsoleted and deleted
                                Peter_L_

                                Hi Ian,

                                I currently view known bugs patched by last RU. There are two bugs fixed related to my issue( maybe there is an error in fix, or cause another behaviour)

                                25943271     rman report obsolete does not report controlfile backup as obsolete

                                24759556     RMAN-08137 unable to delete archives with deletion policy SHIPPED TO ALL STANDBY

                                 

                                Cheers Peter

                                • 13. Re: Backupsets of archived log weren't obsoleted and deleted
                                  Joerg.Sobottka

                                  Wicaz wrote:

                                   

                                  Hi Joerg,

                                  great script, thank you for sharing!

                                  Our backup strategie is:

                                  Every friday "incremental level 0 database plus archivelog"

                                  other days "incremental level 1 database plus archivelog". The backup is managed and reported by cloud control.

                                  As patching with RU 12.2.0.1.180417 all works fine. On regulary intervall I perform an complete restore test on an auxiliary instance (last week for instance).

                                   

                                  Here is the script output:

                                   

                                  COMPLETION_TIME TYPE MBBCPTIME

                                  -------------------- ----------------------------------------------------------- ---------- ----------

                                  2018-JUN-08 Archive Log 7217 1
                                  2018-JUN-09 Archive Log 7336 1
                                  2018-JUN-10 Archive Log 4687 1
                                  2018-JUN-12 Control File 7525 0
                                  2018-JUN-13 Control File 3254 0
                                  2018-JUN-13 Incemental (Level 1) 434 0
                                  2018-JUN-14 Control File 3722 0
                                  2018-JUN-14 Incemental (Level 1) 4396 0
                                  2018-JUN-15 Archive Log 63 1
                                  2018-JUN-15 Control File 357 0
                                  2018-JUN-15 Control File 681 0

                                   

                                  COMPLETION_TIME TYPE MBBCPTIME

                                  -------------------- ----------------------------------------------------------- ---------- ----------

                                  2018-JUN-15 Incemental (Level 0) 3086 2
                                  2018-JUN-15 Incemental (Level 0) 12407 5
                                  2018-JUN-15 Incemental (Level 1) 3402 0
                                  2018-JUN-15 Incemental (Level 1) 34863 6
                                  2018-JUN-16 Archive Log 250 1
                                  2018-JUN-16 Control File 141 0
                                  2018-JUN-16 Incemental (Level 1) 3147 2
                                  2018-JUN-16 Incemental (Level 1) 8100 5
                                  2018-JUN-17 Archive Log 195 2
                                  2018-JUN-17 Control File 644 0
                                  2018-JUN-17 Incemental (Level 1) 2203 1

                                   

                                  COMPLETION_TIME TYPE MBBCPTIME

                                  -------------------- ----------------------------------------------------------- ---------- ----------

                                  2018-JUN-17 Incemental (Level 1) 8840 5

                                   

                                  Regarding "BACKUP OPTIMIZATION on" what kind of trouble makes this setting?

                                   

                                  Thank you, Peter

                                  I think the main problem really is that there isn't a level 0 backup BEFORE June 15th. So to keep a little bit trust in your Recovery Window of 5 Days, Oracle thinks there might be a level 0 or full backup somewhere else which isn't cataloged and therefore keeps the archive logs. I think this will change as soon as you have another level 0 backup done. Then everything older will be reported obsolete and deleted.

                                  As you do have an external RMAN catalog and the database seems to be small, always set CONTROL_FILE_RECORD_KEEP_TIME at least to your Recovery Window * 2, in addition for your database, as you make the level 0 backups only once a week, set it to at least 15 days (2* Incremental Level 0 backups + 1 day).

                                   

                                  Backup optimization on can e.g. skip at a full/Level 0 backup some datafiles if the database sees, there is already one existing in another backup somewhere else and has not changed much - this can lead to Level 0 backups laying around for 3 or 4 weeks even if they would be obsolete after one week...

                                  • 14. Re: Backupsets of archived log weren't obsoleted and deleted
                                    Joerg.Sobottka

                                    Wicaz wrote:

                                     

                                    Hi Ian,

                                    I currently view known bugs patched by last RU. There are two bugs fixed related to my issue( maybe there is an error in fix, or cause another behaviour)

                                    25943271 rman report obsolete does not report controlfile backup as obsolete

                                    24759556 RMAN-08137 unable to delete archives with deletion policy SHIPPED TO ALL STANDBY

                                     

                                    Cheers Peter

                                    Hi Peter,

                                     

                                    I think it's probably more a configuration issue than a bug...

                                    1 2 Previous Next