Solved

No Oracle lsnrctl command RHEL 5.3

Posted on 2014-01-25
80
692 Views
Last Modified: 2014-01-30
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
0
Comment
Question by:trazodone
  • 46
  • 34
80 Comments
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39808573
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
0
 

Author Comment

by:trazodone
ID: 39808696
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.
0
 

Author Comment

by:trazodone
ID: 39808698
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
0
 

Author Comment

by:trazodone
ID: 39808699
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]$
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39808717
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.
0
 

Author Comment

by:trazodone
ID: 39808897
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
0
 

Author Comment

by:trazodone
ID: 39808921
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.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39808939
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!
0
 

Author Comment

by:trazodone
ID: 39808955
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?"
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39808969
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

?
0
 

Author Comment

by:trazodone
ID: 39808979
[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 ~]$
0
 

Author Comment

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


Thanks!
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809005
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.
0
 

Author Comment

by:trazodone
ID: 39809019
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?
0
 

Author Comment

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

Thank you
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809031
>> 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?
0
 

Author Comment

by:trazodone
ID: 39809044
[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
0
 

Author Comment

by:trazodone
ID: 39809045
Additional

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

Author Comment

by:trazodone
ID: 39809048
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
0
 

Author Comment

by:trazodone
ID: 39809067
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.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809087
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?
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809093
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".
0
 

Author Comment

by:trazodone
ID: 39809125
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.
0
 

Author Comment

by:trazodone
ID: 39809140
Is it possible to use SSH tunnel?

Thank you.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809143
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)
)
)
0
 

Author Comment

by:trazodone
ID: 39809201
telnet 192.168.10.107 1521 not work (connecting to 192.168.10.17.... forever)
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809216
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?
0
 

Author Comment

by:trazodone
ID: 39809235
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...
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809248
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.
0
 

Author Comment

by:trazodone
ID: 39809260
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.
0
 

Author Comment

by:trazodone
ID: 39809269
Oracle client I downloaded and installed name "win64_11gR2_client.zip" install on my PC Windows 8.0 64Bit
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809281
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
0
 

Author Comment

by:trazodone
ID: 39809319
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
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809384
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?
0
 

Author Comment

by:trazodone
ID: 39809700
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.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809777
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.
0
 

Author Comment

by:trazodone
ID: 39809923
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.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809931
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.
0
 

Author Comment

by:trazodone
ID: 39809942
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
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809944
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!
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:trazodone
ID: 39809965
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
0
 

Author Comment

by:trazodone
ID: 39809971
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
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809973
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.
0
 

Author Comment

by:trazodone
ID: 39809974
From above

telnet localhost 1000 <<<< This one OK
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809977
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?
0
 

Author Comment

by:trazodone
ID: 39809983
SSH-2.0-OpenSSH_4.3
===========
once I press enter
===========
Protocol mismatch

connection to host lost

press anykey to continue
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809985
Tunnel OK.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809986
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?
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39809989
And prddbs01 is the DB server and 192.168.10.107 is its IP address - is this correct too?
0
 

Author Comment

by:trazodone
ID: 39809994
Yes you correct.

prdapp1 = 192.167.10.101
prddbs01 = 192.168.10.107

No problem for sqlplus command locally
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39810009
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!
0
 

Author Comment

by:trazodone
ID: 39810017
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
0
 

Author Comment

by:trazodone
ID: 39810023
After waiting for 4)  for a long time it end up with ORA-12541 TNS no listener
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39810029
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
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39810030
Just read the "No Listener" comment!

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

Author Comment

by:trazodone
ID: 39810043
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:
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39810051
sqlplus user/password@//prddbs01:1521/npcdb
0
 

Author Comment

by:trazodone
ID: 39810058
sqlplus user/password@//prddbs01:1521/npcdb << OK. appesr SQL>  prompt
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39810170
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.
0
 

Author Comment

by:trazodone
ID: 39810643
I think Oracle version is 11R2

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


Am I correct?
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39810669
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.
0
 

Author Comment

by:trazodone
ID: 39810895
I will ask firewall admin today. I will update you once I get answer why?

Thank you
0
 

Author Comment

by:trazodone
ID: 39811130
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
0
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 39811604
I think it's not a problem of Navicat, but a general Oracle client problem.

If I'm not completely wrong you're not using the Navicat GUI yet, but just the commandline tool "sqlplus", and this is the one which already doesn't work.

I can understand your firewall people, because a Unix/Linux DB server should indeed not redirect ports, it should communicate over this one and only port 1521.

But as we have seen above, this seems not to be true in your case.

We've well noticed that port 1521 is open, because tnsping works, but we have also seen that with sqlplus the server's response never reaches your PC which must be due to some other port(s) being required.

I don't know where this might come from!

Many firewalls have an option to configure an SQL*Net module/proxy to let pass this kind of traffic. Perhaps doing this is what you could suggest to your firewall staff.
0
 

Author Comment

by:trazodone
ID: 39811637
I provided sql developer to them and my colleague to try. They will get back to me soon.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39811654
They should rather test the Oracle Windows client and sqlplus, I keep telling you.
0
 

Author Comment

by:trazodone
ID: 39811676
Yes I meant oracle windows client.
0
 

Author Comment

by:trazodone
ID: 39814358
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.
0
 

Author Comment

by:trazodone
ID: 39814501
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?
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39814589
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!
0
 

Author Comment

by:trazodone
ID: 39814610
Waiting for your reply then I will close this case and give you points.
Thank you for helping.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39814620
>> Waiting for your reply <<

Reply to what, please? Did I miss something?
0
 

Author Comment

by:trazodone
ID: 39814666
They keep telling this way and close the ticket :( btw I can use sql client tool
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39814671
Log in to this famous "107" machine and run

ps -ef | grep tns

Any result?
0
 

Author Comment

by:trazodone
ID: 39814693
[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 ~]$
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39814700
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.
0
 

Author Comment

by:trazodone
ID: 39818344
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.
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 39818447
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.
0
 

Author Comment

by:trazodone
ID: 39818464
Yes I found what you said.
0
 

Author Closing Comment

by:trazodone
ID: 39820196
Thank you
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
Linux users are sometimes dumbfounded by the severe lack of documentation on a topic. Sometimes, the documentation is copious, but other times, you end up with some obscure "it varies depending on your distribution" over and over when searching for …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now