Oracle Database から PostgreSQL への接続を試す

この記事は [JPOUG Advent Calendar 2021](https://adventar.org/calendars/6527) 16 日目の記事です。
昨日は ora_gonsuke777 さんの「[CMAN(Oracle Connection Manager) と ロールベース・サービス/NLB で OCI DBCS Data Guard構成 の Primary接続 をシングル・エンドポイント化してみる。(Oracle Database, Oracle Cloud Infrastructure)](https://qiita.com/ora_gonsuke777/items/9460b784a2a34240fe80)」でした。明日は tomo さんの記事です。

Oracle Database Gateway for ODBC の機能により、ODBC ドライバーを使って PostgreSQL データベースに接続する DATABASE LINK を作成します。

# Oracle Database Gateway for ODBC
Oracle Database Gateway for ODBC は Oracle Database の標準機能であり、エディションを問わず使用できます。「[データベース・ライセンス情報ユーザー・マニュアル](https://docs.oracle.com/cd/F39414_01/dblic/Licensing-Information.html#GUID-B6113390-9586-46D7-9008-DCC9EDA45AB4)」にライセンス情報が記載されています。この機能は ODBC Driver を使って Oracle Database 以外の RDBMS に対して SQL 文を発行することができます。本記事では Red Hat Enterprise Linux 環境で Oracle Database 19c から PostgreSQL 14 に対して接続する方法について記載しています。マニュアルとして「[Oracle Database Gateway for ODBCユーザーズ・ガイド](https://docs.oracle.com/cd/F39414_01/odbcu/index.html)」が提供されています。


# インストール準備
## PostgreSQL 側
PostgreSQL 側に特別な準備は必要ありません。外部から接続を待つアドレス（listen_addresses) とポート番号（port）を設定し、Oracle Database サーバからの接続許可を pg_hba.conf ファイルに記述します。下記の例では Oracle Database サーバのアドレス 192.168.1.100 から postgres データベースへ接続を許可しています。md5 はパスワードによる認証を行う指定です。

```Shell:
$ cat $PGDATA/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    postgres        all             192.168.1.100/32        md5
```

## Oracle Database サーバー側
Oracle Database サーバーには ODBC 経由で接続するため、Linux の ODBC Manager パッケージ、PostgreSQL ODBC Driver、PostgreSQL Client のインストールを行います。下記の例では ODBC Manager のインストールを確認し PostgreSQL 14 の rpm パッケージをインストールしています。

```Shell:
# 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 Database が稼働する Linux 上の ODBC Manager 設定を確認します。

```Shell:
# 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 Driver をインストールすると、/etc/odbcinst.ini ファイルにドライバー情報が追記されます。必要に応じて修正します。

```Shell:
# 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 データベースの[属性](https://www.enterprisedb.com/docs/odbc_connector/13.1.0.1/05_edb-odbc_connection_properties/)を追記します。任意の名前のセクション名（例では pgdsn）の下に情報を追加します。以下は最低限必要な設定項目です。

|項目|説明|
|:---|:---|
|[セクション名]|任意の名前|
|Driver|ドライバー名 odbcinst.ini ファイルから転記|
|Description|任意の説明|
|Database|接続先 PostgreSQL データベース名|
|Servername|接続先ホスト名または TCP/IP アドレス |
|Port|接続先 PostgreSQL の接続ポート番号|

```Shell:
# 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 を接続名として初期化パラメーターの設定を行っています。

```Shell:
$ 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
```

|項目|説明|
|:---|:---|
|HS_FDS_CONNECT_INFO|接続名称|
|HS_FDS_TRACE_LEVEL|トレース・ファイルに出力するレベル|
|HS_FDS_SHAREABLE_NAME|ODBC Driver Managerへのパス|
|HS_LANGUAGE|言語設定|
|HS_FDS_FETCH_ROWS|フェッチサイズ|
|set ODBCINI|ODBC 設定ファイルへのパス|

# ネットワークの設定
次にリスナーの設定を行います。Oracle Database Gateway は異なるデータベースに接続する際にリスナー経由で dg4odbc プロセスを起動します。リスナー設定ファイル（listener.ora）に、外部接続用の SID_LIST を追加します。環境変数 LD_LIBRARY_PATH の設定とプロセス起動用の PROGRAM=dg4odbc の設定を追加します。

```Shell:
$ 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)
  )
)
```

DATABASE LINK から接続するためのエントリを tnsnames.ora ファイルに追加します。ローカルホストのリスナーに対して接続し HS=OK の設定を追加します。

```Shell:
$ cat tnsnames.ora

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

# DATABASE LINK の作成

PostgreSQLインスタンスを参照する DATABASE LINK を作成します。PostgreSQL データベースのユーザー名とパスワードを指定します。ユーザー名とパスワードは大文字／小文字を意識するためにダブル・クオーテーションで囲んでいます。USING 句には tnsnames.ora ファイルに追加した接続子を指定します。以下の例では DATABASE LINK を作成し、pg_am カタログの定義を参照しています。エラーが出力されずにビューの定義が参照できれば成功です。

```SQL:
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:
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 Driver がテーブルの存在チェックでエラーになったログです。

```
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 ディレクトリに作成されます。

```Shell:
$ 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:
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)。
```