Useful query against v$archived_log in recovery situations

SELECT thread#,
         COUNT(*)                        AS cnt,
         MIN(sequence#)                  AS min_seq,
         MAX(sequence#)                  AS max_seq,
         MIN(first_time)                 AS min_time,
         MAX(next_time)                  AS max_time
  FROM   v$archived_log
  WHERE  deleted = 'NO'
  GROUP  BY thread#
  ORDER  BY thread#;

   THREAD#    CNT    MIN_SEQ    MAX_SEQ               MIN_TIME               MAX_TIME
__________ ______ __________ __________ ______________________ ______________________
         1     80       7936       8015 2026-02-16 15:38:02    2026-02-18 14:15:42

What to do if your flash recovery area is filled with "foreign archive logs"?

One of my databases came to a halt when the flash recovery area filled completely.

It wasn't a problem with space preassure, but merely a condition that made the flash recovery area *seem* full, when it really wasn't.

To clear the archiver stuck condition is easy enough, simply increase the value of db_recovery_file_dest_size, but how to fix the source of the problem?

My flash recovery area was, at the time, filled up with foreign archive logs:

SYS@CDB$ROOT SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                         14.04                         0             235          0
BACKUP PIECE                           .06                         0              44          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                  85.8                         0            1558          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

To fix this, log on to the database using rman:

rman target / nocatalog

Here you can list the foreign archivelogs:

list foreign archivelogs all;

They were all from may 2023, and they have ended up here since the database was cloned using storage snapshots.

To clear them out of the flash recovery area, you need to first crosscheck them:

RMAN> crosscheck foreign archivelog all;

Then, delete them:

RMAN> delete noprompt foreign archivelog all ;

The crosscheck is important. Without it, Oracle cannot delete the entries from the controlfile, which means that tha v$flash_recovery_area will still be reported as full.
The flash recovery area is not full anymore:

SYS@CDB$ROOT SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                         18.21                         0             280          0
BACKUP PIECE                           .06                         0              44          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.

Documented in Doc ID 1617965.1 "What commands may be used to remove foreign archivelog files?" at Oracle Support.

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.

How to use RMANs LIST command to find individual archivelogs or a sequence of archivelogs

To list an individual backup, any of the three alternatives bellow would be valid query using rman:

RMAN> list backup of archivelog logseq=120316;
RMAN> list backup of archivelog logseq 120316;
RMAN> list backup of archivelog sequence 120316;

To view backups of archivelog between two sequences:

RMAN> list backup of archivelog sequence between 120316 and 120317;
RMAN> list backup of archivelog from logseq 412593 until logseq 412656;

Use the SUMMARY directive to view only the backupsets affected:

RMAN> list backup of archivelog from logseq 412593 until logseq 412656 summary;
RMAN> list backup of archivelog sequence between 120316 and 120317 summary;

To view backups completed before a specific date:

RMAN> list backup completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list backup of archivelog all summary completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
RMAN> list archivelog all completed before 'sysdate-2';:

To list archivelogs recognized by the controlfile between two sequnce numbers:

RMAN> list archivelog sequence between 110880 and 110881;

To list archivelogs recognized by the controlfile up until yesterday:

RMAN> list archivelog until time 'sysdate-1';

To list archivelogs recognized by the controlfile from two days ago, up until yesterday:

RMAN> list archivelog from time 'sysdate-2' until time 'sysdate-1';

To limit the list of archivelogs to the ones taken on a specific date, in this case, the last of January 2022:

RMAN> list archivelog from time '31.01.2022' until time '01.02.2022';

If you remove the "until time" clause, RMAN will list all the archivelogs from 31.01.2022 up until the latest ones:

RMAN> list archivelog from time '31.01.2022';

Remember, the value of sysdate is the time right now, sysdate-1 is 24hrs ago, sysdate-2 is 48 hours ago, etc.

 select sysdate, sysdate-1, sysdate-2 from dual;

SYSDATE             SYSDATE-1           SYSDATE-2
------------------- ------------------- -------------------
03.02.2022 12:24:12 02.02.2022 12:24:12 01.02.2022 12:24:12