Remember to delete archivelogs from obsolete incarnations after a flashback database operation

I have recently been supporting a customer by setting guaranteed restore points and using these to flash the database back on their signal, typically when they need to redeploy some code for their application.

It's easy to forget that every time you open the database with the RESETLOG options, you create another incarnation of your database.
Here is what my flash recovery area looked like:

cd /fra/SALESDB/archivelogs
du -sh *
300M    2020_01_30
36G     2020_02_03
248M    2020_02_04
1.5G    2020_02_05
273M    2020_02_06
284M    2020_02_07
332M    2020_02_08
3.7G    2020_02_09
510G    2020_02_11
15G     2020_02_12
1.1G    2020_02_13
386M    2020_02_14
237M    2020_02_15
3.7G    2020_02_16
14G     2020_02_17
523G    2020_02_18
1.5G    2020_02_19
208M    2020_02_20
213M    2020_02_21
239M    2020_02_22
3.6G    2020_02_23
217M    2020_02_24
293M    2020_02_25
1.5G    2020_02_26
258M    2020_02_27
261M    2020_02_28
296M    2020_02_29
3.6G    2020_03_01
507G    2020_03_02
216M    2020_03_03
13G     2020_03_04
214M    2020_03_05
211M    2020_03_06
237M    2020_03_07
3.6G    2020_03_08
288M    2020_03_09
244M    2020_03_10
1.6G    2020_03_11
16G     2020_03_12
504G    2020_03_13

The latest incarnation was created on the 12.03.2020:

RMAN> list incarnation of database;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SALESDB 1626528189       PARENT  270012427233 30.01.2020 11:39:03
2       2       SALESDB 1626528189       PARENT  270012433324 30.01.2020 11:51:46
3       3       SALESDB 1626528189       PARENT  270020068677 11.02.2020 11:00:20
4       4       SALESDB 1626528189       PARENT  270020175869 13.02.2020 12:02:52
5       5       SALESDB 1626528189       PARENT  270020238995 18.02.2020 10:50:25
6       6       SALESDB 1626528189       PARENT  270020500540 02.03.2020 13:51:53
7       7       SALESDB 1626528189       CURRENT 270020646622 12.03.2020 14:18:33

This situation caused the flash recovery area to fill, since Oracle wasn't able to delete archivelogs from the previous incarnation.

I solved this by deleting archivelogs from before the last incarnation was created.

First, list the logs you want to delete:

rman target / nocatalog log=list_archlogs.txt'
RMAN> list archivelog all completed before "to_date('12.03.2020 14:18:33')";

When I check the file, these were all older logs, residing in folders dated before the last incarnation was created.

I then deleted them as follows:

delete archivelog all completed before "to_date('12.03.2020 14:18:33')";

The size of the flash recovery area has now dropped to 17% and the file listing of /fra/SALESDB/archivelogs now shows empty folders:

4.0K    2020_01_30
8.0K    2020_02_03
4.0K    2020_02_04
4.0K    2020_02_05
4.0K    2020_02_06
4.0K    2020_02_07
4.0K    2020_02_08
16K     2020_02_09
48K     2020_02_11
4.0K    2020_02_12
4.0K    2020_02_13
4.0K    2020_02_14
4.0K    2020_02_15
4.0K    2020_02_16
4.0K    2020_02_17
52K     2020_02_18
4.0K    2020_02_19
4.0K    2020_02_20
4.0K    2020_02_21
4.0K    2020_02_22
4.0K    2020_02_23
4.0K    2020_02_24
4.0K    2020_02_25
4.0K    2020_02_26
4.0K    2020_02_27
4.0K    2020_02_28
4.0K    2020_02_29
4.0K    2020_03_01
52K     2020_03_02
4.0K    2020_03_03
4.0K    2020_03_04
4.0K    2020_03_05
4.0K    2020_03_06
4.0K    2020_03_07
4.0K    2020_03_08
4.0K    2020_03_09
4.0K    2020_03_10
4.0K    2020_03_11
15G     2020_03_12
504G    2020_03_13

How to work around RMAN-08120: warning: archived log not deleted, not yet applied by standby

Problem scenario:

* You have a primary database with two standby databases.
* One of them is functional, the other one is about to be rebuilt.
* Archived redo logs are piling up on the primary.

You have already set the log_archive_dest_n to DEFERRED on the primary site, and this will prevent logs from being shipped to the standby database that is about to be rebuilt.

The reason for this is explained in Doc ID 1380368.1 on My Oracle Support: Oracle will, by default, also consider deferred locations, before deleting archive logs from the primary.

