on 10-28-2007 12:48 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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;
more to the brspace documentation see please following documents:
as I told before, if you want to use BR*Tools, you can follow the menu after calling <b><i>brtools</i></b>.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.