参考文档 :
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连接至32的ORACLE.
在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
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
如果HS监听使用的ODBC.ini文件没有配置client_encoding.及可能造成编码错误,因为异构连接通过ODBC)
与环境三不同之处,DB1_X64不是连接到DB2_X32的test用户,而是直连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"?>
注意 :
64位linux只能做到使用isql来连接其他非oracle数据库,但是不能使用dblink在数据库中建立连接.(这个有待解决,根据oracle的相关文档,发现只能使用32位的Odbc.建立dblink)
在32位系统上建好DBLINK之后,如何使用普通用户来使用该DBLINK.(该普通用户必须和ODBC与远程数据库建立连接的USERNAME一致,注意大小写)
注意DBLINK中的connect to username,这个username无论你是否用双引号括起来,oracle都认为他是大写的,所以当远程数据库是小写用户时,会有问题.所以建议POSTGRES使用大写用户名,即使没有,那就搞一个大写用户来中转.
注意postgres有timestamp的概念,这个会导致oracle需要在插入时间时使用类似'2008-07-19 20:13:44.33333'格式.
Postgres和oracle的编码集要设置为一样,否则容易出问题,特别是涉及到64位嫁接到32位再连接到Postgres时.
当配置odbc连接至oracle时,不能使用pgbouncer连接池等工具,必须直接连接到postgres.因为Oracle要做事务模式.
当使用这种模式连接是,在insert等语句中不能使用很多函数.因为在Postgres中可能没有这些函数.这也是硬性规定.
每一条涉及HS的DML语句(除了select)之后必须有结束事务的语句(如commit或rollback) 否则会出问题.
关于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配置
