Start Free Trial

asked on

No Oracle lsnrctl command RHEL 5.3

Hello Expert,

I am working on my corporate production server and I found that there is no lsnrctl command.
I am trying to fine using oracle account and go to ORACLE_HOME/bin I saw a lot of executable files but no lsnrctl there.
Trying find command and no hope
ps -ef|grep lsnt <=== not found
BUT I can use command line sqlplus to access.
Friend of mind said it must be listener if not, I am not able to access using just sqlplus from command line.

Thank you,
Nitirat

The listener is not needed for local access.
It is required for connections over the network only.

The name of the listener binary itself is "tnslsnr" which is also what appears in the process list.

Could it be that you performed a "Custom" Oracle installation and didn't select "Oracle Net" and the various "Protocol Adapter" options?

See the available adapters (when logged in as the Oracle user) with

cd $ORACLE_HOME/bin
./adapters ./oracle

ASKER

I cannot complete command ./adapters ./oracle so I did ./adapters as below

======================

[oracle@prdapp01 bin]$ ./adapters ./o
oerr onsctl orabase oraenv orajaxb orapki oraxml oraxsl orion osdbagrp ott owm
[oracle@prdapp01 bin]$ ./adapters ./o
oerr onsctl orabase oraenv orajaxb orapki oraxml oraxsl orion osdbagrp ott owm
[oracle@prdapp01 bin]$ ./adapters ./o
oerr onsctl orabase oraenv orajaxb orapki oraxml oraxsl orion osdbagrp ott owm
[oracle@prdapp01 bin]$ ./adapters

Installed Oracle Net transport protocols are:

IPC
BEQ
TCP/IP
SSL
RAW
SDP/IB

Installed Oracle Net naming methods are:

Local Naming (tnsnames.ora)
Oracle Directory Naming
Oracle Host Naming
Error!!! Oracle Names Server Naming is not completely installed!

Installed Oracle Advanced Security options are:

RC4 40-bit encryption
RC4 56-bit encryption
RC4 128-bit encryption
RC4 256-bit encryption
DES40 40-bit encryption
DES 56-bit encryption
3DES 112-bit encryption
3DES 168-bit encryption
AES 128-bit encryption
AES 192-bit encryption
AES 256-bit encryption
MD5 crypto-checksumming
SHA-1 crypto-checksumming
Kerberos v5 authentication
RADIUS authentication
[oracle@prdapp01 bin]$

======================
How is the result above?
Any way to install listener?

I did not have root access and this server is provided by our vendor.

ASKER

also not found "tnslsnr"

[oracle@prdapp01 bin]$ ps -ef|grep tnslsnr
oracle 26354 25274 0 21:03 pts/5 00:00:00 grep tnslsnr
[oracle@prdapp01 bin]$

Thank you

ASKER

Files list under $ORACLE_HOME/bin

