This discussion is archived
9 Replies Latest reply: Nov 24, 2011 6:47 AM by UW (Germany) RSS

Question about Archive Log Deletion policy

UW (Germany) Explorer
Currently Being Moderated
I've a problem to understand the Archive Log Deletion policy, and I I'd like to this problem explain with the following example.
Messages of the database are in German, but I guess you'll understand them.
SQL> startup
ORACLE-Instance hochgefahren.

Total System Global Area 5344731136 bytes
Fixed Size                  2129240 bytes
Variable Size            2684355240 bytes
Database Buffers         2617245696 bytes
Redo Buffers               41000960 bytes
Datenbank mounted.
Datenbank geöffnet.

SQL> archive log list
Datenbank-Log-Modus              Archive-Modus
Automatische Archivierung             Aktiviert
Archivierungsziel            E:\oracle\thetis_iv\arch
Älteste Online-Log-Sequenz     17917
Nächste zu archivierende Log-Sequenz   17919
Aktuelle Log-Sequenz           17919

SQL> alter system switch logfile;

System wurde geändert.
I created a brand new archive log.
SQL> exit
Verbindung zu Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options beendet

D:\OracleDB\product\11.1.0\db_1\BIN>dir E:\oracle\thetis_iv\arch
 Datenträger in Laufwerk E: ist Volume
 Volumeseriennummer: 3EBD-77E5

 Verzeichnis von E:\oracle\thetis_iv\arch

06.04.2011  15:04    <DIR>          .
06.04.2011  15:04    <DIR>          ..
06.04.2011  15:04        17.137.152 ARC17919_0721667907.001
               1 Datei(en),     17.137.152 Bytes
               2 Verzeichnis(se), 41.073.258.496 Bytes frei
and this is the only archive log in the directory. Now I start rman:
D:\OracleDB\product\11.1.0\db_1\BIN>rman target / catalog rmanrepo@rmanrepo

Recovery Manager: Release 11.1.0.7.0 - Production on Mi Apr 6 15:05:35 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Mit Ziel-Datenbank verbunden: ENTWIV (DBID=21045568)
Kennwort für Recovery-Katalog-Datenbank:
Verbindung mit Datenbank des Recovery-Katalogs

RMAN> show all;

RMAN-Konfigurationsparameter für Datenbank mit db_unique_name ENTWIV sind:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\oracle\thetis_iv\backup\CF_%F_ENTWIV.ORA';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TPDO_OPTFILE=D:\Services\Tivoli\TSM\AgentOBA64\tpdo.opt)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLEDB\PRODUCT\11.1.0\DB_1\DATABASE\SNCFENTWIV.ORA'; # default
The archive log deletion policy says the the logfiles have to be backed up for two times before they get deleted.
Now I backup all archive logs, that havn't been backed up for at least two times.
RMAN> run { backup archivelog all not backed up 2 times
2>       format '%d_AR_%Y%M%D_%s_%t'
3>       tag 'ARCHIVE LOGS'
4>       DELETE ALL INPUT;
5>     }

Starten backup um 06.04.2011 15:08:01
Aktuelles Log archiviert
Zugewiesener Kanal: ORA_SBT_TAPE_1
Kanal ORA_SBT_TAPE_1: SID=253 Device-Typ=SBT_TAPE
Kanal ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
Kanal ORA_SBT_TAPE_1: Backup Set für Archive Log wird begonnen
Kanal ORA_SBT_TAPE_1: Archive Logs in Backup Set werden angegeben
Eingabe-Archive-Log-Thread=1 Sequence=17919 RECID=147 STAMP=747759899
Eingabe-Archive-Log-Thread=1 Sequence=17920 RECID=148 STAMP=747760081
Kanal ORA_SBT_TAPE_1: Piece 1 wird auf 06.04.2011 15:08:02 begonnen
Kanal ORA_SBT_TAPE_1: Piece 1 auf 06.04.2011 15:08:09 beendet
Piece Handle=ENTWIV_AR_20110406_23_747760082 Tag=ARCHIVE LOGS Kommentar=API Version 2.0,MMS Version 5.5.1.0
Kanal ORA_SBT_TAPE_1: Backup Set vollstõndig, abgelaufene Zeit: 00:00:08
Kanal ORA_SBT_TAPE_1: Archive Logs werden gel÷scht
Archive Log-Dateiname=E:\ORACLE\THETIS_IV\ARCH\ARC17919_0721667907.001 RECID=147 STAMP=747759899
Archive Log-Dateiname=E:\ORACLE\THETIS_IV\ARCH\ARC17920_0721667907.001 RECID=148 STAMP=747760081
Beendet backup um 06.04.2011 15:08:10

RMAN> exit

Recovery Manager abgeschlossen.

D:\OracleDB\product\11.1.0\db_1\BIN> dir E:\oracle\thetis_iv\arch
 Datenträger in Laufwerk E: ist Volume
 Volumeseriennummer: 3EBD-77E5

 Verzeichnis von E:\oracle\thetis_iv\arch

