ORA-01034: ORACLE not available ORA-27101: shared

Question

I am getting the above error when trying to connect to a server database from a client using service name orcl. It is getting connected when I am using the other service name i.e. dms

Below is my listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dms)
      (SERVICE_NAME = dms)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
#      (PROGRAM = extproc)
    )
  (SID_DESC =
     (SID_NAME = orcl)
     (SERVICE_NAME = dms)
    # (GLOBAL_DBANME = orcl)
      (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1)
   #  (PROGRAM = extproc)
  )
  )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))

    )
  )

ADR_BASE_LISTENER = /home/oracle/app/oracle
and tnsnames.ora
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
     #(SERVICE_NAME = orcl.localdomain)
      (ORACLE_SID = dms)
      (SERVICE_NAME = orcl)
    )
  )

DMS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.53)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dms)
      (ORACLE_SID = dms)
    )
  )

Please tell me what to do?

Answer 1

I hit the same shared memory realm does not exist symptom (on Windows) but for a different reason. I had just installed Oracle (XE) and after some troubleshooting, established that my installation was corrupt due to the presence of an ORACLE_HOME environment property at the time I installed it.

If this is TLDR, skip to 'So to resolve:'!

My initial symptom was:

Message 850 not found; No message file for product=NETWORK, facility=NL

Apparently the Windows install reads the ORACLE_HOME from the registry and doesn't need (and certainly in my case shouldn't have...) an environment property.

Remove it, as follows:

1. Edit the system environment settings (Windows key and start typing 'env' and you should see this option come up.
2. Delete any User and System Environment Variables called ORACLE_HOME, if present. (make a note of their values, mainly out of interest, but may be of use if you want to put them back for some reason!)
3. Restart your machine. Don't muck around with just a log off - restart your machine. The Windows Oracle install uses Windows services by default and your installation is currently very bad - it needs a restart.

Following the restart I was then able to get error messages other than 'No message file...' and could start looking at what the issue was. Setting the ORACLE_SID to XE and connecting @XE I got as far as the errors in this page, namely the following symptoms:

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

Another symptom was: When launching the 'Get started' page it failed to connect, giving a not found error (if I recall correctly), despite the Windows listener & XE services being started. As noted in another answer, this could be due to the windows services not being started. In my case those services were started, so something else was misconfigured.

At this point, I figured maybe my install had just gone so badly wrong due to the presence of my bad ORACLE_HOME environment property that I should reinstall. (Previous reinstalls hadn't helped, but those had all been before I noticed the ORACLE_HOME system environment property (probably set up by me a year ago!).

So to resolve:

1. Close any app looking at the Oraclexe install directory (editors/explorer/cmd prompts)
2. A quick trip to Add/Remove programs and uninstall OracleXe
3. Double-check you have no ORACLE_HOME environment property set anywhere, remember - Windows will use registry entries to get it.
4. Restart (take no chances - we're in this for the long term!)
5. Did you make sure there was no ORACLE_HOME property?
6. Run the Oracle installer again (as local admin account if applicable)
7. You should be able to rejoice in a working install. I did, at least!

Answer 2

I hope you have resolved your issue. If you still got issue then double check again if you install this Oracle under a domain account. I found a thread that says Oracle XE giving same error when installing under domain account. Please use a local account instead.

Source:

https://community.oracle.com/thread/2141735?start=0&tstart=0

Answer 3

I faced the same issue while creating the connection on SQLDeveloper "ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist"

Solution:

1.Update the listene.ora file to include the SID.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc) (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) (PROGRAM = extproc) ) (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) (PROGRAM = extproc) ) (SID_DESC = ((GLOBAL_DBNAME = XE.DB) ((ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server) ((SID_NAME = XE) ) )

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = USMUMTBALAKDAS2.us.deloitte.com)(PORT = 1521)) ) )

DEFAULT_SERVICE_LISTENER = (XE)

The Oraclexe directory may have the permission set to "ReadOnly", Change the directory/sub-directory permission to read/write and restart the listener services. Problem is solved.

Happy Coding! Cheers!!

Answer 4

Some time. Database will not be mounted correctly , so we need to mount it manually... For that Shut down it and start it up then mount.... Run the following commands

login as oracle user then

'sqlplus / as sysdba;'
'shutdown immediate;'
'startup nomount;'
'alter database mount;'
'alter database open;'

Answer 5

Your listener.ora is misconfigured. There is no orcl service.