Oracle 11g 访问瀚高数据库
一、前提
这个访问瀚高数据库的方式,本质上就是以 Oracle 的透明网关为基础,Oracle 数据库安装的时候默认就安装透明网关了。
场景
操作系统:Centos7.9
处理:x86_64
Oracle数据库版本:11.2.0.4
Oracle数据库IP地址:192.168.70.107
瀚高数据库版本:hgdb-see-4.5.7
瀚高数据库IP地址:192.168.70.169,端口号:5866
二、安装驱动
在 Oracle 服务器上,安装 unixODBC,命令如下:
yum install -y unixODBC unixODBC-devel
需要使用瀚高的提供的驱动包,因为这个支持sm3加密,同时 psqlodb 版本也支持 Oracle11g 的透明网关。下载地址:
链接:https://pan.baidu.com/s/1l67cHDkDltMX-n5Nt3rZAQ 提取码:yyds (如果链接失效,请联系瀚高获取)
将瀚高提供的驱动包解压
tar -xzvf hgodbc-centos7.9_x86-64.tar.gz -C /opt
这里要留意库文件 psqlodbcw.so 的绝对路径:
/opt/HG_ODBC/psqlODBC/lib/psqlodbcw.so
后面配置透明网关要用到。
三、配置文件
Oracle 数据库安装完后,自带透明网关,一般不需要额外单独安装。故在 Oracle 服务器上执行本章节操作。
3.1 配置 ODBC 数据源
切到 Oracle用户下,执行:
vi /home/oracle/.odbc.ini
内容如下:
[HGDB_169]
Description = HighgoDB ODBC
Driver = PostgreSQL
Database = highgo
Servername = 192.168.70.169
UserName = sysdba
Password = Hello@123
Port = 5866
ReadOnly = 0
ConnSettings = set client_encoding to gbk
3.2 配置 listener.ora
Oracle 用户执行:
vi $ORACLE_HOME/network/admin/listener.ora
追加以下内容
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = HGDB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(PROGRAM = dg4odbc)
(ENV="LD_LIBRARY_PATH=/opt/HG_ODBC/psqlODBC/lib")
)
)
说明:
- SID_LIST_LISTENER 后面的 “LISTENER” 是当前文件中监听的实际名称,不能随便命名。
- SID_NAME 是瀚高数据库的实例,可以自定义一个名称,如这里的:HGDB
重启监听:
lsnrctl reload
查看监听状态:
[oracle@dbs ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-OCT-2022 22:38:59
......
Services Summary...
Service "HGDB" has 1 instance(s).
Instance "HGDB", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.qcj.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.qcj.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
看到上面有“Service “HGDB” has 1 instance(s).”就可以了。
3.3 配置 tnsname.ora
Oracle用户执行:
vi $ORACLE_HOME/network/admin/tnsnames.ora
追加以下内容:
HG_LINK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SID = HGDB))
(HS = OK)
)
测试一下:
tnsping HG_LINK 5
正常结果如下:
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-OCT-2022 22:40:08
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.70.107)(PORT = 1521)) (CONNECT_DATA = (SID = HGDB)) (HS = OK))
OK (0 msec)
OK (0 msec)
OK (0 msec)
OK (0 msec)
OK (0 msec)
3.4 配置透明网关
在透明网关所在的主机上,切到Oracle用户执行:
vi $ORACLE_HOME/hs/admin/initHGDB.ora
内容如下:
HS_FDS_CONNECT_INFO = HGDB_169
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /opt/HG_ODBC/psqlODBC/lib/psqlodbcw.so
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
#HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
set ODBCINI = /home/oracle/.odbc.ini
四、测试
4.1 创建数据库连接
用 tnsname.ora 中的链接名:
create public database link hgdb connect to "sysdba" identified by "Hello@123" using 'HG_LINK';
直接定义连接信息,不需要解析:
DROP PUBLIC DATABASE LINK hgdb;
CREATE PUBLIC DATABASE LINK hgdb
CONNECT TO "sysdba"
IDENTIFIED BY "Hello@123"
USING '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.70.107)(PORT = 1521)))
(CONNECT_DATA = (SID = HGDB))
(HS = OK)
)';
以上两种创建连接的方式二选一即可。
4.2 查询
在瀚高数据库下,使用用户 sysdba 登录数据库 highgo,创建表t1,并插入几条数据:
create table t1(id integer, name varchar);
insert into t1 values(1,'瀚高'),(2,'test');
再回到Oracle执行:
select * from "public"."t1"@hgdb;
注意:
瀚高数据库中默认是小写,Oracle默认是大写,所以这里需要用双引号将表名、列名等标识符引起来,否则都会转成大写传到瀚高库将找不到对象。
4.3 修改
插入行:
insert into "public"."t1"@hgdb values(10,'aaa');
更新行:
update "public"."t1"@hgdb set "name"='abc' where "id" = 10;
删除行:
delete from "public"."t1"@hgdb where "id" = 10;
4.4 同义词
如果不想频繁在表后面用个链接名,可以创建别名来代替。
CREATE SYNONYM t1 FOR "t1"@hgdb;
然后就可以这样使用了,不需要加双引号:
select * from t1;
在Oracle本地建的同义词,默认就是大写的。