Не работает блинк из oracle в postgresql
Во время миграции из Oracle в PostgreSQL с помощью ora2pg встал вопрос с несоответствием типов данных между разными базами. По умолчанию не все колонки конвертируется правильно, а отсутствие в Oracle Boolean и вовсе порождает неоднозначность – часть колонок нужно перенести как числа, часть как логические значения. В тоже время hibernate знает все о типах данных и может создать эталонную схему.
Итоговый процесс переноса выглядел следующим образом: создание структуры таблиц через ora2pg, исправление структуры по эталонной схеме, перенос данных, конвертация blob и Boolean, добавление отсутствующих в PostgreSQL функций (nvl, nvl2, regexp_substr), создания оставшейся структуры — индексов, view и прочего.
Под катом накопившиеся за время конвертации sql скрипты для полуавтоматической миграции.
Добавление отсутствующих в PostgreSQL функций
Чтобы не было необходимости в переписывании кода, просто создадим, отсутствующие в PostgreSQL, но которые есть в Oracle и используются в проекте.
nvl(timestamp with time zone, timestamp with time zone)
nvl2(date, date, date)
nvl2(integer, integer, integer)
nvl2(numeric, numeric, numeric)
nvl2(text, text, text)
nvl(timestamp with time zone, timestamp with time zone)
nvl2(timestamp, timestamp, timestamp)
nvl2(varchar, varchar, varchar)
trunc(timestamp with time zone, varchar)
Answers
in Oracle 11 the executable for the gateway configured in the listener.ora is called dg4odbc, not hsodbc. So please correct:
(PROGRAM = hsodbc) to (PROGRAM = dg4odbc)
Once done, please stop and start the listener LISTENER_POSTGRES
Thank you sooo much KGRONAU..i am waiting for you only..i read many of your earlier replies. in fact i have already changed that .and no issues with listener.
But isql is not working. please help me to make the ISQL working.
[[email protected] psqlodbc-09.02.0100]$ isql postgresql
[ISQL]ERROR: Could not SQLConnect
new listener file::
First of all the listener is still not correct, just use (PROGRAM=dg4odbc) and stop and start the listener. The listener will then automatically check out the $ORACLE_HOME/bin directory and load the dg4odbc executable.
Regarding isql, please provide the env you have set before starting isql and then use isql -v so that we get more details:
2. type: isql -v [postgresql
Provide the output of each command.
Thank you sooooo much again sir. Changed the listener as below and it is working
(ADDRESS = (PROTOCOL = TCP)(HOST = scxxxx001)(PORT = 1525))
(ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
i did not set up any ENV, except some thing in LISTENER file, could you please review the below and suggest, what I should do. eagerly waiting for your reply..many thanks.
SSH_CLIENT=172.17.200.79 60780 22
SSH_CONNECTION=172.17.200.79 60780 172.17.1.80 22
[[email protected] admin]$ isql -v postgresql
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
The error message is clear - isql can't find the ODBC DSN and the root cause is because you didn't set the ODBCINI environment variable.
isql needs to know the ODBC details,like the ODBC parameter ODBCINI which contains your ODBC DSN.
So before calling isql set the env:
SIDE NOTE: Please crosscheck that the patches I specified above match your setting.
Now call again isql: isql -v postgresql
when you can connect with isql successfully you can check out DG4ODBC.
First I would check the word size of the ODBC Driver, the Driver manager and DG4ODBC. They all should match. Use these commands:
When they match, then correct again the listener.ora, this time the LD_LIBRARY_PATH to:
Then stop and start the listener and give DG4ODBC a try
many many thanks Kgronau for your reply. I did all the steps as you suggested, some where I am missing. Could you PLEASE help me to solve the problem.
Error that I am getting as below:
ERROR at line 1:
ORA-02019: connection description for remote database not found
Please see the outputs of the file commands :
/usr/lib/libodbc.so: symbolic link to `libodbc.so.1.0.0'
/usr/lib/libodbc.so.1.0.0: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), stripped
[[email protected] lib]$ file /home/oracle/temp/psqlodbc-09.02.0100/.libs/psqlodbcw.so
/home/oracle/temp/psqlodbc-09.02.0100/.libs/psqlodbcw.so: ELF 64-bit LSB shared object, AMD x86-64, version 1 (SYSV), not stripped
[[email protected] lib]$ file /oracle/database/11.2.0.3/bin/dg4odbc
/oracle/database/11.2.0.3/bin/dg4odbc: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), not stripped
Updated the listener as suggested:
(ADDRESS = (PROTOCOL = TCP)(HOST = scflsdb001)(PORT = 1525))
(ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
I am little bit doubtful about my TNS file also, could you please check this also:
(ADDRESS = (PROTOCOL = TCP)(HOST = postgresDEV.office.corp)(PORT = 5432))
There are 2 issues. First the error message you get:
ORA-02019: connection description for remote database not found just means that you did not create a database link called postgresql in the Oracle database. The syntax would be:
create database link postgresql connect to "" identified by " using 'postgresql';
But even when you have created the database link there is a major issue with your libs.
Thank you so much Mr.Kgronau for your reply.
1. I have created a DB Link earlier but used a different name in the query, but now I used the right one, and I have got the below error. Could you please let me know, if my TNS Names.ora is not correct.
SQL> select *from "temp"@pg_link;
select *from "temp"@pg_link
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
Is it enough if I just chose a driver which is 64bit, or should I have to make sure any other issues.
Many Many Many Thanks. I wish I would have knowledge like one day.
I tried everything (including the Answered Questions) and nothing seems to work.
- /u01/oracle/product/11.2.0.4/hs/admin/initPG_LINK.ora
- Tnsnames and listener are fine. I can reach the Oracle database with no issues.
I tried several libraries and none of the worked so far. Different errors for all of them (including the "SQLAllocHandle" error)
Answers
What "answered questions"?
But your problem is in connecting to a non-oracle database, correct? So the fact that you can connect to an oracle database is no more meaningful than the fact that I drive a Honda.
- .
- .
- hgopoer, line 231: got native error 0 and sqlstate IM002; message follows.
- [unixODBC][Driver Manager]Data source name not found, and no default driver specified
- Exiting hgopoer, rc=0 at 2015/06/04-13:53:18
- hgocont, line 2831: calling SqlDriverConnect got sqlstate IM002
- Exiting hgocont, rc=28500 at 2015/06/04-13:53:18 with error ptr FILE:hgocont.c LINE:2851 FUNCTION:hgocont() ID:Something other than invalid authorization
- Exiting hgolgon, rc=28500 at 2015/06/04-13:53:18 with error ptr FILE:hgolgon.c LINE:806 FUNCTION:hgolgon() ID:Calling hgocont
- Entered hgoexit at 2015/06/04-13:53:18
- Exiting hgoexit, rc=0
[unixODBC][Driver Manager]Data source name not found,
Strongly suggests a mis-match between the definition of your db_link, and the tnsnames.ora file, both of which you chose not to share.
Wow! Thanks for the ultrafast response!
If I try to connect with a user, I get this error
You still haven't given us the "missing link". The ddl defining the db link 'pg_link'.
And why are your net config files in /etc instead of their default location at $ORACLE_HOME/network/admin?
Here you have the ddl statement to create it. I created two (public and owned by sys):
Well, I handle many different oracle versions on other servers and, I use s common location for both Listener and Tnsnames.
As long as $TNS_ADMIN is properly pointing there, nothing should be wrong.
Do you see any problem for this setup on that?
- create public database link PG_LINK connect to "pguser" identified by "pgpass" using 'PG_LINK';
- create database link PG_LINK connect to "pguser" identified by "pgpass" using 'PG_LINK';
Ok, now we have the complete trail of connections from your sql statement, through the link, to the listener, to the HS inti file, to the ODBC definition. And with that, I come back to the original error message:
[unixODBC][Driver Manager]Data source name not found, and no default driver
Note that the error message is being reported by ODBC. That means your connection request got to the odbc part of the connection. I've never configured odbc on a *nix machine, so am not sure about the details, but at this point we at least know where to start looking. And with that I fall back on my standard advice: Believe the error message. Work the error message. Google is your friend.
I'm trying to create a dblink from a database Oracle (10.2.0.3) to PostgreSQL database (8.3.11) with DG4ODBC (11.2.0.1.0).
I've installed the postgres driver 64 bit :
I'm configuring the /oracle/.odbc.ini : I'm configuring the /oracle/product/DG4ODBC/hs/admin/initrecord.ora like this : I'm configuring the /oracle/product/DG4ODBC/network/admin/listener.ora I'm updating my /oracle/product/10.2.0/network/admin/tnsnames.ora : In last i'm creating the dblink : When i'm trying to select my postgres database, that doesn't work. Here the /oracle/product/DG4ODBC/hs/log/record.log : What's wrong with my configuration?
thanks for your help
Текстовые поля
В Oracle нет неограниченного текстового поля, вместо этого используется Lob. В PostgreSQL есть специальный тип — text. Чтобы можно было использовать обе базы аннотации текстовых полей должны быть следующими
Для Postgres также используем самописный диалект, знающий о добавленных функциях
Переносим данные
Перед переносом данных проверяем, что не совпадения типов остались только в колонках boolean и bytea(oid), а все колонки с численными значениями имеют правильную точность и не являются ‘double precision’ во избежание случайного округления.
По умолчанию процесс копирования идет в один поток. Меняем в конфигурации на нужное число
Запускаем процесс копирования данных
Best Answer
According to your configuration the unixODBC Driver Manager library was installed into /usr/lib64 - see the location of the libodbc.so file. I assume you've used a package to install the driver manager, so check out the package details where it might have placed all other required libs/source files.
Обрабатываем boolean
Для них требуется удалить если есть значение по умолчанию, поменять тип и вернуть значение по умолчанию
Best Answer
Ed, I was able to figure it out and I actually could find this solution anywhere else (thanks to Bogdan, a friend who recommended to check this)
The library in initPG_LINK.ora was wrong and I dind´t had a simple way to find the correct one so:
- I setup the variable on my environment (I have created a global file for odbc in there)
- I tried to connect to the remote PostgreSQL database with isql and it worked so, the library was present.
- Then, I checked on the isql libraries being called
- I added that line (/usr/local/lob/lobodbc.so.2) into the HS file.
- Then, I added the ENVS parameter with LD_LIBRARY_PATH to the library location
An finally it worked.
Sysdate
3 ответа
Если у вас нет операций удаления в ваших таблицах и таблицы не очень большие, я предлагаю использовать Oracle System Change Number (SCN) на уровне строки, который возвращается псевдостолбцом ORA_ROWSCN (ссылка). Это время фиксации, представленное числом. По умолчанию SCN отслеживается для блока данных, но вы можете включить отслеживание на уровне строки (ключевое слово rowdependencies ). Поэтому вам нужно воссоздать свою таблицу с этим ключевым словом. При запуске процедуры синхронизации вы получаете текущий scn вызовом функции dbms_flashback.get_system_change_number , затем просматриваете все таблицы where ora_rowscn between _last_scn_value_ and _current_scn_value_ . Недостатком является то, что эти псевдостолбцы не индексируются, поэтому вы будете выполнять полное сканирование таблицы, что для больших таблиц является медленным.
Если вы используете операторы удаления, вы должны отслеживать записи, которые были удалены. Для этой цели вы можете использовать одну таблицу журнала со следующими столбцами: table_name, table_id_value, operation (insert / update / delete). Таблица заполняется триггером по базовым таблицам. Итак, для вашего случая, когда сеанс 1 фиксирует данные в базовой таблице, тогда у вас есть запись в таблице журнала для обработки. И вы не увидите этого, пока сеанс не завершится. Так что никаких проблем с порядковыми номерами, которые вы описали.
Надеюсь, это поможет.
Это чисто информационный проект или у вас тут есть клиент? Если у вас есть средний уровень, вы можете использовать ORM, чтобы абстрагироваться от этого и выполнять запись в оба. Вас волнует, совпадают ли последовательности? Можно было бы сделать что-то вроде сбора всех данных для синхронизации с определенной отметки времени (каждая таблица должна иметь отметку времени в формате UTC), а затем взять хэш всех данных и сравнить с тем, что есть в Postgres.
Было бы полезно узнать о некоторых дополнительных требованиях к синхронизации данных и объяснениях, стоящих за этим, например:
Должны ли ключи быть одинаковыми для обеих сред? Почему? Кто просматривает данные, тот же потребитель, смотрящий на оба источника. Почему бы вам просто не использовать ORM для таргетинга только на один db, зачем вам oracle и postgres?
Я видел похожую установку. Приложение на Postgres в основном для отчетности и других второстепенных задач, в то время как основное приложение было на Oracle.
Некоторые из основных таблиц приложений кэшируются в Postgres для удобства. Но такая настройка вызывает проблему с синхронизацией.
Компромиссным решением было сочетание пошаговой синхронизации на основе последовательности в дневное время и полного копирования таблицы за ночь.
Что касается других предлагаемых здесь решений:
Postgres fdw работает медленно для сложных запросов и создает дополнительную нагрузку на внешнюю базу данных, особенно когда предложение where относится как к локальным, так и к сторонним таблицам.
Тот же запрос будет выполняться намного быстрее, если сторонняя таблица кэшируется в postgres.
Инкрементальная / дифференциальная синхронизация с использованием порядковых номеров - попробовала это и работает приемлемо для небольших таблиц, но кошмар начинается с дочерних отношений, возможно, здесь может помочь орм
На мой взгляд, идеальным решением была бы потоковая передача изменений Oracle в Postgres или промежуточный процесс, который реплицирует изменения в Postgres.
Я понятия не имею, как это сделать, поскольку я понял, что для этого требуется приложение Oracle Golden Gate (+ лицензия)
Подготовка
В качестве утилиты для конвертации данных использовалась ora2pg. Процесс использования очень хорошо описан в статье.
Создаем проект в ora2pg, настраиваем проект, и генерируем схему.
Создаем в PostgreSQL схему 'ora_schema' и таблицы по файлу './schema/tables/table.sql'
Переключаем Hibernate в режим create и создаем еще одну эталонную схему 'hb_schema'. В случае использования view в проекте, число таблиц в разных схемах не сойдется. Hibernate сгенерирует полноценные таблицы вместо view и это следует учитывать.
Best Answer
Transformers.ALIAS_TO_ENTITY_MAP
Если используется Transformers.ALIAS_TO_ENTITY_MAP то надо обязательно указывать возвращаемые типы. Oracle по умолчанию приведет ключи к верхнему регистру, PotgresSql к нижнему и несовпадение ключей исправляется только вручную.
Sequence
Oracle и Postgress имеют разный синтаксис nextval от Sequence.
Oracle
Приводим к общему виду. Для этого создаем функции в Postgres и Oracle и везде переписываем на использование этой функции.
Oracle
Использовать напрямую функцию Postgres nextval('my_seq') не получиться, так как хотя Oracle и позволит создать данную функцию, но не позволит выбирать значения.
substring
Обрабатываем oid(bytea)
Создаем процедуру для конвертации bytea в oid
Создаем временную колонку
Удаляем старую колонку
Переименовываем временную колонку
Временные таблицы
В PostgreSQL в отличии от Oracle, временные таблицы создаются каждый раз и живут в рамках сессии. В Oracle только содержимое временных таблиц живет в каких-либо рамках, а сами таблицы созданы постоянно.
Из этого проистекают следующие проблемы:
1) При создании каждой сессии придется создавать временную таблицу. (данный момент можно упростить используя возможность сервера приложений выполнять инициализационный sql-блок при создании новой сессии (connection pool → advanced → Init SQL)
2) Валидация сущностей в hibernate производится в рамках настроенной схемы (если не указать схему, валидация будет производиться по всем доступным схемам). Т.к. в Postgre временные таблицы создаются в отдельных схемах, валидация провалится.
(Можно обойти создав реальную таблицу с такой же структурой. При нативных запросах работа будет производиться с временной таблицей, а не с обычной).
3) Работать с такой временной таблицей возможно только через нативные запросы, т.к. Hibernate во все генерируемые запросы добавляет название схемы (либо надо всю работу со временными таблицами вести через нативные запросы, либо отказываться от временных таблиц и использовать обычную с разграничением данных по уникальному ключу. Оба способа подразумевают переписывание функционала).
Чтобы продолжить работать с временными таблицами в Postgress, как в Oracle воспользуемся табличными функциями и созданием view на основе табличной функции. Подробности здесь
This is not working, could you please let me know, where I should make the change. Thank you so much for all your help in Advance. Guru's, please respond on this.
Answers
Mathieu,
The error causing the problem is this -
Failed to load ODBC library symbol: /usr/lib64/psqlodbc.so(SQLAllocHandle)
Which implies the psqlodbc.so file does not contain sqlallochandle symbol.
However, your gateway listener has the following env setting -
so can you remove the entry -
and stop and start the listener.
Can you then check the psqlodbc.so file -
cd usr/lib64
nm psqlodbc.so | grep SQLAllocHandle
nm -D psqlodbc.so | grep SQLAllocHandle
and check it exists in the file.
thanks a lot for your reply
see below the reply of command nm :
should i guess my driver odbc doesn't work correctly?
Background info:
In general the ODBC set up consists of 2 parts, an ODBC driver manager and an ODBC driver itself. The ODBC driver manager is providing generic ODBC functions and it is also responsible to load the driver specified in the odbc.ini file. The ODBC driver itself is then dedicated for a foreign database and provides specific foreign database functions.
thanks for your reply. Unfortunately, i'm not sure to understand what you explain.
In fact i should have a driver manager and a driver, thats it?
I've download and install this rpm : postgresql-odbc-7.3-8.RHEL4.1.x86_64.rpm.
/usr/lib64/psqlodbc.la
/usr/lib64/psqlodbc.so
/usr/share/psqlodbc/odbc-drop.sql
/usr/share/psqlodbc/odbc.sql
I've supposed thats the /usr/lib64/psqlodbc.so file must be the drver manager. So i've pointed HS_FDS_SHAREABLE_NAME to this file.
But where is the driver? Driver in my odbc.ini must point to the driver file, that's it?
Мы хотим синхронизировать данные (вставить, обновить) из Oracle (11g) в PostgreSQL (10). Наш подход был следующий:
- Триггер таблицы в Oracle обновляет столбец следующим значением из последовательности перед вставкой и обновлением.
- PostgreSQL знает последний обработанный порядковый номер и выбирает строки из Oracle> lastSequenceNumberFetched.
Теперь у нас есть следующая проблема:
- Сеанс 1 в Oracle вставляет строку, записан порядковый номер (скажем, 45), но в Oracle не выполняется COMMIT.
- Сессия 2 в Oracle вставляет строку, записывается порядковый номер (скажем, 49 (потому что последовательности в Oracle могут иметь пробелы)), а в Oracle выполняется COMMIT.
- Сеанс в PostgreSQL извлекает строки из Oracle с sequenceNumber> 44 (потому что lastSequenceNumberFetched равен 44) и получает строку с sequenceNumber 49. Итак, это новый lastSequenceNumberFetched.
- Сессия 1 в Oracle совершает фиксацию.
- Сеанс в PostgreSQL извлекает строки из Oracle с sequenceNumber> 49. Проблема в том, что строка с sequenceNumber 45 никогда не извлекается.
Есть ли лучшие подходы для нашего варианта использования, позволяющие избежать проблемы с отсутствующими данными?
Вы не рассматривали возможность вообще отказаться от репликации и использовать оболочку сторонних данных, которая делает таблицу Oracle доступной в Postgres?
Исправление типов колонок
Смотрим в каких колонках надо произвести изменения
Замены для простых случаев
Читайте также: