{"id":3561,"date":"2014-01-27T22:56:00","date_gmt":"2014-01-27T21:56:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/"},"modified":"2014-01-27T22:56:00","modified_gmt":"2014-01-27T21:56:00","slug":"archivelog-deletion-policy-for-standby-database-in-oracle-data-guard","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/","title":{"rendered":"Archivelog deletion policy for Standby Database in Oracle Data Guard"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nDo you use ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY for your Oracle databases in Data Guard? Maybe you also use the Fast Recovery Area as archive log destination. That&#8217;s good practice! But did you ever check that it works as expected?<\/p>\n<p>What I mean is this:<\/p>\n<ul>\n<li>The archived logs that you don&#8217;t need are reclaimable by the FRA when space is needed<\/li>\n<li>And the archived logs that are required for availability (standby or backup) are not deleted.<\/li>\n<\/ul>\n<p>It&#8217;s not an easy thing to check because Oracle doesn&#8217;t show which archive log is reclaimable. Only the total reclaimable space is shown in v$recovery_area_usage. But that is not sufficient to validate which archivelog sequence is concerned. I&#8217;ll show you below a query that returns the reclaimable status from the archived logs. And you will see that until 12c the APPLIED ON ALL STANDBY does not work as expected. You&#8217;ve probably seen a FRA full at standby site and solved it by deleting archived logs. But this is not the right solution because the FRA is supposed to do that.<\/p>\n<p>Let&#8217;s look at an example I encountered recently. The archivelog deletion policy is set correctly:<\/p>\n<pre><code>RMAN&gt;\u00a0show\u00a0archivelog\u00a0deletion\u00a0policy; \n&nbsp;\nRMAN\u00a0configuration\u00a0parameters\u00a0for\u00a0database\u00a0with\u00a0db_unique_name\u00a0DATABASE_SITE2\u00a0are: \nCONFIGURE\u00a0ARCHIVELOG\u00a0DELETION\u00a0POLICY\u00a0TO\u00a0APPLIED\u00a0ON\u00a0ALL\u00a0STANDBY;<\/code><\/pre>\n<p>This configuration, an 11g feature, allows to delete an archive log as soon as it is applied to all standby destinations. Note that it works if I manually do a &#8216;delete archivelog all;&#8217; but I expect that the archivelogs in the FRA becomes reclaimable automatically.<\/p>\n<p>Unfortunately, this is not the case and the FRA is growing:<\/p>\n<pre><code>\nSQL&gt;\u00a0select\u00a0*\u00a0from\u00a0v$recovery_area_usage\u00a0where\u00a0file_type='ARCHIVED\u00a0LOG'; \n&nbsp;\nFILE_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0PERCENT_SPACE_USED\u00a0PERCENT_SPACE_RECLAIMABLE\u00a0NUMBER_OF_FILES \n--------------------\u00a0------------------\u00a0-------------------------\u00a0--------------- \nARCHIVED\u00a0LOG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a061.11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a043.02\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0467\n<\/code><\/pre>\n<p>Let&#8217;s check everything. We are on the standby database:<\/p>\n<pre><code>\nSQL&gt;\u00a0select\u00a0open_mode,database_role\u00a0from\u00a0v$database; \n&nbsp;\nOPEN_MODE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DATABASE_ROLE \n--------------------\u00a0---------------- \nMOUNTED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0PHYSICAL\u00a0STANDBY\n<\/code><\/pre>\n<p>The archivelogs are going to the Fast Recovery Area:<\/p>\n<pre><code>\nSQL&gt;\u00a0show\u00a0parameter\u00a0log_archive_dest_1 \nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0VALUE \n------------------------------------\u00a0-----------\u00a0------------------------------ \nlog_archive_dest_1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0string\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0location=USE_DB_RECOVERY_FILE_ \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0DEST,\u00a0valid_for=(ALL_LOGFILES, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ALL_ROLES) \n<\/code><\/pre>\n<p>All archived logs are applied (we are in SYNC AFFIRM):<\/p>\n<pre><code>\nDGMGRL&gt;\u00a0show\u00a0database\u00a0'DATABASE_SITE2'; \n&nbsp;\nDatabase\u00a0-\u00a0DATABASE_SITE2 \n&nbsp;\n\u00a0\u00a0Role:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0PHYSICAL\u00a0STANDBY \n\u00a0\u00a0Intended\u00a0State:\u00a0\u00a0APPLY-ON \n\u00a0\u00a0Transport\u00a0Lag:\u00a0\u00a0\u00a00\u00a0seconds \n\u00a0\u00a0Apply\u00a0Lag:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0seconds \n\u00a0\u00a0Real\u00a0Time\u00a0Query:\u00a0OFF \n\u00a0\u00a0Instance(s): \n\u00a0\u00a0\u00a0\u00a0DATABASE \n&nbsp; \nDatabase\u00a0Status: \nSUCCESS\n<\/code><\/pre>\n<p>Well, with that configuration, I expect that all archivelogs are reclaimable &#8211; except the current one.<\/p>\n<p>Let&#8217;s investigate. V$RECOVERY_AREA_USAGE is an aggregate view. If we check its definition, we see that the reclaimable size comes from x$kccagf.rectype.<\/p>\n<p>So I&#8217;ll use it in in conjunction with v$archived_log in order to give the detail about which archived logs are reclaimable:<\/p>\n<pre><code>\nSQL&gt;\u00a0select\u00a0applied,deleted,decode(rectype,11,'YES','NO')\u00a0reclaimable\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0,count(*),min(sequence#),max(sequence#) \n\u00a0 \u00a0 \u00a0from\u00a0v$archived_log\u00a0left\u00a0outer\u00a0join\u00a0sys.x$kccagf\u00a0using(recid)\u00a0\n\u00a0 \u00a0 \u00a0where\u00a0is_recovery_dest_file='YES'\u00a0and\u00a0name\u00a0is\u00a0not\u00a0null \n\u00a0 \u00a0 \u00a0group\u00a0by\u00a0applied,deleted,decode(rectype,11,'YES','NO')\u00a0order\u00a0by\u00a05 \n\/ \n&nbsp;\nAPPLIED\u00a0\u00a0\u00a0DELETED\u00a0RECLAIMABLE\u00a0\u00a0\u00a0COUNT(*)\u00a0MIN(SEQUENCE#)\u00a0MAX(SEQUENCE#) \n---------\u00a0-------\u00a0-----------\u00a0----------\u00a0--------------\u00a0-------------- \nYES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0YES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0429\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05938\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06366 \nYES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a037\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06367\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06403 \nIN-MEMORY\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06404\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06404\n<\/code><\/pre>\n<p>The problem is there: Because of a bug (<a href=\"https:\/\/support.oracle.com\/epmos\/faces\/BugDisplay?id=14227959\">Bug 14227959 : STANDBY DID NOT RELEASE SPACE IN FRA<\/a>) the archivelogs are not marked as reclaimable when the database is in mount mode.<\/p>\n<p>The workaround is to execute dbms_backup_restore.refreshagedfiles. This is what must be scheduled (maybe daily) on the standby. It can be a good idea to do it at the same time as a daily &#8216;delete obsolete&#8217;, so here is the way to call it from RMAN:<\/p>\n<pre><code>RMAN&gt;\u00a0sql\u00a0\"begin\u00a0dbms_backup_restore.refreshagedfiles;\u00a0end;\";<\/code><\/pre>\n<p>But I&#8217;ve found another workaround: just run the CONFIGURE ARCHIVELOG POLICY from RMAN as it refreshes the reclaimable flag &#8211; even when there is no change.<\/p>\n<p>Then, you can run a daily job that does CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY on your database, whatever the role is.<\/p>\n<p>It&#8217;s different for the database where you do the backup, because you want to be sure that the backup is done before an archivelog is deleted: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;<\/p>\n<p>This is a good way to prevent anyone from changing the configuration and keep it close to the backup scripts. At <a href=\"https:\/\/www.dbi-services.com\">dbi services<\/a>, we advise to keep the same configuration on all Data Guard sites for the same database so that a switchover can be done without any problem. For this reason, having a script that depends on the place where the backups are done is a better alternative than a configuration that depends on the database role.<\/p>\n<p>Finally, here is the state of our reclaimable archivelogs after any of these solutions:<\/p>\n<pre><code>\nAPPLIED\u00a0\u00a0\u00a0DELETED\u00a0RECLAIMABLE\u00a0\u00a0\u00a0COUNT(*)\u00a0MIN(SEQUENCE#)\u00a0MAX(SEQUENCE#) \n---------\u00a0-------\u00a0-----------\u00a0----------\u00a0--------------\u00a0-------------- \nYES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0YES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0466\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05938\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06403 \nIN-MEMORY\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06404\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06404 \n \nFILE_TYPE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0PERCENT_SPACE_USED\u00a0PERCENT_SPACE_RECLAIMABLE\u00a0NUMBER_OF_FILES \n--------------------\u00a0------------------\u00a0-------------------------\u00a0--------------- \nARCHIVED\u00a0LOG\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a061.11\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a061.09\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0467\n<\/code><\/pre>\n<p>All applied archived logs are reclaimable and the FRA will never be full.<br \/>\nYou can check the primary as well. Are you sure that Oracle will never delete an archived log that has not been backed up ? Check your deletion policy.<br \/>\nHere is the full query I use for that:<\/p>\n<pre><code>\ncolumn deleted format a7 \ncolumn reclaimable format a11 \nset linesize 120 \nselect applied,deleted,backup_count \n\u00a0,decode(rectype,11,'YES','NO') reclaimable,count(*) \n\u00a0,to_char(min(completion_time),'dd-mon hh24:mi') first_time \n\u00a0,to_char(max(completion_time),'dd-mon hh24:mi') last_time \n\u00a0,min(sequence#) first_seq,max(sequence#) last_seq \nfrom v$archived_log left outer join sys.x$kccagf using(recid) \nwhere is_recovery_dest_file='YES' \ngroup by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#) \n\/\n<\/code><\/pre>\n<p>This is the result on primary where the last archivelog backup has run around 21:00<\/p>\n<pre><code>\nAPPLIED\u00a0\u00a0\u00a0DELETED\u00a0BACKUP_COUNT\u00a0RECLAIMABLE\u00a0COUNT(*)\u00a0FIRST_TIME\u00a0\u00a0\u00a0LAST_TIME\u00a0\u00a0\u00a0\u00a0FIRST_SEQ\u00a0LAST_SEQ \n---------\u00a0-------\u00a0------------\u00a0-----------\u00a0--------\u00a0------------\u00a0------------\u00a0---------\u00a0-------- \nNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0YES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0277\u00a015-jan\u00a017:56\u00a019-jan\u00a009:49\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05936\u00a0\u00a0\u00a0\u00a0\u00a06212 \nNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a0YES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0339\u00a019-jan\u00a010:09\u00a022-jan\u00a021:07\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06213\u00a0\u00a0\u00a0\u00a0\u00a06516 \nNO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a033\u00a022-jan\u00a021:27\u00a023-jan\u00a007:57\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06517\u00a0\u00a0\u00a0\u00a0\u00a06549\n<\/code><\/pre>\n<p>That is fine according to my policy APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK<br \/>\nAnd here is the result on standby where the workaround job has run around 06:00 and redo apply is in SYNC<\/p>\n<pre><code>\nAPPLIED\u00a0\u00a0\u00a0DELETED\u00a0BACKUP_COUNT\u00a0RECLAIMABLE\u00a0COUNT(*)\u00a0FIRST_TIME\u00a0\u00a0\u00a0LAST_TIME\u00a0\u00a0\u00a0\u00a0FIRST_SEQ\u00a0LAST_SEQ \n---------\u00a0-------\u00a0------------\u00a0-----------\u00a0--------\u00a0------------\u00a0------------\u00a0---------\u00a0-------- \nYES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0YES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0746\u00a007-jan\u00a013:27\u00a017-jan\u00a011:17\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05320\u00a0\u00a0\u00a0\u00a0\u00a06065 \nYES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0YES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0477\u00a017-jan\u00a011:37\u00a023-jan\u00a005:37\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06066\u00a0\u00a0\u00a0\u00a0\u00a06542 \nYES\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a08\u00a023-jan\u00a005:57\u00a023-jan\u00a008:14\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06543\u00a0\u00a0\u00a0\u00a0\u00a06550 \nIN-MEMORY\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00\u00a0NO\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a01\u00a023-jan\u00a008:15\u00a023-jan\u00a008:15\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06551\u00a0\u00a0\u00a0\u00a0\u00a06551\n<\/code><\/pre>\n<p style=\"padding-left: 00px\">This is good for my policy APPLIED ON ALL STANDBY &#8211; except that because of the bug mentioned above, redo applied since 06:00 are not yet reclaimable.<\/p>\n<h3>Update SEP-17<\/h3>\n<p>When troubleshooting archivelog deletion policy issue, here is a better query which counts the number of backups for each sequence:<\/p>\n<pre><code>\nset linesize 200 pagesize 1000\ncolumn is_recovery_dest_file format a21\nselect\n deleted,status,is_recovery_dest_file,thread#,min(sequence#),max(sequence#),min(first_time),max(next_time),count(distinct sequence#),archived,applied,backup_count,count(\"x$kccagf\")\nfrom (\nselect deleted,thread#,sequence#,status,name ,first_time, next_time,case x$kccagf.rectype when 11 then recid end  \"x$kccagf\"\n,count(case archived when 'YES' then 'YES' end)over(partition by thread#,sequence#) archived\n,count(case applied when 'YES' then 'YES' end)over(partition by thread#,sequence#) applied\n,sum(backup_count)over(partition by thread#,sequence#) backup_count\n,listagg(is_recovery_dest_file||':'||dest_id,',')within group(order by dest_id)over(partition by thread#,sequence#) is_recovery_dest_file\nfrom v$archived_log left outer join sys.x$kccagf using(recid)\n) group by deleted,status,is_recovery_dest_file,thread#,archived,applied,backup_count\norder by max(sequence#),min(sequence#),thread#,deleted desc,status;\n<\/code><\/pre>\n<p>With the following output:<\/p>\n<pre><code>\nDEL S IS_RECOVERY_DEST_FILE    THREAD# MIN(SEQUENCE#) MAX(SEQUENCE#) MIN(FIRST MAX(NEXT_ COUNT(DISTINCTSEQUENCE#)   ARCHIVED    APPLIED BACKUP_COUNT COUNT(\"X$KCCAGF\")\n--- - --------------------- ---------- -------------- -------------- --------- --------- ------------------------ ---------- ---------- ------------ -----------------\nNO  A YES:1                          1           3233           3233 23-JUN-17 23-JUN-17                        1          1          0            1                 1\nNO  A YES:1,NO:2                     1           3234           5387 23-JUN-17 21-JUL-17                     2154          2          1            1              2154\nNO  A YES:1,NO:2                     1           5388          11596 21-JUL-17 10-OCT-17                     6209          2          1            0              6208\nNO  A YES:1,NO:2                     1          11597          11597 10-OCT-17 10-OCT-17                        1          2          0            0                 0\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . Do you use ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY for your Oracle databases in Data Guard? Maybe you also use the Fast Recovery Area as archive log destination. That&#8217;s good practice! But did you ever check that it works as expected? What I mean is this: The archived logs [&hellip;]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[221,228,96],"type_dbi":[],"class_list":["post-3561","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-data-guard","tag-fast-recovery-area","tag-oracle"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Archivelog deletion policy for Standby Database in Oracle Data Guard - dbi Blog<\/title>\n<meta name=\"description\" content=\"Check which archive log are reclaimable, depending on the archivelog deletion policy.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Archivelog deletion policy for Standby Database in Oracle Data Guard\" \/>\n<meta property=\"og:description\" content=\"Check which archive log are reclaimable, depending on the archivelog deletion policy.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-01-27T21:56:00+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Archivelog deletion policy for Standby Database in Oracle Data Guard\",\"datePublished\":\"2014-01-27T21:56:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/\"},\"wordCount\":784,\"commentCount\":0,\"keywords\":[\"Data Guard\",\"Fast Recovery Area\",\"Oracle\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/\",\"name\":\"Archivelog deletion policy for Standby Database in Oracle Data Guard - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2014-01-27T21:56:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"Check which archive log are reclaimable, depending on the archivelog deletion policy.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Archivelog deletion policy for Standby Database in Oracle Data Guard\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Archivelog deletion policy for Standby Database in Oracle Data Guard - dbi Blog","description":"Check which archive log are reclaimable, depending on the archivelog deletion policy.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/","og_locale":"en_US","og_type":"article","og_title":"Archivelog deletion policy for Standby Database in Oracle Data Guard","og_description":"Check which archive log are reclaimable, depending on the archivelog deletion policy.","og_url":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/","og_site_name":"dbi Blog","article_published_time":"2014-01-27T21:56:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Archivelog deletion policy for Standby Database in Oracle Data Guard","datePublished":"2014-01-27T21:56:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/"},"wordCount":784,"commentCount":0,"keywords":["Data Guard","Fast Recovery Area","Oracle"],"articleSection":["Database Administration &amp; Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/","url":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/","name":"Archivelog deletion policy for Standby Database in Oracle Data Guard - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-01-27T21:56:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"Check which archive log are reclaimable, depending on the archivelog deletion policy.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Archivelog deletion policy for Standby Database in Oracle Data Guard"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3561","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=3561"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/3561\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=3561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=3561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=3561"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=3561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}