1 2 Previous Next 17 Replies Latest reply: May 24, 2012 7:52 AM by user10283237 RSS

    How to re-create controlfile

    431408


      Hello all,


      I get the following errors when I attempt to start the database instance(this was after a power failure which shutdown the machine uncleanly):



      SQL>startup

      ora-01122: database file 3 failed verification check
      ora-01110: database file 3 'C:\oracle\oradata\psdb\CWMIT01.DBF'
      ORA-01207: file is more recent than controlfle - old controlfile


      How do I create a new controlfile to replace the one which got corrupted?


      OS: Windows XP
      Database: Oracle 10g


      Regards,
      Hussam Galal
        • 1. Re: How to re-create controlfile
          The Human Fly
          Dont you have copy/backup for your current controlfile? Dont you have multiple copies of controlfiles (multiplexed)?

          You can use create controlfile command when instance is nomount stage, knowing the location of your datafiles and redo log files.

          If you have backup, startup the database with mount stage, dump controlfile to a trace file and then shutdown, nomount and create the controlfile.

          Recently, I have answered similar question with so much in detail. Search for it.

          Jaffar
          • 2. Re: How to re-create controlfile
            431408

            I have lost my current control file, file is corrupted and I dont have any backup nor the file is mutliplexed, I was wondering what's the command to re-create a new control file?
            • 3. Re: How to re-create controlfile
              The Human Fly
              1. STARTUP NOMOUNT
              2. CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS NOARCHIVELOG ==depends what is your db log mode
              MAXLOGFILES 32
              MAXLOGMEMBERS 2
              MAXDATAFILES 30
              MAXINSTANCES 8
              MAXLOGHISTORY 800
              LOGFILE
              GROUP 1 '/u01/oracle/7.1.6/dbs/log1p716.dbf' SIZE 500K,
              GROUP 2 '/u01/oracle/7.1.6/dbs/log2p716.dbf' SIZE 500K,
              GROUP 3 '/u01/oracle/7.1.6/dbs/log3p716.dbf' SIZE 500K
              DATAFILE
              '/u01/oracle/7.1.6/dbs/systp716.dbf' SIZE 40M,
              '/u01/oracle/7.1.6/dbs/tempp716.dbf' SIZE 550K,
              '/u01/oracle/7.1.6/dbs/toolp716.dbf' SIZE 15M
              ;

              # Recovery is required if any of the datafiles are restored backups,

              # or if the last shutdown was not normal or immediate.

              RECOVER DATABASE

              # Database can now be opened normally.

              ALTER DATABASE OPEN;


              Jaffar
              • 4. Re: How to re-create controlfile
                431408
                Hussien, I have tried performing the command you just gave me, but I got the following error :


                SQL> CREATE CONTROLFILE REUSE DATABASE "PSDB" NORESETLOGS NOARCHIVELOG;
                CREATE CONTROLFILE REUSE DATABASE "PSDB" NORESETLOGS NOARCHIVELOG
                *
                ERROR at line 1:
                ORA-01503: CREATE CONTROLFILE failed
                ORA-01565: error in identifying file
                '%ORACLE_HOME%\DATABASE\DBS1%ORACLE_SID%.ORA'
                ORA-27041: unable to open file
                OSD-04002: unable to open file
                O/S-Error: (OS 2) The system cannot find the file specified.
                • 5. Re: How to re-create controlfile
                  AlokKumar
                  Hi,
                  try out this

                  CREATE CONTROLFILE SET DATABASE "whatever u want" RESETLOGS NOARCHIVELOG;
                  CREATE CONTROLFILE REUSE DATABASE "PSDB" NORESETLOGS NOARCHIVELOG
                  same as the previous one..

                  but make sure your logfile and datafiles maps the location where your datafiles and logfile are actuly stored.this will help you.

                  take care..


                  • 6. Re: How to re-create controlfile
                    The Human Fly
                    Use resetlogs instead of noresetlogs. And let me know.


                    Jaffar

                    • 7. Re: How to re-create controlfile
                      431408
                      I'm still getting the same error message:

                      SQL>shutdown abort
                      SQL> conn sys/passwordHere as sysdba
                      SQL>startup nomount


                      SQL> CREATE CONTROLFILE REUSE DATABASE "PSDB" RESETLOGS NOARCHIVELOG;
                      CREATE CONTROLFILE REUSE DATABASE "PSDB" RESETLOGS NOARCHIVELOG
                      *
                      ERROR at line 1:
                      ORA-01503: CREATE CONTROLFILE failed
                      ORA-01565: error in identifying file
                      '%ORACLE_HOME%\DATABASE\DBS1%ORACLE_SID%.ORA'
                      ORA-27041: unable to open file
                      OSD-04002: unable to open file
                      O/S-Error: (OS 2) The system cannot find the file specified.
                      • 8. Re: How to re-create controlfile
                        The Human Fly
                        It look like that datafiles that have been mentioned in your create controlfile statement has problems. Make sure you have defined all datafile and redolog file name with correct path.

                        Jaffar
                        • 9. Re: How to re-create controlfile
                          Yasu
                          Dear Jaffar,

                          I have faced same problem and i did exactly what u had told ...

                          But am having doubt that after recreating control file how come database was opened with out resetlog option.

                          Please inform when to use resetlogs and noresetlogs in create control file command.

                          Regards,
                          Yasser
                          • 10. Re: How to re-create controlfile
                            Catfive Lander
                            Briefly, you would do a RESETLOGS when re-creating a controlfile that seeks to change the database name (SET DATABASE "Something"), rather than accepting the automatically-generated value of REUSE DATABASE "Something".

                            You would also need to do a resetlogs if you've just performed an incomplete recovery, of course.
                            • 11. Re: How to re-create controlfile
                              670816
                              are there any effects of such control file recovery on backups .lets say backups created by RMAN ?
                              • 12. Re: How to re-create controlfile
                                668822
                                it dosent affect any of your backup,

                                do you have rman or cold backup??

                                if yes then recover database using resetlogs option.

                                and plz check the location of datafiles are appropriate.

                                have you made changes in initSID.ora file regarding path of controlfile?

                                then it must be there


                                reply
                                thanks and regards
                                VD
                                • 13. Re: How to re-create controlfile
                                  670816
                                  I have a hot backup using RMAN

                                  Suppose i figure out that while my database was down all my multiplexed control files are lost

                                  BUT

                                  i had a backup of control file..... i backed it up using

                                  alter database backup control file to trace; command


                                  i execute the script which i have in the above trace file.....

                                  the control file is created and the database goes to Mount state....... and i open my database using RESETLOGS

                                  at this moment would my backups would be valid?

                                  In the sense would i be able use the backups and restore my database to a earlier point.

                                  Reason i ask this beacuse the Control file script i have in the trace file created says >>


                                  Set #1. NORESETLOGS case
                                  --
                                  -- The following commands will create a new control file and use it
                                  -- to open the database.

                                  <b>-- Data used by Recovery Manager will be lost. </b>

                                  -- Additional logs may be required for media recovery of offline
                                  -- Use this only if the current versions of all online logs are
                                  -- available.
                                  -- After mounting the created controlfile, the following SQL
                                  -- statement will place the database in the appropriate
                                  -- protection mode:



                                  Set #2. RESETLOGS case
                                  --
                                  -- The following commands will create a new control file and use it
                                  -- to open the database.

                                  <b>-- Data used by Recovery Manager will be lost</b>

                                  -- The contents of online logs will be lost and all backups will
                                  -- be invalidated. Use this only if online logs are damaged.
                                  -- After mounting the created controlfile, the following SQL
                                  -- statement will place the database in the appropriate
                                  -- protection mode:



                                  Both the cases say that data used by RMAN will be lost.

                                  could you please make clear the consequences of such a control file recovery
                                  • 14. Re: How to re-create controlfile
                                    srsatya
                                    Hi Jaffer,

                                    Could you clarify me that is it not necessary always to mention the character set at the end of the controlfile, while creating the control file....?
                                    I did not see the character set in your above post... or is it a typo mistake.

                                    Edited by: sr.satya on Nov 20, 2008 1:06 AM

                                    Edited by: sr.satya on Nov 20, 2008 1:09 AM
                                    1 2 Previous Next