RMAN Restoring the Control File

Using the Autobackup of the Control File
When you enable the autobackup of your control file and are using a flash recovery area, then
restoring your control file is fairly simple. First connect to your target database, then issue
a startup nomount command, and lastly issue the restore controlfile from autobackup
command:

RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;

Not Using the Autobackup of the Control File

RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from
2> 'C:\FRA\DB1\backupset\2006_09_23\01_mf_ncnnf_TAG20060923T02kc1vgsh.bck';


Restoring Control File Using Recovery Catalog

RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> restore controlfile;

RMAN> connect target /
RMAN> connect catalog rcat/rcat@recov
RMAN> startup nomount;
RMAN> list backup of controlfile;


RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4922 Full 90.64M DISK 00:00:03 20-SEP-09
BP Key: 4996 Status: AVAILABLE Compressed: NO Tag: TAG20090920T002927
Piece Name: /u02/backup/letodb/c-3814852239-20090920-01
Control File Included: Ckp SCN: 723968640 Ckp time: 20-SEP-09


Writing the DBID to the Alert.log File
Another way of recording the DBID is to make sure that it is written to the alert.log file on a
regular basis using the DBMS_SYSTEM package. For example, you could have this SQL code
execute as part of your backup job:

COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));


VERIFYING USE OF A FRA
You can verify whether you are using a flash recovery area (FRA) by issuing the following SQL statement:

SQL> show parameter db_recovery_file_dest;
If you are not using a flash recovery area, then the value of the db_recovery_file_dest
initialization parameter will be null. If you are using a flash recovery area, then there will be a directory
shown in the VALUE column. For example, here’s the FRA for one of our test databases:
NAME TYPE VALUE
---------------------- ----------- ------------------------------
db_recovery_file_dest string /oraback/FRA

you can connect to your target database, issue startup nomount, set the DBID

RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506593;
RMAN> restore controlfile from autobackup;

By default, RMAN will attempt to retrieve from autobackups created within the last seven days only. If you want to modify the default behavior, use the maxdays parameter. This example
instructs RMAN to look for a control file backup created in the last 20 days:

RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> restore controlfile from autobackup maxdays 20;


RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;


RMAN> connect target /
RMAN> startup nomount;
RMAN> set dbid 2601506594;
RMAN> set controlfile autobackup format for device type disk to 'C:\ODUMP\%F';
RMAN> restore controlfile from autobackup;

Unix

RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from '/ora01/app/oracle/product/10.2.0/dbs/1hhu0gn1_1_1';

Windows

RMAN> connect target /
RMAN> startup nomount;
RMAN> restore controlfile from 'C:\ORACLE\PROD\10.2.0\DB_1\DATABASE\5IHU0G6H_1_1';
RMAN> restore controlfile from autobackup maxseq 10;

This example shows the syntax when restoring to a nondefault location and using an autobackup of the control file:

RMAN> connect target /
RMAN> restore controlfile to 'C:\ctl.bk' from autobackup;

RMAN> connect target /
RMAN> connect rmancat/rmancat@rcat
RMAN> restore controlfile to 'C:\ctl.bk';


Restoring Lost Copy of Multiplexed Control File

Problem

You attempted to start your database and receive the following error:
ORA-00205: error identifying controlfile, check alert log for more info
You check your target database alert.log file and verify that Oracle can’t obtain the status of one of your database control files. You wonder whether you can use a good copy of an existing
control file to resolve this issue.

Solution

Modifying the Initialization File

SQL> startup nomount;
SQL> alter system
2 set control_files='/ora01/oradata/BRDSTN/control01.ctl' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;


Copying a Good Control File

RMAN> shutdown immediate;
or using this:
SQL> shutdown immediate;

$ cp /ora01/oradata/BRDSTN/control01.ctl /ora02/oradata/BRDSTN/control02.ctl

Re-creating the Control File

NAMING A TRACE FILE
If you want to specify a text string to be used as part of the trace filename, then use the tracefile_

SQL> alter session set tracefile_identifier='MYTRACE';

Problem
One of the following situations applies:
• You’ve experienced a failure and lost all of your control files, and you belatedly realize that you don’t have a good binary backup of the control file.
• You want to change a database setting that can be modified only by re-creating the control
file.
• You are relocating a large number of datafiles, and you find it easier to re-create the control file with the new names and locations (instead of manually renaming the datafiles).

Solution
You can use the output of the following command to re-create your control file:

SQL> alter database backup controlfile to trace;
The previous command generates a trace file that is placed in your user dump directory.
You can display the location of your user_dump_dest from SQL as follows:

SQL> show parameter user_dump_dest


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG' SIZE 50M,
GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG' SIZE 50M,
GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG' SIZE 50M
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;