LoginSignup
3
7
@plusultra(Noriyoshi Shinoda)

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

Last updated at Posted at 2021-12-15

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

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

Oracle Database Gateway for ODBC

Oracle Database Gateway for ODBC は Oracle Database の標準機能であり、エディションを問わず使用できます。「データベース・ライセンス情報ユーザー・マニュアル」にライセンス情報が記載されています。この機能は ODBC Driver を使って Oracle Database 以外の RDBMS に対して SQL 文を発行することができます。本記事では Red Hat Enterprise Linux 環境で Oracle Database 19c から PostgreSQL 14 に対して接続する方法について記載しています。マニュアルとして「Oracle Database Gateway for ODBCユーザーズ・ガイド」が提供されています。

インストール準備

PostgreSQL 側

PostgreSQL 側に特別な準備は必要ありません。外部から接続を待つアドレス（listen_addresses) とポート番号（port）を設定し、Oracle Database サーバからの接続許可を pg_hba.conf ファイルに記述します。下記の例では Oracle Database サーバのアドレス 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 Database サーバー側

Oracle Database サーバーには ODBC 経由で接続するため、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 Database が稼働する 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 Driver をインストールすると、/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）の下に情報を追加します。以下は最低限必要な設定項目です。

項目 説明
[セクション名] 任意の名前
Driver ドライバー名 odbcinst.ini ファイルから転記
Description 任意の説明
Database 接続先 PostgreSQL データベース名
Servername 接続先ホスト名または TCP/IP アドレス
Port 接続先 PostgreSQL の接続ポート番号 
# 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
項目 説明
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 の設定を追加します。

$ 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 の設定を追加します。

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

$ 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)。
3
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
7