• You commonly issue "alter database backup controlfile to trace" during backups or
• You have a very manual way of keeping a backup of your live system up to data by applying archived redo, "the poor man's standby database"

This scenario assumes the latter case. The archived redo logs have been transferred to the "standby" system and you run "alter database recover automatic until cancel using backup controlfile". The process happily starts media recovery but then halts suddenly:

Sat May 26 11:09:28 2007
Media Recovery Log /export/home/oracle/admin/live/arch/live1_175.log
Sat May 26 11:09:44 2007
Media Recovery Log /export/home/oracle/admin/live/arch/live1_176.log
Sat May 26 11:09:59 2007
Media Recovery Log /export/home/oracle/admin/live/arch/live1_177.log
Sat May 26 11:11:12 2007
Media Recovery Log /export/home/oracle/admin/live/arch/live1_178.log
Sat May 26 11:12:46 2007
Media Recovery Log /export/home/oracle/admin/live/arch/live1_179.log
Sat May 26 11:14:00 2007
Media Recovery Log /export/home/oracle/admin/live/arch/live1_180.log
Sat May 26 11:14:10 2007
File #210 added to control file as 'UNNAMED00210'. Originally created as:
'/u04/oracledb/oradata/stdby/pssmall_idx_04.dbf'
Sat May 26 11:14:20 2007
Errors with log /export/home/oracle/admin/live/arch/live1_180.log.
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Media Recovery failed with error 1244
ORA-283 signalled during: alter database recover automatic until cancel usin...
Sat May 26 11:21:37 2007
 
Note that live is in the "standby" role, standby had to assume "primary" role.
 
The thrilling line here is "Media recovery may continue but open resetlogs may fail" - what's the point in doing media recovery when the result will be unusable?
 
I first checked v$datafile to identify the file:
 
SQL> select name from v$datafile where file#=210;
 
NAME
--------------------------------------------------------------------------------
/export/home/oracle/product/9.2.0/dbs/UNNAMED00210
 
OK - it's got a wrong name and a wrong location: what next? I checked in $ORALCLE_HOME/dbs but there was no such file, renaming was no longer an option. Well then let's create the data file in the correct location:
 
SQL> alter database create datafile
  2  '/u04/oracledb/oradata/live/pssmall_idx_04.dbf';
alter database create datafile
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"/u04/oracledb/oradata/live/pssmall_idx_04.dbf"
 
SQL> alter database create datafile
  2  '/export/home/oracle/product/9.2.0/dbs/UNNAMED00210';
alter database create datafile
*
ERROR at line 1:
ORA-01111: name for data file
/export/home/oracle/product/9.2.0/dbs/UNNAMED00210 is unknown - rename to correct file
 
Well I knew the file did not exist but Oracle refusing to create it was really unexpected. Can I rename it?
 
SQL> alter database rename file '/export/home/oracle/product/9.2.0/dbs/UNNAMED00210' to
  2  '/u04/oracledb/oradata/live/pssmall_idx_04.dbf';
alter database rename file '/export/home/oracle/product/9.2.0/dbs/UNNAMED00210' to
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 210 - new file
'/u04/oracledb/oradata/live/pssmall_idx_04.dbf' not found
ORA-01111: name for data file 210 is unknown - rename to correct file
ORA-01110: data file 210: '/export/home/oracle/product/9.2.0/dbs/UNNAMED00210'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
 
Hmmm, I expected this. After some further attemps I came across the correct syntax for this problem:
 
SQL> alter database create datafile
  2  'UNNAMED00210' as 'pssmall_idx_04.dbf';
 
Database altered.
 
SQL> select name from v$datafile where file#=210;
 
NAME
--------------------------------------------------------------------------------
/export/home/oracle/product/9.2.0/dbs/pssmall_idx_04.dbf
 
SQL> select name from v$datafile where file#=210;
 
NAME
--------------------------------------------------------------------------------
/export/home/oracle/product/9.2.0/dbs/pssmall_idx_04.dbf
 
 
That's good!
 
Now that there is a file, I can copy it elsewhere and rename it! I then copied the file to its intended location using the cp command and bounced the database, opening it in the mount state.
 
SQL> alter database rename file '/export/home/oracle/product/9.2.0/dbs/pssmall_idx_04.dbf'
  2   to 
  3* '/u04/oracledb/oradata/live/pssmall_idx_04.dbf';
 
Database altered.
 
SQL> shutdown immediate
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 1896878696 bytes
Fixed Size                   733800 bytes
Variable Size             318767104 bytes
Database Buffers         1577058304 bytes
Redo Buffers                 319488 bytes
Database mounted.
 
SQL>  select name from v$datafile where ts#=45
  2  /
 
NAME
--------------------------------------------------------------------------------
/u04/oracledb/oradata/live/pssmall_idx.dbf
/u04/oracledb/oradata/live/pssmall_idx_03.dbf
/u04/oracledb/oradata/live/pssmall_idx_01.dbf
/u04/oracledb/oradata/live/pssmall_idx_02.dbf
/u04/oracledb/oradata/live/pssmall_idx_04.dbf <-- has picked it up!
 
The recovery process resumed normally now.