Converting Control Files to be Oracle Managed Files

No bombshells in this post – just a little task I’d not come across before and a complaint at the end.

On a test 10.2.0.4 database at home I have 2 control files, one in the DB_CREATE_FILE_DEST and one in the DB_RECOVERY_FILE_DEST as can be seen below:

NAME
--------------------------------------------------------------------
/u01/oradata/ORCL1/controlfile/o1_mf_5h1bxxg0_.ctl
/u01/app/oracle/flash_recovery_area/ORCL1/controlfile/o1_mf_5h1bxy2m_.ctl

During a bit of testing too mundane to go into I ended up with non-Oracle managed control files:

NAME
--------------------------------------------------------------------
/u01/oradata/orcl1/ORCL1_1.ctl
/u01/oradata/orcl1/ORCL1_2.ctl

After my unproductive testing I then wanted to put the control files back as they were. This was not something I’d considered before and it wasn’t immediately obvious how to go about it. The steps are below for my future reference (and perhaps yours).

Blank out the CONTROL_FILES parameter in the SPFILE and then restart the instance.

SYS @ orcl1 >alter system reset control_files scope=spfile sid='*';

System altered.

SYS @ orcl1 >startup force nomount
ORACLE instance started.

The CONTROL_FILES parameter appears to be set to a location in the Oracle home. This confused me at first but just turn a blind eye to it for now.

SYS @ orcl1 >show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/cntrlorcl1.dbf

If you restore the control files from an RMAN session the resulting files find their way back to being managed by Oracle.

RMAN> RESTORE CONTROLFILE FROM '/u01/oradata/orcl1/ORCL1_2.ctl';

Starting restore at 05-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=/u01/oradata/ORCL1/controlfile/o1_mf_5h6ml6to_.ctl
output filename=/u01/app/oracle/flash_recovery_area/ORCL1/controlfile/o1_mf_5h6ml74f_.ctl
Finished restore at 05-NOV-09

Nice.

Complaint time:

DB_RECOVERY_FILE_DEST. A little thing I like is the naming of this parameter. Perfect – it accurately describes the sort of files you’ll find in there. A little thing that I don’t like is that it’s Sunday best name is “Flash Recovery Area”. In which meeting did that seem like a good idea! Oh – that will be the marketing one.