how to create temp files in temp tablespace

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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;

[former_member204746 · Accepted solution]

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.

[Former Member]

more to the brspace documentation see please following documents:

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

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

[Former Member]

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