Краткая инструкция по настройке ДБ-линка из базы Oracle к PostgreSQL
Описание серверов:
- Oracle DB. Медстатистика: 10.2.XX.XX srv-ms-ora01
- PostgreSQL DB. Больничные: 10.1.XX.XX srv-elnp1-pg01
Для создания линка необходимо открыть доступ между серверами по порту 5432
Настройка PostgreSQL DB
- Создайте пользователя
create user dblinkuser encrypted password 'пароль' CONNECTION LIMIT 50;
GRANT CONNECT ON DATABASE eln TO dblinkuser;
\c eln
grant usage on schema eln to dblinkuser;
GRANT SELECT ON ALL TABLES IN SCHEMA eln TO dblinkuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA eln GRANT SELECT ON TABLES TO dblinkuser;
GRANT USAGE ON SCHEMA public TO dblinkuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dblinkuser;
- Проверка
psql -U dblinkuser -d eln
select * from eln.df;
Должны получить данные
update eln.df set moid=200001003712 where id=1;
Должны получить ошибку: must be: ERROR: permission denied for relation df
- pg_hba
Добавьте в pg_hba.conf строку
host eln dblinkuser 10.2.XX.XX/32 md5
Примените
pg_ctl reload -D xxxxxx
Установка ODBC драйвера
Устанавливаем на хост с базой Oracle. Есть несколько вариантов установки. У нас нет пакетов, поэтому в нашем случае:
- Вариант 1. Из исходников
Скачайте с : https://odbc.postgresql.org/
и по инструкции :
./configure
make
make install
- Вариант 2. С помощью дистрибутива от EDB Postgres
В опциях снимите установку базы, и включите установку ODBC
- Проверка после установки
odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/unixODBC/odbcinst.ini
SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
FILE DATA SOURCES..: /etc/unixODBC/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
Создание подключения, настройки HS Oracle
-
odbc.ini
[ODBC Data Sources]
PG_LINK = PostgreSQL
[PG_LINK]
Debug = 1
CommLog = 1
ReadOnly = yes
Driver = /opt/PostgreSQL/psqlODBC/lib/psqlodbcw.so
Servername = 10.1.XX.XX
FetchBufferSize = 99
Username = dblinkuser
Password = пароль
Port = 5432
Database = eln
[Default]
Driver = /usr/lib64/unixODBC/liboplodbcS.so
Создайте копии
cd /root/
chmod a+rw .odbc.ini
ln .odbc.ini odbc.ini
ln .odbc.ini /etc/odbc.ini
ln .odbc.ini /home/oracle/odbc.ini
ln .odbc.ini /home/oracle/.odbc.ini
ln .odbc.ini /etc/unixODBC/odbc.ini
Проверки
- Получить список подключенных драйверов (тех, для которых созданы записи в odbcinst.ini):
odbcinst -q -d
- Получить список подключенных источников данных (DSN):
odbcinst -q -s
- Полную информацию можно получить:
cat /etc/unixODBC/odbcinst.ini
- Запрос данных из PostgreSql
Выполняем под пользователем root и oracle
isql -v PG_LINK
select * from eln.df;
Должны получить данные из таблиц БД PostgreSql
Настройка Oracle
- Создаем config
cd /u01/oracle/app/product/12.1.0/dbhome_1/hs/admin/
vi initPG_LINK.ora
HS_FDS_CONNECT_INFO=PG_LINK
HS_FDS_TRACE_LEVEL=255
HS_FDS_SHAREABLE_NAME=/opt/PostgreSQL/psqlODBC/lib/psqlodbcw.so
set ODBCINI=/home/oracle/.odbc.ini
set ODBCINSTINI=/etc/unixODBC/odbcinst.ini
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR = UCS2
- tnsnames.ora
cd /u01/oracle/app/product/12.1.0/dbhome_1/network/admin
vi tnsnames.ora
PG_LINK = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523)) (CONNECT_DATA=(SID=PG_LINK)) (HS=OK) )
- listener.ora
cd /u01/oracle/app/product/12.1.0/dbhome_1/network/admin
vi listener.ora
LISTENER_dg4odbc =
(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))))SID_LIST_LISTENER_dg4odbc =(SID_LIST =(SID_DESC =(SID_NAME=PG_LINK)(ORACLE_HOME=/u01/oracle/app/product/12.1.0/dbhome_1)(ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/usr/lib:/u01/oracle/product/11.2.0.4/lib:/u01/oracle/app/product/12.1.0/dbhome_1/dg4msql/driver/lib:/u01/oracle/app/product/12.1.0/dbhome_1/lib")(PROGRAM=dg4odbc)))
lsnrctl stop LISTENER_dg4odbc
lsnrctl start LISTENER_dg4odbc
lsnrctl status LISTENER_dg4odbc
- Проверка
tnsping pg_link
- database link
Создаем database link:
create public database link PG_LINK connect to "dblinkuser" identified by "пароль" using 'PG_LINK';
Проверка:
Возможные ошибки
- ORA-28545
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from PG_ELN_LINK
Tue Jun 04 14:58:02 2019
HS: Unable to establish RPC connection to HS Agent...
HS: ... Agent SID = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv-ms-ora01)(PORT=1521))(CONNECT_DATA=(SID=PG))),
NCR error = 65535 Unable to retrieve text of NETWORK/NCR message 65535
Решение - создать отдельный listener
- ORA-28546
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523))(CONNECT_DATA=(SID=PG_LINK)))
ORA-02063: preceding 2 lines from PG_LINK
Решение - добавьте переменные окружения:
(ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/usr/lib:/u01/oracle/product/11.2.0.4/lib:/u01/oracle/app/product/12.1.0/dbhome_1/dg4msql/driver/lib:/u01/oracle/app/product/12.1.0/dbhome_1/lib")
- ORA-28500
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
c
Решение - Добавьте параметр
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
- Русские буквы испорчены
Решение - Решаем согласно ноте:
Select from PostgreSQL Using DG4ODBC Gives Error ORA-28500 : Error Invalid Byte Sequence For Encoding UTF8 [ID 1369633.1]
HS_NLS_NCHAR = UCS2
