Solved

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

Posted on 2007-11-24
13
1,680 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
  • 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 47

Accepted Solution

by:
schwertner earned 500 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
 
LVL 47

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 47

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
Read about achieving the basic levels of HRIS security in the workplace.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now