如何修复cx_Oracle.DatabaseError: ORA-12170: TNS:连接超时

Question

我正在尝试运行一个Python脚本，从docker映像中将一些数据插入到Oracle表中。

给我的连接字符串如下：

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)))   

我正在尝试使用cx_Oracle包进行连接：

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')

oracle_dsn是连接字符串(减去jdbc:oracle:thin:@部分)

我也尝试过

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

如一些示例所示，但我总是得到以下超时错误：

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

telnet host 1521工作得很好，我也尝试过更改CONNECT_TIMEOUT值。

我也试过

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) 

就像建议的那样，这里，但是我得到了

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

我使用Dockerfile从Docker映像构建运行我的脚本：

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" ]

我不明白怎么回事？

我设置的更新

• 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

我在tnsnames.ora目录中添加了一个TNS_ADMIN文件

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)))

现在，当我尝试sqlplus MY_USER@CNX时，我得到了相同的信息：

ORA-12170: TNS:Connect timeout occured

但至少它似乎接受了连接字符串和用户。在正确设置所有环境变量之前，我只收到关于TNS: listener does not currently know of service requested in connect descriptor或网络服务名称不正确的不同TNS错误消息。

更新2我和给我所有连接信息的人检查过:用户、密码和连接字符串是正确的。他们正在运行OracleDatabase12c (12.1.0.2.0)，根据此页的说法，它与我使用的18c客户机兼容。

我不知道这个超时错误还可能是什么原因？

Answer 1

首先，在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')

关于连接和连接字符串的cx_Oracle手册是这里。

就我个人而言，我会使用19c即时客户端，它将连接到与18c相同的DB版本，并且不需要运行ldconfig。见Node.js和Python中Oracle数据库应用程序的Docker。

用信息更新你的问题，我也可以更新这个答案。