[oracle@prdapp01 bin]$ ls -l
total 123188
-rwxr-xr-x 1 oracle dba 13091 Aug 3 2009 adapters
-rwxr-xr-x 1 oracle dba 25588 Aug 14 2009 adrci
-rwxr-xr-x 1 oracle dba 9060 Feb 15 2008 AgentLifeCycle.pm
-rwxr-xr-x 1 oracle dba 2343 Jul 26 2006 AgentMisc.pm
-rwxr-xr-x 1 oracle dba 17833 Sep 13 2007 AgentStatus.pm
-rwxr-xr-x 1 oracle dba 3265 Jul 31 2008 AgentSubAgent.pm
-rwxr-xr-x 1 oracle dba 2031 Jun 25 2010 bndlchk
-rwxr-xr-x 1 oracle dba 230 Jun 25 2010 chopt
-rw-r--r-- 1 oracle dba 2580 Jun 25 2010 chopt.ini
-rw-r--r-- 1 oracle dba 6461 Jun 25 2010 chopt.pl
-rwxr-xr-x 1 oracle dba 489 Jun 25 2010 commonenv
-rwxr-xr-x 1 oracle dba 495 Apr 14 2006 commonenv.template
-rwxr-xr-x 1 oracle dba 2923 May 25 2007 CompEMagent.pm
-rwxr-xr-x 1 oracle dba 5693 Jan 19 2008 CompEMcentral.pm
-rwxr-xr-x 1 oracle dba 5199 Jan 19 2008 CompEMcore.pm
-rwxr-xr-x 1 oracle dba 4143 Jan 1 2000 coraenv
-rwxr-xr-x 1 oracle dba 2415 Jan 1 2000 dbhome
-rwxr-xr-x 1 oracle dba 2952 Oct 17 2005 Directory.pm
-rwxr-xr-x 1 oracle dba 852278 Aug 14 2009 diskmon.bin
-rwxr-xr-x 1 oracle dba 153 Nov 6 1997 echodo
-rwxr-xr-x 1 oracle dba 1404 Oct 17 2005 EMAgentPatch.pm
-rwxr-xr-x 1 oracle dba 24310 Sep 12 2008 EMAgent.pm
-rwxr-xr-x 1 oracle dba 3518 Jun 25 2010 emca
-rwxr-xr-x 1 oracle dba 2194 Oct 17 2005 EmCommonCmdDriver.pm
-rwxr-xr-x 1 oracle dba 6612 Dec 14 2006 EMconnectorCmds.pm
-rwxr--r-- 1 oracle dba 14484 Jun 25 2010 emctl
-rwxr-xr-x 1 oracle dba 43678 Jun 25 2010 EmctlCommon.pm
-rwxr-xr-x 1 oracle dba 18340 Jun 25 2010 emctl.pl
-rwxr-xr-x 1 oracle dba 14581 Jun 9 2009 emctl.template
-rwxr-xr-x 1 oracle dba 24550 Mar 24 2009 EMDeploy.pm
-rwxr-xr-x 1 oracle dba 1226 Oct 17 2005 emdfail.command
-rwxr-xr-x 1 oracle dba 3451 May 1 2006 EMDiag.pm
-rwxr-xr-x 1 oracle dba 6741 Oct 3 2006 EmKeyCmds.pm
-rwxr-xr-x 1 oracle dba 16758 Mar 18 2009 EMomsCmds.pm
-rwxr-xr-x 1 oracle dba 5653 Sep 6 2006 EMSAConsoleCommon.pm
-rwxr-xr-x 1 oracle dba 12704 Jun 25 2010 emutil
-rwxr-xr-x 1 oracle dba 10174 Jan 19 2008 emutil.bat.template
-rwxr-xr-x 1 oracle dba 39259 Jul 25 2008 emwd.pl
-rwxr-xr-x 1 oracle dba 1635 Jun 25 2010 eusm
-rwxr-xr-x 1 oracle dba 1225215 Aug 14 2009 extjob
-rwxr-xr-x 1 oracle dba 1225215 Aug 14 2009 extjobo
-rwxr-xr-x 1 oracle dba 4324 Jul 25 2009 genagtsh
-rwxr-xr-x 1 oracle dba 9124 Jul 25 2009 genclntsh
-rwxr-xr-x 1 oracle dba 4987 Jun 8 2007 genclntst
-rwxr-xr-x 1 oracle dba 44963 Aug 14 2009 genezi
-rwxr-xr-x 1 oracle dba 2893 Aug 3 2009 gennfgt
-rwxr-xr-x 1 oracle dba 4904 Aug 3 2009 gennttab
-rwxr-xr-x 1 oracle dba 3591 Jul 25 2009 genoccish
-rwxr-xr-x 1 oracle dba 9364 Jul 25 2009 genorasdksh
-rwxr-xr-x 1 oracle dba 2332 Jan 1 2000 gensyslib
-rwxr-xr-x 1 oracle dba 6693253 Jul 31 2009 gnsd
-rwxr-x--- 1 oracle dba 0 Aug 15 2009 kfed
-rwxr-x--- 1 oracle dba 0 Aug 15 2009 kfod
-rwxr-xr-x 1 oracle dba 556887 Aug 14 2009 kgmgr
-rwxr-xr-x 1 oracle dba 16979 Jul 27 2007 LaunchEMagent.pm
lrwxrwxrwx 1 oracle dba 57 Jun 25 2010 lbuilder -> /opt/oracle/product/11.2.0/client_1/nls/lbuilder/lbuilder
-rwxr-xr-x 1 oracle dba 1934553 Aug 2 2009 lcsscan
-rwxr-xr-x 1 oracle dba 1459 May 12 2009 linkshlib
-rwxr-xr-x 1 oracle dba 1582424 Aug 2 2009 lmsgen
-rwxr-xr-x 1 oracle dba 23283 Aug 14 2009 loadpsp
-rwxr-xr-x 1 oracle dba 1081341 Aug 2 2009 lxchknlb
-rwxr-xr-x 1 oracle dba 1657805 Aug 2 2009 lxegen
-rwxr-xr-x 1 oracle dba 4181946 Aug 2 2009 lxinst
-rwxr-xr-x 1 oracle dba 2816 Jun 25 2010 mkstore
-rwxr-xr-x 1 oracle dba 6434 Jun 25 2010 netca
-rw-r--r-- 1 oracle dba 110 Jun 25 2010 netca_deinst.sh
-rwxr-xr-x 1 oracle dba 6289 Jun 25 2010 netmgr
-rwxr-x--- 1 oracle dba 2835 Sep 17 2007 nfsPatchPlugin.pm
-rwxr-xr-x 1 oracle dba 2365 Jan 1 2000 oerr
-rwx------ 1 oracle dba 13535 Jun 25 2010 onsctl
-rwxr-xr-x 1 oracle dba 3093056 Aug 15 2009 orabase
-rwxr-xr-x 1 oracle dba 5036 Jan 1 2000 oraenv
-rwxr-xr-x 1 oracle dba 44 Dec 4 2002 orajaxb
-rwxr-xr-x 1 oracle dba 3139 Jun 25 2010 orapki
-rwxr-xr-x 1 oracle dba 48 Sep 25 2000 oraxml
-rwxr-xr-x 1 oracle dba 48 Sep 25 2000 oraxsl
-rwxr-xr-x 1 oracle dba 47203925 Aug 14 2009 orion
-rwxr-xr-x 1 oracle dba 35653 Jul 31 2009 osdbagrp
-rwxr-xr-x 1 oracle dba 1367 Jun 25 2010 ott
-rwxr-xr-x 1 oracle dba 6869 Jun 25 2010 owm
-rwxr-x--- 1 oracle dba 4472 Apr 14 2008 patchAgtStPlugin.pm
-rwxr-xr-x 1 oracle dba 1488 Oct 17 2005 Path.pm
-rw-r--r-- 1 oracle dba 2810 Jun 25 2010 platform_common
-rwxr-xr-x 1 oracle dba 9544019 Jun 25 2010 proc
-rwxr-xr-x 1 oracle dba 18479997 Jul 31 2009 racgeut
-rwxr-xr-x 1 oracle dba 18709681 Jul 31 2009 racgmain
-rwxr-xr-x 1 oracle dba 6978 Jun 25 2010 rconfig
-rwxr-xr-x 1 oracle dba 4442 Jan 19 2008 RegisterTType.pm
-rwxr-xr-x 1 oracle dba 4598 Jul 15 2009 relink
-rwxr-xr-x 1 oracle dba 2523 Jul 17 2007 sAgentUtils.pm
-rwxr-xr-x 1 oracle dba 2949475 Aug 2 2009 schema
-rwxr-xr-x 1 oracle dba 28900 Jul 31 2008 SecureAgentCmds.pm
-rwxr-xr-x 1 oracle dba 70807 Jun 23 2009 SecureOMSCmds.pm
-rwxr-xr-x 1 oracle dba 34604 May 23 2008 SecureUtil.pm
-rwxr-x--x 1 oracle dba 9197 Jun 25 2010 sqlplus
-rwxr-xr-x 1 oracle dba 8164 Jun 25 2010 srvctl
-rwxr-x--- 1 oracle dba 281 Oct 17 2005 StartAgent.pl
-rwxr-xr-x 1 oracle dba 3261 Jul 13 2009 symfind
-rwxr-xr-x 1 oracle dba 6967 Jun 25 2010 targetdeploy.pl
-rwxr-x--x 1 oracle dba 20505 Jun 25 2010 tnsping
-rwxr-xr-x 1 oracle dba 0 Aug 15 2009 tnsping0
-rwxr-xr-x 1 oracle dba 3013 Jun 25 2010 trcasst
-rwxr-x--x 1 oracle dba 31962 Jun 25 2010 trcroute
-rwxr-xr-x 1 oracle dba 0 Aug 15 2009 trcroute0
-rwxr-xr-x 1 oracle dba 3802 Jun 25 2010 umu
-rwxr-xr-x 1 oracle dba 119416 Jan 1 2000 unzip
-rwxr-xr-x 1 oracle dba 439226 Aug 14 2009 wrc
-rwxr-xr-x 1 oracle dba 3239377 Aug 2 2009 xml
-rwx------ 1 oracle dba 31268 Nov 27 2008 xmlwf
-rwxr-xr-x 1 oracle dba 88565 Jan 1 2000 zip
[oracle@prdapp01 bin]$

