Deleting Archive Log files in a Data Guard environment
Back to overview

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:

  1. SQL> SHOW parameter log_archive_dest_2
  2.  
  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. log_archive_dest_2                   string      service="motdc1", ASYNC NOAFFI
  6.                                                 RM delay=0 optional compressio
  7.                                                 n=disable max_failure=0 max_co
  8.                                                 nnections=1 reopen=300 db_uniq
  9.                                                 ue_name="motdc1" net_timeout=3
  10.                                                 0, valid_for=(online_logfile,a
  11.                                                 ll_roles)

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:

  1. backup as compressed backupset archivelog all not backed up;
  2. delete noprompt archivelog until time 'sysdate - 1' backed up 1 times to device type disk;

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:

  1. select dest_id , sequence# , applied
  2. from v$archived_log
  3. where dest_id = 2
  4. and  sequence#  > ( select max(sequence#) - 10 from  v$archived_log )
  5. order by sequence#
  6. /
  7.  
  8.   DEST_ID  SEQUENCE# APPLIED
  9. ---------- ---------- ---------
  10.         2       1912 YES
  11.         2       1913 YES
  12.         2       1914 YES
  13.         2       1915 YES
  14.         2       1916 YES
  15.         2       1917 YES
  16.         2       1918 YES
  17.         2       1919 YES
  18.         2       1920 YES
  19.         2       1921 NO
  20.  
  21. 10 rows selected.

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

  1. RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY ;
  2.  
  3. old RMAN configuration parameters:
  4. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
  5. new RMAN configuration parameters:
  6. CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
  7. new RMAN configuration parameters are successfully stored
  8. starting full resync of recovery catalog
  9. full resync complete
  10.  
  11. RMAN>

...and doing some log switches...

  1. SQL>  ALTER system switch logfile ;
  2.  
  3. System altered.
  4.  
  5. SQL>  ALTER system switch logfile ;
  6.  
  7. System altered.
  8.  
  9. SQL>  ALTER system switch logfile ;
  10.  
  11. System altered.
  12.  

...we made another archive backup:

  1. RMAN> backup as compressed backupset archivelog all not backed up;

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.

  1. SQL> SELECT dest_id , SEQUENCE# , applied
  2. FROM v$archived_log
  3. WHERE dest_id = 2
  4. AND  SEQUENCE#  > ( SELECT MAX(SEQUENCE#) - 10 FROM  v$archived_log )
  5. ORDER BY SEQUENCE#
  6. /
  7.  2    3    4    5    6
  8.   DEST_ID  SEQUENCE# APPLIED
  9. ---------- ---------- ---------
  10.         2       1930 YES
  11.         2       1931 YES
  12.         2       1932 YES
  13.         2       1933 YES
  14.         2       1934 YES
  15.         2       1935 YES
  16.         2       1936 YES
  17.         2       1937 YES
  18.         2       1938 YES
  19.         2       1939 NO
  20.  
  21. 10 ROWS selected.

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:

  1. SQL> SELECT FILE_TYPE , PERCENT_SPACE_USED , PERCENT_SPACE_RECLAIMABLE , NUMBER_OF_FILES
  2.  2  FROM  V$RECOVERY_AREA_USAGE
  3.  3  /
  4.  
  5. FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
  6. ----------------------- ------------------ ------------------------- ---------------
  7. CONTROL FILE                             0                         0               0
  8. REDO LOG                                 0                         0               0
  9. ARCHIVED LOG                           .43                       .43              13
  10. BACKUP PIECE                             0                         0               0
  11. IMAGE COPY                               0                         0               0
  12. FLASHBACK LOG                        65.63                         0               6
  13. FOREIGN ARCHIVED LOG                     0                         0               0
  14. AUXILIARY DATAFILE COPY                  0                         0               0
  15.  
  16. 8 ROWS selected.

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

  1. 2017-11-06T17:36:12.303898+01:00
  2. Errors in file /oraclebase/db/diag/rdbms/motdc2/motdc2/trace/motdc2_m000_24332.trc:
  3. ORA-19815: WARNING: db_recovery_file_dest_size of 17179869184 bytes is 87.50% used, and has 2147470336 remaining bytes available.
  4. 2017-11-06T17:36:12.308765 +01:00
  5. ************************************************************************
  6. You have following choices to free up space from recovery area:
  7. 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
  8.   then consider changing RMAN ARCHIVELOG DELETION POLICY.
  9. 2. Back up files to tertiary device such as tape using RMAN
  10.   BACKUP RECOVERY AREA command.
  11. 3. Add disk space and increase db_recovery_file_dest_size parameter to
  12.   reflect the new space.
  13. 4. Delete unnecessary files using RMAN DELETE command. If an operating
  14.   system command was used to delete files, then use RMAN CROSSCHECK and
  15.   DELETE EXPIRED commands.
  16. ************************************************************************

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:

  1. Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1927_f00bl4x2_.arc
  2. Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1928_f00cynk7_.arc
  3. Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1929_f00hh2z4_.arc
  4. Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1930_f00lzlvm_.arc
  5. Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1931_f00pj2n2_.arc
  6. Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1932_f00t0ltc_.arc
  7. Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1933_f00xk2p8_.arc
  8. Deleted Oracle managed file /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1934_f0111ltx_.arc

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:

  1. CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

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

  1. DGMGRL> edit database motdc1 set state='apply-off' ;
  2. Succeeded.

