1

I am trying to run a Python script to insert some data into an Oracle table, from a docker image.

I was given following connection string :

jdbc:oracle:thin:@(DESCRIPTION=(CONNECT_TIMEOUT=3)(RETRY_COUNT=2)(FAILOVER=ON)(LOAD_BALANCE=NO) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_1)(PORT=1521))) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_2)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=service_name))) 

I'm trying to connect using cx_Oracle package :

try:
    # establish a new connection
    with cx_Oracle.connect(self.oracle_user,
                            self.oracle_pwd,
                            self.oracle_dsn) as connection:
        logger.info('ElasticsearchFinder.oracle_write : connexion established with DB')

with oracle_dsn being the connexion string (minus the jdbc:oracle:thin:@ part)

I also tried things like

cx_Oracle.connect(self.oracle_user+'/'+self.oracle_pwd+'@'+self.oracle_dsn)

as seen in some examples, but I always get the following timeout error :

cx_Oracle.DatabaseError: ORA-12170: TNS:Connect timeout occurred

telnet host 1521 works fine, I also tried changing the CONNECT_TIMEOUT value.

I also tried

dsn_tns = cx_Oracle.makedsn(self.oracle_host, self.oracle_port, service_name = self.oracle_service_name)
cx_Oracle.connect(self.oracle_user,self.oracle_pwd,dsn_tns)

as suggested here but then I get

cx_Oracle.DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I am running my script from a Docker image build with Dockerfile :

FROM oraclelinux:7.8
RUN  yum -y install oracle-release-el7 && \
     yum-config-manager --enable ol7_oracle_instantclient && \
     yum -y install oracle-instantclient18.3-basic && \
     rm -rf /var/cache/yum
COPY ./fetch_session_iptv.py /opt/
COPY ./conf/fetch_session_iptv.conf /opt/conf/
#COPY ./conf/certs/* /opt/conf/certs/
COPY ./logs /opt/logs
RUN yum install -y \
    #https://yum.oracle.com/repo/OracleLinux/OL7/developer/x86_64/getPackage/oracle-instantclient18.3-basic-18.3.0.0.0-2.x86_64.rpm \
    https://yum.oracle.com/repo/OracleLinux/OL7/developer/x86_64/getPackage/python-cx_Oracle-7.3-1.el7.x86_64.rpm \
    https://yum.oracle.com/repo/OracleLinux/OL7/developer/x86_64/getPackage/python36-pytz-2016.10-2.0.1.el7.noarch.rpm
COPY ./python_requirements/elasticsearch-7.8.0-py2.py3-none-any.whl .
COPY ./python_requirements/certifi-2020.4.5.2-py2.py3-none-any.whl .
COPY ./python_requirements/urllib3-1.25.9-py2.py3-none-any.whl .
RUN pip3 install --user \
    certifi-2020.4.5.2-py2.py3-none-any.whl \
    urllib3-1.25.9-py2.py3-none-any.whl \
    elasticsearch-7.8.0-py2.py3-none-any.whl \
    cx_Oracle
RUN sh -c "echo /usr/lib/oracle/18.3/client64/lib > /etc/ld.so.conf.d/oracle-instantclient.conf"
RUN ldconfig
RUN export ORACLE_HOME=/usr/lib/oracle/18.3/client64/
RUN yum -y install telnet
#CMD ["/bin/bash"]
CMD [ "python3", "/opt/fetch_session_iptv.py" ]

I can't understand what's going wrong?

[UPDATE] I've set

  • ORACLE_HOME=/usr/lib/oracle/18.3/client64/bin
  • TNS_ADMIN=$ORACLE_HOME/admin
  • LD_LIBRARY_PATH=/usr/lib/oracle/18.3/client64/lib
  • PATH=$PATH:$ORACLE_HOME

I've added a tnsnames.ora file in TNS_ADMIN directory, with

CNX=(DESCRIPTION=(CONNECT_TIMEOUT=3)(RETRY_COUNT=2)(FAILOVER=ON)(LOAD_BALANCE=NO) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_1)(PORT=1521))) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_2)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=service_name)))

Now, when I try sqlplus MY_USER@CNX I get the same :

ORA-12170: TNS:Connect timeout occured

but at least it seems to accept the connexion string and user. Prior to setting all the environment variables correctly, I only got different TNS error messages about TNS: listener does not currently know of service requested in connect descriptor or the net service name being incorrect.

[UPDATE 2] I've checked with the person who gave me all connexion info: user, password and connection string are correct. They're running Oracle Database 12c (12.1.0.2.0), which according to this page is compatible with the 18c client I'm using.

I don't know what else could be a possible reason for this timeout error?

Flxnt
  • 177
  • 4
  • 22
  • Have you defined an environment variable called `TNS_ADMIN`, and set pointing to the directory of `TNSNAMES.ORA` file ? – Barbaros Özhan Jun 20 '20 at 14:35
  • I am not using `TNSNAMES.ORA` file to get connection info, is Oracle only checking that file by default? – Flxnt Jun 22 '20 at 08:57
  • Also, when I try to set any environment variable into my Dockerfile (`RUN export my_env_variable = /usr/....`), then `docker exec echo $my_env_variable`, it always returns an empty string, I don't get why it doesn't seem to work – Flxnt Jun 22 '20 at 09:26
  • For a start, never set ORACLE_HOME with Instant Client. It leads to issues such as hitting ORA-1804. Instant Client is not an Oracle Home-based install. Secondly, the default directory for network files is /usr/lib/oracle/18.3/client64/lib/network/admin. Put the files there and unset TNS_ADMIN. – Christopher Jones Jun 24 '20 at 22:35

1 Answers1

1

Start by using the equivalent connection string in cx_Oracle:

self.oracle_dsn = "(DESCRIPTION=(CONNECT_TIMEOUT=3)(RETRY_COUNT=2)(FAILOVER=ON)(LOAD_BALANCE=NO) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_1)(PORT=1521))) (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host_2)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=service_name)))"

try:
    # establish a new connection
    with cx_Oracle.connect(self.oracle_user,
                            self.oracle_pwd,
                            self.oracle_dsn) as connection:
        logger.info('ElasticsearchFinder.oracle_write : connexion established with DB')

The cx_Oracle manual on connecting and connect strings is here.

Personally I'd use 19c Instant Client, which will connect to the same DB versions as 18c - and doesn't need to have ldconfig run. See Docker for Oracle Database Applications in Node.js and Python.

Update your question with information and I can update this answer likewise.

Christopher Jones
  • 9,449
  • 3
  • 24
  • 48