5 Replies Latest reply: Feb 15, 2013 11:46 AM by JohnWatson RSS

    ORA-01219: database not open

    user12004297
      Hello
      i have created a tablespace, however some moved the datafiles of course then oracle crashed, i have done "alter database datafile '/or/oradata/mine.dbf' offline drop;
      however now i cant start oracle
      SQL> shutdown immediate
      ORA-01109: database not open


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

      Total System Global Area 8401256448 bytes
      Fixed Size 2243240 bytes
      Variable Size 1358955864 bytes
      Database Buffers 6996099072 bytes
      Redo Buffers 43958272 bytes
      Database mounted.
      ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-01194: file 1 needs more recovery to be consistent
        • 1. Re: ORA-01219: database not open
          user12004297
          forgot to mention Oracle 11.2.2 on linux


          SQL> recover database using backup controlfile;
          ORA-00279: change 1192176321 generated at 02/14/2013 16:44:38 needed for thread
          1
          ORA-00289: suggestion : /u01/app/oracle/uat2/11.2/dbs/arch1_504_761829780.dbf
          ORA-00280: change 1192176321 for thread 1 is in sequence #504


          ORA-00308: cannot open archived log
          '/u01/app/oracle/uat2/11.2/dbs/arch1_504_761829780.dbf'
          ORA-27037: unable to obtain file status
          Linux-x86_64 Error: 2: No such file or directory
          Additional information: 3
          • 2. Re: ORA-01219: database not open
            EdStevens
            user12004297 wrote:
            forgot to mention Oracle 11.2.2 on linux


            SQL> recover database using backup controlfile;
            ORA-00279: change 1192176321 generated at 02/14/2013 16:44:38 needed for thread
            1
            ORA-00289: suggestion : /u01/app/oracle/uat2/11.2/dbs/arch1_504_761829780.dbf
            ORA-00280: change 1192176321 for thread 1 is in sequence #504


            ORA-00308: cannot open archived log
            '/u01/app/oracle/uat2/11.2/dbs/arch1_504_761829780.dbf'
            ORA-27037: unable to obtain file status
            Linux-x86_64 Error: 2: No such file or directory
            Additional information: 3
            looks like someone moved more that one of your data files. What happened to the archivelog at /u01/app/oracle/uat2/11.2/dbs/arch1_504_761829780.dbf?
            There's more here than you are telling us, but I'd suggest before you do ANYTHING else, you shutdown the database, then copy all the data files, archivelogs, control files, online redo logs (yes, I say back them up as well) init.ora, spfile.ora, password files -- all of them, to a secure location where you are not going to accidentally delete or modify them.
            • 3. Re: ORA-01219: database not open
              JohnWatson
              user12004297 wrote:
              forgot to mention Oracle 11.2.2 on linux


              SQL> recover database using backup controlfile;
              ORA-00279: change 1192176321 generated at 02/14/2013 16:44:38 needed for thread
              1
              ORA-00289: suggestion : /u01/app/oracle/uat2/11.2/dbs/arch1_504_761829780.dbf
              ORA-00280: change 1192176321 for thread 1 is in sequence #504


              ORA-00308: cannot open archived log
              '/u01/app/oracle/uat2/11.2/dbs/arch1_504_761829780.dbf'
              ORA-27037: unable to obtain file status
              Linux-x86_64 Error: 2: No such file or directory
              Additional information: 3
              You may be getting this message because you used the USING BACKUP CONTROLFILE clause, when you you are in fact not using a backup controlfile. Try a simple RECOVER DATABASE; instead. If that fails, there are more possibilities.
              --
              John Watson
              http://skillbuilders.com
              • 4. Re: ORA-01219: database not open
                user12004297
                still failed

                SQL> recover database;
                ORA-00283: recovery session canceled due to errors
                ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


                SQL>
                • 5. Re: ORA-01219: database not open
                  JohnWatson
                  user12004297 wrote:
                  still failed

                  SQL> recover database;
                  ORA-00283: recovery session canceled due to errors
                  ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


                  SQL>
                  OK, then we can move on.

                  It seems that you have not only moved datafiles, but also have an old version of the controlfile. It would be useful if you were to determine how that happened, and if possible replace it with the current controlfile. If you cannot do that for some reason, you need to mount the database and run RECOVER DATABASE USING BACKUP CONTROLFILE; as you did bfore, and when prompted for the archivelog that does not exist, nominate one of your online logfiles. We don't know which one, so try them all.
                  If that doesn't work, there are still possibilities - one of whih is to hire a consultant to sort out this mess (I'm sure my boss would be happy to give you a quote :) )
                  --
                  John Watson
                  http://skillbuilders.com