重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle 19c RAC使用dblink访问PG数据库 [打印本页]

作者: 郑全 时间: 2023-7-4 21:07
标题: Oracle 19c RAC使用dblink访问PG数据库
Oracle 19c RAC使用dblink访问PG数据库

1.环境准备
Oracle  19.19 ORACLE LINUX 8.6
            192.168.51.76 rac01
                              192.168.51.78 rac02
                              192.168.51.75 scanip
PG 14.8 ，ORACLE LINUX 8.6
            192.168.51.90

2.安装postgresql的odbc驱动包
yum install https://download.postgresql.org/ ... o-latest.noarch.rpm
yum download postgresql12-odbc postgresql12-libs

[root@dbserver ~]# ls -ltr postgresql14-*
-rw-r--r-- 1 root root 333440 Jul  4 18:35 postgresql14-odbc-13.02.0000-1PGDG.rhel8.x86_64.rpm
-rw-r--r-- 1 root root 285912 Jul  4 18:35 postgresql14-libs-14.8-2PGDG.rhel8.x86_64.rpm
[root@dbserver ~]#

# rpm -ivh postgresql14-odbc-13.02.0000-1PGDG.rhel8.x86_64.rpm
# rpm -ivh  postgresql14-libs-14.8-2PGDG.rhel8.x86_64.rpm

[root@his01 ~]# rpm -ql postgresql14-odbc-13.02.0000-1PGDG.rhel8.x86_64
/usr/lib/.build-id
/usr/lib/.build-id/03
/usr/lib/.build-id/03/ec5015d070247074c77f8304f0cb6caffa180a
/usr/lib/.build-id/69
/usr/lib/.build-id/69/b84f5ade184436b63a24a0ec4b1031903808a7
/usr/pgsql-14/lib/psqlodbc.so
/usr/pgsql-14/lib/psqlodbca.so
/usr/pgsql-14/lib/psqlodbcw.so
/usr/share/doc/postgresql14-odbc
/usr/share/doc/postgresql14-odbc/readme.txt
/usr/share/licenses/postgresql14-odbc
/usr/share/licenses/postgresql14-odbc/license.txt

3.配置 /etc/odbc.ini

#[$DSN]定义数据源名称
[PG_LINK]
Description       = PostgreSQL connection to his
Driver          = /usr/pgsql-14/lib/psqlodbcw.so
Setup             = /usr/pgsql-14/lib/psqlodbcw.so
#数据库名
Database          = zlcost
#数据库所在的主机名或IP
Servername       = 192.168.51.90
UserName          = postgres
Password          = postgres
#数据库端口
Port             = 5432
SocketBufferSize = 4096
FetchBufferSize = 500
ReadOnly          = Yes
RowVersioning    = No
ShowSystemTables = No
#查询结果的字符编码
ConnSettings    = set client_encoding to UTF8

4.验证配置：

[root@his01 ~]# export ODBCINI=/etc/odbc.ini
[root@his01 ~]# isql --v
unixODBC 2.3.7
[root@his01 ~]# isql PGCOST -v
+---------------------------------------+
| Connected!                         |
|                                     |
| sql-statement                      |
| help [tablename]                   |
| quit                               |
|                                     |
+---------------------------------------+
SQL> select 1;
+------------+
| ?column? |
+------------+
| 1       |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL> select count(*) from "ygjc_record";
+---------------------+
| count             |
+---------------------+
| 173243             |
+---------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

5.配置透明网关

在数据库的 $ORACLEHOME/hs/admin/下面创建initPGCOST.ora文件，这个文件名字中的PGCOST是上面自定义的名字，其中HSFDSCONNECTINFO=PG这个参数指向隐藏文件.odbc.ini中的ODBC定义数据源名称[PGLINK]。在ORACLE_HOME/hs/admin/下面创建initPGCOST.ora文件，这个文件名字中的PGCOST是上面自定义的名字

[oracle@his01 admin]$ cat  $ORACLEHOME/hs/admin/initPGCOST.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 = PGCOST
HS_FDS_TRACE_LEVEL = 16
HS_FDS_SHAREABLE_NAME = /usr/pgsql-14/lib/psqlodbcw.so
HS_NLS_NCHAR=UCS2
#HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk
HS_LANGUAGE=AMERICAN_AMERICA.zl32utf8
HS_KEEP_REMTE_COLUMN_SIZE=ALL
HS_FDS_DEFAULT_SCHEMA_NAME = public
#
# ODBC specific environment variables
#
set ODBCINI=/home/oracle/.odbc.ini

HS_WORKAROUNDS=0x02

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
[oracle@his01 admin]$

后续通过dblink连接PG的时候连接日志文件路径：$ORACLE_HOME/hs/log/，可以进行排错。

6.配置tnsnames.ora文件

vim $ORACLE_HOME/network/admin/tnsnames.ora

PGCOST =
  (DESCRIPTION=
(ADDRESS=
      (PROTOCOL=TCP) (HOST=192.168.51.75) (PORT=1521)
)
(CONNECT_DATA=
   (SID=PGCOST)
)
(HS=OK)
)

7.配置监听文件

vim $GRID_HOME/network/admin/listener.ora

SID_LIST_LISTENER=
  (SID_LIST=
(SID_DESC=
   (SID_NAME=PGCOST)
   (ORACLE_HOME=/u01/app/oracle/product/19.19.0/db_1)
   (PROGRAM=dg4odbc)
   (ENVS=LD_LIBRARY_PATH="/usr/pgsql-14/lib:/u01/app/oracle/product/19.19.0/db_1/lib")
)
  )

lsnrctl reload
lsnrctl status
lsnrctl start

8.创建DBLINK和测试

SQL> conn / as sysdba
Connected.
SQL> create public database link tocostpg connect to "postgres" identified by "postgres" using 'pgcost';

Database link created.

SQL> conn zq/zq@bhis
Connected.
SQL> select count(*) from "ygjc_record"@tocostpg b ;

  COUNT(*)
----------
173329

SQL>

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)