This discussion is archived
4 Replies Latest reply: Apr 17, 2013 12:32 AM by MahirM.Quluzade RSS

Ye olde "delete archivelogs in standby" question

User477708-OC Journeyer
Currently Being Moderated
11.2.0.2.0

According to note
Configure RMAN to purge archivelogs after applied on standby [ID 728053.1]

oracle should remove archive logs if we have deletion policy set to "delete applied on standby" which I have set on primary and standby. Monitoring the alert log we see

"Deleted Oracle managed file /path/to/an/archive/log"

so we think cool, its removing them automatically. Question is, when does it delete them? Right now my standby is in sync, yet my alert log is only sporadically showing my logs being deleted which were applied hours ago which I can visibly verify.
  • 1. Re: Ye olde "delete archivelogs in standby" question
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Oracle automatically deletes archivelogs only when it needs to clear space in the FRA (db_recovery_file_dest).


    Hemant K Chitale
  • 2. Re: Ye olde "delete archivelogs in standby" question
    MahirM.Quluzade Guru
    Currently Being Moderated
    961469 wrote:
    11.2.0.2.0

    According to note
    Configure RMAN to purge archivelogs after applied on standby [ID 728053.1]

    oracle should remove archive logs if we have deletion policy set to "delete applied on standby" which I have set on primary and standby. Monitoring the alert log we see

    "Deleted Oracle managed file /path/to/an/archive/log"

    so we think cool, its removing them automatically. Question is, when does it delete them? Right now my standby is in sync, yet my alert log is only sporadically showing my logs being deleted which were applied hours ago which I can visibly verify.
    Hi,

    "delete applied on standby" it is not start any process for deleting archived logs automatically.
    when you call delete archivelog from rman, then rman check archivelog if applied then deleting,
    else archivelog is not deleting.

    Hemant K Chitale says: Oracle automatically deletes archivelogs only when it needs to clear space in the FRA (db_recovery_file_dest).
    - Yes I agree, it is true.

    But it is not means if archived log applied on standby, then deleting automatically from primary side.
    When need space on FRA, then deletes automatically .

    Thanks.
    Mahir M. Quluzade
    http://www.mahir-quluzade.com
  • 3. Re: Ye olde "delete archivelogs in standby" question
    User477708-OC Journeyer
    Currently Being Moderated
    Hemant K Chitale wrote:
    Oracle automatically deletes archivelogs only when it needs to clear space in the FRA (db_recovery_file_dest).


    Hemant K Chitale
    I had thought this so tested.
    SQL> SELECT
      2  substr(name, 1, 30) name,
      3  space_limit/(1073741824) AS Quota_GB,
      4  space_used/(1073741824) AS Used_GB,
      5  space_reclaimable/(1073741824) AS Reclaimable_GB,
      6  number_of_files AS files
      7  FROM
      8  v$recovery_file_dest ;
    
    NAME                             QUOTA_GB    USED_GB RECLAIMABLE_GB      FILES
    ------------------------------ ---------- ---------- -------------- ----------
    /u00/oracle/flash_recovery_are        310 19.7361012     18.1420259         46
    
    -- bring the db_recovery_dest_size to 20gb to so we know we're over 90%.  according to that first note I posted, the FRA deems anything over 85% as space pressure
    
    SQL>  alter system set db_recovery_file_dest_size=20g scope=both;
    
    System altered.
    
    SQL> SELECT
      2   substr(name, 1, 30) name,
      3   space_limit/(1073741824) AS Quota_GB,
      4   space_used/(1073741824) AS Used_GB,
      5   space_reclaimable/(1073741824) AS Reclaimable_GB,
      6   number_of_files AS files
      7   FROM
      8   v$recovery_file_dest ;
    
    NAME                             QUOTA_GB    USED_GB RECLAIMABLE_GB      FILES
    ------------------------------ ---------- ---------- -------------- ----------
    /u00/oracle/flash_recovery_are         20 19.7437358     18.1420259         47
    
    SQL> 
    So i waited a good ten minutes but still no extra logs cleared out.

    So I switched the logs a couple of times in production as well to see would that help and we did get some deleted then to bring the space used down to 17.5.gb which is just below the 85% mark.
    SQL> SELECT
      2   substr(name, 1, 30) name,
      3   space_limit/(1073741824) AS Quota_GB,
      4   space_used/(1073741824) AS Used_GB,
      5   space_reclaimable/(1073741824) AS Reclaimable_GB,
      6   number_of_files AS files
      7   FROM
      8   v$recovery_file_dest ;
    
    NAME                             QUOTA_GB    USED_GB RECLAIMABLE_GB      FILES
    ------------------------------ ---------- ---------- -------------- ----------
    /u00/oracle/flash_recovery_are         20 17.5075302     15.6462598         47
    
    SQL> 
    quick experiment, dropped to 15gb which is below the 17gb currently used. And yes immediately, I can see the files being deleted.

    so what I take from this is that if there is any space available in the 15% free then Oracle waits to be woken up by the receipt of a log from primary, sees that theres less than 15% free and deletes logs to below that limit. however, if the space free is less than the space used then oracle wakes up itself and deletes the logs. Cool.

    Thanks hemant.

    Edited to fix coding
  • 4. Re: Ye olde "delete archivelogs in standby" question
    User477708-OC Journeyer
    Currently Being Moderated
    Sorry, I was referring to standby side only.

Legend

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