Oracle Connect To PostgreSQL use HS DBLINK-阿里云开发者社区

开发者社区> 数据库> 正文

Oracle Connect To PostgreSQL use HS DBLINK

简介:
今天一位数据仓库的同事问我一个ORACLE连接到PG的问题,突然想起几年前写过类似的文章,现在发表如下:

参考文档 :

  oracle : Heterogeneous Connectivity Administrator's Guide

 

环境一 :

CentOS 5.2 x64

oracle 10.2.0.4

 

 :

unixODBC-2.2.11-7.1

 

postgres-odbc相关 lib :

/usr/lib64/libodbcpsql.so

/usr/lib64/libodbcpsqlS.so

[root@digoal etc]# ll /usr/lib64/libodbcpsql.so

lrwxrwxrwx 1 root root 20 09-17 09:43 /usr/lib64/libodbcpsql.so -> libodbcpsql.so.2.0.0

[root@digoal etc]# ll /usr/lib64/libodbcpsqlS.so

lrwxrwxrwx 1 root root 21 09-17 09:43 /usr/lib64/libodbcpsqlS.so -> libodbcpsqlS.so.1.0.0

 

确保 :

oracle parameters :

Global_names=false

 

操作步骤 :

su - root

updatedb

locate libodbcpsql.so

locate libodbcpsqlS.so

locate odbc.ini

locate odbcinst.ini

 

vi odbcinst.ini

 

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver          = /usr/lib64/libodbcpsql.so

Setup           = /usr/lib64/libodbcpsqlS.so

FileUsage       = 1

 

vi odbc.ini

 

[PostgreSQL]

Description = Test to Postgres

Driver = PostgreSQL   #对应odbcinst.ini中括号

Database = community

Servername = 192.168.1.11

UserName = COUNITY

Password = counityxxxx

Port = 1921

Protocol = 6.4

ReadOnly = 0   #表示可以写,如果不加则不能做INSERT操作

ConnSettings = set client_encoding to UTF8    #设置客户端字符集,以免发生编码错误

 

 

su - oracle 

isql -v db1  #对应odbc.ini中括号 (这个可以测试成功)

 

vi $ORACLE_HOME/network/admin/listener.ora

 

添加

(SID_DESC =

   (PROGRAM = hsodbc)

   (ORACLE_HOME = /app/oracle/product/10.2.0/db_1)

   (SID_NAME = db1)  #对应$ORACLE_HOME/hs/admin/initSID.ora

   (ENVS=LD_LIBRARY_PATH =  /app/oracle/product/10.2.0/db_1/lib:/usr/lib64)    

 )

 

vi $ORACLE_HOME/network/admin/tnsnames.ora

db1=

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.12) (PORT = 1125) )

   )

   (CONNECT_DATA =(SID= db1) )  #对应监听

   (HS=OK)

)

 

vi $ORACLE_HOME/hs/admin/initdb1.ora

HS_FDS_CONNECT_INFO = db1      #对应odbc.ini中括号

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbcpsql.so  #对应odbcinst.ini Driver

HS_FDS_DEFAULT_SCHEMA_NAME = public

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

 

重启lsnrctl

 

进入数据库 :

sqlplus "/ as sysdba"

create public database link pglink using 'db1';

select * from "pg_tables"@db1;

ERROR at line 1:

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 ODBC

难道是64位不被支持??

 

 

环境二 :

操作系统 : centos 5.2 32bit

          oracle 10.2.0.4 for 32bit

 :

unixODBC-2.2.11-7.1

 

postgres-odbc相关 lib :

/usr/lib/libodbcpsql.so

/usr/lib/libodbcpsqlS.so

[root@digoal etc]# ll /usr/lib/libodbcpsql.so

lrwxrwxrwx 1 root root 20 09-17 09:43 /usr/lib/libodbcpsql.so -> libodbcpsql.so.2.0.0

[root@digoal etc]# ll /usr/lib/libodbcpsqlS.so

lrwxrwxrwx 1 root root 21 09-17 09:43 /usr/lib/libodbcpsqlS.so -> libodbcpsqlS.so.1.0.0

 

确保 :

oracle parameters :

Global_names=false

 

操作步骤 :

su - root

updatedb

locate libodbcpsql.so

locate libodbcpsqlS.so

locate odbc.ini

locate odbcinst.ini

 

vi odbcinst.ini

 

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver          = /usr/lib/libodbcpsql.so

Setup           = /usr/lib/libodbcpsqlS.so

FileUsage       = 1

 

vi odbc.ini

 

[db1]

Description = Test to Postgres

Driver = PostgreSQL  #对应odbcinst.ini中括号

Database = sgap_dsm_0

