How to increase Control_file_record_keep_time

When you use RMAN backups without Recovery Catalog RMAN uses controlfile to do it’s house keeping operations such as keeping track of ARCHIVE logs and BACKUP sets.
In such configuration it’s best to increase control_file_record_keep_time from it’s default value of 7 days to whatever your backup retention policy is.  It’s also best to switch from PFILE to SPFILE.

## here we see that SPFILE is not specified (we are using PFILE instead)
SQL> show parameter spfile
NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string

## our control file keep time is set to 7 days (Default)
SQL> show parameter keep_time

NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_file_record_keep_time        integer     7

## first we switch to SPFILE by creating it from our PFILE default location.
SQL> create spfile from pfile;
File created.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string

## you can’t increase the keep_time now – it requires a restart since we just enable spfile
SQL> alter system set control_file_record_keep_time=14 scope=spfile;
alter system set control_file_record_keep_time=14 scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1125210416 bytes
Fixed Size                   457008 bytes
Variable Size             285212672 bytes
Database Buffers          838860800 bytes
Redo Buffers                 679936 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      ?/dbs/[email protected]
SQL>

SQL> alter system set control_file_record_keep_time=14 scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1125210416 bytes
Fixed Size                   457008 bytes
Variable Size             285212672 bytes
Database Buffers          838860800 bytes
Redo Buffers                 679936 bytes
Database mounted.
Database opened.
SQL> show parameter keep_time

NAME                                 TYPE        VALUE
———————————— ———– ——————————
control_file_record_keep_time        integer     14
SQL>
There you have it – now RMAN can keep 14 days worth of BACKUPS and ARCHIVE logs in it’s catalog using CONTROL file alone.  You will also be able to include SPFILE in your RMAN backup set using this command:

CONFIGURE CONTROLFILE AUTOBACKUP ON;
This will greatly increase your ability to recover this database.