• 结构原理

目前大家使用oracle 十分方便，不习惯新的POSTGRERSQL  数据库。也可以利用现在的oracle 数据库服务器资源进行对PG数据进行透传与数据共享访问等。
原理：
  Oracle 11g R2 通过配置透明网关，然后再通过db_link 方式与PG进行数据连接。
 
 



 
 

• 相关环境配置

1. 操作系统

root@tbdb etc]# more centos-release
CentOS release 6.9 (Final)
[root@tbdb etc]#
 
(2) ODBC 版本为：操作系统自带

 
安装完以后会在/etc目录下有两个文件
root@tbdb etc]# ls -l odbc*
-rwxrwxrwx 1 root root 777 Feb 29 13:07 odbc.ini
-rw-r--r-- 1 root root 591 Feb 28 22:26 odbcinst.ini
[root@tbdb etc]#
 
 
配置文件内容：
 
/etc/odbc.ini
 
[TEST_LINK]
Description         = PostgreSQL connection to zdtbdb
Driver              = PostgreSQL
Database            = test
Servername          = 10.143.XX.XX
UserName            =test
Password            =test
Port                =5432
#!Protocol            = 9.3
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
connSettings        =set client_encoding to UTF8
 
 
 
/etc/odbcinst.ini
 
[ODBC]
Trace=yes
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbc.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbc.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1
 
配置好以后，可以测试是否能连接到对端数据库PG
root@tbdb etc]# isql test_link
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>  help
 
SQL> help
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE_QUALIFIER                                                                                                                 | TABLE_OWNER                                                                                                                     | TABLE_NAME                                                                                                                      | TABLE_TYPE                                                                                                                      | REMARKS                                                                                                                                                                                                                                                       |
------------------------------+
| test                                                                                                                            | public                                                                                                                          | student                                                                                                                         | TABLE                                                                                                                           |                                                                                                                                                                                                                                                               |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>
 
证明能连接到对端数据库PG上了。
对两个文件授权，要求Oracle 能读到这两个文件
#chmod  755 odbc*
修改oracle 的.bash_profile 文件
 
PATH=$PATH:$HOME/bin
umask 022
export PATH
export ORACLE_BASE=/oradata/app
export ORACLE_HOME=/oradata/app/product/11g/db_1
export ORACLE_HOSTNAME=tbdb
export ORACLE_SID=tbdb
export PATH=$PATH:/oracle/app:$ORACLE_HOME/bin:$ORACLE_HOME:/usr/sbin
export LD_LIBRARY__ATH=/usr/lib64:/usr/lib:$ORACLE_HOME/lib:$ORACLE_HOME/odbc/lib
export NLS_LANG="SIMPLIFIED CHINESE"_china.zhs16gbk
export PATH
 
 
修改/oradata/app/product/11g/db_1/hs/admin/initmy.ora
 
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
 
#
# HS init parameters
#
HS_FDS_CONNECT_INFO =TEST_LINK
HS_FDS_TRACE_LEVEL =debug
HS_FDS_SHAREABLE_NAME =/usr/lib64/psqlodbc.so
#HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
~
 
其中：HS_FDS_CONNECT_INFO =TEST_LINK        中的test_link 与odbc.ini中的test_link 对应，必须一致。
 
 
修改/oradata/app/product/11g/db_1/network/admin/listener.ora
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = tbdb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER=
 (SID_LIST =
  (SID_DESC=
         (SID_NAME=test)  配置第一个透明网关
         (ORACLE_HOME=/oradata/app/product/11g/db_1)
         (PROGRAM=dg4odbc)
         )
      (SID_DESC=
         (SID_NAME=my)  配置了第二个透明网关
         (ORACLE_HOME=/oradata/app/product/11g/db_1)
         (PROGRAM=dg4odbc)
       )
    )
 
 
ADR_BASE_LISTENER = /oradata/app
 
~
 
