Scenario when the controlfile is lost and there was a datafile added to an existing table space after backup of the controlfile was taken so the controlfile does not know about the new datafile.

1) Start database in nomount state
SQL> connect /as sysdba

SQL> startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

2) Connect to rman to restore control file, to restore the controfile it requires setting DBID
$ $ORACLE_HOME/bin/rman

RMAN> SET DBID 1992878807

executing command: SET DBID

run {
restore controlfile from ‘/u01/oradata/TEST/rman/TEST_3llv5k9u_117’;
}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 12-DEC-10
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20101212
channel ORA_DISK_1: autobackup found: /u01/oradata/TEST/rman/c-1992878807-20101212-01
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/oradata/TEST/control1.ora
output filename=/u01/oradata/TEST/control2.ora
output filename=/u01/oradata/TEST/control3.ora
Finished restore at 12-DEC-10

3) Mount database so one can recover the database
SQL> alter database mount;

Database altered.

4) Recover database (It will issue error about the controlfile does not know about the new datafile)
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12645623 generated at 12/12/2010 11:16:24 needed for thread 1
ORA-00289: suggestion : /u01/oradata/TEST/arch/1_43_737334952.dbf
ORA-00280: change 12645623 for thread 1 is in sequence #43

Note here the archive log file specified by Oracle is 1_43_737334952.dbf but it does not exists so the file needed is the redo log so using ls find the redo log file needed in this case is redo3b.log so it is specified when restoring
$ ls -lrt
..
-rw-r—– 1 oracle oinstall 10087936 Dec 11 22:11 1_40_737334952.dbf
-rw-r—– 1 oracle oinstall 10087936 Dec 11 23:56 1_41_737334952.dbf
-rw-r—– 1 oracle oinstall 10087936 Dec 12 11:16 1_42_737334952.dbf

$ ls -lrt redo*.log
total 1518908
-rw-r—– 1 oracle oinstall 10486272 Dec 11 23:56 redo1b.log
-rw-r—– 1 oracle oinstall 10486272 Dec 11 23:56 redo1a.log
-rw-r—– 1 oracle oinstall 10486272 Dec 12 11:16 redo2b.log
-rw-r—– 1 oracle oinstall 10486272 Dec 12 11:16 redo2a.log
-rw-r—– 1 oracle oinstall 10486272 Dec 12 19:44 redo3b.log
-rw-r—– 1 oracle oinstall 10486272 Dec 12 19:44 redo3a.log

So restoring specifying the redo log file redo3b.log

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/TEST/redo3b.log
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 8: ‘/u01/oradata/TEST/test_04.dbf’

Note the recovery didn’t start as the control file found a datafile it does not know about.

ORA-01112: media recovery not started

5) As datafile is in mount state V$datafile can be queried it shows the new datafile is called /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/oradata/TEST/system_01.dbf
/u01/oradata/TEST/undo_01.dbf
/u01/oradata/TEST/sysaux_01.dbf
/u01/oradata/TEST/test_01.dbf
/u01/oradata/TEST/test.dbf
/u01/oradata/test_02.dbf
/u01/oradata/TEST/test_03.dbf
/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008

6) Shows the datafile does not physical exists
$ ls -l /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008
ls: /u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008: No such file or directory

7) Trying to recover database again it reports the filename needs to be updated in the controfile before proceeding
SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008’
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008’

8) Create datafile from UNNAMED00008 to update controfile
SQL> alter database create datafile ‘/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00008’ as ‘/u01/oradata/TEST/test_04.dbf’;

Database altered.

9) Check v$datafile after the datafile is created note the control file knows about the datafile
SQL> select name from v$datafile;

NAME
——————————————————————————–
/u01/oradata/TEST/system_01.dbf
/u01/oradata/TEST/undo_01.dbf
/u01/oradata/TEST/sysaux_01.dbf
/u01/oradata/TEST/test_01.dbf
/u01/oradata/TEST/test.dbf
/u01/oradata/test_02.dbf
/u01/oradata/TEST/test_03.dbf
/u01/oradata/TEST/test_04.dbf

10) Recover the database
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 12654692 generated at 12/12/2010 19:43:13 needed for thread 1
ORA-00289: suggestion : /u01/oradata/TEST/arch/1_43_737334952.dbf
ORA-00280: change 12654692 for thread 1 is in sequence #43

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/TEST/redo3b.log
Log applied.
Media recovery complete.

11) Open database with resetlogs
SQL> alter database open resetlogs;

Database altered.