Why can't I connect to sqlplus when specifying the ORACLE SID and password from the command line?

1

I have 2 different Red Hat servers with Oracle 12c. On one of them, I can login to sqlplus by specifying the ORACLE SID and password from the command line:

[root@server1 ~]# $ORACLE_HOME/bin/sqlplus ourdbuser@$ORACLE_SID/ourpassword

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 23 20:16:01 2018

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

Last Successful login time: Fri Mar 23 2018 20:14:29 +00:00

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

On the other one, it does not work when I specify the ORACLE SID and password from the command line:

[root@server2 ~]# $ORACLE_HOME/bin/sqlplus ourdbuser@$ORACLE_SID/ourpassword

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 23 20:16:53 2018

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

ERROR:
ORA-12545: Connect failed because target host or object does not exist

But if I don't specify the ORACLE SID and password from the command line, and I type the password into the prompt, then it works:

[root@server2 ~]# $ORACLE_HOME/bin/sqlplus ourdbuser

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 23 20:17:17 2018

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

Enter password:
Last Successful login time: Fri Mar 23 2018 20:12:25 +00:00

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

I tried to see if there's any difference between server1 and server2, and I haven't found anything. Server1:

[root@server1 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
[root@server1 ~]# env | grep "ORACLE"
ORACLE_SID=ourdb
ORACLE_HOME=/opt/oracle/product/12.2.0/db
[root@server1 ~]# ls -lrt /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
-rw-r-----. 1 oracle oinstall 416 Aug  3  2017 /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
[root@server1 ~]# cat /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_OURDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))

OURDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ourdb)
    )
  )

Server2:

[root@server2 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
[root@server2 ~]# env | grep "ORACLE"
ORACLE_SID=ourdb
ORACLE_HOME=/opt/oracle/product/12.2.0/db
[root@server2 ~]# ls -lrt /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
-rw-r-----. 1 oracle oinstall 466 Aug 22  2017 /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
[root@server2 ~]# cat /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/12.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_OURDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))

OURDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ourdb)
    )
  )

What else should I check?

pacoverflow

Posted 2018-03-23T20:35:38.397

Reputation: 1 155

How about using a format such as sqlplus ourdbuser/ourpassword@ORACLE_SID instead, does that help? As per A Using SQL Command Line they give an example as: sqlplus hr/my_hr_password@host_computer_name so I think it's just a syntax issue but easy enough for you to try to eliminate as a solution as least. Let me know how it goes and I'll be happy to add as an answer with more detail if that solves the problem, just tag me back.

– Pimp Juice IT – 2018-03-26T13:14:23.550

Is it still the same "*Connect failed because target host or object does not exist*" error or something different? Did you try also putting in the port number and and host name, etc. in this format sqlplus ourdbuser/ourpassword@OracleHostServer:1521/@ORACLE_SID? The OracleHostServer should be the DNS name of the server the Oracle instance resides or it's IP address that's accessible from the machine you run the command. – Pimp Juice IT – 2018-03-26T15:20:51.317

When I try the command in your first comment, I get the same Connect failed because target host or object does not exist. When I try putting in the hostname/port, I get ORA-12514: TNS:listener does not currently know of service requested in connect descriptor. The Oracle DB is on the same server, and I tried using localhost, its hostname, and its IP, and they all result in that same error. – pacoverflow – 2018-03-26T15:29:46.660

Read over some of these answers to see if any of this helps: https://stackoverflow.com/questions/10786782/ora-12514-tnslistener-does-not-currently-know-of-service-requested-in-connect-d

– Pimp Juice IT – 2018-03-26T16:49:41.963

1It turns out that sqlplus ourdbuser/ourpassword@localhost:1521/$ORACLE_SID worked, which is slightly different from you suggested. But then I found out that wasn't the real issue. The real issue is that someone had changed the domain of the server after Oracle had been installed. The HOST values in the listener.ora and tnsnames.ora files had fully qualified domain names and they did not match what the hostname command returned. After fixing those 2 files, everything worked again. – pacoverflow – 2018-04-05T23:29:37.240

The difference in the commands also included $ORACLE_SID instead of @ORACLE_SID. I went ahead and posted the answer since that sqlplus command wasn't the real problem. Thanks for the help. – pacoverflow – 2018-04-06T01:06:42.473

Answers

1

I took Pimp Juice IT's suggestion of using a different sqlplus command (sqlplus ourdbuser/ourpassword@localhost:1521/$ORACLE_SID), and it worked.

However, that was just a workaround for the real cause of the problem, which was discovered after the server was rebooted and the Oracle listener failed to start. The real problem was that the domain of the server had been changed after Oracle was installed.

The hostname command showed the new domain of the server:

server1.zzz.bbb.ccc.com

But the $ORACLE_HOME/network/admin/listener.ora file contained the old domain:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1.aaa.bbb.ccc.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

The $ORACLE_HOME/network/admin/tnsnames.ora file did as well:

LISTENER_OURSID =
  (ADDRESS = (PROTOCOL = TCP)(HOST = server1.aaa.bbb.ccc.com)(PORT = 1521))

OURSID =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.aaa.bbb.ccc.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oursid)
    )
  )

After changing the domain in those 2 files, the Oracle listener was able to start, and the original sqlplus ourdbuser@$ORACLE_SID/ourpassword command worked.

pacoverflow

Posted 2018-03-23T20:35:38.397

Reputation: 1 155

Asked: 2018-03-23T20:35:38.397

Viewed: 759 times

Active: 2018-05-11T07:58:17.423