By Franck Pachot
.
The deletion policy on a dataguard configuration should be:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
for the site where you don’t backup. It can be the standby or the primary.
and:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
for the site where you do the backups. It can be the primary or the standby.
I’ve always configured it in this way, but I recently discovered that the order of the subclause matters. Do not CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK APPLIED ON ALL STANDBY; because no archivelogs will be reclaimable, and your recovery area will be full. This is probably a bug. I’ll update this post when I have more information about this.
Test case
I’m on the standby database where I do the backups:
Recovery Manager: Release 12.2.0.1.0 - Production on Sun Oct 22 17:37:18 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
RMAN-06568: connected to target database: CDB2 (DBID=693620717, not open)
RMAN>
RMAN-03029: echo set on
I start with all default configuration:
RMAN> show all;
RMAN-06607: RMAN configuration parameters for database with db_unique_name CDB2B are:
RMAN-01005: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
RMAN-01005: CONFIGURE BACKUP OPTIMIZATION OFF; # default
RMAN-01005: CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN-01005: CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
RMAN-01005: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
RMAN-01005: CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
RMAN-01005: CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
RMAN-01005: CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
RMAN-01005: CONFIGURE MAXSETSIZE TO UNLIMITED; # default
RMAN-01005: CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
RMAN-01005: CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
RMAN-01005: CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
RMAN-01005: CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
RMAN-01005: CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
RMAN-01005: CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_CDB2B.f'; # default
I have one full backup and then some archivelog backups:
RMAN> list backup summary;
RMAN-06345: List of Backups
RMAN-06346: ===============
RMAN-06347: Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
RMAN-06348: ------- -- -- - ----------- --------------- ------- ------- ---------- ---
RMAN-06349: 9 B F A DISK 22-OCT 17:05:16 1 1 NO TAG20171022T170250
RMAN-06349: 10 B F A DISK 22-OCT 17:06:23 1 1 NO TAG20171022T170250
RMAN-06349: 11 B F A DISK 22-OCT 17:07:18 1 1 NO TAG20171022T170250
RMAN-06349: 12 B F A DISK 22-OCT 17:07:35 1 1 NO TAG20171022T170732
RMAN-06349: 13 B A A DISK 22-OCT 17:15:09 1 1 NO TAG20171022T171502
RMAN-06349: 14 B F A DISK 22-OCT 17:15:19 1 1 NO TAG20171022T171518
RMAN-06349: 15 B A A DISK 22-OCT 17:27:28 1 1 NO TAG20171022T172721
RMAN-06349: 16 B F A DISK 22-OCT 17:27:39 1 1 NO TAG20171022T172737
I have no obsolete backups:
RMAN> report obsolete;
RMAN-06524: RMAN retention policy will be applied to the command
RMAN-06511: RMAN retention policy is set to redundancy 1
RMAN-06147: no obsolete backups found
APPLIED ON ALL STANDBY
I have 1.8% of the recovery area that has been applied:
RMAN> configure archivelog deletion policy to applied on all standby;
RMAN-06601: new RMAN configuration parameters:
RMAN-01005: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
RMAN-06604: new RMAN configuration parameters are successfully stored
RMAN> select percent_space_used,percent_space_reclaimable,number_of_files from v$recovery_area_usage where file_type='ARCHIVED LOG';
RMAN-01005: PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
RMAN-01005: ------------------ ------------------------- ---------------
RMAN-01005: 1.3 1.08 3
BACKED UP 1 TIMES TO DISK
I have 1.3% of the recovery area that has been backed up:
RMAN> configure archivelog deletion policy to backed up 1 times to disk;
RMAN-06600: old RMAN configuration parameters:
RMAN-01005: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
RMAN-06601: new RMAN configuration parameters:
RMAN-01005: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
RMAN-06604: new RMAN configuration parameters are successfully stored
RMAN> select percent_space_used,percent_space_reclaimable,number_of_files from v$recovery_area_usage where file_type='ARCHIVED LOG';
RMAN-01005: PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
RMAN-01005: ------------------ ------------------------- ---------------
RMAN-01005: 1.3 1.3 3
This looks good. I have some archived logs that have been applied but not backed up yet.
Both in the ‘bad’ order
But now I want to combine both:
RMAN> configure archivelog deletion policy to backed up 1 times to disk applied on all standby;
RMAN-06600: old RMAN configuration parameters:
RMAN-01005: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
RMAN-06601: new RMAN configuration parameters:
RMAN-01005: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK APPLIED ON ALL STANDBY;
RMAN-06604: new RMAN configuration parameters are successfully stored
RMAN> select percent_space_used,percent_space_reclaimable,number_of_files from v$recovery_area_usage where file_type='ARCHIVED LOG';
RMAN-01005: PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
RMAN-01005: ------------------ ------------------------- ---------------
RMAN-01005: 1.3 0 3
Nothing is recoverable here, wich is in my opinion a bug.
Both in the ‘right’ order
Trying the same but with different order:
RMAN> configure archivelog deletion policy to applied on all standby backed up 1 times to disk;
RMAN-06600: old RMAN configuration parameters:
RMAN-01005: CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK APPLIED ON ALL STANDBY;
RMAN-06601: new RMAN configuration parameters:
RMAN-01005: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
RMAN-06604: new RMAN configuration parameters are successfully stored
RMAN> select percent_space_used,percent_space_reclaimable,number_of_files from v$recovery_area_usage where file_type='ARCHIVED LOG';
RMAN-01005: PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
RMAN-01005: ------------------ ------------------------- ---------------
RMAN-01005: 1.3 1.08 3
This look good. 1.08% have been applied and backed up.
I’ve verified the same behavior when backups are done on primary, or on standby, and same in 11.2.0.4, 12.1 and 12.2 with latest RU.
When the deletion policy is starting with the backup clause before the standby clause, the files are not marked as reclaimable. However, they are deleted with a ‘delete archivelog’ statement without the ‘force’ option.
The behavior is the same with ‘shipped to’ instead of ‘applied on’.
So what?
Be careful with the syntax: the ‘applied’ or ‘shipped’ clause must be written before the ‘backup’ one.
Check that archived logs are reclaimable. The query I use for that is in: https://www.dbi-services.com/blog/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard/. When more archived log detail is required, I’ve also this query: https://www.dbi-services.com/blog/drilling-down-vrecoveryareausage/
Always have a look at the recovery area usage after you change the deletion policy, the site where you backup, or after a switchover.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/ALN_web-min-scaled.jpg)