Recently one of our database was migrated. After migration DB is up and running, But we found below errors in the alert log.

 

Errors in file /oracle/app/oracle/admin/test/diag/rdbms/b2btrn/TEST/trace/test_ora_20464.trc:
ORA-01157: cannot identify/lock data file 205 – see DBWR trace file
ORA-01110: data file 205: ‘/test/oradata/b2btrn/tempfile/temp1.304.921456819’

Now I queried dba_temp_files, and got the same error.

 

SQL> select file_name from dba_temp_fileS;
ERROR:
ORA-01157: cannot identify/lock data file 205 – see DBWR trace file
ORA-01110: data file 205: ‘/test/oradata/b2btrn/tempfile/temp1.304.921456819’

And the file is not present physically also:
ls -ltr /test/oradata/b2btrn/tempfile/temp1.304.921456819
/test/oradata/test/tempfile/temp1.304.921456819: No such file or directory

 



 
To fix it , I have dropped the temp file:

 

SQL>select file#,name from v$tempfile
FILE# NAME
———- ——————————————————————-
1 /test/oradata/temp03.dbf
3 /test/oradata/b2bsit1_iastemp.dbf
4 /test/oradata/sit1b2b_iastemp.dbf
2 /test/oradata/b2btrn/tempfile/temp1.304.
SQL> alter database tempfile ‘/test/oradata/b2btrn/tempfile/temp1.304.921456819’ drop;

Database altered.

SQL>select file_name from dba_temp_fileS

FILE_NAME
————————————————————–
/test/oradata/b2bsit1_iastemp.dbf
/test/oradata/sit1b2b_iastemp.dbf
/test/oradata/temp03.dbf