Are you sure that you're on the database server itself instead of a machine running just the Oracle client?

Only the Oracle DB server needs a listener, the client just needs a tnsnames.ora file, usually located in the $TNSADMIN or $ORACLE_HOME/network/admin directory.

Which processes does the Oracle user run right now? Login as this user and run

ps -ef | grep $USER

Any DB server processes?

Anyway. this "bin" diretory is incomplete if it's a DB server! Adapter support is present (linked into orabase), but the required server binaries are missing.

If it's indeed the DB server I think you should immediately contact the person(s) responsible for the Oracle installation (your "vendor"?) to ask them what this is all about!

Do you have a $ORACLE_HOME/network/admin directory? Or a directory $TNS_ADMIN ?

If so what's in there?

If you can run X11 applications you could also start $ORACLE_HOME/bin/netmgr to check the Oracle Net configuration.

ASKER

Sorry for delay. I went out and just back home.

1. I cannot run X11
2.
[oracle@prdapp01 ~]$ cd /opt/oracle/product/11.2.0/client_1/network/admin/
[oracle@prdapp01 admin]$
[oracle@prdapp01 admin]$
[oracle@prdapp01 admin]$ ls -l
total 16
drwxr-xr-x 2 oracle dba 4096 Jun 25 2010 samples
-rw-r--r-- 1 oracle dba 187 May 7 2007 shrept.lst
-rw-r--r-- 1 oracle dba 215 Jun 25 2010 sqlnet.ora
-rw-r--rw- 1 oracle dba 329 Jun 25 2010 tnsnames.ora

