ORA-01157: cannot identify/lock data file 202 - see DBWR trace file - Temp file issue

We were facing below error :- 

SQL> select file_name from dba_temp_files;

select file_name from dba_temp_files

                                   *

ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf'

SQL> select tablespace_name,sum(bytes)/(1024*1024) Temp_Size_MB from dba_temp_files group by tablespace_name;

select tablespace_name,sum(bytes)/(1024*1024) Temp_Size_MB from dba_temp_files group by tablespace_name

                                                                                                      *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf'

Above datafile is dedicated for Tivoli user at database level.

SQL> select username, temporary_tablespace, default_tablespace from dba_users where username='TIVOLI';

username    temporary_tabespace   default_tablespace

------------   ------------------------   ---------------------

TIVOLI         TIVOILTEMPPT              TIVOLIORTS

Before proceeding let's create new temporary tablespace for TIVOLI user:-

SQL> create temporary tablespace IVOLITEMPT1 tempfile '+DATA/' size 2G;

Tablespace created.

Assigned IVOLITEMPT1 temp tablespace to user TIVOLI:-

SQL> alter user tivoli temporary tablespace TIVOLITEMPT1;

User altered.

You can confirm by executing below query:-

SQL> select temporary_tablespace from dba_users where username='TIVOLI';

TEMPORARY_TABLESPACE

------------------------------

TIVOLITEMPT1

1 row selected.

SQL>  select file#,status,name from v$tempfile;

     FILE# STATUS  NAME

---------- ------- --------------------------------------------------

         1 ONLINE  +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

         2 ONLINE  +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

         3 ONLINE  +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL> select username,TEMPORARY_TABLESPACE,DEFAULT_TABLESPACE from dba_users where username='TIVOLI';

USERNAME           TEMPORARY_TABLESPACE       DEFAULT_TABLESPACE

-------------------- ------------------------------ ------------------------------

TIVOLI                   TIVOLITEMPT1                      TIVOLIORTS

tivoliortstempt01.dbf --This is culprit datafile so let's drop it 

SQL> alter database tempfile '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf' offline ;

Database altered.

SQL> select file#,status,name from v$tempfile;

     FILE# STATUS  NAME

---------- ------- --------------------------------------------------

         1 ONLINE  +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

         2 OFFLINE +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

         3 ONLINE  +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL>

SQL>

SQL> select tablespace_name, file_name from dba_temp_files;

TABLESPACE_NAME FILE_NAME

--------------- --------------------------------------------------

TEMP            +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

TIVOLITEMPT1    +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

TIVOLITEMPT     +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

3 rows selected.

SQL>  select file#,status,name from v$tempfile;

     FILE# STATUS  NAME

---------- ------- --------------------------------------------------

         1 ONLINE  +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

         2 OFFLINE +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

         3 ONLINE  +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL>

SQL> alter database tempfile '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf' drop including datafiles;

Database altered.

SQL>

SQL>

SQL>  select file_name from dba_temp_files;

FILE_NAME

-------------------------------------------------------------------------------------

+DATA/ORCLDB/TEMPFILE/temp.308.1160136011

+DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

2 rows selected.

SQL>

SQL>

SQL>  select * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED           INST_ID

--------------- --------------- --------------- ---------- ------------- ----------

TEMP                       3.4084E+10        65011712 3.4069E+10 SHARED

TIVOLITEMPT1         2147483648         1048576 2146435072 SHARED

2 rows selected.

SQL>

SQL>

SQL>