4 Replies Latest reply: Apr 17, 2013 2:32 AM by Mahir M. Quluzade RSS

    Ye olde "delete archivelogs in standby" question

    User477708-OC
      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 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
            Mahir M. Quluzade
            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
              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
                Sorry, I was referring to standby side only.