На Хабре не так много информации об интеграции между различными СУБД, и я решил поделиться своим опытом построения информационной системы на базе Oracle, которая взаимодействует с PostgreSQL (PG). Статья состоит их двух частей: в первой описана конфигурация СУБД, а во второй описан кейс по получению и обработке данных. Надеюсь, этот материал сэкономит Вам время при решении подобных задач.

Постановка задачи

Есть два сервиса, которые взаимодействуют с одной базой на PostgreSQL, они пишут в нее информацию в режиме реального времени. И есть отдельная подсистема, реализованная в хранимых процедурах Oracle, которая должна забирать и обрабатывать эти данные. С точки зрения архитектуры целесообразно выполнить интеграцию на уровне СУБД. Для этого можно использовать Heterogeneous service (HS) — это компонент сервера Oracle, который позволяет получать доступ к данным, хранящимся в системах, отличных от Oracle [1]. Начиная с версии 11g, в состав Oracle включен инструмент DG4ODBC (Database Gateway for ODBC), который выступает посредником: принимает запрос от приложения в Oracle, через HS и ODBC‑драйвер передаёт его в целевую СУБД.

Рис.1. Схема работы гетерогенного сервиса

Тестовый стенд

Сервер с Oracle:

ОС Oracle Linux Server 6.10 (RHEL);
Oracle EE 12.1.0.2;
кодировка базы NLS_CHARACTERSET = CL8ISO8859P5;
unixODBC 2.2.14;

Сервер с PG:

ОС Alt‑Linux Server 10.2 (Mandrake);
PostgresPro Standard 17.4;
кодировка базы ENCODING = UTF8, DATCOLLATE = en_US.UTF-8, datctype = en_US.UTF-8

Настройки на стороне сервера Oracle

Настройка odbc‑драйвера

Первым делом обновляем версию odbc‑драйвера Linux:

yum install unixODBC unixODBC-devel freetds –y

На мою не самую свежую ОС на тот момент нашелся драйвер версии 12:

rpm –iv postgresql12-libs-12.0-1PGDG.rhel6.x86_64.rpm rpm –iv postgresql12-odbc-12.01.0000-1PGDG.rhel6.x86_64.rpm

На версиях старее 12 при любых запросах в удаленную БД возникает ошибка, которую можно увидеть в логе /var/lib/pgpro/std-17/data/log:

UTC > ERROR: column c.relhasoids does not exist at character 245

Это прямой маркер проблемы совместимости. В PostgreSQL 12 изменили системные каталоги (в частности, убрали столбец relhasoids из pg_catalog.pg_class) [4]. Старые версии ODBC‑драйверов содержат жёстко закодированные запросы, которые пытаются этот столбец найти. Когда драйвер сталкивается с его отсутствием, он генерирует ошибку. Рекомендуется ставить самую свежую версию драйверов, доступную для вашей ОС.

Далее настраиваем конфиг /etc/odbcinst.ini

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/pgsql-12/lib/psqlodbcw.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

Затем конфиг /etc/odbc.ini:

[PG] Driver = PostgreSQL – ссылка на предыдущий конфиг
Servername = 192.168.xxx.xxx
Port = 5432
Database = pgnew
SSLmode = disable
Username = orauser
Password = linkpass
VarMaxAsLong = Yes
[Default]
Driver = /usr/lib64/liboplodbcS.so

Проверяем sql‑соединение через утилиту:

isql -v pg orauser linkpass (логин/пароль не обязательно)

На стороне БД PG можно отследить активный сеанс следующим запросом:

SELECT 
*
FROM pg_stat_activity
order by state_change desc;

Соединение установлено.

Настройка гетерогенного сервиса

В директории $ORACLE_HOME/hs/admin создаем файл init<sid>.ora, где <sid> — имя DSN для ODBC с учётом регистра, в нашем случае initPG.ora, со следующим содержанием:

