SPAM 7.02 - Error TP_FAILURE IMPORT_PHASE

today, we want to patch out SPAM, because it's prerequisites before we upgrade our system prior latest version.

we got error TP_FAILIRE at IMPOR_PHASE, what we do ??
Go to log from TP, you can go to /usr/sap/trans/log, and find P<year><month><date>.<SID>, in this case we have P131108.ASM and we got error

2EETP345 19:33:27: Retcode 1: SQL-error "1157-ORA-01157: cannot identify/lock data file 201 - see D
2EETP345 BWR trace file
2EETP345 ORA-01110: data file 201: '/oracle/ASM/sapdata1/temp_1/temp.data1'" in DDL statement for "
2EETP345 PATHISTQ                      "

before we check this log, we already update our TP and R3Trans to Global/Local Kernel

from those log, we check at filesystem /oracle/ASM/sapdata1/, but we could not found that file :-(

lets fix this :

#1 Drop temp_1 datafile and check status of temp_1 datafile

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

     FILE# STATUS

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

NAME

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

         1 ONLINE

/oracle/ASM/sapdata1/temp_1/temp.data1

SQL> select tablespace_name,file_name from dba_temp_files;

select tablespace_name,file_name from dba_temp_files

                                      *

ERROR at line 1:

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

ORA-01110: data file 201: '/oracle/ASM/sapdata1/temp_1/temp.data1'

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIME          TS#     RFILE# STATUS

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

ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

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

NAME

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

         1             3533 31-OCT-13                3          1 ONLINE

READ WRITE          0          0   1268776960       8192

/oracle/ASM/sapdata1/temp_1/temp.data1

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

     FILE# STATUS

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

NAME

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

         1 ONLINE

/oracle/ASM/sapdata1/temp_1/temp.data1

SQL> alter database tempfile '/oracle/ASM/sapdata1/temp_1/temp.data1' drop;

Database altered.

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

no rows selected

#2 Add new temp datafile

SQL> ALTER TABLESPACE PSAPTEMP add tempfile '/oracle/ASM/sapdata1/temp_2/temp.data2' size 10G;

Tablespace altered.

SQL>

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

     FILE# STATUS

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

NAME

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

         1 ONLINE

/oracle/ASM/sapdata1/temp_2/temp.data2

SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME

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

FILE_NAME

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

PSAPTEMP

/oracle/ASM/sapdata1/temp_2/temp.data2

REMEMBER : before adding temp_2 datafile, you must create temp_2 directory below /oracle/ASM/sadpata1

Cheers!! we can continue SPAM again