Oracle DB not starting properly. Error :ORA-01034: ORACLE not available

I have had an issue with Oracle starting properly at startup. When I access the Enter prise manager I get error "Error :ORA-01034: ORACLE not available".  When I try using the Recovery Settings I get "ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist". I followed the instructions in the post : http://www.experts-exchange.com/Databases/Oracle/Q_21250658.html
to try to recover the settings, but it will only restore the database until I restart.

Steps taken:
C:\>SET ORACLE_SID=ORCL
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 23 18:31:13 2007
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile=C:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.1013200718051
ORACLE instance started.
Total System Global Area  595591168 bytes
Fixed Size                  1250356 bytes
Variable Size             163580876 bytes
Database Buffers          427819008 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.

SQL> create spfile='c:\spfileORCL.ora' from pfile='c:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.1013200718051';
File created.

**I then moved the SPFILEORCL.ORA to C:\oracle\product\10.2.0\db_1\dbs and renamed the old one to SPFILEORCL.ORA.OLD

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  595591168 bytes
Fixed Size                  1250356 bytes
Variable Size             197135308 bytes
Database Buffers          394264576 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.
SQL>
------------------------------------
# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )  
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (SID_NAME = orcl)
    )    
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = SEVM20)(PORT = 1521))
    )
  )
 
-----------------------------------------------------------------
# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SEVM20)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )  

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
abeg143Asked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
You can exchange the SPFILE only when Oracle is shut down.
You can not do this with running Oracle.

Also check if you have running firewall on the server and turn it out.

Third thing - check the IP of the server - is it static or DHCP. Must be static.
0
 
imran_fastCommented:
You mean to sya it restores the database when you restart.
so what is your issue now.
0
 
abeg143Author Commented:
Sorry, I inserted the comment about replacing the spfile in the wrong place. I did shutdown Oracle before replacing the spfile. My issue is that the database wil not mount automatically when the server is booted. I have to mount it after every boot. Also, I am using a static IP.
Thanks for your help.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
schwertnerCommented:
Is it Windows XP, SP2?
What about the firewalls, antiviruses?

If yes then go to the Environment Variables and add to the PATH variable the
path to the Oracle_Home directory (if not set). Restart the box

Backup listener.ora file.

Change
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (SID_NAME = orcl)
   )
to

    (SID_DESC =
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (SID_NAME = orcl)
    )

Check the path C:\oracle\product\10.2.0\db_1

In most cases it is:

C:\application\oracle\product\10.2.0\db_1

0
 
abeg143Author Commented:
I am running Windows 2003. No firewalls or antiviruses installed. It is newly formatted.
I made the change to listener.ora, and rebooted, but still same issue. I checked the oracle path and it is correct.
For now, I am running the following commands at startup (from the command prompt) to get the database working properly:
sqlplus / as sysdba
startup

Here is what is in my environment variables:
PATH = C:\oracle\product\10.2.0\db_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem

I did try to fix PERL5LIB env. var. since it had broken references, but it did not help.

OLD PERL5LIB = C:\oracle\product\10.2.0\db_1\perl\5.8.3\lib\MSWin32-x86;
C:\oracle\product\10.2.0\db_1\perl\5.8.3\lib;
C:\oracle\product\10.2.0\db_1\perl\5.8.3\lib\MSWin32-x86;
C:\oracle\product\10.2.0\db_1\perl\site\5.8.3;
C:\oracle\product\10.2.0\db_1\perl\site\5.8.3\lib;
C:\oracle\product\10.2.0\db_1\sysman\admin\scripts;

NEW PERL5LIB = C:\oracle\product\10.2.0\db_1\perl\5.8.3\lib\MSWin32-x86-multi-thread;
C:\oracle\product\10.2.0\db_1\perl\5.8.3\lib;
C:\oracle\product\10.2.0\db_1\perl\site\5.8.3;
C:\oracle\product\10.2.0\db_1\perl\site\5.8.3\lib;
C:\oracle\product\10.2.0\db_1\sysman\admin\scripts;
0
 
crawforditsCommented:
start->run->regedit

navigate to.. HKLM\SOFTWARE\ORACLE\KEY_%YOURORACLEHOME%\

look for ORA_%YOURSID%_AUTOSTART make sure its set to "TRUE"
0
 
abeg143Author Commented:
It was set to true.
0
 
schwertnerCommented:
There are other firewalls - on my computers the sysadmins
install also MicroTrend and it has personal firewall ...
But it seems this is not the reason ...

Check the service (....---> Administrative Tools -----> Services ) in regard of users, groups and
Windows politics. Check if the service is "Autostart". Check if the Admin has installed Oracle.
Check the politics allowed.
Also see the properties of the service.
In 10g Release  on Windows, these services MUST be left to Logon as LocalSystem.  
0
 
crawforditsCommented:
First rename old spfile via windows explorer

then issue

create spfile='C:\oracle\product\10.2.0\db_1\dbs\SPFILE.ora' from
pfile='c:\oracle\product\10.2.\admin\orcl\pfile\init.ora.1013200718051';

then issue

shutdown immediate;

startup;

show parameter spfile;  <-- this will show you that spfile and its path you specified earlier and let you know that oracle is indeed using an spfile not a pfile

if it is null then you didnt create it

HTH



0
 
abeg143Author Commented:
I renamed the spfile, and created the new one with the name SPFILE. When I tried to start it, I got the following error:
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file 'C:\oracle\product\10.2.0\db_1/dbs/spfileor
cl.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.
SQL>
---------------------------
I then created the file again using the original name (SPFILEORCL.ORA) and it started fine. I ran the command and got the information below. I rebooted, and unfortunately still have the same issue.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA
SQL>
0
 
abeg143Author Commented:
The Oracle database, Listener, SQLPLus, and Consoleorcl services are all started and set to automatic startup. They all log on as local system.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.