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

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?

score 100 · Answer 1 · edited May 30 '17 at 08:21

100

Open command prompt and execute the below commands:

set oracle_sid=DATABASE NAME
sqlplus /nolog
conn sys/sys as sysdba
shutdown abort
startup

edited May 30 '17 at 08:21

Draken

3,134
13
34
54

answered Sep 11 '14 at 10:03

Siva Harsha

1,119
1
7
5

2

Can you help me with the same set of commands in Linux? I'm using RHEL. – Ekta Nov 27 '15 at 05:34
3

@EKta: for linux: just use `export ORACLE_SID=` instead and the rest is the same – Peter Butkovic Feb 03 '16 at 13:17
4

why is it that when I am trying to connect with 'connect' cmd, I am getting ORA-27101, but when I am doing conn sys/sys as sydba then I am able to connect?? – ValarDohaeris Nov 28 '18 at 11:50

score 37 · Answer 2 · edited Feb 17 '20 at 13:55

37

Sometimes, the database will not be mounted correctly, so we need to mount it manually. For that, shut it down and start it up then mount. Log in as oracle user, then run the following commands:

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

edited Feb 17 '20 at 13:55

Bondolin

2,793
7
34
62

answered Sep 16 '14 at 07:19

shrikanth Hosamani

371
3
2

mount then flashback to a previous restore point fix it for me – Hoàng Long Jul 01 '16 at 08:03

score 8 · Answer 3 · edited Aug 14 '19 at 11:43

8

SQL> sqlplus "/ as sysdba"
SQL> startup

      Oracle instance started
      ------
      Database mounted.
      Database opened.

SQL> Quit

[oracle@hcis ~]$ lsnrctl start

edited Aug 14 '19 at 11:43

Nicolás Alarcón Rapela

2,714
1
18
29

answered May 30 '17 at 05:36

Ajay Takur

6,079
5
39
55

2

`startup` does not seem to work, seems db setup issue `SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/opt/oracle/product/12.2.0.1/dbhome_1/dbs/initxe.ora'` – prayagupa Feb 23 '19 at 23:19
For a newly installed Oracle 19c Database before creating any database for use, this is what helped start my database. I had not noticed that I hadn't started it and was wondering where the ORA-01034 was coming from. – Joseph May 12 '21 at 10:48

score 5 · Answer 4 · answered Feb 28 '12 at 15:37

5

Make sure that your ORACLE_HOME and ORACLE_SID are correct To see the current values in windows, at the command prompt type

echo %ORACLE_HOME%

Then

echo %ORACLE_SID%

If the values are not your current oracle home and SID you need to correct them. This can be done in Windows environment variables.

Check out this page for more info

answered Feb 28 '12 at 15:37

John Adams

69
1
3

This answer was close enough trigger my solution. Although my $ORACLE_HOME & $ORACLE_SID variables were correct, my process was evoking a parameter file with that called the wrong SID. – retriever123 Mar 24 '14 at 17:01

score 4 · Answer 5 · answered Dec 27 '16 at 14:26

Had the same issue on windows XP. Resolved. The error was caused due to the system log being full. Control Panel -> Administrative Tools -> Event viewer Right click on application log, clear all events, optionaly save the log. Same process for system log. Restart and it should work.

score 4 · Answer 6 · edited Aug 14 '19 at 12:01

This same error occurred to me even though the ORACLE_HOME and ORACLE_SID seemed to be correctly set up.

The problem was in ORACLE_HOME, which is not supposed to end with a slash character. When I removed the ending slash, it started to work properly.

# ❌ INCORRECT
export ORACLE_HOME=/usr/local/oracle/11gR2/

# ✔️ CORRECT
export ORACLE_HOME=/usr/local/oracle/11gR2

So, even if it seems everything is configured fine, check your variables for this.

score 4 · Answer 7 · answered Jul 02 '11 at 09:23

ORA-01034 and ORA-27101 normally indicate that the database instance you're attempting to connect to is shut down and that you're not connected as a user who has permission to start it up. Log on to the server 192.168.1.53 and start up the orcl instance, or ask your DBA to do this for you.

score 3 · Answer 8 · answered Feb 04 '19 at 10:47

3

Run in cmd:

sqlplus / as sysdba;

Then:

SQL> create pfile='c:/init.ora' from spfile;

Remove sga_target line in init.ora file, then:

SQL> create spfile from pfile='c:/init.ora';
SQL> startup;

answered Feb 04 '19 at 10:47

andreikashin

1,528
3
14
21

score 2 · Answer 9 · edited Aug 14 '19 at 12:23

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.

roj · Answer 10 · 2016-08-04T08:08:29.327

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:

Edit the system environment settings (Windows key and start typing 'env' and you should see this option come up.
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!)
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:

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

score 1 · Answer 11 · answered Dec 10 '20 at 13:32

After wasting so much time i got to know that there was mistake in my syntax to connect with DB. I was using colon ":" instead of slash "/".

(1) if you use sid below is the syntax to get connection:

**"jdbc:oracle:thin:@{hostname}:{port}:{SID}"**

(2) if you use service name, below is the syntax to get connection:

"**jdbc:oracle:thin:@//{hostname}:{port}/{servicename}**"

score 0 · Answer 12 · answered May 06 '17 at 03:11

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

score 0 · Answer 13 · answered Nov 17 '19 at 14:34

In my case here, I just had to resurrect a server that was 9 years old, and Oracle was giving me this error.

For some reason, the server had been renamed, but the app/oracle/product/10.2.0/server/network/admin/listener.ora file was still declaring a LISTENER with the old HOST.

I had to put the same name that I had in /etc/hostname in the /etc/hosts, and also fix the name used in the listener.ora.

score 0 · Answer 14 · answered Aug 01 '20 at 11:15

0

Also try directly startup:

sqlplus /nolog
conn / as sysdba
startup

answered Aug 01 '20 at 11:15

yu yang Jian

6,680
7
55
80

score 0 · Answer 15 · answered Jul 11 '11 at 03:00

0

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

answered Jul 11 '11 at 03:00

steve

5,870
1
21
22

score 0 · Answer 16 · answered Dec 09 '21 at 17:33

0

I have seen this issue come up when attempting to start a sqlplus session like this:

sqlplus /nolog
connect user/password

The fix was to remove /nolog and to escape the user/password:

sqlplus 'user/password'

answered Dec 09 '21 at 17:33

alex

6,818
9
52
103

score -1 · Answer 17 · answered Sep 08 '20 at 12:33

-1

SQL> sqlplus "/ as sysdba"

SQL> startup

  Oracle instance started
  ------
  Database mounted.
  Database opened.

SQL> Quit

I also got same problem.I tried above mentioned steps and then it worked for me.You can try.

answered Sep 08 '20 at 12:33

Subhrut Taori

27
1
2
11

1

What does your answer add to existing identical answers? – mustaccio Sep 08 '20 at 19:30

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

17 Answers17

Linked