This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Oct 5, 2012 1:08 AM by IMerino RSS

How to delete the foreign archivelogs in a Logical Standby database

71591 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Acooper Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    72370 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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