Solution:
This can be changed by setting a hidden parameter, _deferred_log_dest_is_valid, from TRUE to FALSE.

To verify that this worked as intended, I listed a couple of the archivelogs on the primary:

RMAN> list archivelog sequence between 110880 and 110881;

List of Archived Log Copies for database with db_unique_name PRODDB01
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
173550  1    110880  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc

173551  1    110881  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc

Now try to delete them from the primary server:

RMAN> delete archivelog sequence between 110880 and 110881;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=78 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=723 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=14 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=295 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc thread=1 sequence=110880
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc thread=1 sequence=110881

Oracle doesn't allow you to delete them, since they haven't been applied to all standby destinations.

To work around this, set the hidden parameter:

alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;

Try deleting them once more shows that Oracle now allows you to do so:

RMAN>  delete archivelog sequence between 110880 and 110881;

List of Archived Log Copies for database with db_unique_name proddb01
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
173550  1    110880  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc

173551  1    110881  A 19-JUL-19
        Name: /fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110880__2rnqx83t_.arc RECID=173550 STAMP=1014076575
Deleted 1 objects

deleted archived log
archived log file name=/fra/proddb01/archivelog/2019_07_19/o1_mf_1_110881__2rrrp5ml_.arc RECID=173551 STAMP=1014076710
Deleted 1 objects

Use

delete noprompt archivelog sequence between 110880 and 110881;

to avoid having to confirm the delete operation.

Overview over archivelogs present in the FRA

The following query reveals that my FRA contains archived logs from the last 3 days only (indicated by an "A" - available).

-- Set your NLS_DATE_FORMAT in your session to avoid ORA-01830: date format picture ends before converting entire input string
alter session set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

select trunc(completion_time),status,count(*)
from v$archived_log
where completion_time > to_date('20.06.2018 00:00:00')
group by trunc(completion_time),status
order by 1 desc;

Output:

TRUNC(COMPLETION_TIME)	STATUS	COUNT(*)
29.06.2018	A	37
28.06.2018	A	57
27.06.2018	A	54
27.06.2018	D	1
26.06.2018	D	75
25.06.2018	D	75
24.06.2018	D	53
23.06.2018	D	55
22.06.2018	D	59
21.06.2018	D	58
20.06.2018	D	54

Archive log files prior to these, have been deleted (indicated by a "D") by rman after having been successfully backed up.

log_archive_format default value

Having trouble finding out why the value of the parameter LOG_ARCHIVE_FORMAT does not seem to apply to your database?

From Oracle 10g and onwards, the default value for LOG_ARCHIVE_FORMAT is %t_%S_%r.dbf

However, this parameter will only make a difference if you are NOT using a "Fast Recovery Area" (in Version 10 Oracle called the same thing a "Flash Recovery Area")

If you have defined a FRA by using the parameters db_recovery_file_dest and db_recovery_file_dest_size, the archivelogs will be automatically named according to OMF (Oracle Managed Files) standard and the value of LOG_ARCHIVE_FORMAT will be ignored.

An example of an OMF managed file in the FRA:

/fra/PRODDB01/archivelog/2014_06_30/o1mv_1_1_9vxzch5_.arc

From the documentation:

"The fast recovery area is an Oracle Database managed space that can be used to hold RMAN disk backups, control file autobackups and archived redo log files. The files placed in this location are maintained by Oracle Database and the generated file names are maintained in Oracle Managed Files (OMF) format."

At first glance, letting the LOG_ARCHIVE_FORMAT default to such a poor naming standard may seem like a bad idea, but I guess it illustrates the point of defining an FRA vs. using conventional archive log destinations.

ksh: script that will send alert when flash recovery are is filling up

#!/usr/bin/ksh
# Vegard Kasa
# 11.07.2013
#
# Alert when FRA is filling up...
#############################################################################################################
export NOW=`date +\%d.\%m.\%y`
export NLS_DATE_FORMAT='DD.MM.YYYY'
####################################################################
# Set the script base to either $DBA_ADMIN in case of original build
# or $DBA_ADMIN_HOME in case of newer build.
####################################################################
if [ -n "${DBA_ADMIN}" ]; then
 ##############
 # Original build
 ##############
 SCRIPT_BASE=$DBA_ADMIN
elif [ -n "${DBA_ADMIN_HOME}" ]; then
 #################
 # Another build
 # uses different
 # variable names
 #################
 SCRIPT_BASE=${DBA_ADMIN_HOME}
fi


