How to configure RMAN to delete crontrol files automatically

oracle-12crman

I am using Oracle Release 12.2.0.1.0 Standard Edition on ODA (Oracle Database Appliance, running linux). My instance "xyz" is running in archivelog mode. RMAN performs backup and unnecessary datafiles are deleted automatically from the backup set.

[oracle@oda ~]$ rman TARGET /
Recovery Manager: Release 12.2.0.1.0 - Production on Fr Aug 31 16:50:05 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Mit Zieldatenbank verbunden: XYZ (DBID=4079461799)
RMAN> show all;
Kontrolldatei der Zieldatenbank wird anstelle des Recovery-Katalogs verwendet
RMAN-Konfigurationsparameter für Datenbank mit db_unique_name XYZ sind:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backup/rman/xyz/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/mnt/backup/rman/xyz/%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u03/app/oracle/fast_recovery_area/XYZ/controlfile/snapcf_xyz.f';

When I check the directory everything looks fine – except for the control files which keep piling up

[oracle@oda1 xyz]$ pwd
/mnt/backup/rman/xyz
[oracle@oda1 xyz]$ l
total 4144832
drwxr-xr-x 2 oracle oinstall       4096 Aug 31 11:00 .
drwxr-xr-x 7 oracle oinstall       4096 Jul  4 14:38 ..
-rw-r----- 1 oracle asmadmin 1072447488 Aug 29 01:02 6ntbnscr_1_1
-rw-r----- 1 oracle asmadmin     943104 Aug 29 01:02 6otbnshm_1_1
-rw-r----- 1 oracle asmadmin   18115072 Aug 29 11:00 6qtbovhi_1_1
-rw-r----- 1 oracle asmadmin   62260224 Aug 30 01:00 6stbqgoj_1_1
-rw-r----- 1 oracle asmadmin 1072734208 Aug 30 01:05 6ttbqgq1_1_1
-rw-r----- 1 oracle asmadmin    1159680 Aug 30 01:05 6utbqh2k_1_1
-rw-r----- 1 oracle asmadmin   10599424 Aug 30 11:00 70tbrjtj_1_1
-rw-r----- 1 oracle asmadmin   42828288 Aug 31 01:00 72tbt54i_1_1
-rw-r----- 1 oracle asmadmin 1073242112 Aug 31 01:02 73tbt54q_1_1
-rw-r----- 1 oracle asmadmin    2316288 Aug 31 01:02 74tbt59l_1_1
-rw-r----- 1 oracle asmadmin   17406976 Aug 31 11:00 76tbu89j_1_1
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 11:11 c-4079461799-20180620-00
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 11:25 c-4079461799-20180620-01
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 13:03 c-4079461799-20180620-02
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 13:48 c-4079461799-20180620-03
-rw-r----- 1 oracle asmadmin   10698752 Jun 20 14:03 c-4079461799-20180620-04
-rw-r----- 1 oracle asmadmin   10698752 Jul 16 10:22 c-4079461799-20180716-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 26 17:32 c-4079461799-20180726-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 27 01:02 c-4079461799-20180727-00
-rw-r----- 1 oracle asmadmin   10698752 Jul 27 11:00 c-4079461799-20180727-01
..

The files beginning with "c" are the control files.

Question: What can I do to delete these files also automatically?

Thank you for your insight!

Best Answer

The RMAN Retention Policy for the control files and backup sets is defined as such:

(emphasis mine)

[CONFIGURE]..

This is the command to start the configuration of the parameter

... RETENTION POLICY ...

Specifies a persistent, ongoing policy for backup sets and copies that RMAN marks as obsolete, that is, not needed and eligible for deletion.
As time passes, RMAN marks backup sets and copies as obsolete according to the criteria specified in the retention policy. RMAN automatically deletes obsolete backup sets and copies in the flash recovery area when space is needed. RMAN does not automatically delete obsolete files outside the flash recovery area: you must manually execute DELETE OBSOLETE to remove them.

... TO REDUNDANCY integer

Specifies that RMAN should retain integer full or level 0 backups of each datafile and control file. The default retention policy setting is REDUNDANCY 1.
If more than integer full or level 0 backups of a datafile or control file exist, then RMAN marks these extra files as obsolete. RMAN then determines the oldest of the retained backups and marks all archived redo logs and log backups older than this backup as obsolete. The DELETE OBSOLETE command removes obsolete datafile backups (full or incremental), control file backups, and archived log backups or image copies.

Applying the Above to Your Environment

In your case you have set the following:

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Which means:

  • keep all full and level 0 backups for up to three versions [back]
  • keep all archive logs in-between these backups as well
  • keep all control file backups of each backup type as well
    • full and level 0 backup
    • archive log backup
    • when any autobackup of the control file is triggered

As you might notice, you could essentially have the same amount of backed up control files as you have the sum of:

  • full backup files
  • level 0 backup files
  • archive log backup files

...or even more. (Read: Oracle 12c – Automatic Control File Backups for more details)

And if you are not backing up to the flash recovery area of your Oracle instance, then you would have to manually delete these files after backing up your database by issuing the following command:

DELETE OBSOLETE;

You could also test this by first running:

REPORT OBSOLETE;

...to check that the control files in the directory are actually obsolete, and then manually delete them in RMAN with the command I mentioned before this one.

Summary

You could be seeing the following issues:

  1. Everthing is OK, the instance has just created a lot of autobackup-ed control files.
  2. The Control Files are outside the FRE and have to deleted manually
  3. You hit a bug (...read on below).

Because we don't know your exact backup scripts and other settings, it's hard to tell which point you are encountering, but you have a few good starting points.

Apparent Bug

There are cases (apparently) where the control file never gets marked as obsolete:

A bug has been filed, and a fix supplied, but it doesn't seem to work in all cases.

You might have to manually delete the control file backups in RMAN.

DELETE COPY OF CONTROLFILE LIKE '/mnt/backup/rman/xyz/<part_name_of_control_file>%';

Answering Your Question

What can I do to delete these files also automatically?

Sorry, but that depends which situation you are hitting and can vary from:

  • Will happen automatically
  • Can be deleted with REPORT OBSOLETE and DELETE OBSOLETE
  • Will only happen manually with DELETE COPY OF CONTROLFILE LIKE ...
  • Might work again after bug fix

References

Related Question