6 Replies Latest reply: Nov 18, 2012 7:29 PM by 585179 RSS

    Archived Redo Log Deletion Policy and Delete Input Clause

    779292
      Hi all,

      I have a quick question on archived redo log deletion policy. I've configured archived redo log deletion policy as given below (other relevant configurations related to this question are also given):
      CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
      CONFIGURE BACKUP OPTIMIZATION ON;
      CONFIGURE DEFAULT DEVICE TYPE TO DISK;
      CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
      I've created backups of the archived redo logs on the disk and none on the sbt. Therefore, when I issue the following command to delete all the archived redo log files, a warning is issued as below:
      RMAN> DELETE ARCHIVELOG ALL;
      
      released channel: ORA_DISK_1
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: SID=70 device type=DISK
      RMAN-08138: WARNING: archived log not deleted - must create more backups
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_412_8bgsnkw9_.arc thread=1 sequence=412
      RMAN-08138: WARNING: archived log not deleted - must create more backups
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_412_8bgsnkw9_.arc thread=1 sequence=412
      RMAN-08138: WARNING: archived log not deleted - must create more backups
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_412_8bgsnkw9_.arc thread=1 sequence=412
      RMAN-08138: WARNING: archived log not deleted - must create more backups
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_413_8bgsr5v1_.arc thread=1 sequence=413
      RMAN-08138: WARNING: archived log not deleted - must create more backups
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_413_8bgsr5v1_.arc thread=1 sequence=413
      RMAN-08138: WARNING: archived log not deleted - must create more backups
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_413_8bgsr5v1_.arc thread=1 sequence=413
      But, when I issue the following command to create another backup of all archived redo logs and delete all the inputs, all the archived redo log files are deleted from all the log archive destinations without any regard to the archived log deletion policy.
      RMAN> BACKUP AS BACKUPSET ARCHIVELOG ALL DELETE ALL INPUT;
      
      Starting backup at 2012-11-17 16:08:06
      current log archived
      using channel ORA_DISK_1
      skipping archived log file /u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_412_8bgsnkw9_.arc; already backed up 1 time(s)
      skipping archived log file /u04/ORACLE/database/archive2/arch_d9133af5_1_735651259_412.arc; already backed up 1 time(s)
      skipping archived log file /u03/ORACLE/database/archive1/arch_d9133af5_1_735651259_412.arc; already backed up 1 time(s)
      skipping archived log file /u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_413_8bgsr5v1_.arc; already backed up 1 time(s)
      skipping archived log file /u04/ORACLE/database/archive2/arch_d9133af5_1_735651259_413.arc; already backed up 1 time(s)
      skipping archived log file /u03/ORACLE/database/archive1/arch_d9133af5_1_735651259_413.arc; already backed up 1 time(s)
      channel ORA_DISK_1: starting archived log backup set
      channel ORA_DISK_1: specifying archived log(s) in backup set
      input archived log thread=1 sequence=414 RECID=1027 STAMP=799603689
      channel ORA_DISK_1: starting piece 1 at 2012-11-17 16:08:10
      channel ORA_DISK_1: finished piece 1 at 2012-11-17 16:08:11
      piece handle=/media/win/f/oracle_backups-1/menqhuva_1_1 tag=TAG20121117T160810 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
      channel ORA_DISK_1: deleting archived log(s)
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_414_8bgt8j36_.arc RECID=1027 STAMP=799603689
      archived log file name=/u04/ORACLE/database/archive2/arch_d9133af5_1_735651259_414.arc RECID=1026 STAMP=799603689
      archived log file name=/u03/ORACLE/database/archive1/arch_d9133af5_1_735651259_414.arc RECID=1025 STAMP=799603689
      channel ORA_DISK_1: deleting archived log(s)
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_412_8bgsnkw9_.arc RECID=1021 STAMP=799603050
      archived log file name=/u04/ORACLE/database/archive2/arch_d9133af5_1_735651259_412.arc RECID=1020 STAMP=799603050
      archived log file name=/u03/ORACLE/database/archive1/arch_d9133af5_1_735651259_412.arc RECID=1019 STAMP=799603050
      archived log file name=/u05/flash_recovery_area/DATABASE/archivelog/2012_11_17/o1_mf_1_413_8bgsr5v1_.arc RECID=1024 STAMP=799603166
      archived log file name=/u04/ORACLE/database/archive2/arch_d9133af5_1_735651259_413.arc RECID=1023 STAMP=799603166
      archived log file name=/u03/ORACLE/database/archive1/arch_d9133af5_1_735651259_413.arc RECID=1022 STAMP=799603166
      Finished backup at 2012-11-17 16:08:14
      RMAN> LIST COPY;
      
      specification does not match any datafile copy in the repository
      specification does not match any control file copy in the repository
      specification does not match any archived log in the repository
      According to the documentation, the DELETE INPUT clause should honour the archived redo log deletion policy.

      >
      As explained in "Configuring an Archived Redo Log Deletion Policy", the BACKUP ... DELETE INPUT and DELETE ARCHIVELOG commands obey the archived redo log deletion policy for logs in all archiving locations. For example, if you specify that logs should only be deleted when backed up at least twice to tape, then BACKUP ... DELETE honors this policy.
      >

      Can anyone explain the reason for the mentioned behaviour experienced by me please?

      I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production in a test environment. Not licensed.

      Thanks in advance!

      Edited by: Andrew Vimales on Nov 17, 2012 2:57 AM
        • 1. Re: Archived Redo Log Deletion Policy and Delete Input Clause
          Shivananda Rao
          Hello,

          Refer the MOS document *Archivelog Deletion Policy Applied On All Standby is Ignored [ID 1313893.1]*

          It seems to be a Bug with RMAN. Also, here is another thread Question about Archive Log Deletion policy which is very identical to the issue you are facing.

          Andrew Vimales     
               
          Handle:     Andrew Vimales
          Status Level:     Newbie
          Registered:     Jun 14, 2010
          Total Posts:     4
          Total Questions:     2 (2 unresolved)
          Please consider closing your questions if you feel that they have been answered by providing appropriate points. Please keep the forum clean !!
          • 2. Re: Archived Redo Log Deletion Policy and Delete Input Clause
            mseberg
            Andrew;


            You do a great job detailing the RMAN issue.


            A redundancy-based retention policy specifies how many backups of each datafile must be retained.

            It might be a bug, but in that example you may notice :
            CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
            A RECOVERY WINDOW and REDUNDANCY are NOT the same thing. Apples are not being compared to apples.

            That said it may still be a bug. However the title of the note is :

            Archivelog Deletion Policy Applied On All Standby is Ignored [ID 1313893.1]

            I don't see anything about a Standby in your post.


            What I would do is change my policy as a Redundancy of 1 is not enough.
            CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
            Don't use this command :
            BACKUP AS BACKUPSET ARCHIVELOG ALL DELETE ALL INPUT;
            Use this command.
            BACKUP AS BACKUPSET ARCHIVELOG ALL NOT BACKED UP 2 TIMES DELETE ALL INPUT;
            This may also help :

            how retention policy affects archive log

            http://oraware.blogspot.com/2008/01/relation-between-rman-retention-period.html



            Best Regards

            mseberg
            • 3. Re: Archived Redo Log Deletion Policy and Delete Input Clause
              rajeysh
              refer:-http://ocpdba.wordpress.com/2009/10/23/best-way-to-deal-with-archived-logs/
              • 4. Re: Archived Redo Log Deletion Policy and Delete Input Clause
                779292
                Thank you rajeysh, mseberg and Shivananda Rao for your reply and providing a workaround for the issue I'm facing.

                As mseberg said the MOS document may not be addressing my issue because I'm experiencing this issue in the local DB and I've no standby.

                >
                That said it may still be a bug. However the title of the note is :

                Archivelog Deletion Policy Applied On All Standby is Ignored [ID 1313893.1]

                I don't see anything about a Standby in your post.
                >

                The identical issue referred by Shivananda Rao (Question about Archive Log Deletion policy also refers to the same MOS document. But the reporter is not sure whether the bug identified by the MOS is identical to his problem. I've no way of accessing these documents as I've no contract with Oracle for support. I'm still learning.

                So, can anyone kindly help me out to confirm that what has been said in the Oracle documentation is correct OR the way RMAN behaves as I explained is correct? I'm confused, because, this has already been reported to Oracle as a result of the identical thread mentioned above by Shivananda Rao. But it seems Oracle has not identified the particular scenario I've described as a bug.

                >
                As explained in "Configuring an Archived Redo Log Deletion Policy", the BACKUP ... DELETE INPUT and DELETE ARCHIVELOG commands obey the archived redo log deletion policy for logs in all archiving locations. For example, if you specify that logs should only be deleted when backed up at least twice to tape, then BACKUP ... DELETE honors this policy.
                >

                Please help!

                Thanks,
                Andrew.

                Edited by: Andrew Vimales on Nov 18, 2012 5:11 PM
                • 5. Re: Archived Redo Log Deletion Policy and Delete Input Clause
                  mseberg
                  Andrew;

                  Can you post your exact version of Oracle?

                  This will narrow down the issue greatly.

                  Also the title of the Doc is "Archivelog Deletion Policy Applied On All Standby is Ignored [ID 1313893.1]"

                  If I read your question correctly you don't have a Standby and you are not using Tape, right?

                  Best Regards

                  mseberg
                  • 6. Re: Archived Redo Log Deletion Policy and Delete Input Clause
                    585179
                    Hi Andrew,

                    This is a known bug in 11.2 version. Please have a look "RMAN ARCHIVELOG DELETION POLICY BACKED UP N TIMES TO 'SBT_TAPE' IGNORED [ID 1487508.1]"

                    It is still under dev team. The deletion policy will honored the same channel of the backup, so it will apply when you do the backup to tape and delete all input

                    Hope it helps

                    Cheers