Applies to:
All Oracle versionsInformation 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.
------------------------
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_
Listener Log File /opt/oracle/product/10.2.0/db_
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(
(DESCRIPTION=(ADDRESS=(
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)))
Command to start Listener:
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=(
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
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_
Log messages written to /opt/oracle/product/10.2.0/db_
Listening on: (DESCRIPTION=(ADDRESS=(
Listening on: (DESCRIPTION=(ADDRESS=(
Connecting to (DESCRIPTION=(ADDRESS=(
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_
Listener Log File /opt/oracle/product/10.2.0/db_
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(
(DESCRIPTION=(ADDRESS=(
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)))
Command to Reload Listener:
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
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
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.
ReplyDeleteif this is your local machine and you power off the machine than database will also be shutdown, so option other that startup every time.
ReplyDeleteThanks for posting in details. It helped a lot.
ReplyDeleteWelcome Friend.
ReplyDeletemy catalog database listener is status is running and my target database tns service is pinging connection successful but when i was connected to
ReplyDelete$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
Thanks.. its very useful
ReplyDeleteThanks a lot
ReplyDeletewhat about for XP?
ReplyDeleteGive some suggestion
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.
ReplyDeleteThen follow the steps given above in the post with below change only.
c:\> set ORACLE_SID={instance_name}
thank you very match
ReplyDeletei solved my problem
Welecome, Friend.
ReplyDeleteWHen i try to start my listener, I get the following error:
ReplyDeleteError listening on xxxxxxx
TNS Permission denied
TNS protocol adapter error
Insufficent privilege for operation
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.
ReplyDeleteThanks for posting in details. It helped a lot.i solved my problem.~Krish
ReplyDeleteThanks.. its very useful
ReplyDeleteHi Krish, Thanks for your warm appreciation.
DeleteCan 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.
ReplyDeleteparthiban,
DeleteThanks 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,
Hi Umesh sharma,
ReplyDeleteThe 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.
it means sql not allowing to connect Database.
ReplyDeleteCheck active user session
kill sessions morethan 100
then it allows to connect oracle.
Yes, This could be another reason.
ReplyDeleteThank you so much!!!! you just solve my problems
ReplyDeleteWelcome friend.
DeleteThank you boss. It saved my lot of time.....
ReplyDeleteWelcome friend.
DeleteThank you very much!! Awesome!!
ReplyDelete