6 Replies Latest reply: Oct 29, 2007 5:01 PM by Eric Brunelle RSS

how to create temp files in temp tablespace

Waqas Ahmed
Currently Being Moderated

Dear all,

 

Due to outage of our SAN, we our out of production for the

last 3 days. By the grace of Almighty we have restored production by

database recovery from our standby backup. Since temp tablespace and

temp data files do not taken as backup for standby, now after recovery

we are getting abap dumps asking for temp_1 and temp_2 datafiles.

 

Please guide us how to create temp files. v$tablespace is showing tablespace PSAPTEMP but datafile are not there

 

Abap dumps are giving these errors as mentioned below

====================================================

The exception must either be prevented, caught within the procedure            

"DATA_SELECTION"                                                              

"(FORM)", or declared in the procedure's RAISING clause.                       

To prevent the exception, note the following:                                  

Database error text........: "ORA-01157: cannot identify/lock data file 256 -  

see DBWR trace file#ORA-01110: data file 256:                                 

'/oracle/SD1/sapdata4/temp_2/temp.data2'"                                     

Internal call code.........: "[RSQL/FTCH/MARA ]"                               

===================================================

 

another one asking for 

 

'/oracle/SD1/sapdata3/temp_1/temp.data1'"

 

 

 

Best Regards

Waqas

  • Re: how to create temp files in temp tablespace
    K. Baran
    Currently Being Moderated

    if you want to add a new tempfile to your TEMP Tablespace,you can do like that.

     

    <i>ALTER TABLESPACE</i> <<b><u>name of TEMP Tablespace</u></b>> ADD TEMPFILE <<b><u><b><u>pfad to the file_and_file name</u></b></u></b>> <b>SIZE</b> <size>;

     

    You can use also the options <i>REUS</i>E <i>autoextend off</i> or <i>on</i> .

     

    e.g:

     

    <i>alter tablespace</i> <u><b>PSAPTEMP</b></u> add <i>tempfile</i> <b><u>'/oracle/SD1/sapdata4/temp_2/temp.data2'</u></b> <i>SIZE 1000K</i> <i>REUSE</i>;

     

    The directory <b><u>temp_2</u></b> should exist.

     

    Or you can use the BR*Tools to create a new datafile. Enter brtools and follow the instructions or menu.

     

    More to TEMP Tablespaces see please following SAP notes:

     

     

    <u><b>659946</b></u> - FAQ: Temporary tablespaces

     

    <u><b>600513</b></u> - ORA-25153 after recovery due to missing tempfiles

     

    and the Oracle Note:

     

    <u><b>160426.1</b></u>: TEMPORARY Tablespaces : Tempfiles or Datafiles ?

     

     

     

    I hope it helps.

    • Re: how to create temp files in temp tablespace
      Waqas Ahmed
      Currently Being Moderated

      Thanks alot Baran for your replies ,

       

      Look

      I want to start rectify this error from creating tempfiles. As you know

      temp tablespace is there but no tempfiles are there because after

      recovery we lost tempfiles, now by looking into control trace file,

      query to create tempfile is simple but i want to know ohter parameters

      important in creating tempfiles like size or type of tempfile to create

      etc which are importnat in SAP, my production SAP is already running

      and users are getting error. Also tell me should i create tempfiles

      while production is running as we usually do in creating datafiles.?

       

       

      i also want to tell you that v$tempfile is showing exact two temp file

      ie

       

      SQL> select NAME, BYTES, ENABLED, STATUS , TS# , blocks, CREATE_BYTES

      from v$tempfile;

       

      NAME       BYTES ENABLED STATUS  TS#  BLOCKS CREATE_BYTES

      -


      -


      -


      -


      -


      -


      -


      -


      /oracle/SD1/sapdata3/temp_1/temp.data1   0 READ WRITE ONLINE

      5  0

      367001600

      /oracle/SD1/sapdata4/temp_2/temp.data2   0 READ WRITE ONLINE

      5  0

      157286400

       

      Create_Byte is showing some values and bytes is showing 0

      whether I  have to drop tempfile first and then to create tempfile.

       

      also tell me size parameter should I use according to above values.

      Best Regards

      Waqas

      • Re: how to create temp files in temp tablespace
        K. Baran
        Currently Being Moderated

        you can create a new TEMP Tablespace and drop the old one.

         

        e.g:

         

        They are only <b><u>examples</u></b>: You should change the <b><u>SIZES</u></b>.

         

        SQL> <i>CREATE TEMPORARY TABLESPACE "<b><u>PSAPTEMP1</u></b>" TEMPFILE '<<b><u>PATH_NAME_OF_NEW_FILE</u></b>>' SIZE <<b><u>SIZE</u></b>>M REUSE AUTOEXTEND ON NEXT  20480K MAXSIZE  10000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K</i>;

         

        SQL> alter database default temporary tablespace <b><u>PSAPTEMP1</u></b>;

         

         

        SQL> drop tablespace PSAPTEMP including contents and datafiles;

         

        SQL> CREATE TEMPORARY TABLESPACE "<b><u>PSAPTEMP</u></b>" TEMPFILE '<<b><u>PATH_NAME_OF_NEW_FILE</u></b>>" SIZE <<b><u>SIZE</u></b>>M REUSE AUTOEXTEND ON NEXT  20480K MAXSIZE  10000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

         

        SQL> alter database default temporary tablespace <b><u>PSAPTEMP</u></b>;

         

        SQL>drop tablespace <b><u>PSAPTEMP1</u></b> including contents and datafiles;

      • Re: how to create temp files in temp tablespace
        Eric Brunelle
        Currently Being Moderated

        do this, easier method:

         

        sqlplus "/ as sysdba"

        ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oracle/trace.sql';

        vi /oracle/trace.sql

         

        check on last lines, you will find SQL command to re-create TEMP files as they were before.

  • Re: how to create temp files in temp tablespace
    K. Baran
    Currently Being Moderated

    what does the following SELECT deliver?

     

    SQL> <u><b>select name from v$tempfile</b></u>;

     

    Which types of tablespaces do you have? DMTS/T (dictionary managed) or  LMTS/T (locally-managed)?

     

    SQL> <i>SELECT CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE

    FROM DBA_TABLESPACES WHERE

    TABLESPACE_NAME</i>='<b><u>PSAPTEMP</u></b>';

     

    See please also these SAP notes about Tablespaces.

     

    <b><u>214995</u></b> - Oracle locally-managed tablespaces in the SAP environment

    <b><u>683075</u></b> - Oracle9i: Default Temporary Tablespace

     

    If you want to use BR*Tools, to create a new tempfile see please the brspace documentation and the following SAP note:

     

    <b><u>647697</u></b> - BRSPACE - New tool for Oracle database administration

     

    best regards

     

    Baran

  • Re: how to create temp files in temp tablespace
    K. Baran
    Currently Being Moderated

    more to the brspace documentation see please following documents:

     

    SAP on Oracle

     

    https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/afc2af2f-0d01-0010-f0ad-dc0e92b3cdcf

     

    as I told before, if you want to use BR*Tools, you can follow the menu after calling <b><i>brtools</i></b>.

Actions