Вы здесь
Решение проблемы PostgreSQL из Oracle database
Опубликовано ср, 10/14/2015 - 14:59 пользователем talifa
Краткое описание логики работы:
1) get connect string from tnsnames (если нужно)
2) set connect request to listener on host bi-6.bi.ru port 1523 for SID PSQL with HS=OK flag
4) listener видит HS=OK и запускает dg4odbc с ORACLE_SID=PSQL
5) dg4odbc читает конфигурацию из файла $ORACLE_HOME/hs/initPSQL.ora
6) устанавливает TRACE, NLS параметры из $ORACLE_HOME/hs/initPSQL.ora
7) устанавливает что ODBCINI=/etc/odbc.ini
8) пытается сделать odbc connect use /usr/pgsql-9.4/lib/psqlodbc.so to DSN [PSQLP] from ODBCINI
2) set connect request to listener on host bi-6.bi.ru port 1523 for SID PSQL with HS=OK flag
4) listener видит HS=OK и запускает dg4odbc с ORACLE_SID=PSQL
5) dg4odbc читает конфигурацию из файла $ORACLE_HOME/hs/initPSQL.ora
6) устанавливает TRACE, NLS параметры из $ORACLE_HOME/hs/initPSQL.ora
7) устанавливает что ODBCINI=/etc/odbc.ini
8) пытается сделать odbc connect use /usr/pgsql-9.4/lib/psqlodbc.so to DSN [PSQLP] from ODBCINI
1 yum install postgresql*odbc
Loaded plugins: security
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql94-odbc.i386 0:09.03.0400-1PGDG.rhel5 set to be updated
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================
Package Arch Version Repository Size
==========================================================================================
Installing:
postgresql94-odbc i386 09.03.0400-1PGDG.rhel5 pgdg94 244 k
Transaction Summary
==========================================================================================
Install 1 Package(s)
Upgrade 0 Package(s)
Total download size: 244 k
Is this ok [y/N]: Y
Downloading Packages:
postgresql94-odbc-09.03.0400-1PGDG.rhel5.i386.rpm | 244 kB 00:01
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : postgresql94-odbc 1/1
Installed:
postgresql94-odbc.i386 0:09.03.0400-1PGDG.rhel5
Complete!
2. cat /etc/odbcinst.ini
[PostgreSQL]
Driver = /usr/pgsql-9.4/lib/psqlodbc.so
FileUsage = 1
Description = PostgreSQL ODBC driver for Linux
Debug = 1
CommLog = 1
3. configure HS for Oracle database
cat /etc/odbc.ini
[PSQL]
Debug = 0
CommLog = 0
Driver = /usr/pgsql-9.4/lib/psqlodbc.so
Database = fininform
Servername = 10.0.1.6
UserName = postgres
Password = postgres
Port = 5432
ReadOnly = 1
Trace = 0
TraceFile = /tmp/sql.log
cat $ORACLE_HOME/network/admin/tnsnames.ora
PSQL =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = bi-6.bipartner.ru) (PORT = 1523))
(CONNECT_DATA = (SID = PSQL)) (HS=OK)
)
cat $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = BI6)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
)
(SID_DESC =
(SID_NAME = BDWH)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(SID_NAME = B11)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
)
(SID_DESC =
(SID_NAME = PSQL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = dg4odbc)
(ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:/u01/app/oracle/product/11.2.0/db_1/bin:/lib:/usr/pgsql-9.4/lib")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = bi-6.bipartner.ru) (PORT = 1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = bi-6.bipartner.ru) (PORT = 1522))
(ADDRESS = (PROTOCOL = TCP) (HOST = bi-6.bipartner.ru) (PORT = 1523))
(ADDRESS = (PROTOCOL = TCP) (HOST = bi-6.bipartner.ru) (PORT = 1524))
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))
)
)
lsnrctl reload ;
create public database link PG_LINK connect to "postgres" identified by "postgres" using 'PG_LINK';
env |grep 'ODBC'
export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini
SQL> select "tablename" from "pg_tables"@PG_LINK where "schemaname"='information_schema';
tablename
--------------------------------------------------------------------------------
sql_implementation_info
sql_languages
sql_packages
sql_parts
sql_sizing
sql_sizing_profiles
sql_features
7 rows selected.
[oracle@bi-6 /]$ isql -v PG_LINK postgres postgres
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect
[oracle@bi-6 /]$ odbcinst -q -s
odbcinst: SQLGetPrivateProfileString failed with .
[oracle@bi-6 /]$ odbcinst -q -d
[PostgreSQL]
strace -f -eopen isql -v PSQL postgres postgres
isql -v PSQL postgres postgres
PostgreSQL_teg:
откуда дровишки:
Post new comment