1. 透明网关

1.1 确认命令

确认透明网关可用

[oracle@orcl-19c admin]$ dg4odbc


Oracle Corporation --- 2024-12-26 20:52:49.772208000

Heterogeneous Agent Release 19.0.0.0.0 - Production  Built with
   Oracle Database Gateway for ODBC

2. 安装 UnixODBC

yum install unixODBC unixODBC-devel.x86_64

3. 下载panwei ODBC驱动和libpq驱动

oracle用户下执行
当前目录 /opt/stone/pw311

mkdir /opt/stone/pw311/odbc -p
mkdir /opt/stone/pw311/oci -p

解压驱动

tar xzvf PanWeiDB_V2.0-ODBC-3.1.1_B01-centos_7-x86_64.tar.gz -C /opt/stone/pw311/odbc
tar zxvf PanWeiDB_V2.0-LIBOCI-3.1.1_B01-centos_7-x86_64.tar.gz -C /opt/stone/pw311/oci

解压后odbc及lib下的文件都复制到同一个folder下面，
此处使用 /u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311

mkdir /u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311
cp /opt/stone/pw311/odbc/odbc/lib/* /u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311/
cp /opt/stone/pw311/odbc/lib/* /u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311/
\cp -f /opt/stone/pw311/oci/lib/* /u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311/

4. 配制ODBC

oracle用户下执行

4.1 配制

连接信息参考：

gsql -h 192.168.56.61 -p 13110 -d orcl -U scott -W scott_123 -r

配制 vi ~/.odbc.ini 内容如下

[PanweiOrcl]
Servername=192.168.56.61
Port=13110
Database=orcl
Driver=/u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311/psqlodbcw.so

4.2 测试

配制好 LD_LIBRARY_PATH 后用 isql 测试

export LD_LIBRARY_PATH=/u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311:${ORACLE_HOME}/lib

[oracle@orcl-19c admin]$ isql -v PanweiOrcl scott scott_123
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * From scott.dept;
+-------+---------------+--------------+
| deptno| dname         | loc          |
+-------+---------------+--------------+
| 10    | ACCOUNTING    | NEW YORK     |
| 20    | RESEARCH      | DALLAS       |
| 30    | SALES         | CHICAGO      |
| 40    | OPERATIONS    | BOSTON       |
+-------+---------------+--------------+

4.3 生成 initSID.ora

复制odbc.ini

rm /u01/app/oracle/product/19.3.0/dbhome_1/hs/.odbc.ini
cp ~/.odbc.ini /u01/app/oracle/product/19.3.0/dbhome_1/hs/

5. 配制 Oracle LISTENER/TNSNAME

5.1 LISTENER

vi $ORACLE_HOME/network/admin/listener.ora 增加下面部分

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=PanweiOrcl)
         (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
         (ENV="LD_LIBRARY_PATH=/u01/app/oracle/product/19.3.0/dbhome_1/lib:/u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311")
         (PROGRAM=dg4odbc)
      )
  )

5.2 TNSNAME

vi $ORACLE_HOME/network/admin/tnsnames.ora

增加 PanweiOrcl

PanweiOrcl =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=PanweiOrcl))
    (HS=OK)
  )

6. 配制 hs/admin/initSID.ora

取名方式 initSID.ora
SID 来自listener.ora中的 SID_NAME=PanweiOrcl

vi $ORACLE_HOME/hs/admin/initPanweiOrcl.ora

内容如下

HS_FDS_CONNECT_INFO = PanweiOrcl
set ODBCINI = /u01/app/oracle/product/19.3.0/dbhome_1/hs/.odbc.ini
HS_FDS_SHAREABLE_NAME = /u01/app/oracle/product/19.3.0/dbhome_1/hs/pw311/psqlodbcw.so
HS_FDS_QUOTE_IDENTIFIER = FALSE
set LowerCaseIdentifier = on
HS_FDS_TRACE_LEVEL = 16
HS_NLS_NCHAR = UCS2
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8

7 测试 listener

重载listener并简单测试

lsnrctl reload
tnsping PanweiOrcl

测试结果

[oracle@orcl-19c admin]$ tnsping PanweiOrcl

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 26-DEC-2024 21:00:21

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=PanweiOrcl)) (HS=OK))
OK (10 msec)

8. 创建dblink

drop database link PanweiOrcl;

create database link PanweiOrcl
connect to "scott" identified by "scott_123"
using 'PanweiOrcl';

测试

SQL> col dname format a30
SQL> col deptno format 9999
SQL> col loc format a30
SQL> select * from scott.dept@PanweiOrcl;

deptno dname         loc
------ ------------------------------ ------------------------------
    10 ACCOUNTING        NEW YORK
    20 RESEARCH         DALLAS
    30 SALES         CHICAGO
    40 OPERATIONS        BOSTON