ORA-01157: cannot identify/lock data file

alert logfile

Tue Sep 27 13:48:55 2011
Errors in file /opt/oracle/admin/cccdb/bdump/cccdb_dbw0_28431.trc:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/opt/oracle/oradatatmp/temp_search.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory



I was getting this error after resorting the database from hot backup .

SQL> exec dbms_stats.gather_fixed_objects_stats;
BEGIN dbms_stats.gather_fixed_objects_stats; END;

*
ERROR at line 1:
ORA-01116: error in opening database file 202 <<<<<<<<<<<<<<<<<<<<<<<<-----
ORA-06512: at "SYS.DBMS_STATS", line 13591
ORA-06512: at "SYS.DBMS_STATS", line 13904
ORA-06512: at "SYS.DBMS_STATS", line 14420
ORA-06512: at line 1



then checked from


SQL> select FILE_NAME from dba_temp_files;
ERROR:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/opt/oracle/oradatatmp/temp_esearch.dbf'




drop temp datafile or recreate one ..

alter database tempfile '/opt/oracle/oradatatmp/temp_search.dbf' drop including datafiles;



SQL> alter database tempfile '/opt/oracle/oradatatmp/temp_search.dbf' drop including datafiles;

Database altered.