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
CA Harvest SCM Release : 13.0 , 14.0 and up
SAN failure
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
<oracle_base folder>/oradata/<instance>/temp01.dbf
Used this command to create temp space:
alter tablespace TEMP add tempfile '<oracle_base folder>/oradata/<instance>/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
<oracle_base folder>/oradata/<instance>/temp03.dbf
TEMP01
<oracle_base folder>/oradata/<instance>/temp01.dbf