12 Replies Latest reply: Jul 8, 2013 12:15 AM by udayaraj RSS

    Archive log automatically getting deleted

    OSSGeek
      Hi,

      My Archive logs are getting automatically deleted from ASM. I keep my Archive logs in FRA. My alert log shows deletions like this:
      Deleted Oracle managed file +DATA1/orcl/archivelog/...
      Retention policy to recovery window is set to 24 days. The +DATA1 ASM disk has lots of free space. Archivelog deletion policy is set to None and I don't run archive deletion from rman too...
      Is there any other option I need to check?
        • 1. Re: Archive log automatically getting deleted
          Mario Alcaide
          Hi,

          I hope this post helps you:

          http://www.oraclefaq.net/2007/10/18/archive-log-automatically-getting-deleted-from-oracle-10g-database-with-omf/

          Regards,

          Mario Alcaide
          • 2. Re: Archive log automatically getting deleted
            635694
            hello,

            connect to target database,

            rman> show all;
            • 3. Re: Archive log automatically getting deleted
              Rafi (Oracle DBA)
              Hi,
              Check the below link:

              http://www.oraclefaq.net/2007/10/18/archive-log-automatically-getting-deleted-from-oracle-10g-database-with-omf/








              Best regards,

              Rafi.
              http://rafioracledba.blogspot.com/
              • 4. Re: Archive log automatically getting deleted
                OSSGeek
                Thank you Mario & Rafi but that link didn't help...

                This is my rman config:
                $ rman target /
                
                Recovery Manager: Release 10.2.0.2.0 - Production on Mon Apr 26 14:40:16 2010
                
                Copyright (c) 1982, 2005, Oracle.  All rights reserved.
                
                connected to target database: ORCL (DBID=689272164)
                
                
                
                RMAN> show all;
                
                
                
                using target database control file instead of recovery catalog
                
                RMAN configuration parameters are:
                
                CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 24 DAYS;
                
                CONFIGURE BACKUP OPTIMIZATION OFF; # default
                
                CONFIGURE DEFAULT DEVICE TYPE TO DISK;
                
                CONFIGURE CONTROLFILE AUTOBACKUP ON;
                
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/var/log/oracle/snapcf/%F';
                
                CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
                
                CONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;
                
                CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 5 BACKUP TYPE TO BACKUPSET;
                
                CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
                
                CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
                
                CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
                
                CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
                
                CONFIGURE MAXSETSIZE TO UNLIMITED; # default
                
                CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
                
                CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
                
                CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
                
                CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl1.f'; # default
                
                
                RMAN>
                • 5. Re: Archive log automatically getting deleted
                  19426
                  The +DATA1 ASM disk has lots of free space
                  Maybe. Much more important here what's the FRA size? Is it possible you have space pressure in the FRA? In case of space pressure it's possible that RMAN deletes archivelogs from the FRA STILLl needed for recovery BUT already backed up. That's still a better solution than to stop the database because the archivelog destination is full and cannot accept new generated archivelogs.
                  Check whether you have such a situation.

                  Werner
                  • 6. Re: Archive log automatically getting deleted
                    OSSGeek
                    Thank you werner,

                    Below queries show that I already have space in FRA...
                    SQL> select (SPACE_LIMIT/1024/1024/1024), (SPACE_USED/1024/1024/1024), (SPACE_RECLAIMABLE/1024/1024/1024) from v$recovery_file_dest;
                    
                    (SPACE_LIMIT/1024/1024/1024) (SPACE_USED/1024/1024/1024) (SPACE_RECLAIMABLE/1024/1024/1024)
                    ---------------------------- --------------------------- ----------------------------------
                    
                                             500                  36.6706381                          3.2632184
                    
                    SQL>
                    SQL> select * from v$flash_recovery_area_usage;
                    
                    FILE_TYPE                            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                    ------------------------------------ ------------------ ------------------------- ---------------
                    CONTROLFILE                                         .02                         0               1
                    ONLINELOG                                           .12                         0              12
                    ARCHIVELOG                                         1.03                       .65              29
                    BACKUPPIECE                                           0                         0               0
                    IMAGECOPY                                             0                         0               0
                    FLASHBACKLOG                                       6.36                      6.15             125
                    
                    6 rows selected.
                    SQL>
                    See my alert log after some "alter system switch logfile;"
                    Tue Apr 27 11:17:46 2010
                    Thread 1 advanced to log sequence 83570
                      Current log# 109 seq# 83570 mem# 0: +DATA1/orcl/onlinelog/group_109.19715.679998731
                      Current log# 109 seq# 83570 mem# 1: +DATA1/orcl/onlinelog/group_109.20362.679998737
                    Tue Apr 27 11:17:47 2010
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_26/thread_3_seq_90114.17125.717358729
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_74.12659.717340839
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_85.18441.717351679
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_8.11194.717355103
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_9.12991.717355123
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_10.17043.717355141
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_1.15281.717355097
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_79.13174.717355163
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_138.15374.717355185
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_139.19996.717357319
                    Tue Apr 27 11:17:48 2010
                    Thread 1 advanced to log sequence 83571
                      Current log# 105 seq# 83571 mem# 0: +DATA1/orcl/onlinelog/group_105.12632.679998535
                      Current log# 105 seq# 83571 mem# 1: +DATA1/orcl/onlinelog/group_105.15649.679998539
                    Tue Apr 27 11:17:49 2010
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_26/thread_2_seq_87077.21363.717362485
                    Tue Apr 27 11:17:49 2010
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_26/thread_4_seq_87136.11714.717365975
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_129.20212.717344739
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_140.11185.717358727
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_5.21454.717358739
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_11.15673.717358753
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_87.12289.717358783
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_12.21878.717358779
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_141.17658.717358807
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_143.20355.717358847
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_142.15020.717358817
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_145.11651.717358889
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_137.13239.717351771
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_86.20900.717355183
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_147.11174.717362357
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_146.10495.717358957
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_149.14900.717362391
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_150.19760.717362403
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_14.20074.717362515
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_148.12779.717362383
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_13.15276.717362485
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_151.13222.717365933
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_152.21251.717365947
                    Tue Apr 27 11:17:53 2010
                    Thread 1 advanced to log sequence 83572
                      Current log# 104 seq# 83572 mem# 0: +DATA1/orcl/onlinelog/group_104.18737.679998357
                      Current log# 104 seq# 83572 mem# 1: +DATA1/orcl/onlinelog/group_104.17641.679998363
                    Tue Apr 27 11:17:53 2010
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_26/thread_1_seq_83563.18107.717366141
                    Tue Apr 27 11:17:54 2010
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_26/thread_4_seq_87137.10526.717366139
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_153.20718.717365963
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_17.21619.717365977
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_18.20172.717365989
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_19.12078.717366005
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_48.19221.717366017
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_154.14279.717366053
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_155.14745.717366079
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_156.17031.717366101
                    Tue Apr 27 11:18:07 2010
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_27/thread_2_seq_87079.20751.717379897
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_157.10653.717366127
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_144.17927.717358881
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_2.18495.717366169
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_20.16857.717369567
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_16.12724.717363263
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_23.13471.717369587
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_89.16244.717369613
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_88.11076.717369605
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_158.11103.717369643
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_159.21379.717369685
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_15.22226.717362555
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_160.13946.717369711
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_6.14447.717369491
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_24.11233.717373129
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_76.10929.717373141
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_90.12864.717373157
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_163.13475.717373171
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_164.20384.717373187
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_165.15963.717373215
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_166.20629.717373249
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_167.21197.717373279
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_161.13500.717369751
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_25.13335.717373553
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_162.17288.717372027
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_22.20002.717372205
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_91.16639.717376749
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_92.15382.717376759
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_169.19652.717376787
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_170.14527.717376801
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_168.10371.717376781
                    Tue Apr 27 11:21:28 2010
                    Thread 1 advanced to log sequence 83573
                      Current log# 103 seq# 83573 mem# 0: +DATA1/orcl/onlinelog/group_103.11697.679998293
                      Current log# 103 seq# 83573 mem# 1: +DATA1/orcl/onlinelog/group_103.21923.679998297
                    Tue Apr 27 11:21:28 2010
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_27/thread_1_seq_83564.19690.717379897
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_27/thread_2_seq_87080.11232.717383337
                    Tue Apr 27 11:22:10 2010
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_26.12608.717374927
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_27.12714.717380335
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_33.17297.717380353
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_34.14547.717380371
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_36.17079.717380389
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_93.18491.717380407
                    Deleted Oracle managed file +DATA1/orcl/flashback/log_174.10790.717380465
                    Tue Apr 27 11:22:11 2010
                    Thread 1 advanced to log sequence 83574
                      Current log# 108 seq# 83574 mem# 0: +DATA1/orcl/onlinelog/group_108.15776.679998703
                      Current log# 108 seq# 83574 mem# 1: +DATA1/orcl/onlinelog/group_108.13383.679998709
                    Tue Apr 27 11:22:11 2010
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_27/thread_1_seq_83566.16358.717383421
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_27/thread_4_seq_87141.11563.717383423
                    Deleted Oracle managed file +DATA1/orcl/archivelog/2010_04_27/thread_3_seq_90119.12158.717383423
                    Tue Apr 27 11:22:25 2010
                    Edited by: OSSGeek on Apr 27, 2010 12:49 AM
                    • 7. Re: Archive log automatically getting deleted
                      Hemant K Chitale
                      "RETENTION POLICY" applies to Backups.

                      So your RETENTION POLICY of 24 days will not prevent ArchiveLogs from being deleted if Oracle determines that it does have Backups of those ArchiveLogs.

                      What you still need to determine is why the ArchiveLogs are being deleted -- generally that would be for space in the FRA. But you seem to have space.



                      Hemant K Chitale
                      • 8. Re: Archive log automatically getting deleted
                        OSSGeek
                        Hemant K Chitale wrote:
                        What you still need to determine is why the ArchiveLogs are being deleted -- generally that would be for space in the FRA. But you seem to have space.
                        Yes!
                        They are also being deleted every time I issue
                        alter system switch logifle;
                        See the above alert log...
                        • 9. Re: Archive log automatically getting deleted
                          Hemant K Chitale
                          Look up Bug# 4911954
                          (Support Note#4911954.8)

                          Compare the contents of V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST


                          Hemant K Chitale
                          http://hemantoracledba.blogspot.com
                          • 10. Re: Archive log automatically getting deleted
                            OSSGeek
                            Thank you Hemant, So seems I need to monitor my FRA with du on ASM.
                            And I still don't know why archive logs are being deleted when I switch log...
                            • 11. Re: Archive log automatically getting deleted
                              757900
                              thanks Hermant - that seems to be the issue I'm experiencing too, the below reset the discrepancy between V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST.

                              alter session set events 'immediate trace name kra_options level 1';
                              execute dbms_backup_restore.refreshagedfiles;
                              • 12. Re: Archive log automatically getting deleted
                                udayaraj

                                Hi I have a similar issue with my Oracle database. Even though there is enough free space available, the Archive logs are automatically deleted. There is no  discrepancy between V$FLASH_RECOVERY_AREA_USAGE and V$RECOVERY_FILE_DEST results. Since these deleted archive logs are within the retention period, I get the RMAN-05019: WARNING: also..

                                {code}

                                BANNER

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

                                Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

                                PL/SQL Release 10.2.0.5.0 - Production

                                CORE    10.2.0.5.0      Production

                                TNS for 64-bit Windows: Version 10.2.0.5.0 - Production

                                NLSRTL Version 10.2.0.5.0 - Production

                                 

                                select * from V$FLASH_RECOVERY_AREA_USAGE;

                                 

                                 

                                FILE_TYPE                PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

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

                                CONTROLFILE                               0                         0               0

                                ONLINELOG                                 0                         0               0

                                ARCHIVELOG                            11.96                     11.42             698

                                BACKUPPIECE                            3.45                      3.32              44

                                IMAGECOPY                             64.58                     64.58             316

                                FLASHBACKLOG                              0                         0               0

                                 

                                select * from V$RECOVERY_FILE_DEST;

                                NAME                           SPACE_LIMIT          SPACE_USED     SPACE_RECLAIMABLE    NUMBER_OF_FILES

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

                                E:\FRA\                        1350 GB              1079.79 GB           1070.86 GB                      1058

                                {code}

                                 

                                 

                                 

                                Could anyone kindly help me in resolving this?

                                 

                                Thank you

                                Kind Regards

                                Udayaraj