This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 11, 2010 5:54 AM by user91525 RSS

Cannot recover drop/re-initilialise undo tablespace: cannot open database

407013 Newbie
Currently Being Moderated
Hi

I have an Oracle test database which I found unable to open, because my undo log somehow got corrupted.

In my alert file I get the following error when I try to open the database:

Errors in file C:\oracle\admin\TESTGEA\udump\ORA29760.TRC:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'C:\ORACLE\ORADATA\TESTGEA\UNDOTBS01.DBF'
...
Error 376 happened during db open, shutting down database

The log file ORA29760.TRC has very little information, it simply says:

KCRA: buffers claimed = 6/6, eliminated = 0
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'C:\ORACLE\ORADATA\TESTGEA\UNDOTBS01.DBF'

This is a bit of a catch 22 as I need to open the database (startup mount won't do) in order to do anything with the corrupted tablespace and it is the corrupted undo tablespace which keeps the database from opening.

I don't care so much about lost transactions but this test database is useful and I would like to get it started if I can.

I am afraid that for this database I have no backups from RMAN or a system backup to fall back upon (I know that's bad but it is not often used.)

Anyone knows how to recover from this? Any help will be greatly appreciated.

Thanks

Hugo

  • 1. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open database
    426455 Newbie
    Currently Being Moderated
    Hi.
    There is a way of getting around this. I assume that the database was shut down in a consistant manner.
    I will try to explain how you can get around this problem in steps:
    1) Take a full offline backup of the entire database including redo,control and datafiles.
    2) Use a backup controlfile to prepare to recreate controlfile.
    - Use another database to create a script:
    alter database backup controlfile to trace as 'c:\recr_ctl.sql' reuse;
    Edit c:\recr_ctl.sql by the following rules:
    Remove the entry that consist of the UNDOTBS1. You want to recreate the controlfiles without the undotbs1
    Be sure to edit the files so that all you datafiles from the original (except UNDOTBS1) is represented in the sql file. Also remember to use correct database name. Remember to change name and path for the redo log files as well.
    3) If you have a init-file remove the following entries:
    *.undo_management='AUTO'
    *.undo_retention=10800
    *.undo_tablespace='UNDOTBS1'
    If you have an spfile, use sqlplus /nolog
    create pfile='' from spfile=''
    - remove these lines from init-file and reverse the process, i.e. create spfile from initfile.
    4) Rename existing controlfiles to *.ctl-old
    5) startup nomount
    6) Recreate the controlfile from which you have edited the recr_sql. You can use noresetlogs or resetlogs. I managed to do this with resetlogs. Use noresetlogs of online redo log is OK
    7) Recover the database if nesacery
    6) Open the database with resetlogs.
    8) Now the database is open and accessible. However the DataDictionary still has stored information about the undotbs1 datafile. It will be shown with the name missing000 in dba_data_files.
    9) Drop the undo tablespace including os file.
    10) Create untotbs1 as undo tablespace
    11) Shutdown database
    12) Startup database with old spfile or the following set in init.ora:
    *.undo_management='AUTO'
    *.undo_retention=10800
    *.undo_tablespace='UNDOTBS1'
    Please let me know how things works put.
    Best Rgds
    Kjell Ove
  • 2. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open database
    115987 Newbie
    Currently Being Moderated
    I have just tested the following and it worked for me. Check if it works for you.

    code]
    SQL> startup
    ORACLE instance started.

    Total System Global Area 135338868 bytes
    Fixed Size 453492 bytes
    Variable Size 109051904 bytes
    Database Buffers 25165824 bytes
    Redo Buffers 667648 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
    ORA-01110: data file 2: 'D:\ORACLE\ORADATA\LOCAL\UNDOTBS01.DBF'


    SQL> alter database datafile 'D:\ORACLE\ORADATA\LOCAL\UNDOTBS01.DBF' offline drop;

    Database altered.

    SQL> alter database open;

    Database altered.

    SQL> alter tablespace UNDOTBS1 add datafile 'D:\ORACLE\ORADATA\LOCAL\UNDOTBS01.DBF' size 250M reuse;
    alter tablespace UNDOTBS1 add datafile 'D:\ORACLE\ORADATA\LOCAL\UNDOTBS01.DBF' size 250M reuse
    *
    ERROR at line 1:
    ORA-01537: cannot add data file 'D:\ORACLE\ORADATA\LOCAL\UNDOTBS01.DBF' - file
    already part of database


    SQL> drop tablespace undotbs1;
    drop tablespace undotbs1
    *
    ERROR at line 1:
    ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


    SQL> create undo tablespace undotbs2 datafile 'D:\ORACLE\ORADATA\LOCAL\UNDOTBS02.DBF' SIZE 50M;

    Tablespace created.

    SQL> alter system set undo_tablespace = 'undotbs2' scope = both;

    System altered.

    SQL> drop tablespace undotbs1;

    Tablespace dropped.

    SQL> shutdown
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.

    Total System Global Area 135338868 bytes
    Fixed Size 453492 bytes
    Variable Size 109051904 bytes
    Database Buffers 25165824 bytes
    Redo Buffers 667648 bytes
    Database mounted.
    Database opened.
    SQL>


  • 3. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open database
    426455 Newbie
    Currently Being Moderated
    Hi.
    Tried Kamaljeet Singh procedure for open the database. It is truly a faster way to achieve your goal. Also, no recreation of controlfiles is necessare, so it is really the preferred way to do it.
    Rgds
    Kjell Ove
  • 4. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open database
    616021 Newbie
    Currently Being Moderated
    I have a similar issue to this but in my case the first datafile in the undo tablespace is in recover online_status. I cannot recover it, however i can close and open the database just fine with shutdown immediate and startup. the tablespace has a second file in it and i can log in as a regular user and create tables and add rows and such.

    I cant create any tablespaces however, if i try to it says the file cannot be read at this time; i was hoping to create a new undo tablespaces and drop the old one. has anyone come accross this issue and if so how can i get a new undo tablespace created and switch over to it?
  • 5. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    Anand... Guru
    Currently Being Moderated
    >
    I cant create any tablespaces however, if i try to it says the file cannot be read at this time; i was hoping to create a new undo tablespaces and drop the old one. has anyone come accross this issue and if so how can i get a new undo tablespace created and switch over to it?
    >


    Do you mean, you are not able to create a new undo tablespace. Is Create undo tablespace command giving some error. If so, please post the ORA error that you are facing.

    Anand
  • 6. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    616021 Newbie
    Currently Being Moderated
    yes i am unable to create any kind of tablespace undo or otherwise. if i try to it errors out saying unable to read datafile 3, which is the current undo tablespaces' first datafile which is in recover status.

    ora-00604: error occurred at recursive sql level 1
    ora-00376: file 3 cannot be read at this time
    ora-01110: data file 3: '/u02/oradata/ORADB/undotbs01/dbf'
  • 7. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    Anand... Guru
    Currently Being Moderated
    Hi..

    What is the oracle database version. Also, post the output of

    sql> show parameter undo

    Anand
  • 8. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    Anand... Guru
    Currently Being Moderated
    Hi..

    As you said you can do clean/proper shutdown and startup, then you can perform following steps.

    1. BACKUP the database before starting

    As, you said your database is up, create a pfile from spfile

    sql> create pfile from spfile;

    2. Do a clean shutdown.

    3. Change the UNDO_MANAGEMENT to manual

    4. Startup restrict mount

    5. ALTER DATABASE DATAFILE '/u02/oradata/ORADB/undotbs01.dbf' OFFLINE DROP;

    6. ALTER DATABASE OPEN

    7. DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;

    8. Create a new undo tablespace

    9. alter system set UNDO_MANAGEMENT=AUTO and alter system set UNDO_TABLESPACE=<tbs_name>

    10. alter database disable restricted session;

    11. Shutdown and startup the database.

    NOTE :-- TAKE A PROPER BACKUP BEFORE STARTING

    HTH
    Anand
  • 9. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    616021 Newbie
    Currently Being Moderated
    version is 11.1.6

    step 7 fails due to active rollback segment

    drop tablespace undotbs1 including contents
    *
    ERROR at line 1:
    ORA-01548: active rollback segment '_SYSSMU1_1186132793$' found, terminate
    dropping tablespace

    Edited by: DaveP44 on Dec 7, 2008 5:59 PM
  • 10. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    Anand... Guru
    Currently Being Moderated
    hi..

    How many undo datafiles do , you have.If 2 then take both of them offline drop.

    Anand
  • 11. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    616021 Newbie
    Currently Being Moderated
    yep they are both offline drop, i get the same error.

    its interesting, even though i run the command for both, the one file stays in recover status, not offline like the good datafile.

    Edited by: DaveP44 on Dec 7, 2008 8:44 PM
  • 12. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    Anand... Guru
    Currently Being Moderated
    Hi..

    Can you post the output of

    select
    segment_name,
    status
    from
    dba_rollback_segs
    where
    tablespace_name='undo_tbs_name';


    Refer to [http://www.dba-oracle.com/t_fix_undo_log_corruption.htm]

    [http://www.oracle-base.com/forums/viewtopic.php?f=1&t=9775]

    HTH
    Anand

    Edited by: Anand... on Dec 8, 2008 11:06 AM ---- Added link
  • 13. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    616021 Newbie
    Currently Being Moderated
    Perfect, I was able to offline the rollback segments as Burleson suggests and create a new undo tablespace and drop the old one.

    thanks so much for your help on this,
    Dave
  • 14. Re: Cannot recover drop/re-initilialise undo tablespace: cannot open databa
    Maran Viswarayar Pro
    Currently Being Moderated
    And do you know the impact of the parameter that you have set.....IT makes the database totaly unsupportable..This is an undocumented parameter.....

    Did you check with Oracle before setting up this parameter..The Database can be realy UNSTABLE and i have burnt my fingers wiht this parameters after opening the DB

    Have you shutdown and removed that parameter?
1 2 Previous Next