This was first published on https://blog.dbi-services.com/archivelog-deletion-policy-on-data-guard-configuration (2017-10-22)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
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
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
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 3This looks good. I have some archived logs that have been applied but not backed up yet.
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 3Nothing is recoverable here, wich is in my opinion a bug.
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 3This 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’.
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://blog.dbi-services.com/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard/. When more archived log detail is required, I’ve also this query: https://blog.dbi-services.com/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.
Thanks for the details with example.
When Im setting this archive deletion policy (applied on standby plus backup to tape/disk in either order), getting invalid method error.
RMAN-08591: WARNING: invalid archived log deletion policy