На Хабре не так много информации об интеграции между различными СУБД, и я решил поделиться своим опытом построения информационной системы на базе Oracle, которая взаимодействует с PostgreSQL (PG). Статья состоит их двух частей: в первой описана конфигурация СУБД, а во второй описан кейс по получению и обработке данных. Надеюсь, этот материал сэкономит Вам время при решении подобных задач.
Постановка задачи
Есть два сервиса, которые взаимодействуют с одной базой на PostgreSQL, они пишут в нее информацию в режиме реального времени. И есть отдельная подсистема, реализованная в хранимых процедурах Oracle, которая должна забирать и обрабатывать эти данные. С точки зрения архитектуры целесообразно выполнить интеграцию на уровне СУБД. Для этого можно использовать Heterogeneous service (HS) — это компонент сервера Oracle, который позволяет получать доступ к данным, хранящимся в системах, отличных от Oracle [1]. Начиная с версии 11g, в состав Oracle включен инструмент DG4ODBC (Database Gateway for ODBC), который выступает посредником: принимает запрос от приложения в Oracle, через HS и ODBC‑драйвер передаёт его в целевую СУБД.
Тестовый стенд
Сервер с 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 PostgreSQLDriver = /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 = 5432Database = pgnewSSLmode = disableUsername = orauserPassword = linkpassVarMaxAsLong = 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 stoplsnrctl 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, сервис не подходит, лучше использовать другие инструменты с координаторами.
