Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Unhappy Unanswered: Problem with UNDO tablespace on 9.2

    I have an instance running on 9.2, of which the UNDO tablespace has been lost when a drive crashed. I have been able to recover the rest of the database files and have copied them over the top of the "default database" files set up on installation, hoping to allow them to be used with a new installation of 9.2. Having also copied the SPFILE and the PWD files over the top of the default ones, all is fine when starting (START and MOUNT are OK) until I get errors showing that the UNDOTBS01 file is (correctly) not part of the database:
    ORA-01122: database file failed verification check
    ORA-01110: data file 2 D:\Data\Oracle\JohnG\UNDOTBS1.DBF
    ORA-1206: file is not part of this database - wrong database ID
    and the database is not OPENed.

    Is there any way in which I can work around this for long enough to start the instance? Obviously until I can, I can't create a new UNDO tablespace, or anything... Failing that, is there any way that I can extract any information from the "old" Oracle database at a command-line level, to allow me to import it into the "new" one (given that I have all of the files apart from the UNDOTBS01)?

    Suggestions gratefully received.

    John Geraghty

  2. #2
    Join Date
    Mar 2002
    Reading, UK
    This link will probably be useful

    but I usually find I kind of muddle my way through recovery trying out different things until it works hopefully : )


  3. #3
    Join Date
    Sep 2003


    Alan -

    Thanks for that... in the meantime I've done some more searching. Found an example of "recovering a database after losing UNDO tablespace":

    SQL> show parameter undo

    undo_management string AUTO
    undo_retention integer 10800
    undo_suppress_errors boolean FALSE
    undo_tablespace string UNDOTBS01

    SQL> alter system set undo_management = manual scope=spfile;

    System altered.

    SQL> shutdown
    ORA-01109: database not open

    Database dismounted.
    ORACLE instance shut down.

    SQL> exit

    sqlplus /nolog

    SQL> connect / as sysdba;
    Connected to an idle instance.
    SQL> startup
    ORACLE instance started.
    < Usual SGA stuff>

    SQL> alter database datafile 'D:\data\oracle\johng\undotbs01.dbf'
    2 offline drop;

    Database altered.

    SQL> alter database open;

    Database altered. ********

    SQL> drop tablespace undotbs01;

    Tablespace dropped.

    SQL> create UNDO tablespace......

    This works OK up to the point I've marked "********", where SQL+ returns
    ORA-0192: ORACLE instance terminated. Disconnection forced

    And it was going so well up to there. Certainly the solution appears to it my problem, just doesn't perform as described... Any ideas?

Posting Permissions

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