我尝试从Oracle数据库连接到PostgreSQL数据库
本文是JPOUG Advent Calendar 2021第16篇文章。昨天ora_gonsuke777先生的文章是「通过CMAN（Oracle连接管理器）和基于角色的服务/NLB将OCI DBCS数据保护配置的Primary连接转变为单一端点（Oracle数据库，Oracle Cloud基础设施）」。明天是tomo先生的文章。
通过Oracle Database Gateway for ODBC的功能，使用ODBC驱动程序创建连接到PostgreSQL数据库的DATABASE LINK。
ODBC 对接 Oracle 数据库的网关
Oracle的ODBC数据库网关是Oracle数据库的标准功能，可以在任何版本中使用。有关许可信息，请参阅”数据库许可信息用户手册”。使用ODBC驱动程序，这个功能可以向除Oracle数据库之外的关系数据库管理系统（RDBMS）发出SQL语句。本文介绍了如何在Red Hat Enterprise Linux环境下连接Oracle Database 19c至PostgreSQL 14。有关详细信息，请参阅”Oracle Database Gateway for ODBC用户指南”提供的手册。
准备安装
PostgreSQL 侧
在安装PostgreSQL时，不需要进行特殊的准备。只需在设置中指定等待外部连接的地址（listen_addresses）和端口号（port），并在pg_hba.conf文件中记录允许来自Oracle数据库服务器的连接。在下面的示例中，允许从Oracle数据库服务器地址192.168.1.100连接到postgres数据库。md5表示使用密码进行身份验证。
$ cat $PGDATA/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host postgres all 192.168.1.100/32 md5
Oracle数据库服务器
为了连接Oracle数据库服务器，需要在Linux上安装ODBC Manager包、PostgreSQL ODBC Driver和PostgreSQL Client。在下面的示例中，确认ODBC Manager已安装并安装了PostgreSQL 14的rpm包。
# rpm -qa | grep ODBC
unixODBC-2.3.7-1.el8.x86_64
# rpm -ivh postgresql14-14.1-1PGDG.rhel7.x86_64.rpm postgresql14-libs-14.1-1PGDG.rhel7.x86_64.rpm postgresql14-odbc-13.02.0000-1PGDG.rhel7.x86_64.rpm
warning: postgresql14-14.1-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:postgresql14-libs-14.1-1PGDG.rhel################################# [ 33%]
2:postgresql14-14.1-1PGDG.rhel7 ################################# [ 67%]
3:postgresql14-odbc-13.02.0000-1PGD################################# [100%]
ODBC配置
确认在运行Oracle数据库的Linux上的ODBC Manager配置。
# odbc_config --odbcini --odbcinstini
/etc/odbc.ini
/etc/odbcinst.ini
# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
安装PostgreSQL ODBC驱动程序后，将在/etc/odbcinst.ini文件中追加驱动程序信息。根据需要进行修改。
# cat odbcinst.ini
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/pgsql-14/lib/psqlodbcw.so
Setup = /usr/pgsql-14/lib/libodbcpsqlS.so
Driver64 = /usr/pgsql-14/lib/psqlodbcw.so
Setup64 = /usr/pgsql-14/lib/libodbcpsqlS.so
FileUsage = 1
在odbc.ini文件中追加连接到PostgreSQL数据库的属性。您可以在任意名称的部分（例如pgdsn）下添加信息。以下是最低必需的配置选项。
# cat /etc/odbc.ini
[pgdsn]
Driver = PostgreSQL
Description = PostgreSQL ODBC Driver
Database = postgres
Servername = 192.168.1.223
Port = 5432
UseDeclareFetch = 1
Debug = 1
LowerCaseIdentifier = 1
ShowOidColumn = 0
设定初始参数
在中国，只需要一个选项。
准备异机种间接（HS）続专用的初始化参数。文件需要在 $ORACLE_HOME/hs/admin 目录下以 init{接口名}.ora 的名称创建。以下示例使用 pgdsn 作为连接名进行初始化参数设置。
$ pwd
/u01/app/oracle/homes/OraDB19Home1/hs/admin
$
$ ls
initpgdsn.ora
$
$ cat initpgdsn.ora
HS_FDS_CONNECT_INFO = pgdsn
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
set ODBCINI=/etc/odbc.ini
网络设置
然后，我们将进行听众设置。当Oracle Database Gateway连接到不同的数据库时，它将通过监听器启动dg4odbc进程。我们需要在监听器设置文件（listener.ora）中添加用于外部连接的SID_LIST。同时，我们还需设置环境变量LD_LIBRARY_PATH以及启动进程的PROGRAM=dg4odbc设置。
$ pwd
/u01/app/oracle/homes/OraDB21Home1/network/admin
$
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME = pgdsn)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/19.0.0/dbhome_1/lib")
(PROGRAM=dg4odbc)
)
)
将连接数据库的入口添加到 tnsnames.ora 文件中。连接到本地主机的监听器，并添加 HS=OK 的设置。
$ cat tnsnames.ora
pgdsn =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA=(SID=pgdsn))
(HS=OK)
)
创建数据库链接
我会创建一个引用PostgreSQL实例的DATABASE LINK。我会提供PostgreSQL数据库的用户名和密码。为了注意大小写，我会用双引号括起用户名和密码。我会在USING子句中指定在tnsnames.ora文件中添加的连接器。在下面的示例中，我将创建一个DATABASE LINK，并引用pg_am目录的定义。如果能够成功地引用查看定义而不输出错误，则表示成功。
SQL> CREATE DATABASE LINK pglink CONNECT TO "demo" identified by "<<PASSWORD>>"
2 USING 'pgdsn';
データベース・リンクが作成されました。
SQL> DESCRIBE "pg_am"@pglink
名前 NULL? 型
----------------------------------------- -------- ----------------------------
oid NOT NULL NUMBER(10)
amname NOT NULL VARCHAR2(189)
amhandler NOT NULL VARCHAR2(765)
amtype NOT NULL CHAR(3)
执行 SQL 语句
由于可以创建 DATABASE LINK，因此可以对 PostgreSQL 上的表执行 SQL 语句。需要注意的是，PostgreSQL 数据库的对象名基本上以小写存储，因此在指定对象名时需要使用双引号。
SQL> SELECT COUNT(*) FROM pg_am@pglink;
SELECT COUNT(*) FROM pg_am@pglink
*
行1でエラーが発生しました。:
ORA-28500:
OracleからOracle以外のシステムへの接続で次のメッセージが戻されました: ERROR: relation "PG_AM" does
not exist;
No query has been executed with that handle {42P01,NativeErr = 1}
ORA-02063: 先行のエラー・メッセージを参照してください3 lines(PGLINK)。
SQL> SELECT COUNT(*) FROM "pg_am"@pglink;
COUNT(*)
----------
7
故障排除
最初的验证使用了Red Hat Enterprise 7和Red Hat Enterprise 8附带的PostgreSQL 9或PostgreSQL 10的ODBC驱动程序。然而，由于无法连接到PostgreSQL 14或在搜索oid列（在PostgreSQL 12中已弃用）时出现错误等问题，我们决定安装适用于PostgreSQL 14的新驱动程序。以下是PostgreSQL 10 ODBC驱动程序在进行表存在检查时出现错误的日志。
2021-10-19 00:37:20.221 JST [4142] ERROR: column c.relhasoids does not exist at character 245
2021-10-19 00:37:20.221 JST [4142] STATEMENT: select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids, attidentity, c.relhassubclass from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.oid = 16984) inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum
2021-10-19 00:37:20.222 JST [4142] ERROR: current transaction is aborted, commands ignored until end of transaction block
当发生错误时，需要检查的日志文件将被创建在 $ORACLE_HOME/hs/log 目录中。
$ pwd
/u01/app/oracle/homes/OraDB19Home1/hs/log
$ ls
37951.log pgdsn_agt_38220.trc pgdsn_agt_6608.trc
38059.log pgdsn_agt_38282.trc pgdsn_agt_6811.trc
38174.log pgdsn_agt_38728.trc pgdsn_agt_7245.trc
38220.log pgdsn_agt_39133.trc pgdsn_agt_7432.trc
38282.log pgdsn_agt_39282.trc pgdsn_agt_7970.trc
pgdsn_agt_10390.trc pgdsn_agt_39390.trc pgdsn_agt_8004.trc
pgdsn_agt_37657.trc pgdsn_agt_5867.trc pgdsn_agt_8021.trc
pgdsn_agt_37721.trc pgdsn_agt_5954.trc pgdsn_agt_8081.trc
pgdsn_agt_37951.trc pgdsn_agt_5994.trc pgdsn_agt_8451.trc
pgdsn_agt_38059.trc pgdsn_agt_6143.trc pgdsn_agt_8526.trc
pgdsn_agt_38174.trc pgdsn_agt_6328.trc
如果在初始化参数HS_LANGUAGE中设置除AMERICAN_AMERICA.WE8ISO8859P1以外的值，那么SQL语句的执行将会失败，因此无法完成汉字编码转换。
SQL> INSERT INTO "data1"@pglink VALUES (0, '漢字');
INSERT INTO "data1"@pglink VALUES (0, '漢字')
*
行1でエラーが発生しました。:
ORA-28500:
OracleからOracle以外のシステムへの接続で次のメッセージが戻されました: ERROR: invalid byte sequence
for encoding "UTF8": 0xbf;
Error while preparing parameters {22021,NativeErr = 1}
ORA-02063: 先行のエラー・メッセージを参照してください3 lines(PGLINK)。
