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
Release : 13.0 , 14.0
Component :
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
/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