oracle 11g 创建 dblink 连接 postgreSQL一点补充

 

最新需要通过Oracle连接到PosgreSQL,碰到了一点问题，参考文章很详细，但是listener.ora配置有点小问题，会导致配置出错。

正确的配置例子是红色部分:

验证listener的结果如下:

同时也把配置和结果以文本的方式粘贴处理，供参考:

listener.ora

# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=PG_LINK)
         (ORACLE_HOME=O:\app\Henry\product\11.2.0\dbhome_1)
         (PROGRAM=dg4odbc)
      )
      (SID_DESC=
         (SID_NAME=ORCL)
         (ORACLE_HOME=O:\app\Henry\product\11.2.0\dbhome_1)
         (GLOBAL_DBNAME=ORCL)
      )
  )

#CONNECT_TIMEOUT_LISTENER = 0 

tnsnames.ora

# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

PG_LINK  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=PG_LINK))
    (HS=OK)
  ) 

initPG_LINK.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = PG_LINK
HS_FDS_TRACE_LEVEL = DEBUG


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

lsnrctl status 结果:

LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 06-5月 -2019 17:22:58

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
启动日期                  06-5月 -2019 16:56:28
正常运行时间              0 天 0 小时 26 分 34 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
监听程序参数文件          O:\Oracle11g\Henry\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
监听程序日志文件          O:\app\Henry\diag\tnslsnr\DESKTOP-9DEIBSS\listener\alert\log.xml
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
服务摘要..
服务 "ORCL" 包含 1 个实例。
  实例 "ORCL", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "PG_LINK" 包含 1 个实例。
  实例 "PG_LINK", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功