Restoring Controlfiles and Spfile

Backup and Recovery

When restoring the control file, the default destination is all of the locations defined in the CONTROL_FILES initialization parameter. If you do not set the CONTROL_FILES initialization parameter, the database uses the same rules to determine the destination for the restored control file as it uses when creating a control file if the CONTROL_FILES parameter is not set.

When you are restoring your control file to a location not listed in the CONTROL_FILES initialization parameter. In that case, you create a copy of your control file in the specified location without touching your running database.

Note: After restoring the control files of your database from backup, you must perform complete media recovery of the database, and then open your database with the RESETLOGS option.

Restore of the Control File from Control File Autobackup

If you are not using a recovery catalog, you must restore your control file from an autobackup. If you want to restore the control file from autobackup, the database must be in a NOMOUNT state.

RMAN> SET DBID 691421794 RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; If your database is still running then use: RMAN> restore until time ‘sysdate-1’ CONTROLFILE to ‘D:\controlfile4042012_control01.ctl’ from autobackup; Note: The until time clause set the time validity for controlfile. By default RMAN looks for autobackup for 7 days. If you want to override this then simply use this: RMAN> restore until time ‘sysdate-1 ‘CONTROLFILE to ‘D:\controlfile4042012_control01.ctl’ from autobackup maxdays 100; It will force RMAN to look for copies more days.

Restoring a Control File When Using a Recovery Catalog

Restoring a lost control file from autobackup is easier when using a recovery catalog than when using only the control file to store the RMAN repository. The recovery catalog contains a complete record of your backups, including backups of the control file. Therefore, you do not have to specify your DBID or control file autobackup format.

C:\RMAN TARGET SYS/[email protected] CATALOG RMAN/[email protected] RMAN> RESTORE CONTROLFILE;

The restored control file is written to all locations listed in the CONTROL_FILES initialization parameter.

Restore of the Control File from a Known (Default) Location

RMAN> RESTORE CONTROLFILE from ‘C:\oracle1\oradata\orcl3\CONTROL01.CTL’;

Restore of the Control File to a New Location


You can perform this operation with the database in NOMOUNT, MOUNT or OPEN states, because you are not overwriting any of the control files currently in use. Any existing file named ‘filename’ is overwritten. After restoring the control file to a new location, you can then update the CONTROL_FILES initialization parameter to include the new location.

NOTE: After you restore your database using a backup control file, you must run RECOVER DATABASE and perform an OPEN RESETLOGS on the database

Restoring the Server Parameter File (SPFILE) from Backup

If you lose your server parameter file (SPFILE), RMAN can restore it to its default location or to a location of your choice.

Unlike the loss of the control file, the loss of your SPFILE does not cause your instance to immediately stop. Your instance may continue operating, although you will have to shut it down and restart it after restoring the SPFILE.

If the database is up at the time of the loss of the SPFILE, connect to the target database.

C:\rman TARGET /

If the database is not up when the SPFILE is lost, and you are not using a recovery catalog, then you must set the DBID of the target database

Shut down the instance and restart it without mounting. When the SPFILE is not available, RMAN starts the instance with a dummy parameter file. For example:


Restore the server parameter file. If restoring to the default location


If Restoring to a nondefault location…..


Restart the instance with the restored file. If restarting with a server parameter file in a nondefault location, then create a new pfile.


Restore of the SPFILE from the Control File Autobackup

If you have configured control file autobackups, the SPFILE is backed up with the control file whenever an autobackup is taken.

RMAN> SET DBID 691421794; RMAN> RESTORE SPFILE FROM AUTOBACKUP; RMAN> restore until time ‘sysdate-1’ SPFILE to ‘C:\temp\SPFILE’ from autobackup maxdays 100; After restoring SPFILE You can convert it to PFILE so you can easily see the setting. Complex scenario You have lost all database files including CONTROLFILE and SPFILE. You only have backups created by RMAN. In that case You have to eventually install Database Software. Then setup environment variables and start recovery. On Windows platform You have to create Service to be able to startup oracle. You probably remember instance name and SID. If You don’t it can be usually found in some TNSNAMES files on client machines. But You probably do not know DBID. You can easily find DBID – it’is part of the name of autobackup file.  RMAN target=/ At the beginning set Database ID: RMAN> SET DBID 691421794; Then startup instance: RMAN> startup force nomount; RMAN> RUN { SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO D:\BACKUP\RMAN\HOTBACKUP\%F’; RESTORE SPFILE FROM AUTOBACKUP; } I suggest also creating PFILE from SPFILE. Then You should check if all file locations are correct, all directories exists and Oracle have rights to write in them. RMAN> shutdown; RMAN> startup force nomount; RMAN> RUN         { SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO D:\BACKUP\RMAN\HOTBACKUP\%F’; RESTORE CONTROLFILE FROM AUTOBACKUP;         } So you have controlfile take the database in mount phase and perform Media recovery.