Here is the steps to recover those missing database datafiles.
16:33:32 SQL> select file#,name from v$datafile;
FILE# NAME
---------- ---------------------------------------------------
1 /u01/app/oracle/oradata/rev1/system01.dbf
2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
4 /u01/app/oracle/oradata/rev1/users01.dbf
16:33:43 SQL> set time off;
SQL> set time off;
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ---------------------------------------------------
1 /u01/app/oracle/oradata/rev1/system01.dbf
2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
4 /u01/app/oracle/oradata/rev1/users01.dbf
SQL> alter database backup controlfile to trace;
Database altered.
SQL> alter database backup controlfile to trace;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database backup controlfile to trace;
Database altered.
SQL>
[oracle@rac1 udump]$ cp rev1_ora_6901.trc /home/oracle/Desktop/ctlnew.sql
[oracle@rac1 udump]$ pwd
/u01/app/oracle/admin/rev1/udump
[oracle@rac1 udump]$
SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/rev1/users02.dbf' size 2m;
Tablespace altered.
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ---------------------------------------------------
1 /u01/app/oracle/oradata/rev1/system01.dbf
2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
4 /u01/app/oracle/oradata/rev1/users01.dbf
5 /u01/app/oracle/oradata/rev1/users02.dbf
SQL>
CREATE CONTROLFILE REUSE DATABASE "REV1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/rev1/redo01.log' SIZE 5M,
GROUP 2 '/u01/app/oracle/oradata/rev1/redo02.log' SIZE 5M,
GROUP 3 '/u01/app/oracle/oradata/rev1/redo03.log' SIZE 5M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/rev1/system01.dbf',
'/u01/app/oracle/oradata/rev1/undotbs01.dbf',
'/u01/app/oracle/oradata/rev1/sysaux01.dbf',
'/u01/app/oracle/oradata/rev1/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@rac1 udump]$ rm -rf /u01/app/oracle/oradata/rev1/*.ctl
[oracle@rac1 udump]$ rm -rf /u01/app/oracle/oradata/rev1/*.ctl
[oracle@rac1 rev1]$ pwd
/u01/app/oracle/oradata/rev1
[oracle@rac1 rev1]$ ls -ltr
total 791480
-rw-r----- 1 oracle oinstall 104865792 Oct 31 15:44 temp01.dbf
-rw-r----- 1 oracle oinstall 5243392 Oct 31 16:30 redo02.log
-rw-r----- 1 oracle oinstall 5243392 Oct 31 16:39 redo03.log
-rw-r----- 1 oracle oinstall 2105344 Oct 31 16:53 users02.dbf
-rw-r----- 1 oracle oinstall 10493952 Oct 31 16:53 users01.dbf
-rw-r----- 1 oracle oinstall 26222592 Oct 31 16:53 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 Oct 31 16:53 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 Oct 31 16:53 sysaux01.dbf
-rw-r----- 1 oracle oinstall 5243392 Oct 31 16:53 redo01.log
[oracle@rac1 rev1]$
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 146801344 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> select status from v$instance;
STATUS
----------
STARTED
SQL> shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 146801344 bytes
Database Buffers 293601280 bytes
Redo Buffers 2973696 bytes
SQL> @/home/oracle/Desktop/recreate_ctlfile.sql
Control file created.
SQL> recover database using backup controlfile;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database using backup controlfile;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/rev1/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> col name format a55
SQL> select file#,name from v$datafile;
FILE# NAME
---------- -------------------------------------------------------
1 /u01/app/oracle/oradata/rev1/system01.dbf
2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
4 /u01/app/oracle/oradata/rev1/users01.dbf
5 /u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00005
SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00005' to '/u01/app/oracle/oradata/rev1/users02.dbf';
Database altered.
SQL> recover datafile 5;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/rev1/redo01.log ORA-00342: archived log does not have expected resetlogs SCN 805021
ORA-00334: archived log: '/u01/app/oracle/oradata/rev1/redo01.log'
SQL> recover datafile 5;
ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> select * from v$recover_file;
no rows selected
SQL> select file#,name from v$datafile;
FILE# NAME
---------- -------------------------------------------------------
1 /u01/app/oracle/oradata/rev1/system01.dbf
2 /u01/app/oracle/oradata/rev1/undotbs01.dbf
3 /u01/app/oracle/oradata/rev1/sysaux01.dbf
4 /u01/app/oracle/oradata/rev1/users01.dbf
5 /u01/app/oracle/oradata/rev1/users02.dbf
SQL> select file#,name,status from v$datafile;
FILE# NAME STATUS
---------- ------------------------------------------------------- ----------
1 /u01/app/oracle/oradata/rev1/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/rev1/undotbs01.dbf ONLINE
3 /u01/app/oracle/oradata/rev1/sysaux01.dbf ONLINE
4 /u01/app/oracle/oradata/rev1/users01.dbf ONLINE
5 /u01/app/oracle/oradata/rev1/users02.dbf OFFLINE
SQL>
SQL> alter database datafile 5 online;
Database altered.
SQL> alter system switch logfile;
System altered.
reference:http://hemantoracledba.blogspot.com/2010/08/adding-datafile-that-had-been-excluded.html
