I can't get Oracle to Recover due to corrupt TEMP file!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

  1. #1
    Join Date
    Oct 2000


    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.

  2. #2
    Join Date
    Mar 2001
    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

    svrmgr>alter database open;


  3. #3
    Join Date
    Dec 2000

    Question What are the reasons my dB TEMP files would become corrupt?

    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

  4. #4
    Join Date
    Mar 2001

    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


  5. #5
    Join Date
    Jul 2000

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width