8 Replies Latest reply on Jul 5, 2011 3:37 PM by 824063

    ORA-01034: Oracle not available after service restart

    591445
      Hello,

      My first thread here.
      Product: Windows 2003R2 w/ Oracle (10.2.0.1), database created using "dbca".
      Installed services, automatic and running at Windows services.msc:
      ==> OracleDBConsolecm2
      ==> OracleServiceCM2
      ==> OracleTNSListener
      Very fresh install... everything running fine. Also via browser, I can connect to OEM Database Control.
      ==========================================
      I reboot the Windows 2003 server... and after reboot... my database/service named CM2 does not connect anymore. At Windows services.msc I can see those 3 services above up and running. BUT at Windows task manager-> performance... I see that commited memory is low. What I mean is: When the service/database CM2 is running fine...(working)... the commited memory is about 1GB. But after windows reboot or service restart... (equal at logic point of view)... the amount of commited memory is near 300MB (mainly used by the Windows).

      Then... I get a lot of ORA-xxxxxx error messages but I guess the root of problem is here: ORA-01034. Why? Response:

      Below is what I did:
      ======================================================
      C:\>set oracle_sid=cm2

      C:\>sqlplus /nolog

      SQL*Plus: Release 10.2.0.1.0 - Production on Sex Ago 3 22:08:38 2007

      Copyright (c) 1982, 2005, Oracle. All rights reserved.

      SQL> conn sys as sysdba
      Informe a senha:
      Conectado a uma instancia inativa.
      SQL> select * from v$instance;
      select * from v$instance
      *
      ERRO na linha 1:
      ORA-01034: ORACLE not available

      SQL> startup
      Instancia ORACLE iniciada.

      Total System Global Area 612368384 bytes
      Fixed Size 1250452 bytes
      Variable Size 184552300 bytes
      Database Buffers 423624704 bytes
      Redo Buffers 2940928 bytes
      Banco de dados montado.
      Banco de dados aberto.
      SQL> select count(*) from v$instance;

      COUNT(*)
      ----------
      1

      SQL>
      ============================================
      So... I have to always startup manually my CM2 service, because when I restart service at Windows services.msc... it says that the service CM2 is restarted and running... but the cause is ORA-01034... and I have to startup manually as above.

      Another information: When my instance runs fine... I have an "oracle.exe" in the process list of task manager using large amount of memory. And when the instance/service does not work... I see an oracle.exe using by 10MB of memory. BUT in both cases I see my services/instances running at Windows services.msc;

      How to solve this problem?

      Thanks,


      mson77
        • 1. Re: ORA-01034: Oracle not available after service restart
          591452
          Bon dia,

          I have just posted a very similar note. I do not know why but the Windows service and the Oracle instance get out of synch. I do the following to get the instance up and running:
          Set oracle_sid=XYZ
          SQLPLUS /NOLOG
          CONN SYS/???@XYZ AS SYSDBA
          STARTUP
          EXIT

          Have reported this to Oracle but they told me to rerun ORADIM. Not good enough!
          Regards
          • 2. Re: ORA-01034: Oracle not available after service restart
            591445
            Hello,

            Yes.
            I issued these commands manually to have the database running/mounted/open.
            I am using Win2003 server and at task manager-> process tab I see the "oracle.exe" using only 11MB when the database is not working even when the services.msc tells me that the service is running.

            I posted this subject before at:
            http://www.orafaq.com/forum/?t=rview&goto=256362

            and "DreamzZ user" told me to use "oradim" to recreate the SID... but his recommendation deals with PFILE... something like "initXXX.ora"... but I created my DB using "dbca" and I guess it uses SPFILE instead (10g 10.2.0.1 Win32).

            Then I stopped to follow his recommendation.

            But... how can I use ORADIM to recreate my SID/DB... if I don't have PFILE... I just have a file called "init.ora" at "C:\oracle\product\10.2.0\admin\cm2\scripts"

            I found this tip:
            http://www.dba-oracle.com/oracle9iAS_tips_windows_service.htm

            But this tip regards PFILE... :((((

            Regards,

            PS: another one with same problem:
            Re: Using ORADIM to create instance on Oracle 10g R2, windows platform
            • 3. Re: ORA-01034: Oracle not available after service restart
              591445
              Hello,

              I found my SPFILE... and using it I create PFILE.
              Using ORADIM tool I deleted my instance named CM3 (test instance);
              I created new SID named CM3 based on PFILE above.

              Then CM3 starts up and running. But when I restart the service using Windows Service Tool... the database is not mounted/opened... and again I have to using sqlplus... startup manually the database.

              Procedure not good enough...!

              I posted also at:
              http://www.orafaq.com/forum/m/256442/110238/#msg_256442


              Thanks for your help.
              • 4. Re: ORA-01034: Oracle not available after service restart
                591445
                Hello ALL,

                Finally solved!

                Format the hard disk and install a fresh Win2003R2.
                With this fresh Windows install... Oracle databases were created... and initializing instances are perfect now.

                a) from services tool...
                b) from hardware reboot...

                Thank you everybody... I really appreciate a lot your helps!

                Regards,


                mson77
                • 5. Re: ORA-01034: Oracle not available after service restart
                  593360
                  One thing to check. If the Oracle service starts, but you get the "Connected to idle instance" is in the Registry on windows boxes.

                  Start->Run-> type in REGEDIT and hit return.

                  In the Registry editor, navigate to HKEY_LOCAL_MACHINE\software\Oracle\

                  Under this key, you should have a key like: Ora_db10g_Home1, or whatever you called your ORACLE_HOME. Under there, you should see: ORA_<SID>_AUTOSTART.  Make sure that is set to TRUE.  This starts the actual instance when the service starts.  Otherwise, if set to FALSE, the service will start, but the database is "idle". 

                  Hope this helps.
                  • 6. Re: ORA-01034: Oracle not available after service restart
                    591445
                    Hello,

                    I went to windows registry and have check all the entries on:
                    Hkey_LM\SOFTWARE\ORACLE\KEY_home

                    All the entries were perfect.
                    I solved the problem formatting and reinstalling Windows 2003.

                    Appreciate you attention.
                    Regards,
                    • 7. Re: ORA-01034: Oracle not available after service restart
                      598222
                      I'm new to this, then I need the http accessor to the database administration, and this doesn't work neither. I can mount the database from sqlplus as the first post advice, so it seems working there, but when I try to connect to it with SQL Developer I get the error: ORA-01033: Oracle initialization or shutdown in progress.
                      So I can't check anything there.
                      By the way my version of 10g is express edition.

                      Please help!!


                      Regards,

                      GS
                      • 8. Re: ORA-01034: Oracle not available after service restart
                        824063
                        Hi,
                        I know this thread is outdated. I had the same error the last day but couldn't find much abt this topic.

                        I got this rectified by using ORADIM utility

                        ------

                        C:\>oradim -DELETE -SID sid
                        Instance deleted.

                        C:\>oradim -NEW -SID sid -SYSPWD sys -STARTMODE auto -SPFILE

                        OPW-00005: File with same name exists - please delete or rename
                        Instance created.


                        C:\>SQLPLUS sys/sys as sysdba

                        SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 5 20:49:13 2011

                        Copyright (c) 1982, 2005, Oracle. All rights reserved.

                        Connected to an idle instance.

                        SQL> startup
                        ORACLE instance started.

                        Total System Global Area 452984832 bytes
                        Fixed Size 1249536 bytes
                        Variable Size 121638656 bytes
                        Database Buffers 327155712 bytes
                        Redo Buffers 2940928 bytes
                        Database mounted.
                        Database opened.

                        SQL> select status from V$INSTANCE;

                        STATUS

                        OPEN

                        --------


                        Regards
                        Remil Chalat