1 2 Previous Next 15 Replies Latest reply: Jan 11, 2010 7:54 AM by user91525 RSS

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

    407013
      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
          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
            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
              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
                DaveP44
                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...
                  >
                  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
                    DaveP44
                    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...
                      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...
                        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
                          DaveP44
                          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...
                            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
                              DaveP44
                              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...
                                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
                                  DaveP44
                                  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
                                    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