Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-24
13
Medium Priority
?
1,699 Views
Last Modified: 2013-12-18
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)
    )
  )
0
Comment
Question by:abeg143
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
13 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 20345074
You mean to sya it restores the database when you restart.
so what is your issue now.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 2000 total points
ID: 20349201
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
 

Author Comment

by:abeg143
ID: 20350617
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 48

Expert Comment

by:schwertner
ID: 20356251
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
 

Author Comment

by:abeg143
ID: 20359546
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
 
LVL 9

Expert Comment

by:crawfordits
ID: 20361264
start->run->regedit

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

look for ORA_%YOURSID%_AUTOSTART make sure its set to "TRUE"
0
 

Author Comment

by:abeg143
ID: 20361725
It was set to true.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 20363892
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
 
LVL 9

Expert Comment

by:crawfordits
ID: 20365219
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
 

Author Comment

by:abeg143
ID: 20368299
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
 

Author Comment

by:abeg143
ID: 20368379
The Oracle database, Listener, SQLPLus, and Consoleorcl services are all started and set to automatic startup. They all log on as local system.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question