SQLPlus configuration problem or firewall issue

Question

I need to connect to a remote Oracle DB, so I requested to open the firewall on the port 1521 to the DB and the replica.

[root@ansible-test ~]$ telnet <oracle_db_active> 1521
Trying <oracle_db_active>...
Connected to <oracle_db_active>.
Escape character is '^]'.
^CConnection closed by foreign host.
[root@ansible-test ~]$ telnet <oracle_db_replica> 1521
Trying <oracle_db_replica>...
Connected to <oracle_db_replica>.
Escape character is '^]'.
^CConnection closed by foreign host.

I'm able to telnet to the DB on port 1521.
But when I try to connect using SQLPlus I get an error as the host doesn't exist.

[root@ansible-test ~]$ echo exit |sqlplus "<user>/<pass> @(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <oracle_db_active>)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = <oracle_db_replica>)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = <service>)(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 12)(DELAY = 5))))"
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Sep 7 17:37:23 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
Enter user-name: Enter password:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

I have another server in the same network as the DB and I'm able to connect with the same string.
I don't have an sql.ora or tnsname.ora configure on any of the servers.
The only difference between the two servers is that one is RedHat and the other centos.

Do I need to open any other port to the DB o it is a configuration issue?

Thanks (sorry for all the redacting)

• Jim D: What output does the tnsping command produce?
• Jim D: And please tell me you don't have an open port 1521 in the Internet
• Wernfried Domscheit: Maybe remove all the space characters from DB name. Anyway, usually you define an alias for this in your tnsnames.ora file or in a name server.
• radicaled: Jim, no, the port is not open to internet, this are 2 internal networks and the ports are open. Regarding the tnsnames.ora, I'm using the connection string, so as far as I understand there is no need for it. I have also remove all the spaces with no luck

Answer 1

I end up finding the answer :)
On my test server on the same network as the DB I made a tcpdump and I notice that my connection started on the primary IP and the DB reply through the secondary IP. There was a request on the DNS server in the middle.

I made the same test on my ansible-test server and found out that there was problem on then DNS request and fail the connection. Basically, it was not able to understand the secondary IP name.

So this end up being a weird networking problem.

Thanks All.

Answer 2

I once had a similar scenario like the one you are having. I deployed Oracle Connection Manager on RedHat 6. Everything configured through ansible. Everything was working on my Vagrant Oracle Linux during development, but once deployed to RedHat I had these ORA- messages. Everything was correct, but I never found the reason why I couldn't connect to the database. I replaced the node with Oracle Linux and it worked. I possibly could have replaced the node with another RedHat node. Like I said, same configuration rolled out with ansible. Sometimes you cannot waste more time on a problem. Recreate node and run your ansible playbook.

• miracle173: that my be a useful comment but this is not an answer