Deleting Archive Log files in a Data Guard environment

Deleting Archive Log files in a Data Guard environment

Published on: Category: Oracle

We recently configured an Oracle 12.2 database environment with a primary database, and a physical standby database managed by Oracle Data Guard.

The backup on the primary database removed all archive log files after a successful backup. For a normal standalone database this is a common configuration, but in a Data Guard environment this is not sufficient.

What will happen if your standby database is unavailable? Before you delete any files on the primary database, you should be sure you no longer need them for the recovery of the standby database.     

Initial configuration

When you use the Data Guard Broker for the configuration of your standby database, it will create a remote archive destination on the primary, connecting to a service on the standby database:

We scheduled backups on both the primary and the standby database. Part of the RMAN backup script was the cleanup of all archive log files after a day, if there was a successful backup:

When to remove archive log files?

Our backup script was not really bulletproof. Although we kept our archive log files for at least a day, this is not really what we wanted. The files should only be removed after we are sure all transaction had been successfully applied to the standby database.

When querying the v$archived_log view for log_archive_dest_2 we can check what files are applied on the standby:

Why not let Oracle handle it?

Rather than changing the backup script it might be easier to let the database handle it. All files in the FRA could be managed by the database.

The documentation describes how we can configure automatic cleanup policy of the archive log files after they are applied on the standby database. We changed this setting in the RMAN backup configuration, and also removed the delete statement from the backup script.

Testing

After changing the RMAN configuration...

...and doing some log switches...

...we made another archive backup:

I first expected that the archive log files would have been removed at the moment of the backup, but this was not the case. The files were still in the FRA.

Maybe the files were not applied to the standby database? I checked the v$archived_log view again, but all the files - except the current - had been applied to the standby.

This was not really doing what I expected, so back to the documentation: “For primary databases, the archived redo log files are eligible for deletion after they are applied on the standby.”  So, the files are not deleted immediately, but they are eligible for deletion.

You can check this in the V$RECOVERY_AREA_USAGE view. We can see that some space for “Archived Log” is reclaimable, so eligible for deletion:

After more load on the database the FRA fills up to 85%, and this triggers a warning in the alert log:

This warning gives some suggestions on how to fix this issue. The first one is a bit strange. If we use Data Guard, we should consider changing the RMAN ARCHIVELOG DELETION POLICY. I think this is what we just did...

A little later the backup triggers another log switch, and we see more messages in the alert log. The database removes the oldest archive log files:

So, the automatic cleanup works, but only after space pressure in the FRA removes the files marked as eligible for deletion, as explained in the documentation. And the definition of space pressure seems to be an 85% usage of the FRA, which might trigger an ORA-19815 warning.

Back to manual cleanup?

Although the solution above does what it should do, I’m not really happy with this. The files are not really removed after being applied on the standby as the configuration suggests, and more serious; the ORA-19815 warning could trigger a daily monitoring alarm on a perfectly working database.

A good solution seems to be the configuration of the delete policy in RMAN, in combination with a daily “delete noprompt archivelog all” in the backup script.

For testing we first configure the policy:

Then, in the data guard manager, we stop the apply on the standby database:

After that, we make some log switches:

We can check that the new archives are not applied on the standby database:

Next, we make a backup in RMAN:

And, as part of the backup procedure, we try to delete the archivelog files:

The files are still protected against a delete. As we can see the statement triggers a RMAN-08120 warning, because the transactions are not yet applied on the standby.

When restoring the redo apply on the standby, the delete from RMAN is also working again.

Other Setups

In this example we used a Maximum Performance protection mode in Data Guard. Our goal was to apply all transactions on the standby as soon as possible, but with a minimal impact on the primary database.

Data Guard can also be used in various other configurations. You can configure an “apply delay”, which can protect you for user errors on the standby database. Another configuration is the Snapshot Standby database, where you temporarily stop the redo apply on the standby, and open the database for testing purposes.

In both these cases your policy could check if the transactions are shipped to, but not applied on the standby database:

Conclusion

The “CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;” setting in RMAN is a good protection for the archive log files in a Data Guard environment where the standby is used for disaster recovery. It protects the files from being deleted before all the transactions are applied on the standby.

This setting also configures an automatic cleanup of the archive log files from the FRA. But files might only be removed after a confusing ORA-19815 warning.

A better solution is not to wait for the automatic cleanup, but to delete the files as part of the backup procedure.

If, for some reason, the transactions are not applied on the standby, the delete command triggers a RMAN-08120 warning, and the archived log files are not deleted. And if the FRA is filling up with a ORA-19815 warning, the automatic cleanup will still remove all files that are eligible for deletion.  

Documentation: https://docs.oracle.com/database/122/RCMRF/CONFIGURE.htm#GUID-B5094E73-C26C-4FED-AE39-8C2E9540050A__CHDIFEEE

Bastiaan Bak
About the author Bastiaan Bak

DBA with over 15 years of experience. Experience in various branches, with several modules. Including: Oracle database, Oracle RAC, Oracle EBS and PL/SQL.

More posts by Bastiaan Bak
Comments
Reply