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?
Best Answer
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.