Oracle : Problem PSAPTEMP

Today, we already finished clone SAP Production which is will be tested to Functional team, after we decide to let Functional team, we got problem when we want to extend TABLESPACE.

we run BRtools to extend TABLESPACE, but in the middle processing we got issues there is a temp datafile does not exist when we restore database, we check again our backup and we did not find temp datafile.

here the error message:

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

and we follow the detail error, the Oracle said that temp.data1 does not exist at Oracle Data  ( /oracle/SID/sapdata1/temp_1/temp.data1 )

In this case, our oracle in Open condition and we can access SAP.

We try to check at Oracle Level

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIME          TS#     RFILE# STATUS
---------- ---------------- --------------- ---------- ---------- -------
ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
         1             6043 17-JUN-10                3          1 ONLINE
READ WRITE          0          0   1468006400       8192
/oracle/BMP/sapdata1/temp_1/temp.data1

         2        851216007 12-AUG-11                3          2 ONLINE
READ WRITE 8589934592    1048576   8589934592       8192
/oracle/BMP/sapdata1/temp_2/temp.data2

     FILE# CREATION_CHANGE# CREATION_TIME          TS#     RFILE# STATUS
---------- ---------------- --------------- ---------- ---------- -------
ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------

One simple idea is we try to Alter Database and reuse (create temp data file).

SQL> alter tablespace PSAPTEMP add tempfile '/oracle/BMP/sapdata1/temp_1/temp.data1' size 1468006400 reuse autoextend off;
alter tablespace PSAPTEMP add tempfile '/oracle/BMP/sapdata1/temp_1/temp.data1' size 1468006400 reuse autoextend off
*
ERROR at line 1:
ORA-01537: cannot add file '/oracle/BMP/sapdata1/temp_1/temp.data1' - file
already part of database

But, it does not work :-( , Oracle still check at v$tempdatafile;

Ok lets we try to drop and re-create temp data file .

SQL> alter tablespace PSAPTEMP drop tempfile '/oracle/BMP/sapdata1/temp_1/temp.data1';

Tablespace altered.

SQL> alter tablespace PSAPTEMP add tempfile '/oracle/BMP/sapdata1/temp_1/temp.data1' size 8192M;

Tablespace altered.

Great!!! it works and we can extend tablespace.