06.04.2011  15:08    <DIR>          .
06.04.2011  15:08    <DIR>          ..
               0 Datei(en),              0 Bytes
               2 Verzeichnis(se), 41.090.396.160 Bytes frei
rman deleted all archive logs, even I they are on tape only once by now.
Thats not what I expected. Where is my mistake?
  • 1. Re: Question about Archive Log Deletion policy
    Levi-Pereira Guru
    Currently Being Moderated
    Hi,

    I do new tests it's very strange.
    BACKUP ARCHIVELOG command is not obeying the policy of archivelog.

    You can open a SR on MOS. (to check bugs)

    I reproduce the same test and the result was the same, it seems that this is a bug.
    ...
    CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
    ...
    RMAN> backup archivelog all not backed up 2 times delete all input;
    
    Starting backup at 06-APR-11
    current log archived
    using channel ORA_DISK_1
    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=15 RECID=16 STAMP=747753711
    input archived log thread=2 sequence=20 RECID=17 STAMP=747753714
    input archived log thread=1 sequence=16 RECID=19 STAMP=747753729
    input archived log thread=2 sequence=21 RECID=18 STAMP=747753729
    channel ORA_DISK_1: starting piece 1 at 06-APR-11
    channel ORA_DISK_1: finished piece 1 at 06-APR-11
    piece handle=+DATA/orcl/backupset/2011_04_06/annnf0_tag20110406t132210_0.304.747753731 tag=TAG20110406T132210 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=+DATA/orcl/archivelog/2011_04_06/thread_1_seq_15.293.747753711 RECID=16 STAMP=747753711
    archived log file name=+DATA/orcl/archivelog/2011_04_06/thread_2_seq_20.295.747753715 RECID=17 STAMP=747753714
    archived log file name=+DATA/orcl/archivelog/2011_04_06/thread_1_seq_16.294.747753729 RECID=19 STAMP=747753729
    archived log file name=+DATA/orcl/archivelog/2011_04_06/thread_2_seq_21.298.747753729 RECID=18 STAMP=747753729
    Finished backup at 06-APR-11
    
    RMAN> list archivelog all;
    
    specification does not match any archived log in the repository
    Oracle Docs Says:
    The BACKUP ARCHIVELOG ... DELETE INPUT command deletes archived log files after they are backed up. 
    This command eliminates the separate step of manually deleting archived redo logs.
    With DELETE INPUT, RMAN deletes only the specific copy of the archived log chosen for the backup set.
    With DELETE ALL INPUT, RMAN deletes each backed-up archived redo log file from all log archiving destinations.
    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.
    http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV89524

    But in ours case it's not honors this policy.

    Only with the FORCE command should this happen. But it is not our case.
    Oracle Docs:
     If FORCE is not specified on the deletion commands, 
     then these deletion commands obey the archived log deletion policy. 
    If FORCE is specified, then the deletion commands ignore the archived log deletion policy.
    http://download.oracle.com/docs/cd/E11882_01/backup.112/e10643/rcmsynta010.htm#RCMRF113

    Alternatively you can do the following:
    Set the commands separately.

    Check this:
    RMAN>  run {
    2> backup archivelog all not backed up 2 times ;
    3> delete archivelog all backed up 2 times to disk;
    4> }
    
    Starting backup at 06-APR-11
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archived log backup set
    channel ORA_DISK_1: specifying archived log(s) in backup set
    input archived log thread=2 sequence=22 RECID=21 STAMP=747755128
    input archived log thread=1 sequence=17 RECID=20 STAMP=747755127
    channel ORA_DISK_1: starting piece 1 at 06-APR-11
    channel ORA_DISK_1: finished piece 1 at 06-APR-11
    piece handle=+DATA/orcl/backupset/2011_04_06/annnf0_tag20110406t134528_0.295.747755129 tag=TAG20110406T134528 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 06-APR-11
    
    released channel: ORA_DISK_1
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=78 instance=orcl1 device type=DISK
    RMAN-08138: WARNING: archived log not deleted - must create more backups
    archived log file name=+DATA/orcl/archivelog/2011_04_06/thread_1_seq_17.298.747755127 thread=1 sequence=17
    RMAN-08138: WARNING: archived log not deleted - must create more backups
    archived log file name=+DATA/orcl/archivelog/2011_04_06/thread_2_seq_22.294.747755129 thread=2 sequence=22
    
    RMAN>
    Edited by: Levi Pereira on Apr 6, 2011 1:35 PM
  • 2. Re: Question about Archive Log Deletion policy
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I wouldn't run this as one command
    backup archivelog all not backed up 2 times  ..... DELETE ALL INPUT;
    As you've discovered, the latter portion (the DELETE) doesn't take cognisance of the "not backed up 2 times".


    Hemant K Chitale
  • 3. Re: Question about Archive Log Deletion policy
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    I just ran
    list archivelog all;
    backup archivelog all not backed up 2 times DELETE ALL INPUT;
    and did see this message :
    deleting archive log(s)
    Hemant K Chitale
  • 4. Re: Question about Archive Log Deletion policy
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Can you try :
     backup archivelog all not backed up 2 times DELETE INPUT;
    i.e. do not specify "delete ALL input".


    Hemant K Chitale
  • 5. Re: Question about Archive Log Deletion policy
    UW (Germany) Explorer
    Currently Being Moderated
    Deleting in the archive logs in a 2nd step like
    run { backup archivelog all not backed up 2 times
          format '%d_AR_%Y%M%D_%s_%t'
          tag 'ARCHIVE LOGS';
    
          delete archivelog all backed up 2 times to 'SBT_TAPE';
        }
    gives me the desired result. But now I say explicitly what has to be deleted and the Archive Log Deletion Policy is ignored.
    So it indeed seems, that there is a bug in rman and I'll open a SR at metalink aka Oracle support.

    To Hemant: It makes no difference whether I use DELETE INPUT or DELETE ALL INPUT. And in the statement
     backup archivelog all not backed up 2 times ... 'ARCHIVE LOGS' DELETE ALL INPUT; 
    the DELETE ALL INPUT shouldn't refer to the 2 times in the first part of the statement but to the Archive Log Deletion Policy.
  • 6. Re: Question about Archive Log Deletion policy
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    It makes no difference whether I use DELETE INPUT or DELETE ALL INPUT
    and
    the DELETE ALL INPUT shouldn't refer to the 2 times in the first part of the statement but to the Archive Log Deletion Policy
    Yes
    the BACKUP ... DELETE INPUT and DELETE ARCHIVELOG commands obey the archived redo log deletion policy
    Just wondering if there was a bug with the handling in DELETE ALL INPUT. So, I suggested you try DELETE INPUT.
    I can't test it because I don't have ArchiveLog deletion policy.

    Hemant K Chitale
  • 7. Re: Question about Archive Log Deletion policy
    Levi-Pereira Guru
    Currently Being Moderated
    Hi,
    So it indeed seems, that there is a bug in rman and I'll open a SR at metalink aka Oracle support.
    Please put here Oracle Solution.

    Thank you
    Levi Pereira
  • 8. Re: Question about Archive Log Deletion policy
    Levi-Pereira Guru
    Currently Being Moderated
    Oracle confirmed the bug:
    Bug 9477668: ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; NOT WORKING

    Use this tech note to resolve issue:
    *Archivelog Deletion Policy Applied On All Standby is Ignored [ID 1313893.1]*

    Oracle's solution is the same solution used in this thread.

    Regards,
    Levi Pereira
  • 9. Re: Question about Archive Log Deletion policy
    UW (Germany) Explorer
    Currently Being Moderated
    I didn't watch this thread for a while, because I had a working solution. So I didn't notice the last message.
    I'm not sure whether the mentioned bugs are identical with my problem. For my service request I got this answer from oracle:

    ------
    1.YOu have configured backup optimization which means that you cannot have more than 1 backup of the same file:
    With CONFIGURE BACKUP OPTIMIZATION ON; you will never have two copies of the same archivelog

    If you want o backup your archivelogs twice, then set:

    CONFIGURE BACKUP OPTIMIZATION OFF;

    2. With backup delete all input all archivelogs matching the condition will be deleted:

    backup archivelog all not backed up 2 times format '%d_AR_%Y%M%D_%s_%t' tag 'ARCHIVE LOGS' DELETE ALL INPUT;

    The above will backup and delete the archivelogs not backed up twice, if you want to delete archivelogs when there will be two backups use:

    backup archivelog all not backed up 2 times format '%d_AR_%Y%M%D_%s_%t' tag 'ARCHIVE LOGS'
    delete archivelog all; ==> This should respect the archivelog deletion policy


    Syntax Element Description
    DELETE [ALL] INPUT Deletes the input files after successfully backing them up.
    Specify this option only when backing up archived redo logs, datafile copies (COPY OF or DATAFILECOPY), or backup sets. The BACKUP ARCHIVELOG command only backs up one copy of each distinct log sequence number, so if the DELETE INPUT option is used without the ALL keyword, RMAN only deletes the copy of the file that it backs up.

    Specifying the DELETE INPUT option is equivalent to issuing the DELETE command for the input files. When backing up archived redo logs, RMAN uses the configured settings to determine whether an archived redo log can be deleted (CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP).

    The ALL option applies only to archived redo logs. If you run DELETE ALL INPUT, then the command deletes all copies of corresponding archived redo logs or datafile copies that match the selection criteria in the BACKUP command (as shown in Example 2-19). For example, if you specify the SEQUENCE n clause, then RMAN deletes all archived redo logs with same sequence number n.

    Note: The database retains archived redo logs in the flash recovery as long as possible and deletes them automatically when additional disk space is required. You can use the BACKUP DELETE INPUT, DELETE ARCHIVELOG, and DELETE OBSOLETE commands to delete logs manually from locations inside or outside the recovery area. You do not need to specify BACKUP DELETE INPUT when backing up the recovery area because the database automatically deletes logs based on the archived log deletion policy and other flash recovery area rules.
    -----

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points