Работа с информацией в базе данных PostgreSQL через базу данных Oracle

Информационная система многих крупных предприятий имеет целый "зоопарк" приложений написанных с использованием различных технологий. У каждого приложения может быть не только своя база данных, но они могут использовать даже различные СУБД. Это ставит вопрос доступа к информации другого приложения. Для его решения Oracle предоставляет общую технологию для подключения из баз данных Oracle к базам данных других СУБД - Heterogeneous Services (HS). Для каждой СУБД, отличной от Oracle, запускается отдельный процесс, с помощью которого СУБД Oracle подключается к ней – Heterogeneous Services Agent (HS Agent). Этот процесс называется "шлюз" (Oracle Database Gateway). Он состоит из двух частей: общий код агента и драйвер для доступа.

Архитектура гетерогенного процесса подключения Oracle к другим СУБД

Среди драйверов для доступа есть несколько драйверов для конкретных СУБД (MS SQL Server, Sybase, Informix, Teradata, DB2) и универсальный драйвер для доступа через ODBC. Шлюз устанавливается и настраивается только на одном компьютере (и это необязательно компьютер с СУБД Oracle), а клиентское программное обеспечение с различных компьютеров подключается к базе данных Oracle через Oracle Net и работает через это подключение с базой данных других СУБД.
Давайте пошагово рассмотрим настройку подключения к базе данных PostgreSQL через Oracle Database Gateway for ODBC (DG4ODBC).

Подключение к БД используя шлюз с драйвером для доступа через ODBC

У меня на тестовой виртуальной машине уже был установлен Oracle Database 12c Release 2. Шлюз для ODBC установился с ней в комплекте.

