How to drop a lost TEMPFILE after failed drive

How to drop a lost TEMPFILE after failed drive

When a drive fails and you "lose" a tempfile the status in v$tempfile will show 
ONLINE:

   SQL> select file#,status,name from v$tempfile;
   
        FILE# STATUS  NAME
   ---------- ------- ---------------------------------------------
            1 ONLINE  /u04/oradata/SINAME/temp01.dbf
            2 ONLINE  /u02/oradata/SINAME/temp02.dbf

... but dba_temp_files will tell the "real" story:

   SQL> select file_name,status from dba_temp_files;
   select file_name,status from dba_temp_files
                                *
   ERROR at line 1:
   ORA-01187: cannot read from file 201 because it failed verification tests
   ORA-01110: data file 201: '/u04/oradata/SINAME/temp01.dbf'

to resolve this issue you can simply drop the missing tempfile without causing 
any trouble:

   SQL> alter database tempfile  '/u04/oradata/SINAME/temp01.dbf' drop;
   
   Database altered.

   SQL> select file_name,status from dba_temp_files;
   
   FILE_NAME
   ---------------------------------
   STATUS
   ---------
   /u02/oradata/SINAME/temp02.dbf
   AVAILABLE

you then simply add the file back and it gets created:

   ALTER TABLESPACE TEMP ADD TEMPFILE 
      '/u04/oradata/SINAME/temp01.dbf' SIZE 838860800  
   REUSE AUTOEXTEND OFF;

You can drop and create the TEMPFILE:

ALTER DATABASE TEMPFILE  '/u04/oradata/SINAME/temp01.dbf' DROP;
-- this will not drop physical file

ALTER TABLESPACE BMC_TEMP ADD tempfile  '/u04/oradata/SINAME/temp01.dbf' SIZE 
48M REUSE;
-- this will add file; either reuse or without it.