修改/oradata/app/product/11g/db_1/network/admin/tnsnames.ora
 增加这个连接串
my =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.143.XX.XX)(PORT = 1521))
    (CONNECT_DATA =
      (SID =my)
     )
      (HS = OK)
  )
~
 
 
配置完以后，运行配置情况
 
 
[oracle@tbdb admin]$ lsnrctl status
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-2月 -2020 16:37:05
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=tbdb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                29-2月 -2020 13:38:02
Uptime                    0 days 2 hr. 59 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oradata/app/product/11g/db_1/network/admin/listener.ora
Listener Log File         /oradata/app/diag/tnslsnr/tbdb/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tbdb)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "my" has 1 instance(s).
  Instance "my", status UNKNOWN, has 1 handler(s) for this service...
Service "tbdb" has 1 instance(s).
  Instance "tbdb", status READY, has 1 handler(s) for this service...
Service "tbdbXDB" has 1 instance(s).
  Instance "tbdb", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@tbdb admin]$
 
 

• 测试过程

 
 

1. 创建db_link

Create database db_link my  connect to “test” identified by “test” using ‘my’;
 

2. 测试数据

SQL> select * from "student"@my; 
        id
----------
name
--------------------------------------------------------------------------------
       age homeaddress                                             grade
---------- -------------------------------------------------- ----------
         1
zyq
        22 ddsdfa                                                      3
         2
zyq2
        23 sta                                                        12
        id
----------
name
--------------------------------------------------------------------------------
       age homeaddress                                             grade
---------- -------------------------------------------------- ----------
 
如果查询单个字段
 
 
SQL> select "id" from "student"@my;
 
        id
----------
         1
         2
 
SQL> desc "student"@my;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 id                                        NOT NULL NUMBER(10)
 name                                      NOT NULL VARCHAR2
 age                                       NOT NULL NUMBER(10)
 homeaddress                                        CHAR(255)
 grade                                              NUMBER(10)
 
SQL>
 
 

• 排错过程

 
 
SQL> select * from "student"@my;
ERROR at line 1:
ORA-28500: 连接 ORACLE 到非 Oracle 系统时返回此信息:
ERROR: relation "student" does not exist;
No query has been executed with that handle {42P01,NativeErr = 1}
ORA-02063: 紧接着 3 lines (起自 my)
 
需要在PG数据库对此表进行授权
 
[root@tbdb etc]# su - postgres
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.
 
postgres=# \c test
psql (8.4.20)
You are now connected to database "test".
test=# grant select on table student to test;
GRANT
test=#
 
也可以看一下
 
[oracle@tbdb log]$ pwd
/oradata/app/product/11g/db_1/hs/log
[oracle@tbdb log]$ ls -lrt
total 724
-rw-r--r-- 1 oracle dba    203 Feb 29 11:23 test_agt_28785.trc
-rw-r--r-- 1 oracle dba  14823 Feb 29 11:52 test_agt_30197.trc
-rw-r--r-- 1 oracle dba  10026 Feb 29 11:52 test_agt_30229.trc
acle dba  18344 Feb 29 13:45 my_agt_3763.trc
-rw-r--r-- 1 oracle dba  15867 Feb 29 13:46 my_agt_3775.trc
-rw-r--r-- 1 oracle dba  15870 Feb 29 13:46 my_agt_3790.trc
-rw-r--r-- 1 oracle dba  57202 Feb 29 13:51 my_agt_3576.trc
-rw-r--r-- 1 oracle dba  13495 Feb 29 13:56 test_agt_3892.trc
-rw-r--r-- 1 oracle dba  19865 Feb 29 16:43 my_agt_12066.trc
[oracle@tbdb log]$ vi my_agt_12066.trc
 