Servername = 172.16.10.7

UserName = dsm

Password = dsm

Port = 1921

Protocol = 6.4

 

 

su - oracle 

isql -v db1  #对应odbc.ini中括号 (这个可以测试成功)

 

vi $ORACLE_HOME/network/admin/listener.ora

 

添加

(SID_DESC =

   (PROGRAM = hsodbc)

   (ORACLE_HOME = /app/oracle/product/10.2.0/db_1)

   (SID_NAME = db1)  #对应$ORACLE_HOME/hs/admin/initSID.ora

   (ENVS=LD_LIBRARY_PATH =  /app/oracle/product/10.2.0/db_1/lib:/usr/lib)  #这里需要两个库目录,一个是Odbc管理库的目录,一个是Oracle数据库的Lib

 )

 

vi $ORACLE_HOME/network/admin/tnsnames.ora

db1=

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.12) (PORT = 1125) )

   )

   (CONNECT_DATA =(SID= db1) )  #对应监听

   (HS=OK)

)

 

vi $ORACLE_HOME/hs/admin/initdb1.ora

HS_FDS_CONNECT_INFO = db1      #对应odbc.ini中括号

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so  #对应odbcinst.ini Driver

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

 

重启lsnrctl

 

进入数据库 :

sqlplus "/ as sysdba"

create public database link pglink using 'db1';

select * from "pg_tables"@db1;

成功

 

 

需求 : ODBC Connectivity Requirements

To use an ODBC agent, you must have an ODBC driver installed on the same machine

as the Oracle database server. On UNIX, you must have an ODBC driver manager

available on the same machine. The ODBC driver manager and driver must meet the

following requirements:

 The ODBC driver (and the non-Oracle system) must support a minimum

transaction isolation level of read committed.

 On Windows machines, the 32-bit ODBC driver must have compliance level to

ODBC standard 2.5.

 On Windows machines, the ODBC driver and driver manager must conform to

ODBC application program interface (API) conformance Level 1 or higher. If the

ODBC driver or driver manager does not support multiple active ODBC cursors,  

the complexity of SQL statements that you can execute using Generic Connectivity

is restricted.

 On UNIX machines, the ODBC driver must be 32-bit and must have compliance

level to ODBC Standard 2.5 and have a conformance level 1 or higher. If the ODBC

driver works with an ODBC driver manager, the ODBC driver manager must be

compliant with ODBC Standard 2.5 or higher

 

发现一个BUG:

  32位的ORACLE服务器通过ODBC连接至64位的POSTGRES.

  64位的ORACLE服务器通过DBLINK连接至32ORACLE.

  64位服务器上,当执行了多条INSERT语句,插入到postgres中的表时.commit,然后select这个表就会报错

    SQL> select * from tbl_test_p64@pgprot;

select * from tbl_test_p64@pgprot

              *

ERROR at line 1:

ORA-28502: internal communication error on heterogeneous database link

ORA-02063: preceding line from P64

ORA-02063: preceding 2 lines from PGPROT

 

而在32位的ORADCLE上做同样的操作则不会出现这样的问题.

 

 


环境三 :

DB1 : oracle_x64 (UTF8)

DB2 : oracle_x32 (UTF8)

PG : postgresql_x64 (UTF8)

connect APP : tomcate

Oracle Connect To PostgreSQL use HS DBLINK - 德哥@Digoal - The Heart,The World.
 

ORA2_X32 配置 :

       rpm :

unixODBC-2.2.11-1.RHEL4.1  (或者更高,也可以下源码编译)

 

       /etc/odbc.ini

[p64]

Description = Test to Postgres

Driver = PostgreSQL

Database = bj_dsm_0

Servername = 172.16.10.7

UserName = COMMUNITY

Password = community2008

Port = 1921

Protocol = 6.4

ReadOnly = 0

ConnSettings = set client_encoding to UTF8

 

       /etc/odbcinst.ini

[ODBC]

Trace = No

Trace File = /tmp/sql.log

Pooling = Yes

# Included in the unixODBC package

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver          = /usr/lib/libodbcpsql.so

Setup           = /usr/lib/libodbcpsqlS.so

FileUsage       = 1

 

       $ORACLE_HOME/hs/admin/initHSSID.ora

-- cat /app/oracle/product/10.2.0/db_1/hs/admin/initp64.ora

HS_FDS_CONNECT_INFO = p64

HS_FDS_TRACE_LEVEL = on

HS_FDS_SHAREABLE_NAME = /usr/lib/libodbcpsql.so

HS_FDS_DEFAULT_SCHEMA_NAME = public

