OraFAQ Forum: Client Tools » Cannot connect to DB remotely using sqlplus

Home » Other » Client Tools » Cannot connect to DB remotely using sqlplus (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cannot connect to DB remotely using sqlplus [message #574640]

Mon, 14 January 2013 10:17

djulian
Messages: 4
Registered: January 2013

Junior Member

Hi,

I cannot seem to connect to my Oracle database from my windows machine using sqlplus. I can ping the Oracle machine, I can tnsping the Oracle instance and I can login as sys from Windows, but I can't login as the user I would like to use. I can login as the user on the server. Any suggestions?

tnsnames.ora
-------------
dmuODB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dmudms01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = odb)
)
)

ODB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dmudms01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = odb)
)
)

mdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = demodc)(PORT = 1531))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mdb)
)
)

Windows prompt:
---------------
C:\Users\XXXXX>tnsping dmuodb

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 14-JAN-2
013 11:12:05

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

Used parameter files:
C:\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 = dmudms01
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = odb)))
OK (110 msec)

C:\Users\XXXXX>ping dmudms01

Pinging dmudms01.demo.virtual [10.127.145.88] with 32 bytes of data:
Reply from 10.127.145.88: bytes=32 time=37ms TTL=56
Reply from 10.127.145.88: bytes=32 time=36ms TTL=56
Reply from 10.127.145.88: bytes=32 time=35ms TTL=56
Reply from 10.127.145.88: bytes=32 time=35ms TTL=56

Ping statistics for 10.127.145.88:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 35ms, Maximum = 37ms, Average = 35ms

C:\Users\XXXXX>sqlplus hk_nes/demo@dmuodb

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 14 11:01:07 2013

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

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

C:\Users\XXXXX>sqlplus sys/sys@dmuodb as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 14 11:13:40 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Linux prompt
------------
-bash-3.2$ sqlplus hk_nes/demo@dmuodb

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 14 10:11:43 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

-bash-3.2$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 14-JAN-2013 10:15:30

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 14-JAN-2013 09:27:27
Uptime 0 days 0 hr. 48 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11gR22/network/admin/listener.ora
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dmudms01.demo.virtual)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "odb" has 2 instance(s).
Instance "odb", status UNKNOWN, has 1 handler(s) for this service...
Instance "odb", status READY, has 1 handler(s) for this service...
Service "odbXDB" has 1 instance(s).
Instance "odb", status READY, has 1 handler(s) for this service...
The command completed successfully
-bash-3.2$

Thanks for any help.
--
Danny

Report message to a moderator

Re: Cannot connect to DB remotely using sqlplus [message #574644 is a reply to message #574640]

Mon, 14 January 2013 11:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

every connection request that arrives at the Listener gets logged into Listener Log File.
using COPY & PASTE, post excerpt from Listener Log File that shows the connection request from the Windows client that failed to login.

Report message to a moderator

Re: Cannot connect to DB remotely using sqlplus [message #574647 is a reply to message #574644]

Mon, 14 January 2013 12:28

djulian
Messages: 4
Registered: January 2013

Junior Member

Below is the contents Listener Log File:

<msg time='2013-01-14T12:24:12.079-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46' version='1'>
<txt>System parameter file is /oracle/product/11gR22/network/admin/listener.ora
</txt>
</msg>
<msg time='2013-01-14T12:24:12.080-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>Log messages written to /oracle/diag/tnslsnr/dmudms01/listener/alert/log.xml
</txt>
</msg>
<msg time='2013-01-14T12:24:12.080-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>Trace information written to /oracle/diag/tnslsnr/dmudms01/listener/trace/ora_4679_47154350796736.trc
</txt>
</msg>
<msg time='2013-01-14T12:24:12.080-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>Trace level is currently 0
</txt>
</msg>
<msg time='2013-01-14T12:24:12.080-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>
</txt>
</msg>
<msg time='2013-01-14T12:24:12.083-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>Started with pid=4679
</txt>
</msg>
<msg time='2013-01-14T12:24:12.084-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
</txt>
</msg>
<msg time='2013-01-14T12:24:12.085-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dmudms01.demo.virtual)(PORT=1521)))
</txt>
</msg>
<msg time='2013-01-14T12:24:12.110-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>Listener completed notification to CRS on start
</txt>
</msg>
<msg time='2013-01-14T12:24:12.110-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
</txt>
</msg>
<msg time='2013-01-14T12:24:12.112-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>14-JAN-2013 12:24:12 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=dmudms01)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
</txt>
</msg>
<msg time='2013-01-14T12:24:33.860-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>14-JAN-2013 12:24:33 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=odb)(CID=(PROGRAM=oracle@dmudms01)(HOST=dmudms01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.127.145.46)(PORT=37690)) * establish * odb * 0
</txt>
</msg>
<msg time='2013-01-14T12:24:33.873-06:00' org_id='oracle' comp_id='tnslsnr'
type='UNKNOWN' level='16' host_id='dmudms01'
host_addr='10.127.145.46'>
<txt>14-JAN-2013 12:24:33 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=odb)(CID=(PROGRAM=oracle)(HOST=dmudms01)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.127.145.46)(PORT=37691)) * establish * odb * 0
</txt>
</msg>

--
Danny

Report message to a moderator

Re: Cannot connect to DB remotely using sqlplus [message #574651 is a reply to message #574647]

Mon, 14 January 2013 12:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

posted excerpt shows no entry from any attempt like below

>sqlplus hk_nes/demo@dmuodb

I want to see proof that the Windows client is attempting to log into the same DB.

Report message to a moderator

Re: Cannot connect to DB remotely using sqlplus [message #574655 is a reply to message #574651]

Mon, 14 January 2013 13:01

djulian
Messages: 4
Registered: January 2013

Junior Member

I would like to see proof as well. Can you give me some pointers on what to do? I've added the following to my listener.ora file and restarted the listener service but don't seem to be seeing anything in /oracle/diag/tnslsnr/dmudms01/listener/alert/log.xml or /oracle/diag/tnslsnr/dmudms01/listener/trace/listener.log related to my remote query.

listener.ora
-------------

DIAG_ADR_ENABLED=on
TRACE_LEVEL_listener=admin
TRACE_TIMESTAMP_listener=true
ADR_BASE=/oracle/network/trace
logging_listener = on

Linux
-----
-bash-3.2$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 14-JAN-2013 12:58:26

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 14-JAN-2013 12:46:08
Uptime 0 days 0 hr. 12 min. 17 sec
Trace Level admin
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11gR22/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/dmudms01/listener/alert/log.xml
Listener Trace File /oracle/diag/tnslsnr/dmudms01/listener/trace/ora_4981_46947032453056.trc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dmudms01.demo.virtual)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "odb" has 2 instance(s).
Instance "odb", status UNKNOWN, has 1 handler(s) for this service...
Instance "odb", status READY, has 1 handler(s) for this service...
Service "odbXDB" has 1 instance(s).
Instance "odb", status READY, has 1 handler(s) for this service...
The command completed successfully
-bash-3.2$

--
Danny

Report message to a moderator

Re: Cannot connect to DB remotely using sqlplus [message #574657 is a reply to message #574655]

Mon, 14 January 2013 13:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>ORA-01017: invalid username/password; logon denied
for Windows client to report error above, it is talking to some DB; exactly which DB is now indeterminate.
Which ever DB the client connects with, it will be recorded in Listener Log File.
You may need to packet sniff to determine what is actually happening on the Network.

Report message to a moderator