8 Replies Latest reply: May 5, 2012 11:29 AM by 653084 RSS

    Datafile from Undo tablespace is lost

    653084
      Hi all,

      I was trying to clone a database from a cold backup. I copied all the data files from clone source but I did not realise that 3 files from undo tablespace were not copied due to space issue. But by the time I realised my clone source was over-written with lates data. I have created a controlfile generation script from which I removed name of 3 missing data files. Idea was to create controlfile and then open database with resetlogs. this is how my ccf generation script looks-

      STARTUP NOMOUNT
      CREATE CONTROLFILE REUSE SET DATABASE "RP_CP" RESETLOGS NOARCHIVELOG
      MAXLOGFILES 16
      MAXLOGMEMBERS 2
      MAXDATAFILES 300
      MAXINSTANCES 1
      MAXLOGHISTORY 1460
      LOGFILE
      GROUP 1 (
      --datafile list
      (minus 3 missing undo data files)
      --
      --
      CHARACTER SET WE8ISO8859P1
      ;
      ALTER DATABASE OPEN RESETLOGS;

      But I am unable to start database and database is forced shut.

      Your help is much appreciated.

      Edited by: RP123409 on May 5, 2012 12:08 AM
        • 1. Re: Datafile from Undo tablespace is lost
          rp0428
          >
          Your help is much appreciated.
          >
          You need to provide the EXACT steps you are using and a copy of the exact error messages that you are getting. You also have not provided the Oracle version you are using.
          • 2. Re: Datafile from Undo tablespace is lost
            krramnik
            Yes. Please provide more information as mentioned above.

            What is the output you have got for 'ALTER DATABASE OPEN RESETLOGS;' ?

            Also provide last 100 lines of the alert log in target database which is refusing to come up.
            • 3. Re: Datafile from Undo tablespace is lost
              krramnik
              You may want to use these steps from Database restore without temp, undo and control files.

              You can use a COLD Backup without an Undo Tablespace.
              1. Configure your instance parameter file (initSID.ora or spfileSID.ora) with UNDO_MANAGEMENT='MANUAL';
              (If you are using an SPFILE, you would STARTUP NOMOUNT and issue an ALTER SYSTEM SET UNDO_MANAGEMENT='MANUAL';
              OR you would create a text INIT file by doing a strings on the SPFILE and then edit the initSID.ora file)
              2. Restore your database. But NOT Recover yet.
              3. Issue ALTER DATABASE DATAFILE n OFFLINE commands (where n is the FILE_ID or FILE# which you can get from v$DATAFILE) for each of the "old" undo files
              4. Issue a RECOVER DATABASE UNTIL CANCEL
              5. CANCEL
              6. Issue an ALTER DATABASE OPEN RESETLOGS
              6. Do a CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE .... to create the Undo Tablespace
              7. Do an ALTER TABLESPACE TEMP ADD TEMPFILE ... to "create" the TempFile
              8. Reset UNDO_MANAGEMENT to 'AUTO' and UNDO_TABLESPACE to 'UNDOTBS1'
              9. Restart

              Good luck with the recovery!
              • 4. Re: Datafile from Undo tablespace is lost
                839439
                Hi

                you are approaching towards right direction , but your controlfile script is not correct .it should be something like this


                CREATE CONTROLFILE SET DATABASE "RP_CP" RESETLOGS NOARCHIVELOG
                MAXLOGFILES 16
                MAXLOGMEMBERS 2
                MAXDATAFILES 300
                MAXINSTANCES 1
                MAXLOGHISTORY 1460
                LOGFILE
                GROUP 1 (
                --datafile list
                (minus 3 missing undo data files)
                --
                --
                CHARACTER SET WE8ISO8859P1
                ;


                check the below link this may help you
                http://neeraj-dba.blogspot.in/2011/05/cold-clonning-using-controlfile-backup.html



                --neeraj                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                • 5. Re: Datafile from Undo tablespace is lost
                  653084
                  First of all thanks to you all for your help.

                  Sorry I did not give enough information...was too tired after sitting for 18 hours!!(not an excuse though..;)

                  Here are the details-

                  OS - Sun Solaris 10
                  DB - Oracle 9i
                  Using pfile
                  Noarchivelog

                  and yes, I got force shutdown with ora 600; I believe it was because undo management was AUTO.

                  I have decided not to try anything on the copy of datafile that that I have from old clone source.

                  So what I am going to do is, create a similar situation on one of my test systems and try to do the recovery using these steps-

                       Copy all the DB files to another server..
                       Create a ccf generation script and from this file remove unavailable undo datafile
                       In the pfile changed undomanagement from AUTO to MANUAL
                       From ccf file create controlfile.
                       Then manually opened database with resetlogs

                  Hopefully this is going to work!!

                  Will keep posted. But in case someone thinks there is a flaw in my steps please advise.

                  Once again thanks to you all
                  • 6. Re: Datafile from Undo tablespace is lost
                    mBk77
                    Try:

                    1.Open the Db in mount state
                    2.Create the controlfile, dont remove the undo datafiles
                    3. offline the datafile belonging to undo TS.
                    alter database datafile 'undotbs01.dbf' offline;
                    Then drop the datafiles.
                    4 add a new datafile to the undo TS.
                    5 start the database.
                    • 7. Re: Datafile from Undo tablespace is lost
                      653084
                      But my situation is that after copying datafiles from the clone source database was never brought up. Anyway since I am going to try on a test environment, I will try both options. Whichever works.
                      • 8. Re: Datafile from Undo tablespace is lost
                        653084
                        Hi All, thanks for your support, at last i managed to do recovery in my test environment. This is what I did (as mentioned in my previous post)-

                        Copy all the DB files to another server..
                        Create a ccf generation script and from this file remove unavailable undo datafile
                        In the pfile changed undomanagement from AUTO to MANUAL
                        commented out - transactions_per_rollback_segment (Dont know if this makes any diff)
                        From ccf file created controlfile.
                        Then alter database open reset

                        In the v$datafile I can see entry for the missing file -

                        /dir/product/9208/dbs/MISSING00003 RECOVER(status)

                        Under mount restrict mode I tried this -

                        alter database datafile '/dir/product/9208/dbs/MISSING00003' offline drop;

                        Command was executed but I can still see entry in the v$datafile

                        Is there any way I can remove this entry from v$datafile?

                        Thanks.

                        Edited by: RP123409 on May 5, 2012 5:28 PM