cat << EoF > ${SCRIPT_BASE}/sql/get_sum_fra.sql
set termout   off
set trimspool on
set verify    off
set feedback  off
set echo      off
set linesize  200
set heading   off
set pagesize  0
spool ${SCRIPT_BASE}/log/alert_fra_full_${NOW}.log
SELECT    LTRIM(sum(PERCENT_SPACE_USED))
FROM      V\$FLASH_RECOVERY_AREA_USAGE;
exit
EoF
sqlplus -s / as sysdba @${SCRIPT_BASE}/sql/get_sum_fra.sql
##########################################################
# Send the output via e-mail to the designated receipients
##########################################################
PRC=`cat ${SCRIPT_BASE}/log/alert_fra_full_${NOW}.log`
if [ ${PRC} -gt 80 ]; then
 echo "Warning: In database ${ORACLE_SID}, the FRA is ${PRC} percent used!"  |mailx -r monitoring@yourdomain.com -s "FRA usage report for database ${ORACLE_SID}" dba@yourdomain.com
fi
exit

How the Archived Redo Log Deletion Policy works

Archived redo logs can be deleted automatically by the database or as a result of user-initiated RMAN commands. Note that *only* logs in the flash recovery area can be deleted automatically by the database.

You can use RMAN to create a persistent configuration that governs when archived redo logs are eligible for deletion from disk, by using the ARCHIVELOG DELETION POLICY. The archived redo log deletion policy is configured to NONE by default.

When the Archived Redo Log Deletion Policy Is ENABLED

You can use the CONFIGURE ARCHIVELOG DELETION POLICY command to specify when archived redo logs are eligible for deletion. This deletion policy applies to all archiving destinations, including the flash recovery area.

For archived redo log files in the flash recovery area, the database retains them as long as possible and automatically deletes eligible logs [only] when additional disk space is required.

You can also manually delete eligible logs from any location, whether inside or outside the flash recovery area, when you issue BACKUP ... DELETE INPUT or DELETE ARCHIVELOG.

When the Archived Redo Log Deletion Policy Is DISABLED

In this case, RMAN considers archived redo log files in the recovery area as eligible for deletion if they meet both of the following conditions:

1. The archived redo logs, whether in the flash recovery area or outside of it, have been transferred to the required remote destinations specified by LOG_ARCHIVE_DEST_n.
2. The archived redo logs have been backed up at least once to disk or SBT or the logs are obsolete according to the backup retention policy.

The backup retention policy considers logs obsolete only if the logs are not needed by a guaranteed restore point and the logs are not needed by Oracle Flashback Database.


Source: Oracle Documentation

How does Oracle manage disk space in the Flash Recovery Area?

Files in the recovery area are permanent or transient.

Permanent files are active files used by the database instance (like control files).
All files that are not permanent are transient.

In general, Oracle Database eventually deletes transient files after they become obsolete under the backup retention policy or have been backed up to tape.

Space in the flash recovery area is balanced among backups and archived logs that must be kept according to the retention policy, and other files which may be subject to deletion.
Oracle Database does not delete eligible files from the flash recovery area until the space must be reclaimed for some other purpose.
Thus, files recently moved to tape are often still available on disk for use in recovery. The recovery area can thus serve as a cache for tape.
When the flash recovery area is full, Oracle Database automatically deletes eligible files to reclaim space in the recovery area as needed.

The following rules govern when files become eligible for deletion from the recovery area:
• Permanent files are never eligible for deletion.
• Files that are obsolete under the retention policy are eligible for deletion.
• Transient files that have been copied to tape are eligible for deletion.
• Archived redo logs are not eligible for deletion until all the consumers of the logs have satisfied their requirements. Consumers of logs can include RMAN, standby databases, Oracle Streams databases, and the Flashback Database feature.

The safe and reliable way to control deletion of files from the flash recovery area is to configure your retention policy and archived log deletion policy.
To increase the likelihood that files moved to tape are retained on disk, increase the flash recovery area quota.

Source: Oracle Documentation

How to instruct Oracle to use the flash recovery area as archive log destination

Find out what the current setting is:

sqlplus / as sysdba
SQL> show parameter db_recovery
NAME                         TYPE          VALUE
---------------------------- ------------- ------------------------
db_recovery_file_dest         string        /data/oracle/PRODDB/f01
db_recovery_file_dest_size    big integer   500G

show parameter log_archive
NAME                         TYPE             VALUE
--------------------------- -------------- --------------------------
log_archive_dest_1           string        LOCATION=/data/oracle/PRODDB/u01/newarchloc/

Then, switch to the flash recovery area:

alter system set log_archive_dest_1='location=use_db_recovery_file_dest' scope=both;