HS_FDS_CONNECT_INFO = PG – должно совпадать с [PG] в конфиге /etc/odbc.ini HS_FDS_SHAREABLE_NAME = /usr/pgsql-12/lib/psqlodbcw.so – драйвер PG HS_LANGUAGE=AMERICAN_AMERICA.UTF8 – кодировка, соответствующая кодировке удаленной базы PG HS_NLS_NCHAR=UCS2 – используется для преобразования символов
*два последний параметра подбираются индивидуально.

Если при попытке выполнить запрос в удаленную БД вы получаете ошибку:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[

значит у вас установлены неверные значения для HS_ LANGUAGE и HS_NLS_NCHAR. Эта ошибка возникает, когда набор символов базы данных Oracle (значение параметра NLS_CHARACTERSET) имеет значение AL32UTF8 [2]. Чтобы узнать, какой набор символов используется в базе данных Oracle, выполните запрос:

SELECT parameter, value
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';

Посмотреть кодировку базы на PG можно так:

SELECT 
"datname",
pg_encoding_to_char("encoding"),
datcollate,
datctype
FROM "pg_database";

Настройка листнера listener.ora в $ORACLE_HOME/network/admin. Добавляем настройки для обработки входящих запросов на подключение:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PG)
(PROGRAM = dg4odbc)
(ORACLE_HOME = /opt/oracle/product/12.1.0/dbhome_1)
)
)

После перезапускаем его:

lsnrctl reload
или
lsnrctl stop
lsnrctl start

Настройка tnsnames.ora в $ORACLE_HOME/network/admin:

PG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.xxx.xxx)(PORT = 1521))(CONNECT_DATA = (SID = PG))(HS = OK))

Логи HS‑сервиса можно найти в $ORACLE_HOME/hs/log.

На стороне сервера PostgreSQL

В удаленной базе создаем пользователя, которым будем стучаться к ней по линку:

CREATE USER orauser PASSWORD 'linkpass';
SET password_encryption  = 'md5';

Выдаем сетевой доступ для сервера Oracle в конфиге /var/lib/pgpro/std-17/data/pg_hba.conf:

host pgnew orauser 192.168.xxx.xxx/32 md5
или
host all all 192.168.xxx.xxx/24 md5

где 192.168.xxx.xxx — ip адрес сервера Oracle.

Создание линка в мастер‑системе

CREATE DATABASE LINK PG
  CONNECT TO "orauser" IDENTIFIED BY "linkpass"
  USING 'PG';

Таким образом, мы получили возможность читать и обрабатывать данные из удаленной БД через линк.

SELECT "cname" FROM  "clients"@PG where "client_id" = 128;
...
UPDATE "payments"@pg SET "state" = 3 WHERE "oper_id" = 299;

Решение прикладной задачи

Более детально задачей сервиса является получение данных из базы PG, обработка на своей стороне и изменение статуса в удаленной базе. При параллельной обработке большого объема данных целесообразно захватывать строки порциями и обновлять их статус после обработки также пачками, например так:

BEGIN
  -- чтение пачки с блокировкой свободных строк
  SELECT ...
  BULK COLLECT INTO tRows
  FROM data_tbl
  WHERE status = 1
  AND ROWNUM <= 100
  FOR UPDATE SKIP LOCKED;
  
  -- обработка
  PROCESS_DATA(tRows);
  
  -- изменение статуса
  FORALL i IN 1..tRows.count
   UPDATE data_tbl d SET status = 2 WHERE d.id = tRows(i).id;
END;

Но при работе с ODBC‑драйвером необходимо помнить о его ограничениях. Это касается поддержки конструкций типа SELECT ... BULK COLLECT INTO … FOR UPDATE SKIP LOCKED.

Для запуска подобного рода команд существует виртуальный пакет DBMS_HS_PASSTHROUGH. Он позволяет выполнить команду, как будто она выполняется на удаленном сервере. Благодаря нему успешно выполняются такие несложные конструкции как SELECT … FOR UPDATE для одной записи, INSERT INTO ... VALUES... ON CONFLICT DO UPDATE SET ... и некоторые другие.