set ODBCINI=/etc/odbc.ini

 

       $ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = /app/oracle/product/10.2.0/db_1)

      (PROGRAM = extproc)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = skymobi)

      (ORACLE_HOME = /app/oracle/product/10.2.0/db_1)

      (SID_NAME = skymobi)

    )

    (SID_DESC =

      (PROGRAM = hsodbc)

      (ORACLE_HOME = /app/oracle/product/10.2.0/db_1)

      (SID_NAME = p64)

      (ENVS=LD_LIBRARY_PATH = /app/oracle/product/10.2.0/db_1/lib:/usr/lib)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

    )

  )

 

       $ORACLE_HOME/network/admin/tnsnames.ora

p64 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =127.0.0.1)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = p64)

    )

    (HS=OK)

  )

 

       dblink :

create public database link p64 using 'p64';

 

       views :

              conn test/test

create view tbl_cmnt_user_info as select * from "tbl_cmnt_user_info"@p64;

 

ORA1_X64 配置 :

       $ORACLE_HOME/network/admin/tnsnames.ora

p64 =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP) (HOST = 10.1.2.22) (PORT = 1521) )

   )

   (CONNECT_DATA =(service_name= skymobi) )

 )

 

       DBLINK : 

              create public database link p64 connect to test identified by test using 'p64';

 

       views :

              create view tbl_cmnt_user_info as select * from test.tbl_cmnt_user_info@p64;

 

 

TOMCATE 配置 :

<?xml version="1.0" encoding="utf8"?>

 

 

监控 :

ps -efw|grep hsodbc

vi $ORACLE_HOME/hs/log/….

 

环境四 :

DB1 : oracle_x64 (UTF8)

DB2 : oracle_x32 (UTF8)  (作为HS agent服务器)

PG : postgresql_x64 (UTF8)

connect APP : tomcate 

Oracle Connect To PostgreSQL use HS DBLINK - 德哥@Digoal - The Heart,The World.
 

如果HS监听使用的ODBC.ini文件没有配置client_encoding.及可能造成编码错误,因为异构连接通过ODBC)

 

与环境三不同之处,DB1_X64不是连接到DB2_X32test用户,而是直连HS AGENT.所以不需要在DB2上建立DBLINK作为中转.

DB1 : oracle_x64 (UTF8) 配置 :

ORA1_X64 配置 :

       $ORACLE_HOME/network/admin/tnsnames.ora

p64 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =10.1.2.22)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = p64)

    )

    (HS=OK)

  )

 

       DBLINK : 

              create public database link p64 using 'p64';

 

       views :

              conn test/test

create view tbl_cmnt_user_info as select * from "tbl_cmnt_user_info"@p64;

 

 

TOMCATE 配置 :

<?xml version="1.0" encoding="utf8"?>

 

 

 

 

注意 :

  64linux只能做到使用isql来连接其他非oracle数据库,但是不能使用dblink在数据库中建立连接.(这个有待解决,根据oracle的相关文档,发现只能使用32位的Odbc.建立dblink)

  32位系统上建好DBLINK之后,如何使用普通用户来使用该DBLINK.(该普通用户必须和ODBC与远程数据库建立连接的USERNAME一致,注意大小写)

  注意DBLINK中的connect to username,这个username无论你是否用双引号括起来,oracle都认为他是大写的,所以当远程数据库是小写用户时,会有问题.所以建议POSTGRES使用大写用户名,即使没有,那就搞一个大写用户来中转.

  注意postgrestimestamp的概念,这个会导致oracle需要在插入时间时使用类似'2008-07-19 20:13:44.33333'格式.

  Postgresoracle的编码集要设置为一样,否则容易出问题,特别是涉及到64位嫁接到32位再连接到Postgres.

  当配置odbc连接至oracle,不能使用pgbouncer连接池等工具,必须直接连接到postgres.因为Oracle要做事务模式.

  当使用这种模式连接是,insert等语句中不能使用很多函数.因为在Postgres中可能没有这些函数.这也是硬性规定.

  每一条涉及HSDML语句(除了select)之后必须有结束事务的语句(commitrollback) 否则会出问题.

  关于ODBC的配置请参考http://www.unixodbc.org/odbcinst.html

  当连接oracle的用户在postgres中不存在时,会报错.所以需要保证使用HS的用户在POSTGRES中也存在.(注意ORACLE只支持大写的DBLINK,postgreS中建role请使用大写)(而且用户名密码,oracle中和postgres中需相同)

  (odbc.ini 中的密码删除)

  64位的oracle不能使用Odbc做异构连接.

   具体参考oracle文档

 

 

附录:

odbc.ini配置

Oracle Connect To PostgreSQL use HS DBLINK - 德哥@Digoal - The Heart,The World.
 



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章