[SOLVED] Oracle client not connecting to Oracle server (separate docker containers)

Oracle client not connecting to Oracle server (separate docker containers)

My problem is that I'm not able to connect to my Oracle Database (version 21.3.0 running in one docker container) from my Oracle client (version 21 running in a separate docker container).

Here is the context of the execution.

On my computer (host) I'm on windows. From my windows I run a linux virutal machine (through virtual-box) which I use to run my docker containers (it's a personal development environment).

I built the 2 docker images my self following the official instructions from here https://github.com/oracle/docker-images. Used the OracleDatabase subdirectory (version 21.3.0) and the OracleInstantClient/oraclelinux7 (version 21). (Installation binaries are downloaded during the image build from official repos).

To run my Oracle Database server I use this command line :

docker run -dti --network=sai -p 1521:1521 -e ORACLE_PWD=password123 --name=oracle-xe oracle/database:21.3.0-xe

I've created the user-defined network "sai" so that both containers can see each other.

For the client I've used this tnsnames.ora in default directory /usr/lib/oracle/21/client64/lib/network/admin/ :

XE =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-xe)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SID = XE)
            (SERVICE_NAME = XE)
        )
    )

Here is my command line to run the client : docker run -ti --network=sai --rm oracle-instantclient bash If I install ping utility in my client container I can ping oracle-xe and it's responding.

when I run sqlplus and after entering username/password I got this message : ORA-12162: TNS:net service name is incorrectly specified

I've added trace and here is the content sqlnet.log

2022-12-07T11:22:53.330824+00:00
Create Relation ADR_CONTROL
Create Relation ADR_INVALIDATION
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS

After some research, it appeared that it was because the ORACLE_SID variable was not set. I tried first to add it in the tnsnames.ora (as you can see) but it didn't change anything so I tried using the following command: export ORACLE_SID=XE

Now I get another error : ORA-12545: Connect failed because target host or object does not exist

and now this is the content of the sqlnet.log file:

2022-12-07T11:28:22.644437+00:00
***********************************************************************
Fatal NI connect error 12545, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/usr/lib/oracle/21/client64/lib/bin/oracle)(ARGV0=oracleXE)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')(DETACH=NO))(CONNECT_DATA=(CID=(PROGRAM=sqlplus)(HOST=c09ab9b0630a)(USER=root))(CONNECTION_ID=7zw32Sp/AAfgUwMAEqxRbQ==)))

  VERSION INFORMATION:
        TNS for Linux: Version 21.0.0.0.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 21.0.0.0.0 - Production
  Version 21.8.0.0.0
  Time: 07-DEC-2022 11:28:22
  Tracing to file: /root/oradiag_root/diag/clients/user_root/host_3819129240_110/trace/ora_7_140531135179328.trc
  Tns error struct:
    ns main err code: 12545

TNS-12545: Connect failed because target host or object does not exist
    ns secondary err code: 12560
    nt main err code: 515

TNS-00515: Connect failed because target host or object does not exist
    nt secondary err code: 2
    nt OS err code: 0

What is weird in that trace is that HOST=c09ab9b0630a is actually my client container (not my database container oracle-xe) and it is confirmed (I think) by the LOCAL=YES. It's like my tnsnames.ora isn't taken into account. I tried also to set the variable TNS_ADMIN but it doesn't change anything.

What makes me think the problem is a configuration problem in the client container is that I can connect to my database container from my sql developer on my Windows (host). To succeed that, I forwarded my port 1521 to my virtual machine, I used the -p option on my database container and I setup the tnsnames.ora on windows as the following :

XE =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = XE)
        )
    )

That is working fine.

In the Client container :

I tried to change the values in the tnsnames.ora by putting the real name of the container or the ip of my host (with the -p option server side).

I tried removing the -p option when running the database container.

In all those cases I got the same errors.

Reading the sqlnet.log file I feel like the tnsnames.ora is not taken into account and so it's looking to the wrong place. But I'm not oracle expert.

Thanks for the help

EDIT

The problem is definitely with the tnsnames.ora on the client side because when I use the following command in my client container it works :

sqlplus userName/password@oracle-xe/XE

But I really need to figure out that tnsnames.ora problem.

Solution

For those who would like the answer... The problem was that you can't ommit the connect string when using sqlplus. So at least provide the TNS entry like this :

sqlplus username/password@XE

this works fine.

Best Regards