Tuesday, April 29, 2008

ORA-01034: ,ORA-27101: shared memory realm does not exist

Error Description:
-----------------------

Whenever you try to connect to database it returns message,

ORA-01034 : ORACLE not available
ORA-27101 : shared memory realm does not exist


Cause of the Problem:
--------------------------

This problem happens whenever ORACLE_SID or ORACLE_HOME is not properly set. Or for normal users whenever oracle database is not started. For remote users there may be a problem in listener.

Solution of the Problem:
----------------------------
A)For Local Connections:
----------------------------

1)Verify the ORACLE_SID and ORACLE_HOME is set properly. You can check the variable in UNIX by,

SQL> !echo $ORACLE_SID
data1

SQL> !echo $ORACLE_HOME
/oracle/app/oracle/product/10.2.0/db_1

If it is set incorrectly then set on UNIX by

$ export ORACLE_SID=db_name_here (on ksh,sh)
$ setenv ORACLE_SID=db_name_here (on csh)


Remember that ORACLE_SID is case sensitive on UNIX.

2)Verify the database instance is running.

On UNIX you can verify by,
SQL>!ps -ef |grep smon

It will return a row as ora_smon_your_database_name

If it does not return such row then your database is not started. So, start it after setting proper sid.

$sqlplus / as sysdba
$startup


On windows system press CTRL+ALT+DEL and see the process and look for oracle.exe. If it is not present there then from right click my computer>select manage>services>and select oracle database service and start it.
B)For Remote Connections:
-------------------------------

1)Verify the ORACLE_HOME is set correctly for all listed Oracle databases. If the ORACLE_HOME points to the different oracle version software, but the database was created using a different version, then this error can occur.

2)Review the listener.ora file and it is properly set. Like if you set ORACLE_HOME path inside listener ended by slash(/) then the error will come.

Incorrect:
(ORACLE_HOME = /oracle/app/oracle/product/10.2.0/)
Correct:
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)


3)If listener.ora use SID then verify that you have set properly ORACLE_SID. Also be sure about listener services by issuing lsnrctl services.

4)Verify the database is running on server machine while you connect as normal user.

Other links
http://arjudba.blogspot.com/2008/07/database-startup-fails-with-error-ora.html
http://arjudba.blogspot.com/2008/05/startup-fails-with-oracle-error-ora.html
http://arjudba.blogspot.com/2008/05/database-startup-fails-with-errors-ora.html

7 comments:

boxer said...

I am having terrible with Oracle DataBase. It showing Oracle Nort available and Shared memory realm exists

Arju said...

1)At first, you set by correct ORACLE_SID by,

export ORACLE_SID=test (on unix)
set ORACLE_SID=test (on windows)

2)sqlplus / as sysdba

If you see "connect to idle instance" message then issue,

SQL>startup

Anonymous said...

Reboot of OracleInstance may help. It works for me.

Anonymous said...

Hi Arju, Thanks for the TIPS..it solved my problem!

I had the ORA-01034 problem when setting up a cloned 8.1.5 Oracle database on a SUN server box.

Connections from the Win client PC gives ORA-01034 when connecting via SQL*PLUS and running the Test in the Net Conf Assistant.

Tnsping works fine, Listener are up and DB are Open and running. I was totally baffled as all the settings are completely the same as the LIVE server.

Turn out the issues lies in the UNIX case sensitive nature. The ECHO - ORACLE SID indicates the DB name in Capital case.
Updating the DB ID in both LISTENER.ORA and TNSNAMES.ORA to UPPER case solved the issue after a quick listener restart!

Warrior of God said...

It also may not be mounted in the filesystem where Oracle is installed. Check if the file is mounter, else use mount command to mount the file system.

Chuck Cottrill said...

I encountered the same problem on linux (redhat enterprise). We had two users, one having the problem (me), the other not having the problem. Looking at the environment:

$ env |grep ORA
ORACLE_SID=ora02
ORACLE_BASE=/opt/Oracle/
ORACLE_HOME=/opt/Oracle//product/11.2.0/dbhome_2

Note that my ORACLE_BASE has a "/" at the end - so you need to make sure that BOTH ORACLE_HOME and ORACLE_BASE do not have "/" at the end. Thanks oracle!

Anonymous said...

hi dear I did all the steps on window xp plate form to remove the error shared memory does not exist, i also check the oracle.exe is present in processes, and other services of oracle like tns... etc are also running but when i try to link my database it gives me that error. Actualy I updated two columns of my data base one by one, and it gives me the message 317 rows updated. it was working fine but when i restarted my computer i'm facing that error.if possible please send me mail on following address 'aamergondal@gmail.com' any further help will be appreciated, thanks to all.
Amygol