This was first published on https://blog.dbi-services.com/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard (2014-01-28)
Republishing here for new followers. The content is related to the the versions available at the publication date
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:
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 6404FILE_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.
Tested and validated:-) Cheers jko
“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’
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
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: http://www.dbi-services.com/index.php/blog/entry/drilling-down-vrecoveryareausage Regards, Franck.
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>
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
–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.
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
–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>
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.
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.
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;
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
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 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.
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.
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.
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.
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.
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.
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
Thanks for your response
Nice post!
Thanks. Very useful. Was about to write a scheduled job in EM to delete the archive logs .
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.