ntered hgogprd
HOSGIP for "HS_FDS_TRACE_LEVEL" returned "debug"
Entered hgosdip
 setting HS_OPEN_CURSORS to default of 50
 setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
 setting HS_FDS_RECOVERY_PWD to default value
 setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
 setting HS_IDLE_TIMEOUT to default of 0
 setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
 setting HS_NLS_NCHAR to default of "AL32UTF8"
 setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
 setting HS_FDS_DATE_MAPPING to default of "DATE"
 setting HS_RPC_FETCH_REBLOCKING to default of "ON"
 setting HS_FDS_FETCH_ROWS to default of "100"
 setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
 setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
 setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
 setting HS_FDS_MAP_NCHAR to default of "TRUE"
 setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
 setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
 setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
 setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
 setting HS_FDS_QUERY_DRIVER to default of "TRUE"
 setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
 setting HS_FDS_QUOTE_IDENTIFIER to default of "TRUE"
 setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
 setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
 setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
 setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
 setting HS_FDS_DELAYED_OPEN to default of "TRUE"
 setting HS_FDS_WORKAROUNDS to default of "0"
Exiting hgosdip, rc=0
 ORACLE_SID is "my"
 Product-Info:
  Port Rls/Upd:4/0 PrdStat:0
  Agent:Oracle Database Gateway for ODBC
  Facility:hsa
  Class:ODBC, ClassVsn:11.2.0.4.0_0019, Instance:my
Exiting hgogprd, rc=0
Entered hgoinit
HOCXU_COMP_CSET=1
HOCXU_DRV_CSET=852
HOCXU_DRV_NCHAR=873
HOCXU_DB_CSET=852
HS_LANGUAGE not specified
LANG=en_US.UTF-8
                             TY             NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
  4 INTEGER     N          4          4   0/  0    0   0   0 id
 -1 LONGVARCHAR N         80          0   0/  0    0   0 220 name
  4 INTEGER     N          4          4   0/  0    0   0   0 age
  1 CHAR        Y         50         50   0/  0    0   0 200 homeaddress
  4 INTEGER     Y          4          4   0/  0    0   0   0 grade
 0 rows fetched
Exiting hgoftch, rc=1403 at 2020/02/29-16:42:29
Entered hgoclse, cursor id 1 at 2020/02/29-16:43:46
Exiting hgoclse, rc=0 at 2020/02/29-16:43:46
Entered hgodafr, cursor id 1 at 2020/02/29-16:43:46
 Free hoada @ 0x917f00
Exiting hgodafr, rc=0 at 2020/02/29-16:43:46
Entered hgopars, cursor id 1 at 2020/02/29-16:43:46
 type:0
SQL text from hgopars, id=1, len=32 ...
     00: 53454C45 43542041 312E2269 64222046  [SELECT A1."id" F]
     10: 524F4D20 22737475 64656E74 22204131  [ROM "student" A1]
Exiting hgopars, rc=0 at 2020/02/29-16:43:46
Entered hgoopen, cursor id 1 at 2020/02/29-16:43:46
hgoopen, line 87: NO hoada to print
 Deferred open until first fetch.
Exiting hgoopen, rc=0 at 2020/02/29-16:43:46
Entered hgodscr, cursor id 1 at 2020/02/29-16:43:46
 Allocate hoada @ 0x917f00
Entered hgodscr_process_sellist_description at 2020/02/29-16:43:46
Entered hgopcda at 2020/02/29-16:43:46
 Column:1(id): dtype:4 (INTEGER), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:0
Exiting hgopcda, rc=0 at 2020/02/29-16:43:46
hgodscr, line 466: Printing hoada @ 0x917f00
 MAX:1, ACTUAL:1, BRC:100, WHT=5 (SELECT_LIST)
DTY         NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
  4 INTEGER N          4          4   0/  0    0   0   0 id
Exiting hgodscr, rc=0 at 2020/02/29-16:43:46
Entered hgoftch, cursor id 1 at 2020/02/29-16:43:46
hgoftch, line 138: Printing hoada @ 0x917f00
 MAX:1, ACTUAL:1, BRC:100, WHT=5 (SELECT_LIST)
