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!

Anonymous said...

Thank you very much, you've saved my day