3. $USER is oracle and huge process running. (Attached file)

Thank you.
ps.log

ASKER

Hi

Now I know REAL DB server.

this is listener sys=atus

[oracle@prddbs01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 26-JAN-2014 00:07:22

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 16-AUG-2010 21:13:00
Uptime 263 days 21 hr. 58 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/11.2.0/grid/network/admin/listener.ora
Listener Log File /opt/oracle/diag/tnslsnr/prddbs01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.103)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.105)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "npcdb" has 1 instance(s).
Instance "npcdb1", status READY, has 1 handler(s) for this service...
Service "npcdbXDB" has 1 instance(s).
Instance "npcdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@prddbs01 ~]$ sqlplus <user>/<password>@npcdb

look good but still cannot use my Oracle client (Navicat) that got error: connection failed because target host or object does not exist ORA-12545

I am trying many way.

Ok, so I was right and prdapp01 is not a DB server which fact makes it quite normal that there is no listener running and there there is no lsnrctl binary (your original question!).

sqlplus npcdb/npcdb@npcdb

means user npcdb, password npcdb and service npcdb

Is this really correct?

The service name sems OK, because there is a listening process for it.

Does "npcdb" resolve to one of the IP addresses where prddbs01 is listening to?

Is the user name indeed "npcdb", and is the password also "npcdb"?

