Tuesday, February 7, 2012

ORA-01034 & ORA-27101: Shared Memory Realm Does Not Exist

ORA-27101 and ORA-01034  combined may occurs in various scenarios .One of the reason for this error is that the database is not up. When we try to connect with oracle database or sometimes during installation we may get this error . We may face this error in some other scenario's too .  Here are few possible scenario's (  some scenario's are from window platform and some are from Linux  platform ) .

Case 1 :  When try to connect with normal user we get the error as

SQL*Plus: Release Production on Mon Feb 6 11:22:08 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Enter user-name: scott
Enter password:
ORA-01034: ORACLE not available
ORA-27101:  shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

To solve this issue , set the ORACLE_SID and start the database using the "sys" user as sysdba and start the database ,

C:\>set ORACLE_SID=noida
C:\>sqlplus sys/xxxx@noida as sysdba
SQL*Plus: Release Production on Mon Feb 6 11:25:13 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

Case  2 :  On a system with multiple IP addresses, when we connect with sqlplus locally on the server  (i.e. "sqlplus user/password") everything is OK, but connecting through a TNS alias, either from the network or locally on the server, we get the the following errors :  

$ sqlplus user/password@db_alias
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

We need to Manually startup the database or check whether the listener is started or not . So either connect as
$export ORACLE_SID=noida 
$ sqlplus sys/password as sysdba 
check the status of listener if not started then start the listener  as
lsnrctl> stop
$export ORACLE_SID=noida
$sqlplus sys/password@noida as sysdba
SQL> startup

Case 3 :  Make sure while connecting, that the ORACLE_SID and ORACLE_HOME is correctly set . There should not be trailing trash in ORACLE_HOME  path . Remove the extra "/" from the end of ORACLE_HOME

Incorrect Home location:    (ORACLE_HOME = /u01/app/oracle/product/11.2.0/)
Correct Home location :     (ORACLE_HOME = /u01/app/oracle/product/9.2.0)

Once , we have correctly set  the path  then  reload  the listener and  set or export  ORACLE_SID  before connecting as :
Linux  : 
$export ORACLE_SID=noida 
$sqlplus sys/xxxx@noida as sysdba
SQL> startup

Window  : 
C:\> set ORACLE_SID=noida
C:\> sqlplus sys/xxxx@noida as sysdba
C:\> startup 

Case 4 :   Sometimes in case of window ,  if event log is full , then we get this error . So delete the event logs and try to connect again .

Case 5 :  Sometimes , in case of window , we resolve this issue by simply restart the oracle services . so restart the window services as
start --> cmd  --- > net stop oracleserviceXXXX
--- >> net start oracleservice .

Enjoy     :-) 


Madhu Sudhana Reddy said...

good document

Anonymous said...

Thanks a lot guru! You saved my day.

Ewerton Costa said...

When I execute the startup command I get TNS:listener does not currently know of service requested in connect descriptor. Any hints?

Anonymous said...

Thanks a lot. got my issue resolved.

Anonymous said...

Thank you man! I was about to reinstall everything from scratch!

Srikanth said...

Thankyou Boss. Helped me a lot !!! Srikanth Pai

Anonymous said...

Thank you very much, you've saved my day

Anonymous said...

Thank you and good day.
I have a similar problem with:
Install oracle express 11g in windows xp
first ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0,
I try to make their own recommendations and to execute:
sqlplus sys / mgr @ xe as sysdba
the second error:
SQL*Plus: Release Production on Mar Ene 13 10:31:54 2015

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

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file 'C:\oraxe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) El sistema no puede hallar el archivo especificado.
Help me pls.

san said...

You saved my day thanks

madhusudan sapkota said...

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Windows\system32>set ORACLE_SID=noida

C:\Windows\system32>sqlplus sys/password@noida as sysdba

SQL*Plus: Release Production on Sat Mar 26 15:55:06 2016

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

ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name:

jssay said...

nice post, you solved my issue perfectly. thanks a lot.

Anonymous said...

Good info, it helped well.

Anonymous said...

This document saved my day...Got this error during the downtime phase of the upgrade.
But after following the steps mentioned , could resolve the issue.