5 Replies Latest reply on Apr 1, 2019 12:30 AM by DuncanG

    Archived Log created during full backup deleted before completion

    Duncan Gardner

      We're running full weekly backups and daily increments. Archive Log files backup up every thirty minutes. Oracle RAC. DB is 11.2.0.4. Linux.

      During the weekly backup of the biggest database on the box, get ORA-19588. It runs for a bit over 24 hours.

      input archived log thread=1 sequence=82550 RECID=227416 STAMP=993778235

      RMAN-00571: ===========================================================

      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

      RMAN-00571: ===========================================================

      RMAN-03002: failure of backup command at 12/02/2018 02:17:51

      ORA-19588: archived log RECID 227417 STAMP 993778236 is no longer valid

       

       

      Recovery Manager complete.

      I can see another achived log with same STAMP, but different RECID being backed up by archivelog backup. But I imagine that's unrelated

      input archived log thread=1 sequence=82550 RECID=227416 STAMP=993778235

      input archived log thread=2 sequence=85294 RECID=227418 STAMP=993778236

      channel ORA_DISK_1: starting piece 1 at 02-Dec-2018 01:31:31

      channel ORA_DISK_1: finished piece 1 at 02-Dec-2018 01:33:16

      piece handle=*********/2018_12_02/o1_mf_annnn_ARCHIVELOG_BACKUP_g056t7h1_.bkp tag=ARCHIVELOG_BACKUP comment=NONE

      channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45

       

      Finished backup at 02-Dec-2018 01:33:16

      Looking at V$ARCHIVED_LOG, the file RMAN complains about is marked as deleted

      select recid, stamp, name, dest_id, first_change#, deleted, status, completion_time, backup_count, is_recovery_dest_file

      from V$ARCHIVED_LOG

      where stamp = 993778236;

       

       

      RECID

      STAMP

      NAME

      DEST_ID

      FIRST_CHANGE#

      DELETED

      STATUS

      COMPLETION_TIME

      BACKUP_COUNT

      IS_RECOVERY_DEST_FILE

      227417

      993778236

      2

      40655126962

      YES

      D

      02/Dec/2018 01:30:36

      0

      NO

      227418

      993778236

      +FRA/*********/2018_12_02/thread_2_seq_85294.3958.993778235

      1

      40655126966

      NO

      A

      02/Dec/2018 01:30:36

      1

      YES

      227419

      993778236

      2

      40655126966

      YES

      D

      02/Dec/2018 01:30:36

      0

      NO

      From posts on similar issues, CROSSCHECK is recommended - we already have CROSSCHECK COPY. However (a) I wouldn't want to delete the record of a file I might need, and (b) the file is created AFTER the back starts on 1st December anyway.

      It seems likely that the archivelog backup is interfering with the weekly backup, though I can't see why - it does no delete.

       

      Weekly script:

      set echo on

      CONFIGURE CONTROLFILE AUTOBACKUP ON;

      CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

      #

      # Run a weekly full database backup with a keep clause to create a self contained archival backupset and exclude it from the normal backup retention policy.

      #

      crosscheck copy;

      backup to destination '*************weekly' tag 'WEEKLY_FULL_FATHER' database keep until time "sysdate + interval '1' month";

      catalog start with '*****************weekly';

      delete noprompt backupset tag WEEKLY_TAPE;

      backup force backupset completed after "SYSDATE-2" from tag 'WEEKLY_FULL_FATHER'  tag WEEKLY_TAPE FORMAT '/dr4100/tape/%d_%U';

      #change backup tag WEEKLY_TAPE keep until time "sysdate+7";

      delete noprompt obsolete;

       

      Archivelog script (every 30 mins)

      CONFIGURE CONTROLFILE AUTOBACKUP OFF;

      run{

      backup tag='ARCHIVELOG_BACKUP' to destination '*****' archivelog all;

      backup to destination '**********' current controlfile tag='CONTROL_FILE' spfile tag='SPFILE';

      }

       

      CONFIGURE CONTROLFILE AUTOBACKUP ON;

       

       

      RMAN settings

      RMAN> show all;

       

      using target database control file instead of recovery catalog

      RMAN configuration parameters for database with db_unique_name ****are:

      CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

      CONFIGURE BACKUP OPTIMIZATION ON;

      CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

      CONFIGURE CONTROLFILE AUTOBACKUP ON;

      CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

      CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

      CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

      CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

      CONFIGURE MAXSETSIZE TO UNLIMITED; # default

      CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

      CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

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

      CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

      CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/********.f';

       

      RMAN>

       

       

       

       

       

      input archived log thread=1 sequence=82550 RECID=227416 STAMP=993778235

      RMAN-00571: ===========================================================

      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

      RMAN-00571: ===========================================================

      RMAN-03002: failure of backup command at 12/02/2018 02:17:51

      ORA-19588: archived log RECID 227417 STAMP 993778236 is no longer valid

       

      Recovery Manager complete.

        • 1. Re: Archived Log created during full backup deleted before completion
          Dude!

          Why such strange and compliated backup strategy? What probably happens here is that your backup scripts are overlapping or not completing in due time as expected. While one of the scripts is still performing the backup, another one is running evaluating information obsolete according to your retention setting.

           

          Why the backup or archivelogs every 30 minutes - you must have a lot of duplicate backups and have a lot of unnecessary storage space and I/O processing.

           

          #
          Run a weekly full database backup with a keep clause to create a self
          contained archival backupset and exclude it from the normal backup
          retention policy.

          #

           

          Since your full  backup does not include necessary archivelogs you will not be able to use these "self-conteined" backups to restore and recover the database. There is no such thing like a self-contained RMAN backup. A backup is either consistent or inconsistent. If you backup the database while it is running then the backup is always inconsistent and you will require archivelogs to recover the database to a consistent state.

           

          Why not talking advantage of incremental level backups, inlcuding archivelogs, and let RMAN manage the retention policy? For example:

           

          Keep controlfile autobackup on and set the retention window to 30 days. Configure the database control_file_record_keep_time init parameter to 35 days. Also enable block change tracking (BCT) if you have enterprise version.

           

          The run the following weekly backup:

          > backup incremental level 0 database plus archivelog delete input;

           

          And during weekdays at night:

          > backup incremental level 1 database plus archivelog delete input;

           

          If you do not want to delete archivelogs you can omit "delete input" and also create additional archivelog backups between your incrementel level backup schedule, for example:

           

          > backup archivelog all not backed up;

           

           

           

           

           

           

           

          • 2. Re: Archived Log created during full backup deleted before completion
            top.gun

            "It seems likely that the archivelog backup is interfering with the weekly backup"

            Looks like the opposite.

            There is the potential for the weekly the interfere with the archivelog backup due to the "delete noprompt obsolete;".

             

            "From posts on similar issues, CROSSCHECK is recommended - we already have CROSSCHECK COPY."

            Actually it's not recommended. What the CROSSCHECK does is mask the fact that your backups are overlapping.

            One backup job deletes obsolete (eg archivelogs created before the latest database weekly backup), while the other is backing up the same files, and so there is a problem.

            Normally your backup job will fail - and that is the behaviour that you want because you need an indicator that signals that your backups are overlapping.

            By using the CROSSCHECK in your scripts, you are masking the overlap problem.

            • 3. Re: Archived Log created during full backup deleted before completion
              Duncan Gardner

              Thanks. This is only part of the backup strategy. There are daily incremental backups. And it's all copied to another machine running a warm backup.

              You've confirmed my thoughts about CROSSCHECK masking an underlying issue.

              But no explanation as to why the archived log has been deleted.

              Is it just the case that you should never have two rman operations against a DB at once? I assumed that was OK, so long as they didn't explicitly interfere.

              • 4. Re: Archived Log created during full backup deleted before completion
                Dude!

                You are evaluating archivelogs in one of the scripts against your RMAN retention policy of 7 days and then delete the data. The RMAN retention policy applies to backups and archivelogs unless you have a specific archivelog deletion policy. Since you do not have an archivelog deletion policy, it is possible that archivelogs are deleted in one of the scripts, while another is probably still running trying to backup the data.

                • 5. Re: Archived Log created during full backup deleted before completion
                  DuncanG

                  It turns out there was another unix script that deleted the archivelog copy, and RMAN wanted that file (although another copy was still available). Putting that on hold while the backup is running resolved the issue, though made it all more complex.