Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

when i was trying to connect my remote server as-

sqlplus SYSTEM/oracle@192.168.0.171:1521/kannel

it shows me this error-

SQL*Plus: Release 11.2.0.2.0 Production on Fri Nov 15 15:36:52 2013
copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

I have checked my listener, it is running

[root@roracle bin]# ./lsnrctl status

 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-NOV-2013 15:42:24

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

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
 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=TCP)(HOST=localhost)(PORT=1521)))
 TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
 TNS-00511: No listener
 Linux Error: 111: Connection refused

tnsnames.ora file contain -

KANNEL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
     (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = kannel)
     )
 )

listener.ora file contain

SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
   (GLOBAL_DBNAME = kannel)
   (ORACLE_HOME = /opt/u01/app/product/11.2.0/dbhome_1)
   (SID_NAME = kannel)
 )
)

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

sqlnet.ora file contain-

SQLNET.AUTHENTICATION_SERVICES= (ALL)

NAMES.DIRECTORY_PATH= (TNSNAMES,ONAMES,HOSTNAME)

Oracle home path -

$ORACLE_HOME=/opt/u01/app/product/11.2.0/dbname_1

Oracle base path -

$ORACLE_BASE=/opt/u01/app

i have set environment variable

[root@roracle bin]# env | sort

}
_=/bin/env
CVS_RSH=ssh
G_BROKEN_FILENAMES=1
HISTCONTROL=ignoredups
HISTSIZE=1000
HOME=/root
HOSTNAME=roracle
LANG=en_IN
LESSOPEN=|/usr/bin/lesspipe.sh %s
LOADEDMODULES=
LOGNAME=root
LS_COLORS=rs=<......something........>:
MAIL=/var/spool/mail/root
module=() {  eval `/usr/bin/modulecmd bash $*`
MODULEPATH=/usr/share/Modules/modulefiles:/etc/modulefiles
MODULESHOME=/usr/share/Modules
OLDPWD=/root
ORACLE_BASE=/opt/u01/app/
ORACLE_HOME=/opt/u01/app/product/11.2.0/dbhome_1
ORACLE_SID=kannel
PATH=/usr/lib64/qt-3.3/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
PWD=/opt/u01/app/product/11.2.0/dbhome_1/bin
QTDIR=/usr/lib64/qt-3.3
QTINC=/usr/lib64/qt-3.3/include
QTLIB=/usr/lib64/qt-3.3/lib
SHELL=/bin/bash
SHLVL=1
SSH_CLIENT=192.168.0.83 35604 22
SSH_CONNECTION=192.168.0.83 35604 192.168.0.171 22
SSH_TTY=/dev/pts/0
TERM=xterm
USER=root

When i access database as a localhost -

./sqlplus / as sysdba[oracle@roracle bin]$ ./sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 15 16:04:02 2013

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

 Connected to an idle instance.

 SQL> select v$database;
 select v$database
 *
 ERROR at line 1:
 ORA-01034: ORACLE not available
 Process ID: 0
 Session ID: 0 Serial number: 0

I Don't understand where i am mistaking.....Can somebody help?

share|improve this question

migrated from stackoverflow.com Nov 15 '13 at 15:07

This question came from our site for professional and enthusiast programmers.

5  
Actually from the output of lsnrctl status you posted above it appears that the listener is not running –  Marco Baldelli Nov 15 '13 at 10:44
    
I tried to start listener as ./lsnrctl start, but after that it shows same status. How can i start listener in some other way ? –  Saagar Nov 15 '13 at 10:48
    
Don't try to start the listener in some other way - check the output of lsnrctl start and the log files to find out what's wrong. –  Frank Schmitt Nov 15 '13 at 12:43
2  
Since you're checking the status as root, I assume you try to start it as root, as well. Don't do that (see stackoverflow.com/questions/10496026/… ) –  Frank Schmitt Nov 15 '13 at 12:45
    
check Yours /etc/hosts entries 127.0.0.1 localhost.localdomain localhost 192.168.1.102 rac1.localdomain rac1 –  hanPL Feb 28 at 20:53

3 Answers 3

Could it be that you have started the listener process as root? I can see you are trying lsnrctl status as user root, so please make sure that you start the listener process as the oracle user, and then start the database (also as the Oracle user)

Also if you get "Connected to an idle instance", this means that you are connecting to an instance that is not running, or you have set the environment wrong for connecting to the instance you want to connect to.

Make sure you have the Oracle software directory in your path, such as /u01/app/oracle/product/11.0/dbhome_1/bin

and ORACLE_SID set to the database instance name.

share|improve this answer

For the

ORA-12541: TNS:no listener :

Your listener is setup to only bind to localhost though you are trying to access it using 192.168.0.171 so you will have to add this interface to your listener.ora:

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.171)(PORT = 1521))

For the

Connected to an idle instance. ... ORA-01034: ORACLE not available

Your database instance is not started, issue the startup; command first and check the output.

share|improve this answer
    
You don't start the database, you start the instance. –  Colin 't Hart Feb 28 at 21:33
1  
You are correct you start an instance of the database. But i think a bit totum pro parte can be allowed here.To quote Oracle documentation "Starting Up a Database - When you start up a database, you create an instance of that database and you determine the state of the database." : docs.oracle.com/cd/B28359_01/server.111/b28310/… –  Dieter DHoker Mar 1 at 4:17

Please login as oracle user and try these steps.

Set your oracle environment

$ . oraenv # note the dot!
$ <input your SID>

Login and fire up.

$ sqlplus / as sysdba 
SQL>startup
SQL>exit;

Start the listener, make sure the database registers.

$ lsnrctl start
$ lsnrctl status
share|improve this answer
    
I have logged in as user(oracle) and followed your steps ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/opt/u01/app/product/11.2.0 dbhome_1/dbs/initkannel.ora' now what should I do...? –  user30589 Nov 16 '13 at 7:24
    
I would normally start the listener before the database instance. –  Colin 't Hart Feb 28 at 21:32

protected by Community Apr 28 at 14:32

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?