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

Applies to:

All Oracle versions
Information in this document applies to any platform.

Cause:

The main reason for this error is your database is not up. When you try to access your database this Oracle message arise.

Solution:

Here, I am taking an example of Unix system having database.

[oracle@ ~]$ export ORACLE_SID=test01

Now, I try to log in to my database using.

[oracle@linuxitserver1 ~]$ sqlplus test_user6/test_user6

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 29 10:25:37 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Here is the error message.

To resolve this error i need to start my database using following commands

 [oracle@ ~]$ export ORACLE_SID=test01
[oracle@ ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 29 10:29:11 2011

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

Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  534462464 bytes
Fixed Size                  2228200 bytes
Variable Size             322961432 bytes
Database Buffers          201326592 bytes
Redo Buffers                7946240 bytes
Database mounted.
Database opened.
SQL>

Now you can connect to your database.

SQL> connect test_user6/test_user6
Connected.

If you are using same system to connect user then you don't need to use tns name otherwise you need tns name to connect to database server.

For that your listener must be running. To check listener use following commands:

Command to check Listener Status:

[oracle@test instance]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 25-MAY-2011 11:46:47

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.4.***)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                24-MAY-2011 10:38:30
Uptime                    1 days 1 hr. 8 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.4.***)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test01" has 1 instance(s).
  Instance "test01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully.

If your output is similar to above every thing is fine. If you see like below:

[oracle@test instance]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 25-MAY-2011 11:48:46

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(
PROTOCOL=TCP)(HOST=192.168.4.***)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory
Here you need to start the listener service using below command:

Command to start Listener:

[oracle@test instance]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 25-MAY-2011 11:49:08

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /opt/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.4.***)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.4.***)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                25-MAY-2011 11:49:10
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.4.***)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "test01" has 1 instance(s).
  Instance "test01", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

I am here writing command for stopping and reloading Listener as Well.

Command to Stop Listener:

[oracle@test instance]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 25-MAY-2011 11:47:54

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.4.***)(PORT=1521)))
The command completed successfully

Command to Reload Listener:

[oracle@test instance]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 26-MAY-2011 03:53:03

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.4.***)(PORT=1521)))
The command completed successfully

Labels:

26 comments:

  1. AnonymousMay 25, 2012 at 11:42 PM

    do I have to entering command "startup" at sqlplus prompt everytime I powering on my computer? Is there any other ways to resolve this problem? I need some enlightenment here. Thanks.

    ReplyDelete
  2. Umesh SharmaMay 28, 2012 at 9:40 AM

    if this is your local machine and you power off the machine than database will also be shutdown, so option other that startup every time.

    ReplyDelete
  3. AnonymousSeptember 27, 2012 at 12:57 AM

    Thanks for posting in details. It helped a lot.

    ReplyDelete
  4. Umesh SharmaSeptember 27, 2012 at 2:30 AM

    Welcome Friend.

    ReplyDelete
  5. Balu cOctober 17, 2012 at 8:00 AM

    my catalog database listener is status is running and my target database tns service is pinging connection successful but when i was connected to
    $rman target / catalog rman/rman@hyderabad
    its throw error like this
    ERROR:
    ORA-01034: ORACLE not available
    ORA-27101: shared memory realm does not exist
    Linux Error: 2: No such file or directory

    then what i do this this scenario please tell me

    ReplyDelete
  6. AnonymousNovember 8, 2012 at 1:40 AM

    Thanks.. its very useful

    ReplyDelete
  7. AnonymousNovember 11, 2012 at 6:30 AM

    Thanks a lot

    ReplyDelete
  8. SomeOneDecember 1, 2012 at 5:51 AM

    what about for XP?
    Give some suggestion

    ReplyDelete
  9. Umesh SharmaDecember 2, 2012 at 8:40 AM

    Unfortunately, i haven't worked so much on Windows, but according to me you need to first start Oracle database Services from windows services if not started.

    Then follow the steps given above in the post with below change only.

    c:\> set ORACLE_SID={instance_name}

    ReplyDelete
  10. محمد سعوديFebruary 6, 2013 at 8:17 AM

    thank you very match
    i solved my problem

    ReplyDelete
  11. umesh sharmaFebruary 6, 2013 at 11:15 PM

    Welecome, Friend.

    ReplyDelete
  12. AshFebruary 26, 2013 at 12:10 AM

    WHen i try to start my listener, I get the following error:

    Error listening on xxxxxxx
    TNS Permission denied
    TNS protocol adapter error
    Insufficent privilege for operation

    ReplyDelete
  13. umesh sharmaFebruary 26, 2013 at 3:32 AM

    what user you are using to do so ? You need to use Oracle user for this operation. I believe you are using some other user.

    ReplyDelete
  14. KrishApril 8, 2013 at 7:40 AM

    Thanks for posting in details. It helped a lot.i solved my problem.~Krish

    ReplyDelete
  15. KrishApril 8, 2013 at 7:41 AM

    Thanks.. its very useful

    ReplyDelete
    Replies
    1. umesh sharmaApril 8, 2013 at 7:44 AM

      Hi Krish, Thanks for your warm appreciation.

      Delete
  16. parthibanJune 14, 2013 at 4:51 AM

    Can you please share the steps of how to recover a crashed database server. If possible, please share the how to take rman backup to recover a crashed database. Thanks in advance.

    ReplyDelete
    Replies
    1. umesh sharmaJune 14, 2013 at 5:08 AM

      parthiban,

      Thanks for you comment. To recover a crashed database you must have RMAN or Expdp backups of database and on top of it there could be n number of situations for crash and recovery.

      To Take RMAN back fist of all you database must be in archive log mode http://www.dbas-oracle.com/2011/05/how-to-turn-archiving-on-and-off.html and again there are so many possibilities RMAN backups as well.

      You can visit following post on my blog:

      http://www.dbas-oracle.com/2013/01/rman-basic-backup-commands.html
      http://www.dbas-oracle.com/2011/10/rman-backup-restoration-for-database.html
      http://www.dbas-oracle.com/2008/10/rman-recovery-documentation.html

      For more detail, I would suggest to see RMAN online documentation for more knowledge about it.

      http://docs.oracle.com/cd/B28359_01/server.111/b28294/rman.htm

      Thanks,

      Delete
  17. parthibanJune 14, 2013 at 4:56 AM

    Hi Umesh sharma,
    The information shared in this site is really Amazing. Can you please share the steps to recover a crashed oracle database server.
    Please also share how to take RMAN backup to recover a crashed oracle database server. I have searched several sites but I could not find the exact steps. Thanks in advance.

    ReplyDelete
  18. AnonymousNovember 12, 2013 at 10:25 PM

    it means sql not allowing to connect Database.
    Check active user session
    kill sessions morethan 100
    then it allows to connect oracle.

    ReplyDelete
  19. umesh sharmaNovember 13, 2013 at 4:47 AM

    Yes, This could be another reason.

    ReplyDelete
  20. AnonymousNovember 28, 2013 at 12:34 AM

    Thank you so much!!!! you just solve my problems

    ReplyDelete
  21. NarasimhaJune 30, 2014 at 4:03 AM

    Thank you boss. It saved my lot of time.....

    ReplyDelete
  22. Pablo FSJanuary 15, 2015 at 7:09 PM

    Thank you very much!! Awesome!!

    ReplyDelete