Adding a DataFile that had been excluded from a CREATE CONTROLFILE

Here is a scenario where a CREATE CONTROLFILE was used to recover a database but one of the datafiles was (inadvertently) excluded from the CREATE CONTROLFILE statement.
The Database when, OPENed, sets the file as a MISSING file. This is because it is present in the Data Dictionary but not in the Controlfile.
The datafile can be "added" back (if it is really available on disk or has been restored earlier) and "re-recovered".

SQL> REM ########### The controlfile was created without the "example01.dbf" datafile
SQL> REM #### An INCOMPLETE RECOVERy was performed
SQL> REM #### The database was OPENed with RESETLOGS
SQL> REM ##  The datafile has been added back with an ALTER DATABASE RENAME FILE 'MISSING05.DBF' TO 'example01.dbf'

SQL> @create_cntrlfile
SQL> spool create_cntrlfile
SQL> 
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  880803840 bytes
Fixed Size                  2087992 bytes
Variable Size             218104776 bytes
Database Buffers          654311424 bytes
Redo Buffers                6299648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORT24FS" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oracle_fs/Databases/ORT24FS/redo01.dbf'  SIZE 50M,
  9    GROUP 2 '/oracle_fs/Databases/ORT24FS/redo02.dbf'  SIZE 50M,
 10    GROUP 3 '/oracle_fs/Databases/ORT24FS/redo03.dbf'  SIZE 50M,
 11    GROUP 4 '/oracle_fs/Databases/ORT24FS/redo04.dbf'  SIZE 50M,
 12    GROUP 5 '/oracle_fs/Databases/ORT24FS/redo05.dbf'  SIZE 50M
 13  -- STANDBY LOGFILE
 14  DATAFILE
 15    '/oracle_fs/Databases/ORT24FS/system01.dbf',
 16    '/oracle_fs/Databases/ORT24FS/sysaux01.dbf',
 17    '/oracle_fs/Databases/ORT24FS/users01.dbf',
 18    '/oracle_fs/Databases/ORT24FS/undotbs.dbf'
 19  CHARACTER SET WE8ISO8859P1
 20  ;

Control file created.

SQL> -- Configure RMAN configuration record 1
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','OFF');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 2
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 3
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 8 DAYS');

PL/SQL procedure successfully completed.

SQL> -- Configure RMAN configuration record 4
SQL> VARIABLE RECNO NUMBER;
SQL> EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');

PL/SQL procedure successfully completed.

SQL> -- Commands to re-create incarnation table
SQL> -- Below log names MUST be changed to existing filenames on
SQL> -- disk. Any one log file from each branch can be used to
SQL> -- re-create incarnation records.
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- ALTER DATABASE REGISTER LOGFILE '/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_1_%u_.arc';
SQL> -- Recovery is required if any of the datafiles are restored backups,
SQL> -- or if the last shutdown was not normal or immediate.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
ORA-00279: change 4325510 generated at 08/25/2010 16:46:41 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_43_%u_.arc
ORA-00280: change 4325510 for thread 1 is in sequence #43


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf
ORA-00279: change 4325717 generated at 08/25/2010 16:54:02 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_44_%u_.arc
ORA-00280: change 4325717 for thread 1 is in sequence #44
ORA-00278: log file '/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf' no
longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle_fs/Databases/ORT24FS/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4325717 generated at 08/25/2010 16:54:02 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_44_%u_.arc
ORA-00280: change 4325717 for thread 1 is in sequence #44


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' REUSE
*
ERROR at line 1:
ORA-01119: error in creating database file
'/oracle_fs/Databases/ORT24FS/temp01.dbf'
ORA-17610: file '/oracle_fs/Databases/ORT24FS/temp01.dbf' does not exist and no
size specified
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle_fs/Databases/ORT24FS/temp01.dbf' size 100M autoextend on next 100M maxsize 8000M;

Tablespace altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/oracle_fs/Databases/ORT24FS/undotbs.dbf
/oracle_fs/Databases/ORT24FS/users01.dbf
/oracle_fs/Databases/ORT24FS/sysaux01.dbf
/oracle_fs/Databases/ORT24FS/system01.dbf
/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005

SQL> alter database create datafile '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
  2  as '/oracle_fs/Databases/ORT24FS/example01.dbf';
alter database create datafile '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
*
ERROR at line 1:
ORA-19723: Cannot recreate plugged in read-only datafile 5
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'


SQL> alter database rename file '/oracle_fs/ora10204/product/10.2.0.4/dbs/MISSING00005'
  2  to '/oracle_fs/Databases/ORT24FS/example01.dbf'; 

Database altered.

SQL> recover datafile 5;
ORA-00279: change 4325511 generated at 08/25/2010 16:46:41 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_08_25/o1_mf_1_43_%u_.arc
ORA-00280: change 4325511 for thread 1 is in sequence #43


Specify log: {=suggested | filename | AUTO | CANCEL}
/oracle_fs/ArchiveLogs/ORT24FS/1_43_725069822.dbf
Log applied.
Media recovery complete.
SQL> select * from v$recover_file;

no rows selected

SQL> alter database datafile 5 online;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
           1           4034914 23-JUL-10                 3632263 04-JUN-10
PARENT     725069822                  0 NO

           2           4325718 25-AUG-10                 4034914 23-JUL-10
CURRENT    727983988                  1 NO


SQL> exit 

The example01.dbf file was not included in the CREATE CONTROLFILE statement although it was present on disk. The OPEN RESETLOGS after the Incomplete Recovery created a new Incarnation of the database. Yet, I was able to "add" the datafile back by RENAMEing the "MISSING" file.

.
.
.