Oracle 10G Physical Standby Temp Files

Hi all!

I have a Standby Physical Oracle Database. I created this database copying all datafiles from the primary site to the standby site, and creatig a standby control file with the "create standby controlfile command".

My primary site have three temp files, that was copied to the standby site.

My standby site have the parameter standby_file_management = auto.

So, today, i created a new temp file to my primary site, and, expected, that this file should be created on the standby too, as heapens with the datafiles, but, this doens,t heapean...

Inspecting my alert_XXXX.log file, it says the folowing message:
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
         been restored.  It may be necessary to add files to these
         tablespaces.  That can be done using the SQL statement:

         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE

         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TS_TEMP

So, i'd like to know why this message is appearing and if the tempfiles are created automatically on standby site. If no, should i create the files using the command above?

Could i have problems in a failover if this file won't created?

PS.: The Standby Site Status and Log_Application Services is still working perfectly.

Thanks all!

Who is Participating?
schwertnerConnect With a Mentor Commented:
The temporary tablespaces do not need to be transfered.
They are used only by sort operations.
I think you can create them on the standby if they are not created automatically.
In fact you do not need the temporary files on the standby, because you will use
selects with sortings there only after failover of the primary.
regisdanielAuthor Commented:
Ok schwertner! Thanks for your fast answer.


1) How should i proceed on a failover? The database won't crash because these files ins't there?
2) How could i create these datafiles after a failover? Using the normal process? (ADD TEMPFILE ..."?

Thanks again!
No, the database will not crash only because you do not have temporary files.
In most cases they aren't used at all.
But you can add them on the standby - it is easy.
There are bugs and dark corners in the synchronyzation between Primary and Standby especially
when structural changes are made on the Primary. So you have to investigate the documentation
of Oracle (I mean the pdfs).

After failover the first step is to run the Standby as Primary. You will not have troubles to do this.
After that you can add the TEMP files using the traditional way of Oracle:
or using OEM for this.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.