By the way, you should not publish your passwords! I can clean this up for you if you like!

ASKER

Yes please remove password for me.

Yes user/password/service name correct.
I did not understand "Does "npcdb" resolve to one of the IP addresses where prddbs01 is listening to?"

I mean, if you enter

host npcdb

do you see one of the IP addresses shown with "lsnrctl status" ("HOST=...") on the DB server?

Or better, look at your tnsnames.ora file. Is the service npcdb associated with one of those addresses, or with a hostname which resolves to one of those addresses?

What do you get with

tnsping npcdb

?

ASKER

[oracle@prddbs01 ~]$ tnsping npcdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-JAN-2014 00:55:10

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/opt/oracle/product/11.2.0/dbhome/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prddbs-crs-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = npcdb)))
OK (0 msec)
[oracle@prddbs01 ~]$

ASKER

[oracle@prddbs01 ~]$ host npcdb
Host npcdb not found: 3(NXDOMAIN)
[oracle@prddbs01 ~]$

Thanks!

Seems we have a misunderstanding here!

Didn't you try to reach the DB from your machine "prdapp01" initially?
If so, you must run the tnsping utility from that machine (the one where you want to issue "sqlplus ..."), of course!

Anyway, the service npcdb exists and is reachable.

But according to tnsping this service runs on host "prddbs-crs-scan".
Is this the DB server you want to connect to?

And from the same machine where "tnsping npcdb" works "sqlplus @npcdb" should also work, i.e. ask you for user and password.

The hostname must only resolve to the correct IP when using it instead of a service name, but you're using a service name which is OK.

ASKER

It look OK

[oracle@prdapp01 admin]$ tnsping npcdb

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 26-JAN-2014 01:10:47

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:
/opt/oracle/product/11.2.0/client_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.107)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = npcdb)))
OK (10 msec)
[oracle@prdapp01 admin]$

=====================================
But according to tnsping this service runs on host "prddbs-crs-scan".
Is this the DB server you want to connect to?

Yes "prddbs-crs-scan" is 192.168.10.107
=====================================
And from the same machine where "tnsping npcdb" works "sqlplus @npcdb" should also work, i.e. ask you for user and password.

Yes it work

=====================================

Sorry I have no idea now because I did not understand all but I can check it for you.
What should I do next?

ASKER

[oracle@prdapp01 admin]$ host npcdb
Host npcdb not found: 3(NXDOMAIN)
[oracle@prdapp01 admin]$

Thank you

>> Yes it work <<

What works?

tnsping from prdapp01 to npcdb works, as it seems.

So "sqlplus @npcdb" works fom prdapp01, too?

If so, that's what you were trying to achieve, or am I wrong?

ASKER

[oracle@prdapp01 admin]$ sqlplus @npcdb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 26 01:27:31 2014

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

SP2-0310: unable to open file "npcdb.sql"
Enter user-name:

======================
Yes and I can login successful

ASKER

Additional

With only password without @npcdb not work
but work for password follow by @npcdb

ASKER

Look like it should work when I connect using Navicat on my PC.
I can ping both prdapp1,prddbs01 and prddbs-crs-scan

But... got ORA-12545

ASKER

I am wondering it is possible that outside connection from my pc to server in blocked by firewall or some security because this server I need to connect VPN first then I can access using putty.

What do you mean with "ping"?

If you mean that the normal ICMP ping works but TNS ping doesn't then it could well be possible that access to the DB is blocked (port 1521).

But if tnsping works then the listener can be reached and ORA-12454 should not occur.