After that, we make some log switches:

  1. SQL> ALTER system switch logfile ;
  2.  
  3. System altered.
  4.  
  5. SQL>  ALTER system switch logfile ;
  6.  
  7. System altered.
  8.  
  9. SQL>  ALTER system switch logfile ;
  10.  
  11. System altered.

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

  1. SQL> SELECT dest_id , SEQUENCE# , applied
  2. FROM v$archived_log
  3. WHERE dest_id = 2
  4. AND  SEQUENCE#  > ( SELECT MAX(SEQUENCE#) - 10 FROM  v$archived_log )
  5. ORDER BY SEQUENCE#
  6. / 
  7.   DEST_ID  SEQUENCE# APPLIED
  8. ---------- ---------- ---------
  9.         2       1940 YES
  10.         2       1941 YES
  11.         2       1942 YES
  12.         2       1943 YES
  13.         2       1944 YES
  14.         2       1945 YES
  15.         2       1946 YES
  16.         2       1947 NO
  17.         2       1948 NO
  18.         2       1949 NO
  19.  
  20. 10 ROWS selected.

Next, we make a backup in RMAN:

  1. RMAN> backup as compressed backupset archivelog all not backed up;
  2.  
  3. Starting backup at 06-NOV-17
  4. current log archived
  5. using channel ORA_DISK_1
  6. using channel ORA_DISK_2
  7. using channel ORA_DISK_3
  8. using channel ORA_DISK_4
  9. skipping archived logs of thread 1 from sequence 1945 to 1946; already backed up
  10. channel ORA_DISK_1: starting compressed archived log backup set
  11. channel ORA_DISK_1: specifying archived log(s) in backup set
  12. input archived log thread=1 sequence=1947 RECID=4426 STAMP=959363627
  13. channel ORA_DISK_1: starting piece 1 at 06-NOV-17
  14. channel ORA_DISK_2: starting compressed archived log backup set
  15. channel ORA_DISK_2: specifying archived log(s) in backup set
  16. input archived log thread=1 sequence=1950 RECID=4432 STAMP=959363700
  17. channel ORA_DISK_2: starting piece 1 at 06-NOV-17
  18. channel ORA_DISK_3: starting compressed archived log backup set
  19. channel ORA_DISK_3: specifying archived log(s) in backup set
  20. input archived log thread=1 sequence=1948 RECID=4428 STAMP=959363642
  21. channel ORA_DISK_3: starting piece 1 at 06-NOV-17
  22. channel ORA_DISK_4: starting compressed archived log backup set
  23. channel ORA_DISK_4: specifying archived log(s) in backup set
  24. input archived log thread=1 sequence=1949 RECID=4430 STAMP=959363644
  25. channel ORA_DISK_4: starting piece 1 at 06-NOV-17
  26. channel ORA_DISK_2: finished piece 1 at 06-NOV-17
  27. piece handle=/orarman/db/MOTDC2/gnsitejl_1_1.bck tag=TAG20171106T175500 comment=NONE
  28. channel ORA_DISK_2: backup set complete, elapsed time: 00:00:00
  29. channel ORA_DISK_1: finished piece 1 at 06-NOV-17
  30. piece handle=/orarman/db/MOTDC2/gmsitejl_1_1.bck tag=TAG20171106T175500 comment=NONE
  31. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:00
  32. channel ORA_DISK_3: finished piece 1 at 06-NOV-17
  33. piece handle=/orarman/db/MOTDC2/gositejl_1_1.bck tag=TAG20171106T175500 comment=NONE
  34. channel ORA_DISK_3: backup set complete, elapsed time: 00:00:00
  35. channel ORA_DISK_4: finished piece 1 at 06-NOV-17
  36. piece handle=/orarman/db/MOTDC2/gpsitejl_1_1.bck tag=TAG20171106T175500 comment=NONE
  37. channel ORA_DISK_4: backup set complete, elapsed time: 00:00:00
  38. Finished backup at 06-NOV-17
  39.  
  40. Starting Control File and SPFILE Autobackup at 06-NOV-17
  41. piece handle=/orarman/db/MOTDC2/c-390140924-20171106-10 comment=NONE
  42. Finished Control File and SPFILE Autobackup at 06-NOV-17
  43.  
  44. RMAN>

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

  1. RMAN> delete noprompt archivelog all backed up 1 times to device type disk;
  2.  
  3. released channel: ORA_DISK_1
  4. released channel: ORA_DISK_2
  5. released channel: ORA_DISK_3
  6. released channel: ORA_DISK_4
  7. allocated channel: ORA_DISK_1
  8. channel ORA_DISK_1: SID=2822 device type=DISK
  9. allocated channel: ORA_DISK_2
  10. channel ORA_DISK_2: SID=4234 device type=DISK
  11. allocated channel: ORA_DISK_3
  12. channel ORA_DISK_3: SID=4467 device type=DISK
  13. allocated channel: ORA_DISK_4
  14. channel ORA_DISK_4: SID=4705 device type=DISK
  15. RMAN-08120: warning: archived log not deleted, not yet applied by standby
  16. archived log file name=/orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1946_f014trpk_.arc thread=1 sequence=1946
  17. RMAN-08120: warning: archived log not deleted, not yet applied by standby
  18. archived log file name=/orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1947_f014wv4x_.arc thread=1 sequence=1947
  19. RMAN-08120: warning: archived log not deleted, not yet applied by standby
  20. archived log file name=/orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1948_f014xblo_.arc thread=1 sequence=1948
  21. RMAN-08120: warning: archived log not deleted, not yet applied by standby
  22. archived log file name=/orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1949_f014xdo5_.arc thread=1 sequence=1949
  23. RMAN-08120: warning: archived log not deleted, not yet applied by standby
  24. archived log file name=/orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1950_f014z3z6_.arc thread=1 sequence=1950
  25. List of Archived Log Copies for database with db_unique_name MOTDC2
  26. =====================================================================
  27.  
  28. Key     Thrd Seq     S Low Time
  29. ------- ---- ------- - ---------
  30. 1300755 1    1945    A 06-NOV-17
  31.        Name: /orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1945_f014l36l_.arc
  32.  
  33. deleted archived log
  34. archived log file name=/orafra/db/MOTDC2/archivelog/2017_11_06/o1_mf_1_1945_f014l36l_.arc RECID=4422 STAMP=959363283
  35. Deleted 1 objects

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:

  1. RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY ;

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 (8)
  1. om 08:08

    Thanks for your Quality information the product developed by the company is perfect.Thanks for giving such a wonderful blog. https://www.erptree.com/course/oracle-r12-financials-training-in-ameerpet-hyderabad/

    Reageer
  2. om 09:09

    Thanks Bastiaan! This blog helped me.

    Reageer
  3. om 06:06

    Thanks for informative article
    I wonder if behavior is same when using shipped option like below

    CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

    Reageer
    1. om 09:09

      Thank you. The "shipped" option can be used in situations where we do not apply the redo on the standby database immediately.

      2 situations when the redo is shipped to the standby, but not applied are :
      - DELAY=minutes attribute of the LOG_ARCHIVE_DEST_n initialization parameter
      - when you convert the standby database to a snapshot standby

      So, then you can choose to create a policy that deletes the archives from the primary when they are shipped to the standby , but not yet applied. The rest of the behavior should be the same indeed.

  4. om 11:11

    Very useful information ..Great

    Reageer
  5. om 12:12

    Does FRA retention target db_flashback_retention_target plays part in this delayed removal ?

    What if we want to set threshold for archive logs deletion(which are already applied on standby DB) ? is there any way we can do that ?

    Reageer
  6. om 14:02

    Thank you so much for sharing this well written article with all - very objectively presented indeed!

    God Bless!

    Reageer
  7. om 15:03

    Thank You so much for such a detailed and simple explanation with examples.

    Reageer
Reply