Archivelog deletion policy for Standby Database in Oracle Data Guard

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:

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.

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
/

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:

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:

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’

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.

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

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).

–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;

–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;

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.

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

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;

–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;

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;

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.

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.

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.

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

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;

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;

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

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.

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.

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 ?

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.

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.

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.

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?

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.

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!