Yuan Yao, An Oracle ACE's Blog

One of my clients reported that some archive logs were not backed up by their backup job. We replicated this issue in a test environment, and here is the process of our experiment.

First, we checked the v$archived_log view but couldn’t find the relevant records. We also used RMAN’s LIST command but still couldn’t find the records.

Next, we checked the control file for ARCHIVED LOG records:

SQL> SELECT
    RECORD_SIZE,
    RECORDS_TOTAL,
    RECORDS_USED
FROM
    V$CONTROLFILE_RECORD_SECTION
WHERE
    type = 'ARCHIVED LOG';
    
  RECORD_SIZE   RECORDS_TOTAL   RECORDS_USED
          584             224            224

We found that both RECORDS_TOTAL and RECORDS_USED were 224, indicating that the space in the control file allocated for archived logs was full.

Then, we checked the generation time distribution of the current archived log files in the database:

SQL> SELECT
  2      TRUNC(completion_time, 'DD') AS completion_date,
  3      COUNT(*) AS log_count
  4  FROM
  5      v$archived_log
  6  GROUP BY
  7      TRUNC(completion_time, 'DD')
  8  ORDER BY
  9      TRUNC(completion_time, 'DD');
COMPLETION_DATE     LOG_COUNT
27-JUN-24                   1
28-JUN-24                   4
29-JUN-24                  10
01-JUL-24                   3
03-JUL-24                  40
04-JUL-24                  59
08-JUL-24                   1
09-JUL-24                   4
12-JUL-24                  21
16-JUL-24                  10
17-JUL-24                  71

11 rows selected.

The control file by default retains records for 7 days, as determined by the control_file_record_keep_time parameter. Here, we found that earlier archived logs were over 7 days old.

While executing multiple ALTER SYSTEM SWITCH LOGFILE commands, we monitored the distribution of archived log file’s generation time:

SQL> /
COMPLETION_DATE     LOG_COUNT
28-JUN-24                   4
29-JUN-24                  10
01-JUL-24                   3
03-JUL-24                  40
04-JUL-24                  59
08-JUL-24                   1
09-JUL-24                   4
12-JUL-24                  21
16-JUL-24                  10
17-JUL-24                  72

10 rows selected.

SQL> /
COMPLETION_DATE     LOG_COUNT
29-JUN-24                   4
01-JUL-24                   3
03-JUL-24                  40
04-JUL-24                  59
08-JUL-24                   1
09-JUL-24                   4
12-JUL-24                  21
16-JUL-24                  10
17-JUL-24                  82

9 rows selected.

SQL> /
COMPLETION_DATE     LOG_COUNT
04-JUL-24                   6
08-JUL-24                   1
09-JUL-24                   4
12-JUL-24                  21
16-JUL-24                  10
17-JUL-24                 182

6 rows selected.

SQL> /
COMPLETION_DATE     LOG_COUNT
12-JUL-24                  21
16-JUL-24                  10
17-JUL-24                 202

3 rows selected.

We observed that Oracle continually deletes oldest archived logs. However, once all archived logs older than 7 days are deleted, it stops deleting further.

At this point, we checked the control file for archived log records again:

SQL> SELECT
    RECORD_SIZE,
    RECORDS_TOTAL,
    RECORDS_USED
FROM
    V$CONTROLFILE_RECORD_SECTION
WHERE
    type = 'ARCHIVED LOG';

  RECORD_SIZE   RECORDS_TOTAL   RECORDS_USED
          584             448            233

We found that RECORDS_TOTAL had expanded from 224 to 448, and RECORDS_USED had increased from 224 to 233, indicating that the control file had automatically expanded.

From this experiment, we can understand Oracle’s mechanism for deleting archived logs: When there is space pressure in the control files, Oracle tries to manage the space by deleting records of archived logs that are older than the period specified by control_file_record_keep_time . However, while it deletes these records from the control files, it does not delete the actual archived log files from the disk. If there are no archived logs that meet the deletion criteria  (i.e., all are within the control_file_record_keep_time), the control files will extend automatically to accommodate new records.

In a real-world scenario, one of my clients set control_file_record_keep_time to 356 days. This led to the control file extending to over 1 TB.

Given the above understanding, a practical solution for backing up all archived log is to set the backup and deletion cycle for archived logs to less than 7 days while keeping the control_file_record_keep_time parameter at its default value.