1 2 Previous Next 16 Replies Latest reply: Oct 5, 2012 3:08 AM by IMerino RSS

    How to delete the foreign archivelogs in a Logical Standby database

    71591
      How do I remove the foreign archive logs that are being sent to my logical standby database. I have files in the FRA of ASM going back weeks ago. I thought RMAN would delete them.

      I am doing hot backups of the databases to FRA for both databases. Using ASM, FRA, in a Data Guard environment.
      I am not backing up anything to tape yet.

      The ASM FRA foreign_archivelog directory on the logical standby FRA keeps growing and nothing is get deleted when
      I run the following command every day.

      delete expired backup;
      delete noprompt force obsolete;


      Primary database RMAN settings (Not all of them)

      CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 9 DAYS;
      CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
      CONFIGURE DB_UNIQUE_NAME 'WMRTPRD' CONNECT IDENTIFIER 'WMRTPRD_CWY';
      CONFIGURE DB_UNIQUE_NAME 'WMRTPRD2' CONNECT IDENTIFIER 'WMRTPRD2_CWY';
      CONFIGURE DB_UNIQUE_NAME 'WMRTPRD3' CONNECT IDENTIFIER 'WMRTPRD3_DG';
      CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

      Logical standby database RMAN setting (not all of them)

      CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 9 DAYS;
      CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
      CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

      How do I cleanup/delete the old ASM foreign_archivelog files?
        • 1. Re: How to delete the foreign archivelogs in a Logical Standby database
          CKPT
          How do I cleanup/delete the old ASM foreign_archivelog files?
          To delete archive logs on Logical standby instead of RMAN, you can use parameter LOG_AUTO_DELETE to TRUE by using DBMS_LOGSTBY package

          LOG_AUTO_DELETE
          Automatically deletes archived redo log files once they have been applied on the logical standby database. Set to TRUE to enable automatic deletion of archived redo log files, and FALSE to disable automatic deletion.The default value is TRUE.

          http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lsbydb.htm#BDCIEGEI

          HTH.
          • 2. Re: How to delete the foreign archivelogs in a Logical Standby database
            mseberg
            Hello;
            rman target sys/<password> nocatalog
            rman>crosscheck archivelog all
            rman>delete expired archivelog all
            Or keep a few days ( change in RMAN script )

            delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-2';

            This is what I use, Old archive does not build up and RMAN is happy.

            Best Regards

            mseberg
            • 3. Re: How to delete the foreign archivelogs in a Logical Standby database
              71591
              OK, the default is TRUE which is what it is now
              from DBA_LOGSTDBY_PARAMETERS

              LOG_AUTO_DELETE     TRUE          SYSTEM     YES

              I am not talking about deleting the Archive logs files for the Logical database that it is creating, but the Standby archive log files being sent to the Logical Database after they have been applied.

              They are in the alert log as follows under RFS LogMiner: Registered logfile

              RFS[1]: Selected log 4 for thread 1 sequence 159 dbid -86802306 branch 763744382
              Thu Jan 12 15:44:57 2012
              *RFS LogMiner: Registered logfile [+FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_12/thread_1_seq_158.322.772386297] to LogM*
              iner session id [1]
              Thu Jan 12 15:44:58 2012
              LOGMINER: Alternate logfile found. Transition to mining archived logfile for session 1 thread 1 sequence 158, +FRA/wmrtprd2/
              foreign_archivelog/wmrtprd/2012_01_12/thread_1_seq_158.322.772386297
              LOGMINER: End mining logfile for session 1 thread 1 sequence 158, +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_12/threa
              d_1_seq_158.322.772386297
              LOGMINER: Begin mining logfile for session 1 thread 1 sequence 159, +DG1/wmrtprd2/onlinelog/group_4.284.771760923                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
              • 4. Re: How to delete the foreign archivelogs in a Logical Standby database
                mseberg
                No, something else is going on here.

                The closest note I found is :

                LOGMINER GENERATES CORRUPT REDO BLOCK DETECTED: BAD CHECKSUM [ID 751286.1]

                I'd being using Oracle support for this one.

                Best Regards

                mseberg

                Later

                The other thought is does RMAN on the Standby see these files?

                You could test with something like this :

                $ORACLE_HOME/bin/rman <<EOF    
                connect target / 
                crosscheck archivelog all;  
                delete noprompt expired archivelog all; 
                exit
                EOF
                If RMAN see them you can use it to remove them and the "crosscheck archivelog all" should keep the system happy.

                The other question is does v$archived_log see them?

                If no to both you can consider just deleting them.


                Edited by: mseberg on Feb 3, 2012 6:12 PM
                • 5. Re: How to delete the foreign archivelogs in a Logical Standby database
                  Dr. Paranoid-Oracle
                  What version? Logical standby should delete them automatically unless you told it not to since 10.2.

                  EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'TRUE');

                  That would make sure they are deleted. Perhaps someone set that to FALSE?


                  Edit: I did not see that it was set to true above sorry. That is the command just for the logs coming from the Primary.

                  Edited by: Dr. Paranoid on Feb 3, 2012 2:38 PM
                  • 6. Re: How to delete the foreign archivelogs in a Logical Standby database
                    71591
                    Update info

                    Managing a logical DB says

                    Foreign archived logs contain redo that was shipped from the primary database. There are two ways to store foreign archive logs:

                    In the fast recovery area

                    In a directory outside of the fast recovery area

                    Foreign archived logs stored in the fast recovery area are always managed by SQL Apply. After all redo records contained in the log have been applied at the logical standby database, they are retained for the time period specified by the DB_FLASHBACK_RETENTION_TARGET parameter (or for 1440 minutes if DB_FLASHBACK_RETENTION_TARGET is not specified). You cannot override automatic management of foreign archived logs that are stored in the fast recovery area.

                    Foreign archived logs that are not stored in fast recovery area are by default managed by SQL Apply. Under automatic management, foreign archived logs that are not stored in the fast recovery area are retained for the time period specified by the LOG_AUTO_DEL_RETENTION_TARGET parameter once all redo records contained in the log have been applied at the logical standby database. You can override automatic management of foreign archived logs not stored in fast recovery area by executing the following PL/SQL procedure:

                    My db_flashback_retention_target is set to 900

                    the DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'TRUE'); was never changed or executed so itis the default of TRUE. There is query above that shows it set to true.

                    Here are the oldest foreign_archivelogs from Jan 09, 2012

                    +FRA/WMRTPRD2/foreign_archivelog/WMRTPRD/2012_01_09/thread_1_seq_151.282.772121427      
                    +FRA/WMRTPRD2/foreign_archivelog/WMRTPRD/2012_01_09/thread_1_seq_152.280.772121427            
                    +FRA/WMRTPRD2/foreign_archivelog/WMRTPRD/2012_01_09/thread_1_seq_153.281.772121427  
                    +FRA/WMRTPRD2/foreign_archivelog/WMRTPRD/2012_01_09/thread_1_seq_154.284.772121427               
                    +FRA/WMRTPRD2/foreign_archivelog/WMRTPRD/2012_01_09/thread_1_seq_154.285.772121427  

                    and when I look at

                    select file_name, applied from sys.DBA_LOGSTDBY_LOG
                    order by file_name

                    1     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_09/thread_1_seq_151.282.772121427     YES
                    2     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_09/thread_1_seq_152.280.772121427     YES
                    3     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_09/thread_1_seq_153.281.772121427     YES
                    4     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_09/thread_1_seq_154.285.772121427     YES
                    5     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_09/thread_1_seq_155.283.772121427     YES
                    6     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_09/thread_1_seq_156.290.772127063     YES
                    7     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_12/thread_1_seq_157.320.772380765     YES
                    8     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_12/thread_1_seq_158.322.772386297     YES
                    9     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_13/thread_1_seq_159.330.772449543     YES
                    10     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_13/thread_1_seq_160.332.772451719     YES
                    11     +FRA/wmrtprd2/foreign_archivelog/wmrtprd/2012_01_13/thread_1_seq_161.334.772455353     YES

                    I see no reason why these should not be getting deleted. Oracle 11.2.0.3, Non-RAC
                    • 7. Re: How to delete the foreign archivelogs in a Logical Standby database
                      71591
                      select * from sys.DBA_LOGSTDBY_PARAMETERS

                      Never changed, still the default of TRUE.

                      NAME     LOG_AUTO_DELETE
                      VALUE     TRUE
                      UNIT     
                      SETTING     SYSTEM
                      DYNAMIC     YES
                      • 8. Re: How to delete the foreign archivelogs in a Logical Standby database
                        71591
                        Since everything seems to be set correctly, I guess the question is "When will they automatically get deleted?" My FRA is only about 10% used so when will Oracle actually delete the foreign archivelog files?

                        I ran EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;

                        and now select * from sys.DBA_LOGMNR_PURGED_LOG

                        has 36 rows in it all the way thru Jan 31, where before it was empty.
                        • 9. Re: How to delete the foreign archivelogs in a Logical Standby database
                          StephenA
                          Did you ever find the solution to this problem?

                          I've got exactly the same issue - auto delete is set to on, the retention is set to 1440 minutes but I've got foreign redo logs going back over 17 days.

                          Steve
                          • 10. Re: How to delete the foreign archivelogs in a Logical Standby database
                            Seberg
                            Known bug.

                            Bug 13448652 - LOG_AUTO_DELETE setting reverts to FALSE on Logical Standby [ID 13448652.8]

                            In the future please open your own question.
                            • 11. Re: How to delete the foreign archivelogs in a Logical Standby database
                              StephenA
                              I thought it was easier to ask if they had found a solution rather than asking the same question and being told sarcastically to try doing a search rather than starting a new thread..

                              As it happens I am not suffering from that bug - I do not have any messages in the alert log stating that the auto delete has been disabled.... but thank you for your friendly help.

                              Edited by: StephenA on Sep 20, 2012 2:46 AM
                              • 12. Re: How to delete the foreign archivelogs in a Logical Standby database
                                IMerino
                                As StephenA and jeharr asks, Did someone ever find the solution to this problem?

                                I have a very similar behaviour, but different versions (Linux and 11.2.0.2), log_auto_delete not working since the start of sql apply, also I have no error messages about log_auto_delete in the alert log. I also checked that I have this setting to the default value and with the default retention. The bug 13448652 in this case I think doesn't apply:

                                SQL> select * from dba_logstdby_parameters where name = 'LOG_AUTO_DELETE';

                                NAME VALUE UNIT SETTING DYNAMIC
                                ------------------------------ ------------------------------ -------------------- -------------------- --------------------
                                LOG_AUTO_DELETE TRUE SYSTEM YES


                                SQL> show parameter DB_FLASHBACK_RETENTION_TARGET

                                NAME TYPE VALUE
                                ------------------------------------ ----------- ------------------------------
                                db_flashback_retention_target integer 1440

                                I have 2004 foreign archived logs! :

                                SQL> select file_name, applied from sys.DBA_LOGSTDBY_LOG order by file_name;

                                FILE_NAME APPLIED
                                ---------------------------------------------------------------------------------------------------- --------
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_06_28/o1_mf_1_1275_7yrpv2vt_.arc YES
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_06_28/o1_mf_1_1276_7yrpv2sl_.arc YES
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_06_28/o1_mf_1_1277_7yrpv2s8_.arc YES
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_06_28/o1_mf_1_1278_7yrpv2xw_.arc YES
                                [...]
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_09_26/o1_mf_1_1899_864g9ooj_.arc YES
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_09_26/o1_mf_1_1900_864rv88h_.arc YES
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_09_26/o1_mf_1_1901_865ldxrt_.arc YES
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_09_26/o1_mf_1_1902_865ldxrm_.arc YES
                                /bbdd_oiabxyz2/archivelog/oiabxyz2/foreign_archivelog/oeprmisb/2012_09_26/o1_mf_1_1903_865lf061_.arc CURRENT

                                2004 rows selected.

                                The recovery file dest size it's at 62.43%:

                                SQL> SELECT substr(name, 1, 30) name,
                                2 space_limit/1024/1024 AS quota,
                                3 space_used/1024/1024 AS used_MB,
                                4 space_reclaimable/1024/1024 AS reclaimable_MB,
                                5 number_of_files AS files
                                6 FROM v$recovery_file_dest ;

                                NAME QUOTA USED_MB RECLAIMABLE_MB FILES
                                ---------------------------------------- ---------- ---------- -------------- ----------
                                /bbdd_oiabxyz2/archivelog 143360 89598.79 89421.50 2004

                                SQL> select * from v$flash_recovery_area_usage;

                                FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
                                -------------------- ------------------ ------------------------- ---------------
                                CONTROL FILE 0 0 0
                                REDO LOG 0 0 0
                                ARCHIVED LOG 0 0 0
                                BACKUP PIECE 0 0 0
                                IMAGE COPY 0 0 0
                                FLASHBACK LOG 0 0 0
                                FOREIGN ARCHIVED LOG 62.43 62.38 2004

                                Someone find some workarround to automatically delete foreign archived logs?

                                I also try to execute explicitly DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'TRUE') , but nothing changes, only a new line in the alert log saying:

                                2012-09-26 14:50:53.582000 +02:00
                                LOGSTDBY: APPLY_SET: LOG_AUTO_DELETE changed to TRUE

                                In my alert log I have some messages like :

                                2012-06-29 08:55:24.662000 +02:00
                                LOGMINER: Turning ON Log Auto Delete

                                Are there any automatic deletion workaround? or I must use DBMS_LOGSTDBY.PURGE SESSION and check DBA_LOGMNR_PURGED_LOG for manually delete them
                                • 13. Re: How to delete the foreign archivelogs in a Logical Standby database
                                  Valentin Minzatu
                                  I do not have a place to try, but can you test this:
                                  delete expired foreign archivelog all;
                                  • 14. Re: How to delete the foreign archivelogs in a Logical Standby database
                                    StephenA
                                    I can delete the files using RMAN - the problem I have is that they are not being deleted automatically.

                                    It might also be a co-incidence but the number of rows in DBA_LOGMNR_PURGED_LOG doesn't increase unless I manually run EXECUTE DBMS_LOGSTDBY.PURGE_SESSION;
                                    1 2 Previous Next