I can't get Oracle to Recover due to corrupt TEMP file!!!

[johng]

I am running Oracle 8i on Windows NT 4.0 and have lost connection to my database and am unable to recover because of the following error...

ORA-01200 TEMP file H:/TEMP1 is not the size it is expecting...

Is there a way to force Oracle to "accept" this or is there a work around.

[santoshym]

Hi John,

Try to find out from the Alert log and trace file what the problem is.You are lucky I hope this is just the Temporary datafile and does not hold any data.I had tried two options

First thing take a backup of the corrupted datafile

Option 1

I tried to restore from my latest backup which had not worked

Option 2

Perform the following steps

a) Start the database in mount mode

svrmgr> startup mount

b) Perform an offline drop of the data file that is the temp
Iam assuming there is no data in that data file if there is
any data in the data file do not perform the below steps
execute the following command

svrmgr>Alter database dbname
datafile 'h:\temp1' offline drop;

Replace dbname with the name of ur database and specifiy
the path of the datafile

c) Then open the database using the following
command

svrmgr>alter database open;

Regards

[Juler-Bugs]

Hi...why would a TEMP file become corrupt like this? There were no hardware issues relating to the server the database is located on. Is this a WINDOWS NT related issue? I would really like to research what the possible causes are.

Any input would be appreciated...Thanks

[santoshym]

Hi

I feel the max size of a datafile is <= 4 GB on windows NT so if you had autoextend on the Temp datafile it is got extended beyond the limit of 4 GB maybe this might be one of the reson as to why it got corrupted I personally dont allow a Temp datafile to extend beyond 4 GB on windows NT.

But Oracle said they fixed this Bug in the later releases of Oracle 8.0.5

Offcourse If you have acces to metalink.oracle.com if you search thru for Ora-01200 you will be able to find more reasons as to what went wrong

Regards

[shawish_sababa]

Hi

i have this from the metalink and it is from Oracle Support:

"You can offline drop the datafile specified in the error message, open the database, take the tablespace offline, drop the tablespace and recreate it.

startup mount your database
alter database datafile 'filespec' offline drop;
alter database open;
alter tablespace TEMPTABLESPACE offline;
drop tablespace TEMPTABLESPACE;
delete datafiles associated with the TEMPTABLESPACE from the OS
create tablespace TEMPTABLESPACE datafile 'filespec' size xxm;
"
this is what oracle is saying, you can follow there advice