Infrastructure at your Service

Franck Pachot

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:

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           6404

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

 

34 Comments

  • jko says:

    Tested and validated:-)
    Cheers
    jko

     
  • jun lu says:

    “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’

     
  • Rick chen says:

    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.

     
  • Rick Chen says:

    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.

     
  • Rick CHEN says:

    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>

     
  • Rick CHEN says:

    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

     
  • Rick CHEN says:

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

     
  • Rick CHEN says:

    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

     
  • Rick CHEN says:

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

     
  • Rick CHEN says:

    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.

     
  • Rick CHEN says:

    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.

     
  • Rick Chen says:

    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

     
  • Rick Chen says:

    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

     
  • RIck CHEN says:

    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

     
  • Pankaj says:

    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.

     
  • Pravan says:

    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.

     
  • Sam says:

    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.

     
  • venkateswarlu says:

    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.

     
  • Vasanth says:

    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.

     
    • Vasanth says:

      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.

     
    • Vasanth says:

      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

       

Leave a Reply


3 × nine =

Franck Pachot
Franck Pachot

Technology Leader