3

I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain:

1) I have an Oracle 11.2 server running on a virtual host, say host1
2) I set up another VM, say host2 (CentOS 6.5 x86_64) with oracle-instantclient 11.2.0.4 64bit, built up from a minimal install ISO
3) I also downloaded a CentOS 6.5 x86_64 VM image from the net (a desktop install with everything already installed), say host3, on which I installed the same Oracle instantclient

I use the same environment and tnsnames.ora file on both host2 and host3, something like:

MONSTER=
(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CRM)))

Issue:
On host2, I cannot get rid of the ORA-01017 error:

host2> sqlplus sys/<passwd>@monster as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 1 12:09:51 2014

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

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

BUT, on host3, everything is OK:

host3> sqlplus sys/<passwd>@monster as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 1 06:21:59 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release ...
SQL>

Has someone an idea why connecting from one host is OK while connecting from the other one is KO?

NB: Needless to say this is not an issue of case sensitivity, or bad orapwd, or remote_login_passwordfile not set to EXCLUSIVE, since it works from host3.

Thanks

Improve this question
3
  • Does ping host1 reveal the same IP address on both hosts?
    – user1822
    Aug 1, 2014 at 12:49
  • Yes, same IP address on both hosts
    – galli2000
    Aug 1, 2014 at 13:00
  • Did you try to see what happens at your network interface on the DB server, what the connect descriptor looks like in the end whenever you try to connect from the client machine? I usually employ tcpdump or Wireshark for the debugging purposes.
    – YasirA
    Aug 1, 2014 at 13:38

3 Answers 3

Reset to default
2

At first make sure the database host is available ping host1.

Then you have to install a client and configure service name.

To install the oracle client correctly and be able to use it you have to:

  1. Choose the "Administrator" type of the oracle client installation, not instant client, it provides you database administrator tools.
  2. After your client installation run Net Manager (netmgr) to add a new service name. When you start testing the connection (Final step), make sure the "scott" user is available in your database.
  3. Then run tnsping YOUR_SERVICE_NAMECONNECTION_NAME(Thanks @vapcguy for the correction) - tnsping is a tool that comes in the administration installation).
  4. Run sqlplus sys/passwd@<YOUR_SERVICE_NAME> as sysdba or sqlplus sys/passwd@host1:PORT/<DATABASE_SERVICE_NAME>
Improve this answer
1
  • On #3, it isn't always the service name. The SERVICE_NAME in the connection string in a tnsnames.ora file can be one thing, the name given to the connection string might be another - as it was in my case. tnsping will fail if you use the SERVICE_NAME instead of the name given to the connection string, which is actually called the CONNECTION NAME, when you are filling this out in Oracle SQL Developer. So you must, actually, do tnsping CONNECTION NAME, instead, or you will get TNS-03505: Failed to resolve name.
    – vapcguy
    Jun 23, 2016 at 19:34
1

I found out the solution:
If the machine has a hostname defined in /etc/sysconfig/network, this name also needs to be defined in /etc/hosts, period.

This is what I had:

host2> cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=host2

host2> cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.2.8 host1

So as to fix the issue with sqlplus, change /etc/hosts to be:

host2> cat /etc/hosts
127.0.0.1   host2 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         host2 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.2.8 host1

Or change /etc/sysconfig/network to be:

host2> cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=localdomain.localhost

As a conclusion, the ORA-01017 error message was just misleading.

Improve this answer
0

Had a similar issue, myself, where my app could connect from Visual Studio, but not when published to a server. Nothing here resolved it for me, though zaratustra's post helped, but so I thought I'd link to my post - ORA-01017: invalid username/password;logon denied - to show how to troubleshoot these - or at least, how I did, before I gave up and posted my question!

The way I had to solve mine was changing a registry entry: HKLM\System\CurrentControlSet\Control\Lsa\FIPSAlgorithmPolicy\Enabled from 1 to 0.

Seems there's a bug with the 12c client and how its encryption algorithm for sending out the password to the server is not FIPS-compliant, so it never gets there. Granted, this was with an 11g client, but it might help someone in the future, if they see this and have a 12c client and the other fixes given here don't work for them.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.