Afaik Navicat supports TNS mode, so you should only have to specify the service name in the connection setup, and you must have a valid tnsnames.ora file.

Most important: You must have installed the Oracle Client or Oracle Instantclient on your PC!
The Oracle PC Client has a tnsping tool, but the Instantclient has not!

Do you have the Oracle Client, and can you run tnsping from your PC against npcdb?

If you don't have tnsping you can try from a PC CMD window:

telnet dbhostname 1521

where dbhostname is the TCP/IP hostname of the DB server, not the service name!

You should see something like "Connected to ..." if the host/port is reachable, but if access is blocked you should see something like "Connection refused".

ASKER

I just install Oracle client tool and try tnsping 192.168.10.107 1521 from my PC
I cannot access with error TNS-12535 TNS:operation timeout

So it is not possible ti access? May I request my vendor to allow this?

Thank you.

ASKER

Is it possible to use SSH tunnel?

Thank you.

tnsping should be run against the service name, not the hostname!

The service name is what you configured in the tnsnames.ora file which came with the client!

The tnsnames.ora file associates (roughly speaking) a service name with a host, a port on that host and the protocol used.

Afaik you can use the client tool "netmgr" to change this file:

Choose Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Net Manager

See here how to use it:
http://docs.oracle.com/cd/B10501_01/network.920/a96580/admintoo.htm#483397

Your tnsnames.ora file should finally contain something like this:

npcdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = prddbs-crs-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = npcdb)
)
)

ASKER

telnet 192.168.10.107 1521 not work (connecting to 192.168.10.17.... forever)

But no "Connection refused" message, so we can't tell for sure that the port is not reachable!

Telnet cannot really communicate with the DB, and this was never intended. We just wanted to test reachability of the port!

Did you set up tnsnames.ora and retry tnsping, as suggested?

ASKER

tnsnames.ora is

# tnsnames.ora Network Configuration File: C:\app\nitirat\product\11.2.0\client_2\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

NPCDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.107)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = npcdb)
)
)

=======================
command in cmd

tnsping npcdb
and
tnsping NPCDB

I got

TNS-03505 Failed to resolve name...

Seems that tnsping cannot find your tnsnames.ora file.

Is the environment variable ORACLE_HOME set correctly?

Should be something like C:\app\nitirat\product\11.2.0\client_2

Issue "set" in the CMD box to check.

ASKER

No ORACLE_HOME and other ORACLE_XXX in my environment. I installed oracle client already. What should I do. I add ORACLE_HOME manually but did not work.

Thank you.

ASKER

Oracle client I downloaded and installed name "win64_11gR2_client.zip" install on my PC Windows 8.0 64Bit

Set the TNS_ADMIN variable manually to

C:\app\nitirat\product\11.2.0\client_2\NETWORK\ADMIN

and retry.

You can also try

tnsping //192.168.10.107:1521

and/or

tnsping //192.168.10.107:1521/npcdb

(This is called EZCONNECT)

Any success?

If the above succeeds you can also try:

sqlplus username/password@//192.168.10.107:1521/npcdb

ASKER

Please see attached pictures.

tnsping //192.168.10.107:1521/npcdb <<<< OK

sqlplus username/password@//192.168.10.107:1521/npcdb <<<< Waiting forever
oracle2.PNG
oracle3.PNG

Please turn off the Windows firewall and retry.

If the problem persists there might in fact be an external firewall. But very strange that tnsping would work then ...

An ssh tunnel could be an option if everything else fails. Do you have PuTTY's "plink" commandline utility installed?

ASKER

I turned off firewall already before doing above. I heard about plink but never use it. I will download and try first. If you are available will you please provide some command to make SSH tunnel? Thank you.

Once you installed plink you can open a tunnel in a minimized CMD window like below. Run in an CMD window:

START /MIN plink -N -L 1521:192.168.10.107:1521 username@192.168.10.107

username is the name of a user who can connect to the DB server via ssh.

Now try:

sqlplus username/password@//localhost:1521/npcdb

Since you now have a tunnel you must specify localhost as the target host. The connection will be forwarded through the tunnel.

ASKER

START /MIN plink -N -L 1521:192.168.10.107:1521 username@192.168.10.107 <<< OK

sqlplus username/password@//localhost:1521/npcdb <<< last forever

So strange.

Please bring the minimized plink window into foreground and check for error messages.

Does a normal plink work, i. e. start a shell?

plink username@192.168.10.107

If this works, and if there are no error messages from plink then I'll be soon out of ideas, sorry.

If the DB server runs a sendmail daemon you can run this simple test to check whether a tunnel can basically work with that server (close the running tunnel first by hitting Ctl-C in the plink window):

start /MIN plink -N -L 1000:192.168.10.107:25 username@192.168.10.107

telnet localhost 1000

Do you see messages from the sendmail daemon on the dB server?

If so then a tunnel is basically possible and I really don't know why this shouldn't work with Oracle.

Enter "quit" to terminate the sendmail session.

ASKER

No sendmail available on this server.

plink username@192.168.10.107 <<< This one work!

No error found in plink tunnel cmd. please see attached picture
plink.png

If there is no sendmail running we can try to telnet to telnet if it isn't disabled on the DB host:

start /MIN plink -N -L 1000:192.168.10.107:23 username@192.168.10.107

telnet localhost 1000

Do you see a telnet login prompt? If so, log in and check which host you're on. It should be the DB host!

ASKER

I connect from prdapp1 to prddbs01 result "The remote system refused the connection."

telnet localhost 1000 <<< press any key to continue... then return to telnet local prompt

ASKER

I tried start /MIN plink -N -L 1000:192.168.10.107:22 username@192.168.10.107

It is OK!

Maybe telnet not allow on prddbs01

The prompt you're seeing is most probably not the telnet local prompt - it's the prompt from the DB server. That's why I suggested logging in and checking the hostname.

ASKER

From above

telnet localhost 1000 <<<< This one OK

What do you mean with OK? telnet to the ssh port should give you the SSH daemon header line, probably starting with "OpenSSH ...". Was this the case?

Anyway, a tunnel seems possible, and I really don't know why it shouldn't work with Oracle.

To be sure: You say that sqlplus works from a Linux machine running the Oracle client to the DB server, and it works locally on the DB server itself - is this correct?

ASKER

SSH-2.0-OpenSSH_4.3
===========
once I press enter
===========
Protocol mismatch

connection to host lost

press anykey to continue

Tunnel OK.

Again:

To be sure: You say that sqlplus works from a Linux machine running the Oracle client to the DB server, and it works locally on the DB server itself - is this correct?

And prddbs01 is the DB server and 192.168.10.107 is its IP address - is this correct too?

ASKER

Yes you correct.

prdapp1 = 192.167.10.101
prddbs01 = 192.168.10.107

No problem for sqlplus command locally

Sorry for asking but I must be sure that I'm on the cutting edge:

1) You have installed the Oracle client on your PC
2) You cannot run from your PC: sqlplus user/password@//prddbs01:1521
3) You can start a tunnel with: plink -N -L 1521:prddbs01:1521 username@prddbs01
4) Using the tunnel you cannot run: sqlplus user/password@//localhost:1521

All correct? Please double-check everything!

ASKER

1) correct
2) correct but because of running on windows. I replace "prddbs01" with IP 192.168.10.107
3) correct but with IP not hostname (same as 2)
4) correct but sqlplus welcome message appeared and last forever (see attached)
sqlplus1.PNG

ASKER

After waiting for 4) for a long time it end up with ORA-12541 TNS no listener

Forgot:

5) You can run from your PC: tnsping //192.168.10.107:1521
6) You can run on your PC after starting the tunnel: tnsping //localhost:1521

And again:

7) You can run from a Linux PC with Oracle client: tnsping //prddbs01:1521
8) You can run from a Linux PC with Oracle client: sqlplus user/password@//prddbs01:1521

