Linux – sqlplus: ORA-01017 logon denied from one host but OK from another

linuxoraclesqlplus

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

Best Answer

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>
Related Question