Также в драйвере есть проблемы с длинными текстовыми полями (более 255 символов) и нестандартными типами данных, которые будет необходимо преобразовывать вручную [3]. Но главным минусом на мой взгляд оказалось то, что шлюз dg4odbc не поддерживает двухфазную фиксацию транзакций (Two‑Phase Commit, 2PC). Если вы попытаетесь провести изменения данных в обоих базах в рамках одной транзакции, то получите ошибку:

ORA-02047: cannot join the distributed transaction in progress

В этом случае вам придется отдельно фиксировать изменения в каждой базе и проверять успешное завершение операций. Поддержка savepoint‑ов также отсутствует. Вы сами отвечаете за атомарность данных. Одним из вариантов решения подобных задач является применение автономных транзакций (PRAGMA AUTONOMOUS_TRANSACTION) в Oracle. Алгоритм работы будет выглядеть следующим образом:

1) загружаем данные во временное хранилище Oracle (в автономной транзакции):

INSERT INTO load_data_temp (...)
(SELECT ... FROM "data_tbl"@dblink WHERE "state" = 1);

2) в цикле идем по загруженным записям и пробуем захватить их с проверкой текущего статуса:

  FOR rROW IN (SELECT * FROM load_data_temp) LOOP
    
         cSQL :=    ' SELECT "id" '||
                ' FROM "data_tbl" p' ||
                ' WHERE "id" = ? ' ||
                ' AND "state" = ? ' ||
                ' FOR UPDATE SKIP LOCKED '
                ;
     cSQL := '
        declare
          v_cursor BINARY_INTEGER;
          nr INTEGER; 
        begin
          v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@dblink;
          DBMS_HS_PASSTHROUGH.PARSE@dblink(v_cursor, '''|| cSQL ||''');
          DBMS_HS_PASSTHROUGH.BIND_VARIABLE@dblink(v_cursor, 1, '''||to_char(p_doc_id)||''');
          DBMS_HS_PASSTHROUGH.BIND_VARIABLE@dblink(v_cursor, 2, '''||to_char(1)||''');
          nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@dblink(v_cursor);
          DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@dblink(v_cursor);
          :rn_bl := nr;
        end;';
          
    EXECUTE IMMEDIATE cSQL USING OUT iret;
…
  END LOOP;

3) если удалось заблочить очередную запись, запускаем ее обработку в автономной транзакции:

PROCESS_DATA(tRows);

4) если обработка на шаге 3 прошла успешно, меняем статус удаленной записи:

     cSQL := 'UPDATE "data_tbl" SET "state" = ? WHERE "oper_id" = ? ' ;
     cSQL := '
        declare
          v_cursor BINARY_INTEGER;
          nr INTEGER; 
        begin
          v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@dblink;
          DBMS_HS_PASSTHROUGH.PARSE@dblink(v_cursor, '''|| cSQL ||''');
          DBMS_HS_PASSTHROUGH.BIND_VARIABLE@dblink(v_cursor, 1, '''||to_char(p_state)||''');
          DBMS_HS_PASSTHROUGH.BIND_VARIABLE@dblink(v_cursor, 2, '''||to_char(p_doc_id)||''');
          nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@dblink(v_cursor);
          DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@dblink(v_cursor);
          :rn := nr;
        end;';
EXECUTE IMMEDIATE cSQL USING OUT iret;

5) фиксируем текущую транзакцию с изменениями в удаленной базе на каждой итерации.

Разумеется, в случае ошибки на шаге 4,5 мы получим рассогласование данных на обоих базах, ибо все изменения в нашей базе уже зафиксированы в автономных транзакциях. Необходимо производить процесс отката этих изменений, либо специальную обработку сбойных строк.

Выводы

Технология работы с удаленной БД через гетерогенный сервис Oracle подходит для чтения, несложной обработки и агрегирования данных из разных СУБД. Для распределенных транзакций, то есть для изменения данных одновременно в нескольких БД там, где требуется 2PC, сервис не подходит, лучше использовать другие инструменты с координаторами.

Источники

Интеграция Oracle с PostgreSQL через гетерогенный сервис