No temp tablespace after recovery

Products

• CA Harvest Software Change Manager

Issue/Introduction

SAN failure requiring a restore from tape of the Oracle database.  Missing temporary tablespace that is specific for Harvest

This is the error:

E03020003: Database error [CA Harvest SCM][ODBC Oracle driver][Oracle]ORA-25153: Temporary Tablespace is Empty

How can I recreate the correct temp table space, or repopulate the one that was created?

SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

TABLESPACE_NAME                SUM(BYTES_USED) SUM(BYTES_FREE)
------------------------------ --------------- ---------------
TEMP01                                 1048576       208666624

 

Cause

SAN failure

Environment

Release : 13.0 , 14.0

Component :

Resolution

Steps to correct

 

select tb.tablespace_name, tf.file_name from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';

 

This showed I had two temp spaces, but one didn't have a data file associated with it:

 

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP

 

TEMP01
/opt/app06/oracle/oradata/oracapp06/temp01.dbf

 

Used this command to create temp space:

 

alter tablespace TEMP add tempfile '/opt/app06/oracle/oradata/oracapp06/temp03.dbf' size 2789212160 reuse autoextend on next 10485760 maxsize unlimited;

 

Now:

 

SQL> select tb.tablespace_name, tf.file_name from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';

 

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/opt/app06/oracle/oradata/oracapp06/temp03.dbf

 

TEMP01
/opt/app06/oracle/oradata/oracapp06/temp01.dbf