Thanks for the question, abdullah.
Asked: August 16, 2017 - 5:36 pm UTC
Last updated: August 17, 2017 - 1:31 am UTC
Version: 11.2
Viewed 10K+ times! This question is
You Asked
Hi
I have one production database , and a replica database of it which is open in READ-ONLY mode.
The recovery of the read-only database is done once every night.
I have created a new temporary file on the production environment.
CREATE TEMPORARY TABLESPACE TEMP_NEWSS TEMPFILE '/u06/oradata/imenq/datafiles/temp101.dbf' SIZE 1024M AUTOEXTEND ON;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEWSS;
Now, i have not issued any command on the read-only database for the same. The recovery of the production database is running fine ,
and all the data are intact on read-only database. But , now i am getting the error ORA-25153: Temporary Tablespace is Empty whenever i am running any query on the read only database.
I have tried the following command on read-only instacne :-
alter database rename file '/u06/oradata/imenq/datafiles/temp_new.dbf' to '/u05/oradata/imenq/datafiles/temp101.dbf';
But still , the issue is the same.
select * from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- -------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/u06/oradata/imenq/datafiles/temp101.dbf
1 TEMPNEW 1073741824 131072 ONLINE
1 NO 0 0 0 1072693248 130944
and Connor said...
Depending on how you do the duplicating, temp files are not transferred to a replica database, and with good reason. Just a waste of resources, because after all, its temporary stuff.
When opening a read only database, you can just have the following in your startup scripts:
alter database tempfile '/u06/oradata/imenq/datafiles/temp101.dbf' drop ;
alter tablespace TEMPNEW add tempfile '/u06/oradata/imenq/datafiles/temp101.dbf' reuse;
or just drop the TEMPNEW tablespace and re-create it. Either will be just about instantaneous because temp files are sparse.
Is this answer out of date? If it is, please let us know via a Comment