DTY         NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
  4 INTEGER N          4          4   0/  0    0   0   0 id
 Performing delayed open.
 SQLBindCol: column 1, cdatatype: -16, bflsz: 4
 SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4
 SQLFetch: row: 1, column 1, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)
 SQLFetch: row: 2, column 1, bflsz: 4, bflar: 4
 SQLFetch: row: 2, column 1, bflsz: 4, bflar: 4, (bfl: 4, mbl: 4)
 2 rows fetched
Exiting hgoftch, rc=0 at 2020/02/29-16:43:46
Entered hgoftch, cursor id 1 at 2020/02/29-16:43:46
hgoftch, line 138: Printing hoada @ 0x917f00
 MAX:1, ACTUAL:1, BRC:2, WHT=5 (SELECT_LIST)
DTY         NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME
  4 INTEGER N          4          4   0/  0    0   0   0 id
 0 rows fetched
Exiting hgoftch, rc=1403 at 2020/02/29-16:43:46
                                                    
 
 

• PG数据库相关操作

 
 本次使用的PG为操作系统本身带的PostGresDB
  首次使用时，先初始化
 service postgresql initdb
 然后修改自动启动服务
#chkconfig postgresql on
#service postgresql start
 修改配置文件
 
root@tbdb data]# pwd
/var/lib/pgsql/data
[root@tbdb data]# ls -lrt
total 84
-rw------- 1 postgres postgres     4 Feb 28 22:04 PG_VERSION
drwx------ 2 postgres postgres  4096 Feb 28 22:04 pg_twophase
drwx------ 2 postgres postgres  4096 Feb 28 22:04 pg_tblspc
drwx------ 4 postgres postgres  4096 Feb 28 22:04 pg_multixact
-rw------- 1 postgres postgres  1631 Feb 28 22:04 pg_ident.conf
drwx------ 3 postgres postgres  4096 Feb 28 22:04 pg_xlog
drwx------ 2 postgres postgres  4096 Feb 28 22:04 pg_subtrans
drwx------ 2 postgres postgres  4096 Feb 28 22:04 pg_clog
-rw-r--r-- 1 root     root      3469 Feb 28 22:17 \
-rw------- 1 postgres postgres  3529 Feb 28 23:22 pg_hba.conf
-rw------- 1 postgres postgres    46 Feb 28 23:23 postmaster.pid
-rw------- 1 postgres postgres    57 Feb 28 23:23 postmaster.opts
-rw------- 1 postgres postgres 16876 Feb 28 23:36 postgresql.conf
drwx------ 2 postgres postgres  4096 Feb 29 00:00 pg_log
drwx------ 7 postgres postgres  4096 Feb 29 10:24 base
drwx------ 2 postgres postgres  4096 Feb 29 12:32 global
drwx------ 2 postgres postgres  4096 Feb 29 16:58 pg_stat_tmp
 
[root@tbdb data]# vi  postgresql.conf

# - Connection Settings -
 
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directory = ''             # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal
 
 
修改pg_hba.conf文件
 
 
 
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
local   all         all                              ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident
host    all         all         0.0.0.0/0             trust
host    all         all         10.0.0.0/8            md5
 
# IPv6 local connections:
host    all         all         ::1/128               ident
 
关闭防火墙或添加相关端口到防火墙中的IPTABLE.CONF文件中。
 
重启相关服务：
#service  postgresql  restart
root@tbdb data]# service postgresql restart
Stopping postgresql service: [  OK  ]
Starting postgresql service: [  OK  ]
[root@tbdb data]#
 
 
连接到PG数据库
[root@tbdb data]# su - postgres
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.
 
postgres=#
postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
                                                             : postgres=CTc/postgres
                                                             : test=CTc/postgres
(4 rows)
 
postgres-#
 
 
通过工具软件连接到上面