Устанавливаем любой 64-х битный ODBC-драйвер для PostgreSQL. Например, можно взять официальный драйвер ODBC для PostgreSQL – psqlODBC (на данный момент для загрузки доступна версия 13.02).
Добавляем 64-х битный системный источник данных с драйвером ODBC для PostgreSQL (System DNS).
Имя пользователя и пароль для подключения к базе данных можно не указывать, т.к. его будем указывать позже при создании DATABASE LINK.
На более старых версиях Oracle может потребоваться 32-х битный источник данных, т.к. раньше шлюз был 32-х битным.
Настраиваем Oracle Database Gateway for ODBC. В каталоге $ORACLE_HOME\hs\admin создаем файл инициализации для нашего подключения. Его имя должно иметь формат initSID.ora, где SID – это "системный идентификатор шлюза", который будет идентифицировать экземпляр шлюза. Обязательным параметром для PostgreSQL в файле инициализации является только наименование источника данных ODBC – HS_FDS_CONNECT_INFO. Для Linux, в отличие от Windows, еще обязательно необходимо указать путь к файлу ODBC-драйвера (параметр HS_FDS_SHAREABLE_NAME). Что бы не путать с наименованием источника данных ODBC ("PG_TEST_DB"), назовем системный идентификатор шлюза "PG_DB" (хотя мне нравится одинаковое название для всех элементов схемы подключения). В каталоге c:\oracle\product\12.2.0\dbhome_1\hs\admin\ создаем файл initPG_DB.ora:
```
HS_FDS_CONNECT_INFO = PG_TEST_DB
HS_FDS_TRACE_LEVEL = OFF
```
Настраиваем Oracle Net Listener. В параметр SID_LIST_LISTENER конфигурационного файла listener.ora (если не настроена переменная среды TNS_ADMIN, то файл находится в каталоге $ORACLE_HOME\network\admin) необходимо добавить системный идентификатор шлюза. В файл c:\oracle\product\12.2.0\dbhome_1\network\admin\listener.ora добавляем наш SID "PG_DB":
```
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\product\12.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\oracle\product\12.2.0\dbhome_1\bin\oraclr12.dll")
    )
      (SID_DESC=
         (SID_NAME=PG_DB)
         (ORACLE_HOME=c:\oracle\product\12.2.0\dbhome_1)
         (PROGRAM=dg4odbc)
      )
  )
```
и перезапускаем listener.
Создаем DATABASE LINK. Первый вариант – с настройкой SQL*Net подключения. В конфигурационный файл tnsnames.ora (если не настроена переменная среды TNS_ADMIN, то файл находится в каталоге $ORACLE_HOME\network\admin) необходимо добавить маппирование имени для системного идентификатора шлюза. В файл c:\oracle\product\12.2.0\dbhome_1\network\admin\tnsnames.ora добавляем TNS-псевдоним TNS_PG_DB связанный с PG_DB (префикс "TNS" снова для того, чтобы было нагляднее).
```
TNS_PG_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = PG_DB))
    (HS = OK)
  )
```
Обратите внимание на "(HS = OK)" – это указывает на то, что подключение использует Oracle Heterogeneous Services. Создаем DATABASE LINK выполнив команду с любого компьютера, на котором настроен доступ к базе данных Oracle:
```
create database link LINK_TO_PG
  connect to "pg_user"
  identified by pg_user_password 
  using 'TNS_PG_DB';
```
где LINK_TO_PG – наименование DATABASE LINK'а, pg_user – имя пользователя PostgreSQL имеющего доступ к нужным таблицам, pg_user_password – пароль пользователя PostgreSQL, TNS_PG_DB – TNS-псевдоним указанный в tnsnames.ora компьютера с настроенным Oracle Database Gateway.
Второй вариант проще, так как не требует настройки tnsnames.ora. В команде создания DATABASE LINK'а просто пропишем параметры TNS-псевдонима:
```
create database link LINK_TO_PG
  connect to "pg_user"
  identified by pg_user_password
  using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PG_DB))(HS=OK))';
```
Проверяем работу. В базе данных PostgreSQL от имени pg_user создаем тестовую таблицу.
```
CREATE TABLE users
(
   id         INT           not null,
   fio        VARCHAR(200)  not null,
   login_name VARCHAR(25)   not  null,
   note       VARCHAR(2000) null,
   CONSTRAINT pk_users PRIMARY KEY (id)
);

INSERT INTO users (id, fio, login_name, note) 
VALUES 
  (1, 'Администратор', 'admin', NULL),
  (2, 'Иванов Иван Иванович', 'ivanov', 'Директор'),
  (3, 'Петров Петр Петрович', 'petrov', 'Программист');
```
На компьютере, на котором настроен доступ к базе данных Oracle, выполняем запрос к таблице в базе данных PostgreSQL:
Как вы видите, у нас проблема с кодировкой. Ее причина кроется не в настройках NLS_LANG или других параметров Oracle Database, даже не в PostgreSQL. Как обычно, виновата MS Windows. Для решения проблемы с кодировкой в региональных настройках MS Windows для приложений, которые не используют unicode, необходимо установить русский язык. Заходим в Control Panel => Clock, Language, and Region => Region => Administrative => Language for non-Unicode programs и меняем локаль с "English" на "Russian".
После перезагрузки компьютера выполняем запрос снова.
Обратите внимание, что имя пользователя при создании DATABASE LINK и все идентификаторы в запросе взяты в двойные кавычки. Это связано с тем, что согласно стандарту SQL, идентификаторы по умолчанию должны приводятся к верхнему регистру. Но разработчики PostgreSQL нарушили это правило и приводят их к нижнему. Поэтому, если не взять идентификатор в двойные кавычки, то СУБД преобразует его наименование, и мы получим ошибку "ORA-00904: недопустимый идентификатор".

Как вы видите, организация доступа к информации в базе данных PostgreSQL (как и в любой другой СУБД) через Oracle Database Gateway требует минимальных настроек. При этом разработчики получают в свои руки мощный инструмент, который берет на себя не только организацию выполнения простых SQL-запросов на получение данных из других баз, но и возможность выполнения гетерогенных запросов (когда таблицы запроса находятся в разных базах данных), а также позволяет модифицировать данные с поддержкой механизма транзакций.

Продолжение:

Установка и настройка Oracle Database Gateway for ODBC (DG4ODBC) для доступа к БД PostgreSQL на компьютере, на котором не установлена СУБД Oracle

ИТ-записки Чорнага кашака

08 ноября 2022