Oracle 10G Physical Standby Temp Files

Posted on 2005-05-09
Last Modified: 2008-03-06
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!

Question by:regisdaniel
    LVL 47

    Accepted Solution

    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.

    Author Comment

    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!
    LVL 47

    Expert Comment

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Init.ora File Points to SPFILE 6 50
    SQL Query 6 55
    apply oracle patches on windows error 2 31
    CREATE TABLE syntax 4 33
    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now