Just read the "No Listener" comment!

If there is indeed no listener then points 5, 6, 7, and 8 above cannot be correct!

ASKER

5) correct
6) correct
7) correct

8) I found something

[npc@prdapp01 ~]$ sqlplus user/password@//prddbs01:1521

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 26 20:40:51 2014

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

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Enter user-name:

sqlplus user/password@//prddbs01:1521/npcdb

ASKER

sqlplus user/password@//prddbs01:1521/npcdb << OK. appesr SQL> prompt

This last command was from Linux, right?

So there is a listener, and the Windows message is misleading.

It seems that that your Oracle DB version is below 9i?? Here you can well reach the DB server on port 1521, but the server's response comes back over a different port which is obviously blocked by the firewall. This should normally not happen with stateful firewalls and plain sqlplus (not ssh tunnel!), but who knows how your firewall is configured ...
Many firewalls have an option to allow SQL connections (statefully). Can you talk to your firewall admin in this regard?

Additionally, please let me know which version your DB server is running. Maybe there are options to change its behaviour, by forcing the use of the original listener port for the sqlplus connections (share this port), so we can use the ssh tunnel anyway.

ASKER

I think Oracle version is 11R2

[oracle@prdapp01 ~]$ cd /opt/oracle/product/11.2.0/client_1/network/admin/

Am I correct?

Most probably.

I think you should really contact your firewall admin to convince him to open it up for SQL connections (not just port 1521, which is already open!) between your PC and the DB server.
That way you won't need (better: you can't even use) an SSH tunnel, and life will become just easy.

We'll have big trouble to cheat your firewall with a tunnel, that's sure.
I'm still looking for Oracle options to find a way, but without success up to now.

ASKER

I will ask firewall admin today. I will update you once I get answer why?

Thank you

ASKER

Hi,

They said their side is open port 1521 and this maybe problem on my laptop!!!
They ask me to try again on other PC. BTW They have never try using SQL client such as Navicat at their side. So now I need to prove at my side first. Will update you soon

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I provided sql developer to them and my colleague to try. They will get back to me soon.

They should rather test the Oracle Windows client and sqlplus, I keep telling you.

ASKER

Yes I meant oracle windows client.

ASKER

Hi,

Now I can access db server by using IP 192.168.10.105 Maybe this one is DB HA server with 192.168.10.107
I am waiting for the reason from support.

ASKER

Hi,

They said 105 is the gate way to access real DB server. 107 is another linux client.
I still wonder in only linux client why I can found listener and can access sqlplus and get welcome message?

Do you have idea?

The welcome message comes from your local sqlplus binary, that's OK.

But tnsping will never say "OK" when there's no listener running on the target machine!

Please re-examine your environment! If tnsping really works with 107 then it's not just "another linux client", that's sure!

ASKER

Waiting for your reply then I will close this case and give you points.
Thank you for helping.

>> Waiting for your reply <<

Reply to what, please? Did I miss something?

ASKER

They keep telling this way and close the ticket :( btw I can use sql client tool

Log in to this famous "107" machine and run

ps -ef | grep tns

Any result?

ASKER

[oracle@prddbs01 ~]$ ps -ef|grep tns
oracle 23122 1 0 2010 ? 02:38:28 /opt/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle 26360 26283 0 18:33 pts/5 00:00:00 grep tns
oracle 32547 1 0 2010 ? 01:14:33 /opt/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
[oracle@prddbs01 ~]$

This is really the "107" machine?

If so, it's a DB server running even 2 listeners, and not just a Linux client.

ps -ef | grep oracle

to see what else is running there.

ASKER

Sorry for delay. I was away.
I found a lot of process running by oracle user. I think this server must be one of oracle DB server.

Most probably.

And if you found process names starting with e. g. ora_pmon_ , ora_smon_ , ora_dbw0_ etc. then it's positively a DB server.

ASKER

Yes I found what you said.

ASKER

Thank you