Infrastructure at your Service

Franck Pachot

Archivelog deletion policy for Standby Database in Oracle Data Guard

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’s good practice! But did you ever check that it works as expected?

What I mean is this:

  • The archived logs that you don’t need are reclaimable by the FRA when space is needed
  • And the archived logs that are required for availability (standby or backup) are not deleted.

It’s not an easy thing to check because Oracle doesn’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’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’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.

Let’s look at an example I encountered recently. The archivelog deletion policy is set correctly:

RMAN> show archivelog deletion policy; 
 
RMAN configuration parameters for database with db_unique_name DATABASE_SITE2 are: 
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

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 ‘delete archivelog all;’ but I expect that the archivelogs in the FRA becomes reclaimable automatically.

Unfortunately, this is not the case and the FRA is growing:


SQL> select * from v$recovery_area_usage where file_type='ARCHIVED LOG'; 
 
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES 
-------------------- ------------------ ------------------------- --------------- 
ARCHIVED LOG                      61.11                     43.02             467

Let’s check everything. We are on the standby database:


SQL> select open_mode,database_role from v$database; 
 
OPEN_MODE            DATABASE_ROLE 
-------------------- ---------------- 
MOUNTED              PHYSICAL STANDBY

The archivelogs are going to the Fast Recovery Area:


SQL> show parameter log_archive_dest_1 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_ 
                                                 DEST, valid_for=(ALL_LOGFILES, 
                                                  ALL_ROLES) 

All archived logs are applied (we are in SYNC AFFIRM):


DGMGRL> show database 'DATABASE_SITE2'; 
 
Database - DATABASE_SITE2 
 
  Role:            PHYSICAL STANDBY 
  Intended State:  APPLY-ON 
  Transport Lag:   0 seconds 
  Apply Lag:       0 seconds 
  Real Time Query: OFF 
  Instance(s): 
    DATABASE 
  
Database Status: 
SUCCESS

Well, with that configuration, I expect that all archivelogs are reclaimable – except the current one.

Let’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.

So I’ll use it in in conjunction with v$archived_log in order to give the detail about which archived logs are reclaimable:


SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable
           ,count(*),min(sequence#),max(sequence#) 
     from v$archived_log left outer join sys.x$kccagf using(recid) 
     where is_recovery_dest_file='YES' and name is not null 
     group by applied,deleted,decode(rectype,11,'YES','NO') order by 5 
/ 
 
APPLIED   DELETED RECLAIMABLE   COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#) 
--------- ------- ----------- ---------- -------------- -------------- 
YES       NO      YES                429           5938           6366 
YES       NO      NO                  37           6367           6403 
IN-MEMORY NO      NO                   1           6404           6404

The problem is there: Because of a bug (Bug 14227959 : STANDBY DID NOT RELEASE SPACE IN FRA) the archivelogs are not marked as reclaimable when the database is in mount mode.

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 ‘delete obsolete’, so here is the way to call it from RMAN:

RMAN> sql "begin dbms_backup_restore.refreshagedfiles; end;";

But I’ve found another workaround: just run the CONFIGURE ARCHIVELOG POLICY from RMAN as it refreshes the reclaimable flag – even when there is no change.

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.

It’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;

This is a good way to prevent anyone from changing the configuration and keep it close to the backup scripts. At dbi services, 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.

Finally, here is the state of our reclaimable archivelogs after any of these solutions:


APPLIED   DELETED RECLAIMABLE   COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#) 
--------- ------- ----------- ---------- -------------- -------------- 
YES       NO      YES                466           5938           6403 
IN-MEMORY NO      NO                   1           6404           6404 
 
FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES 
-------------------- ------------------ ------------------------- --------------- 
ARCHIVED LOG                      61.11                     61.09             467

All applied archived logs are reclaimable and the FRA will never be full.
You 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.
Here is the full query I use for that:


column deleted format a7 
column reclaimable format a11 
set linesize 120 
select applied,deleted,backup_count 
 ,decode(rectype,11,'YES','NO') reclaimable,count(*) 
 ,to_char(min(completion_time),'dd-mon hh24:mi') first_time 
 ,to_char(max(completion_time),'dd-mon hh24:mi') last_time 
 ,min(sequence#) first_seq,max(sequence#) last_seq 
from v$archived_log left outer join sys.x$kccagf using(recid) 
where is_recovery_dest_file='YES' 
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#) 
/

This is the result on primary where the last archivelog backup has run around 21:00


APPLIED   DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME   LAST_TIME    FIRST_SEQ LAST_SEQ 
--------- ------- ------------ ----------- -------- ------------ ------------ --------- -------- 
NO        YES                1 NO               277 15-jan 17:56 19-jan 09:49      5936     6212 
NO        NO                 1 YES              339 19-jan 10:09 22-jan 21:07      6213     6516 
NO        NO                 0 NO                33 22-jan 21:27 23-jan 07:57      6517     6549

That is fine according to my policy APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK
And here is the result on standby where the workaround job has run around 06:00 and redo apply is in SYNC


APPLIED   DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME   LAST_TIME    FIRST_SEQ LAST_SEQ 
--------- ------- ------------ ----------- -------- ------------ ------------ --------- -------- 
YES       YES                0 NO               746 07-jan 13:27 17-jan 11:17      5320     6065 
YES       NO                 0 YES              477 17-jan 11:37 23-jan 05:37      6066     6542 
YES       NO                 0 NO                 8 23-jan 05:57 23-jan 08:14      6543     6550 
IN-MEMORY NO                 0 NO                 1 23-jan 08:15 23-jan 08:15      6551     6551

This is good for my policy APPLIED ON ALL STANDBY – except that because of the bug mentioned above, redo applied since 06:00 are not yet reclaimable.

Update SEP-17

When troubleshooting archivelog deletion policy issue, here is a better query which counts the number of backups for each sequence:


set linesize 200 pagesize 1000
column is_recovery_dest_file format a21
select
 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")
from (
select deleted,thread#,sequence#,status,name ,first_time, next_time,case x$kccagf.rectype when 11 then recid end  "x$kccagf"
,count(case archived when 'YES' then 'YES' end)over(partition by thread#,sequence#) archived
,count(case applied when 'YES' then 'YES' end)over(partition by thread#,sequence#) applied
,sum(backup_count)over(partition by thread#,sequence#) backup_count
,listagg(is_recovery_dest_file||':'||dest_id,',')within group(order by dest_id)over(partition by thread#,sequence#) is_recovery_dest_file
from v$archived_log left outer join sys.x$kccagf using(recid)
) group by deleted,status,is_recovery_dest_file,thread#,archived,applied,backup_count
order by max(sequence#),min(sequence#),thread#,deleted desc,status;

With the following output:


DEL S IS_RECOVERY_DEST_FILE    THREAD# MIN(SEQUENCE#) MAX(SEQUENCE#) MIN(FIRST MAX(NEXT_ COUNT(DISTINCTSEQUENCE#)   ARCHIVED    APPLIED BACKUP_COUNT COUNT("X$KCCAGF")
--- - --------------------- ---------- -------------- -------------- --------- --------- ------------------------ ---------- ---------- ------------ -----------------
NO  A YES:1                          1           3233           3233 23-JUN-17 23-JUN-17                        1          1          0            1                 1
NO  A YES:1,NO:2                     1           3234           5387 23-JUN-17 21-JUL-17                     2154          2          1            1              2154
NO  A YES:1,NO:2                     1           5388          11596 21-JUL-17 10-OCT-17                     6209          2          1            0              6208
NO  A YES:1,NO:2                     1          11597          11597 10-OCT-17 10-OCT-17                        1          2          0            0                 0

53 Comments

  • jko says:

    Tested and validated:-)
    Cheers
    jko

  • jun lu says:

    “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.”
    how to do this? a shell script?

  • jun lu,
    usually we have a job that check the database role, and do the backup depending on the role. Then when you do the backup you also do ‘APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK’ and in the other cases you just do ‘CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY’

  • Rick chen says:

    if the daily database&archivelog backup are executed on primary database, then on the standby site, just do ‘CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY’ cannot remove the applied archivelog files on standby site. should any other delete operations be taken on standby site ?
    Thanks
    Rick

  • Hi Rick,
    Yes. The archived logs should become ‘reclaimable’ once applied. But you may encounter the bug above where the ‘reclaimable’ status is not refreshed automatically on a database in mount. You have either to exec dbms_backup_restore.refreshagedfiles; or to run the ‘configure archivelog deletion policy’ again.

  • Rick Chen says:

    Thanks Franck,
    ++1.My DG is in oracle12c(12.1.0.1), is this bug fixed in oracle12c ?
    ++2.The daily backup is executed in primary site, there are many archive log files in standby site. I run ‘CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY’ manually on standby site, no any archived log file being deleted. this is my problem. is there any other delete operation required on standby site?
    Regards and thanks,
    Rick

  • Hi rick,
    As far as i know that bug is fixed in 12c and with your configuration you should not need any delete operation on standby site. You can use my query above to check which files (and whether they were applied).
    you can have all detail with the query in: https://www.dbi-services.com/index.php/blog/entry/drilling-down-vrecoveryareausage
    Regards,
    Franck.

  • Rick CHEN says:

    Thanks Franck,
    It doesnot work in my DG. Daily backup is on primary site. query on bote sites:
    –On Primary
    SQL> show parameter db_recover

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_recovery_file_dest string /u01/app/oracle/fast_recovery_
    area
    db_recovery_file_dest_size big integer 4800M
    SQL> select * from v$recovery_area_usage;

    FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
    ———————– —————— ————————- ————— ———-
    CONTROL FILE .21 0 1 0
    REDO LOG 7.29 0 7 0
    ARCHIVED LOG 1.81 1.81 12 0
    BACKUP PIECE .43 .22 2 0
    IMAGE COPY 0 0 0 0
    FLASHBACK LOG 0 0 0 0
    FOREIGN ARCHIVED LOG 0 0 0 0
    AUXILIARY DATAFILE COPY 0 0 0 0

    8 rows selected.

    SQL> select sequence#, name, applied, deleted, IS_RECOVERY_DEST_FILE, backup_count, standby_dest from v$archived_log where sequence# between 2005 and 2014;

    SEQUENCE# NAME APPLIED DEL IS_ BACKUP_COUNT STA
    ———- —————————————————————————————- ——— — — ———— —
    2005 sbdb YES NO NO 0 YES
    2005 NO YES YES 0 NO
    2006 sbdb YES NO NO 0 YES
    2006 NO YES YES 0 NO
    2007 sbdb YES NO NO 0 YES
    2007 NO YES YES 0 NO
    2008 sbdb YES NO NO 0 YES
    2008 NO YES YES 0 NO
    2009 sbdb YES NO NO 0 YES
    2009 NO YES YES 0 NO
    2010 sbdb YES NO NO 0 YES
    2010 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2010_b6nbzjyq_.arc NO NO YES 1 NO
    2011 sbdb YES NO NO 0 YES
    2011 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2011_b6nc6mtq_.arc NO NO YES 1 NO
    2012 sbdb YES NO NO 0 YES
    2012 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2012_b6nc6n6c_.arc NO NO YES 1 NO
    2013 sbdb YES NO NO 0 YES
    2013 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2013_b6nc6s33_.arc NO NO YES 1 NO
    2014 sbdb YES NO NO 0 YES
    2014 /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_11_18/o1_mf_1_2014_b6nz59ml_.arc NO NO YES 0 NO

    20 rows selected.

    SQL> select database_role, open_mode, current_scn, db_unique_name from v$database;

    DATABASE_ROLE OPEN_MODE CURRENT_SCN DB_UNIQUE_NAME
    —————- ——————– ———– ——————————
    PRIMARY READ WRITE 32384354 prod

    SQL>

    –On standby:
    SQL> show parameter db_recover

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_recovery_file_dest string /home/oracle/fra
    db_recovery_file_dest_size big integer 4800M
    SQL> select * from v$recovery_area_usage;

    FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
    ———————– —————— ————————- ————— ———-
    CONTROL FILE .26 0 1 0
    REDO LOG 7.29 0 7 0
    ARCHIVED LOG 5.95 0 54 0
    BACKUP PIECE .52 .26 2 0
    IMAGE COPY 0 0 0 0
    FLASHBACK LOG 0 0 0 0
    FOREIGN ARCHIVED LOG 0 0 0 0
    AUXILIARY DATAFILE COPY 0 0 0 0

    8 rows selected.

    SQL> select sequence#, name, applied, deleted, IS_RECOVERY_DEST_FILE, backup_count, standby_dest from v$archived_log where sequence# between 2005 and 2014;

    SEQUENCE# NAME APPLIED DEL IS_ BACKUP_COUNT STA
    ———- ———————————————————————— ——— — — ———— —
    2005 /home/oracle/fra/SBDB/archivelog/2014_11_17/o1_mf_1_2005_b6mrj0xb_.arc YES NO YES 0 NO
    2006 /home/oracle/fra/SBDB/archivelog/2014_11_17/o1_mf_1_2006_b6mrv5wl_.arc YES NO YES 0 NO
    2007 /home/oracle/fra/SBDB/archivelog/2014_11_17/o1_mf_1_2007_b6mrxzl8_.arc YES NO YES 0 NO
    2008 /home/oracle/fra/SBDB/archivelog/2014_11_17/o1_mf_1_2008_b6ms1d1l_.arc YES NO YES 0 NO
    2009 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2009_b6nbzjp5_.arc YES NO YES 0 NO
    2010 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2010_b6nbzky2_.arc YES NO YES 0 NO
    2011 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2011_b6nc6nqn_.arc YES NO YES 0 NO
    2012 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2012_b6nc6o6b_.arc YES NO YES 0 NO
    2013 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2013_b6nc6t1j_.arc YES NO YES 0 NO
    2014 /home/oracle/fra/SBDB/archivelog/2014_11_18/o1_mf_1_2014_b6nz5bh1_.arc YES NO YES 0 NO

    10 rows selected.

    SQL> select database_role, open_mode, current_scn, db_unique_name from v$database;

    DATABASE_ROLE OPEN_MODE CURRENT_SCN DB_UNIQUE_NAME
    —————- ——————– ———– ——————————
    PHYSICAL STANDBY READ ONLY WITH APPLY 32384482 sbdb

    SQL>

  • Rick CHEN says:

    Continued:
    After RMAN backup&delete taken in primary site, all archived log files (applied on all standby) have been deleted, but those files on standby site havenot, still there. checked and confirmed on both DB and OS (on standby site).

    Regards,
    Rick CHEN

  • Rick CHEN says:

    –On Primary:
    SQL> select applied,deleted,decode(rectype,11,’YES’,’NO’) reclaimable,
    count(*),min(sequence#),max(sequence#)
    2 3 from v$archived_log left outer join sys.x$kccagf using(recid)
    4 where is_recovery_dest_file=’YES’ and name is not null
    5 group by applied,deleted,decode(rectype,11,’YES’,’NO’) order by 5;

    APPLIED DEL REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    ——— — — ———- ————– ————–
    NO NO YES 11 2010 2020
    NO NO NO 1 2021 2021

    SQL>

    –On Standby
    SQL> select applied,deleted,decode(rectype,11,’YES’,’NO’) reclaimable,
    count(*),min(sequence#),max(sequence#)
    2 3 from v$archived_log left outer join sys.x$kccagf using(recid)
    4 where is_recovery_dest_file=’YES’ and name is not null
    5 group by applied,deleted,decode(rectype,11,’YES’,’NO’) order by 5;

    APPLIED DEL REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    ——— — — ———- ————– ————–
    YES NO NO 53 1968 2020
    IN-MEMORY NO NO 1 2021 2021

    SQL>

  • Hi Rick,
    Thanks to have given all information.
    From that it seems that there is still a bug. Another bug because the one I was talking about is only when database is mount. Archivelog that have APPLIED=YES should become reclaimable when ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY. You have only one standby (no cascading), right? I think you can open a SR with that.

  • Rick CHEN says:

    Thanks Franck,
    ++1. Yes, only one standby standby site, and no any cascading standby site.
    ++2. May I make it clear, is it on the standby site or primar site, to execute “CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY” ?
    ++3. Let me change standby DB to mount status with MRP started, and check rectype of archived log files on standby site.
    Regards,
    Rick CHEN

  • It’s on the standby site that you ever re-run the configure or executed the refreshagedfiles, as a workaround for the bug

  • Rick CHEN says:

    –On standby site:
    restart standby database to mounted status and start MRP process
    –On primary site:
    several archive log current operations.

    then check again:
    –Primary site:
    SQL> select applied,deleted,decode(rectype,11,’YES’,’NO’) reclaimable,
    count(*),min(sequence#),max(sequence#)
    2 3 from v$archived_log left outer join sys.x$kccagf using(recid)
    4 where is_recovery_dest_file=’YES’ and name is not null
    5 group by applied,deleted,decode(rectype,11,’YES’,’NO’) order by 5;

    APPLIED DEL REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    ——— — — ———- ————– ————–
    NO NO YES 18 2010 2027
    NO NO NO 1 2028 2028

    SQL>

    –standby site:
    SQL> select applied,deleted,decode(rectype,11,’YES’,’NO’) reclaimable,
    2 count(*),min(sequence#),max(sequence#)
    3 from v$archived_log left outer join sys.x$kccagf using(recid)
    4 where is_recovery_dest_file=’YES’ and name is not null
    5 group by applied,deleted,decode(rectype,11,’YES’,’NO’) order by 5;

    APPLIED DEL REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    ——— — — ———- ————– ————–
    YES NO NO 60 1968 2027
    IN-MEMORY NO NO 1 2028 2028

    SQL>

  • Rick CHEN says:

    Thanks Franck,
    On standby site re-run configure manually , it works.
    Now the question is how to delete all those applied&reclaimable archived log files on standby site.
    When backup and delete archivelog all on primary, it seems no affact on standby site. Reclaimable archived log files have NOT been deleted on standby site.
    –On Primary Site:
    SQL> select applied,deleted, rectype, decode(rectype,11,’YES’,’NO’) reclaimable, count(*),min(sequence#),max(sequence#)
    2 from v$archived_log left outer join sys.x$kccagf using(recid)
    where is_recovery_dest_file=’YES’ and name is not null
    3 4 group by applied,deleted,rectype, decode(rectype,11,’YES’,’NO’)
    5 order by 6;

    APPLIED DEL RECTYPE REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    ——— — ———- — ———- ————– ————–
    NO NO 11 YES 3 2079 2081
    NO NO NO 2 2082 2083

    SQL>
    –On Standby Site:
    SQL> select applied,deleted, rectype, decode(rectype,11,’YES’,’NO’) reclaimable, count(*),min(sequence#),max(sequence#)
    2 from v$archived_log left outer join sys.x$kccagf using(recid)
    3 where is_recovery_dest_file=’YES’ and name is not null
    4 group by applied,deleted,rectype, decode(rectype,11,’YES’,’NO’)
    5 order by 6;

    APPLIED DEL RECTYPE REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    ——— — ———- — ———- ————– ————–
    YES NO 11 YES 113 1968 2080
    YES NO NO 2 2081 2082
    IN-MEMORY NO NO 1 2083 2083

    SQL>

  • Hi, I’ve read only quickly and will come back on it (I’m currently attending DOAG) but it seems that it works. Once files are flagged as reclaimable then they will be deleted once oracle needs space on the FRA.

  • Rick CHEN says:

    Yes, it does.
    Waiting for oracle delete once space needed on FRA is a passive solution.
    Is there any active solution to delete those reclaimable archived logs to release free space ? especially if it is on the primary site with RMAN backup.

    Regards,
    Rick

  • Hi Rick,
    Yes there is. The delete archivelog (without FORCE) from rman should delete only the reclaimable archived logs. But you usually don’t need that. The big advantage of the Fast Recovery Area is that the files are managed by Oracle. You just have to adapt monitoring to monitor v$recovery_area_usage.
    Personally, I find the ‘passive solution’ term a bit pejorative, as it is the exactly what’s cool in the feature: you keep files as long you don’t need to delete them. Then faster when you need to recover from them.

  • Rick Chen says:

    Thanks Franck,
    Let’s change active/passive solution to other words. how about “RMAN script manual management” and “Oracle server automatic management”
    I wonder how we can delete those reclaimable archived logs of standby site by using “RMAN script management” on primary site ?
    You know, sometime we just want to manage archived logs on both sites, e.g. by “RMAN script” , what is the specific delete statement ?
    Regards and thanks,
    Rick Chen

  • Rick Chen says:

    Sometimes with a group of transactions generating many archived logs, shipped and applied on standby site, and are reclaimable. With monitoring v$reocvery_area_usage most space are used, but we are not sure when those space will be reclaimed by oracle server.
    In the test DG testing environments, waiting more than one hour, all reclaimable archived logs have not been deleted yet by oracle server automatically.
    SQL> select * from v$recovery_area_usage;

    FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
    ———————– —————— ————————- ————— ———-
    CONTROL FILE .6 0 1 0
    REDO LOG 17.09 0 7 0
    ARCHIVED LOG 59.87 59.87 44 0
    BACKUP PIECE .61 0 1 0
    IMAGE COPY 0 0 0 0
    FLASHBACK LOG 0 0 0 0
    FOREIGN ARCHIVED LOG 0 0 0 0
    AUXILIARY DATAFILE COPY 0 0 0 0

    8 rows selected.

    SQL> select applied,deleted, rectype, decode(rectype,11,’YES’,’NO’) reclaimable, count(*),min(sequence#),max(sequence#)
    2 from v$archived_log left outer join sys.x$kccagf using(recid)
    3 where is_recovery_dest_file=’YES’ and name is not null
    4 group by applied,deleted,rectype, decode(rectype,11,’YES’,’NO’)
    5 order by 6;

    APPLIED DEL RECTYPE REC COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
    ——— — ———- — ———- ————– ————–
    YES NO 11 YES 43 2120 2162
    NO NO NO 1 2163 2163

    SQL>

  • I still say that you don’t have to delete archivelogs because they are managed by oracle. That’s the reason for FRA and deletion policy.
    If you want to manage standby archived logs from the primary you have to use a rman catalog and play with the CHANGE ARCHIVELOG … DB_UNIQUE_NAME:
    https://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta009.htm#sthref530

  • RIck CHEN says:

    Thanks a lot Franck.
    I agree to use FRA and RMAN deletion policy to manage standby site archived logs automatically. On the other hand, “RMAN scripts management” is ready but will not been used in routine archived logs management.
    Really much appreciated.
    Regards,
    Rick

  • Pankaj says:

    Hi
    I have 4 standby database in which if I had differed two standby database is it still delete archives on primary and two standby database is in sync.

  • Hi Pankaj,
    You can have a standby and maintain a gap. Then deletion will depend on the policy which can be ‘shipped to all standby’ – then archived logs can be deleted from primary even when not applied yet- or ‘applied to all standby’.
    Regards,
    Franck.

  • Pravan says:

    Hi Frank,

    Has you tested this with Active Data Guard where the database is “READ ONLY WITH APPLY” instead of “mounted” ?

    Thanks

  • Hi Pavan,
    Good question as MOS says it’s because of mount state. I’ve not nested and have no ADG in 11.2.0.3 currently to test. Let see if twitter can help:
    https://twitter.com/FranckPachot/status/609225513928949761
    Regards,
    Franck.

  • Sam says:

    I got the same archived log management problem in standby db.

    For executing the following statement at standby database …

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

    … we must set at least one of the standby databases to ‘mandatory’.

    If it is mandatory, any connection problem between primary db and standby db may affect primary db operation!

    Is it true?

  • Hi Sam,
    You don’t need to set the standby destination as mandatory. The protection mode (max performance/availability/protection), in addition to the timeout properties, will determine how the primary behaves when the standby is not available.
    Regards,
    Franck.

  • venkateswarlu says:

    hi frank,
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

    i have one standby database, plan to enable archive log deletion policy. i need one clarification is it mandate to use “BACKED UP 1 TIMES TO Disk” clause in standby database ?
    And after applying archive logs on standby, immediately it delete’s applied logs or not? if it is not when it will happen ?

    Thanks ,
    Venkat.

  • Hi Venkat,
    If you define APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK the archived logs will be reclaimable once they are applied and backed up from the Standby. So this is ok of you do the backups from the Standby. But backups done from the primary will not be seen there. They can be seen from RMAN connected to catalog only.
    When they become reclaimable, the are deleted only when space is needed, so you don’t see it. Check (and monitor) V$RECOVERY_AREA_USAGE to be sure that non-reclaimable files do not fill the FRA.
    Regards,
    Franck.

  • Vasanth says:

    Hi Frank,

    First of all, thanks much for posting this write up.

    If I understand correctly, configuring “CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED ON ALL STANDBY” will mark the archive logs in standby database as reclaimable “YES” once its applied.So FRA can delete those marked logs and reclaim space when required(FRA does this automatically in oracle 12c, I hope).

    My question is, how do we delete the archive logs in the active(primary) instance ? I want to delete the archive logs in the active once its copied to the standby server.
    Considering our application have quick db growth with restricted FRA size, we have clear archive logs every 2 hours. Currently we are doing like below:
    find /opt/oracle/base/fast_recovery_area/$UPPER_DBID/archivelog -type f -mmin +120 -delete
    crosscheck archivelog all;
    delete noprompt expired archivelog all;
    So, we are seeing whether the “”CONFIGURE ARCHIVELOG DELETION POLICY” can be used in active instance as well.
    Please suggest.

    • Vasanth says:

      One correction, we are using “delete noprompt archivelog until time “sysdate-2/24″;” and not “delete noprompt expired archivelog all;” currently.

  • Hi Vasanth,
    Yes, you understand correctly about the standby.
    For the primary, you can do the same: CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED ON ALL STANDBY will mark them reclaimable as soon as they are applied on the standby.
    Then you don’t need to delete them.
    Note that it means that you are doing no backups at all, right?
    Regards,
    Franck.

    • Vasanth says:

      Hi Franck,

      Thanks much for your quick response.
      Yes, currently we dont do backup of these logs and I understand that, this is with no backups.

      Thanks,
      Vasanth

  • venkateswarlu says:

    Thanks for your response

  • Max says:

    Thanks. Very useful. Was about to write a scheduled job in EM to delete the archive logs .

  • Gerrit Haase says:

    Hello Franck,

    thanks, still a useful post.

    What I wonder about is, if you are not using RMAN at all, how is the FRA archivelog retention/deletion policy configured then? Is it really required to use RMAN for this, or can I do it with Data Guard as well?

    Regards,
    Gerrit

    • Hi Gerrit,
      If you don’t backup your archived logs with RMAN, then you cannot use the ‘BACKED UP … TIMES TO …’ part of the policy because Oracle does not know which you backed up. Using only the ‘… ON ALL STANDBY’ policy risks to delete archived logs that are not backed up in case of space pressure in the FRA. Maybe you can have two destinations for archived logs: one to FRA managed by ‘… ON ALL STANDBY’ policy and another one you manage yourself (backup with your tool, delete with rman command). Or better: use RMAN to backup archived logs elsewhere.
      Regards,
      Franck.

  • Dennis H says:

    Hello Franck…just wanted to say thanks for this post! It has been very helpful in solving the exact issue you post on here. I was wrestling with it for a couple of days. I had to create the Data Guard standby involved using the DGMGRL utility since the Cloud Control application had trouble doing due to the fact that the primary database INSTANCE_NAME parm is in upper case, but the DB_NAME parm is in lower case. Creating this outside the Cloud Control required some manual intervention, including your solution to change the ARCHIVELOG DELETION POLICY. Thanks!

  • Sham G says:

    Dear Franck Pachot ,

    Thanks for good post.
    We have Active DG setup (12.1.0.2)

    I found one more way .. but i am not sure whether it is good or NOT ?

    I set “_log_deletion_policy”=ALL on Primary and Standby

    SQL> alter system set “_log_deletion_policy”=ALL scope=spfile;
    ..
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
    ..

    Above 2 commands were configured on both servers ( Primary and Standby)
    Still archives are NOT deleted automatically, until unless space pressure occurs.
    Please have a look and provide me solution.

    Thanks in advance.

  • Hi Sham,
    AFAIK this parameter was for 10g and is about non mandatory destinations.
    Regards,
    Franck.

  • Lokman says:

    I hit a similar problem recently. Set up:

    19c (19.8) Physical Standby configured to use fra.
    Backups run in Primary database.
    In Primary: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO ‘SBT_TAPE’;
    In Standby: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
    Standby is always in-sync with primary to within a few log sequence numbers.

    Yet when the standby fra utilization approached 100%, no archived logs were being automatically deleted.

    Doc ID 2022958.1 says this is caused by a deferred log archive destination. In my case, the standby had been set with a value for log_archive_dest_2 and log_archive_dest_state_2 was ‘DEFER’. The intention was to enable log_archive_dest_2 upon activation of this database into a new primary.

    Once log_archive_dest_2 was reset to an empty string, the standby fra started auto-deleting un-needed archived logs again.

  • Hi Lokman,
    Thanks for pointing this out here, this kind of behaviour change is always good to know.
    >> The intention was to enable log_archive_dest_2 upon activation of this database into a new primary.
    Did you try to address this with VALID_FOR=(ALL_LOGFILES,STANDBY_ROLE) in log_archive_dest_2?
    Franck.

  • Frank Li says:

    Franck:

    I always read your articles and have learned a lot from you. For this archivelog deletion from standby database issue. My situation is different. I would like to get some advice from you.

    1. We have Primary and standby configuration. But we do not use data guard. Archivelog files are stored in ASM disk device for primary and shipped to a NFS diskspace on standby. We configure RMAN archivelog deletion as :
    RMAN configuration parameters for database with db_unique_name VBAEDW1_DR are:
    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
    archivelog files will be deleted with script only they are applied to standby and also be backed up twice. This part is OK. My question is on standby, all shipped archivelog files are stored on NFS disk on stansby database. After we synch standby with primary, we need to manually delete these archivelog files from NFS disk. Our primary and standby DB use the same RMAN catalog DB. When we connect to target (standby) and catalog DB and issue delete archivelog until ‘sysdate -3’; It always come up as: RMAN-06214: Archivelog /dataarchdw02/EDW1/arch/edw1_1_26530_1050168673.arc. Some archivelog files are not valid by crosscheck command. Under this situation, should I only CONNECT TO TARGET (standby) DB and no catalog DB connection. This is will only use standby controlfile to delete the archivelog files from NFS disk. I am sorry to put this post so long. Thanks

  • Hi Frank Li,
    If I understood correctly (I’ve read it quickly) here is my guess. RMAN considers that files with same name, on device type tape, are the same when seen from primary or standby. But when device type is DISK (your case) they are considered different. Then maybe you have to change the db unique name they are associated with (RMAN> CHANGE DB_UNIQUE_NAME … FROM … TO) in order to delete them from the standby.
    But you say that you don’t use Data Guard. I guess you are in standard edition and apply the logs with the recover command. I don’t think RMAN knows they are applied and then you can’t use the deletion policy.

  • Frank Li says:

    Franck:
    Thank you so much for your input. You are correct. Our DB is 12.1.0.2 with 189TB. Last year, they hired consultant to do database migration from M5 Solaris to M8 Solaris. They only install Grid home as version 18c, Oracle Home are still 12.1.0.2. Then they use ASM and configured RAC three nodes for production. Also they configured primary and standby in RAC. When they created the standby, I don’t know which method they used. But they left many uncleared files either on system or on backup sbt_tape. We took over the database in the end of 2020. Now My job is to clean up these leftovers. Due to database size, any RMAN maintenance job will take long time and generated huge log files. I separated DB backup and DB maintenance cronjob as two part. DB maintenance job always pump up these RMAN configuration parameters for database with db_unique_name VBAEDW1_DR. Here VBAEDW1_DR is standby db_unique_name. But I tried to clean all files with standby db_unique_name on standby. It didn’t work because standby do not have RMAN backup DB or archivelogs. All backup executed from primary. If I changed db_unique_name on primary for these files, will it cause any problem on Primary because this is prod DB. I must be very careful. Thanks for your help.

  • Frank Li, ok I understand. It is hard to say there’s no harm. To the database itself, sure there is not. But you don’t want mess-up the backup availability for a 190TB database for sure. Maybe you can try this CHANGE command on a very old backup, only one, and check what is visible or not from both side (RESTORE…PREVIEW to check it could be restored). the best would be to define small sets of files (on tag, or completion time) so that you don’t have to run this maintenance job on the whole until all the cleanup is done. Then It may even be possible to run it from the prod instance. Those are just ideas… can’t say more especially with critical huge prod behind.

  • Frank Li says:

    Franck:

    Thank a lot. I will take your approach to test in small files. I have got a lot of information and real solution on Oracle DB before. I will continue to read your articles and posts.

  • Frank Li says:

    Franck:

    As we discussed change db_unique_name for old backup set or archived logs in last post, these old backups or logs was generated during database migration. The backups or logs even do not exist. But they still recorded in either control file or catalog database. So If I un-register target database from RMAN catalog and register this database back to catalog. Then new registration will be in a new repository. In this way, crosscheck backup or archivelog will not hit to these old backup sets or archived logs. Is this correct and is it a good approach to resolve my problems with database maintenance errors? Thanks

  • Frank, this is ok if what is in the controlfile is sufficient for you. You can also catalog what remains, just in case.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Data Hero
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod