Oracle數據庫網關可以透明地訪問其他數據庫,Oracle HS Agent將SQL語句轉換為非Oracle數據庫(PostgreSQL)可以理解的SQL語句,并通過ODBC 數據源發送該SQL語句,在PostgreSQL中執行后將結果返回Oracle數據庫,本文介紹如何配置并訪問PostgreSQL數據庫。
操作系統:RHEL 6.9
軟件版本:Oracle 11.2.0.4
主機地址:192.168.21.13
監聽端口:1521
操作系統:RHEL 7.8
軟件版本:PostgreSQL 10.2
主機地址:192.168.21.125
服務端口:5432
數據庫名:MYPGDB
用戶密碼:mypguser/oracle
安裝并創建相關數據庫,使用pgadmin測試,確保遠程連接正常。
yum -y install unixODBC*
yum -y install postgresql-odbc postgresql-libs
odbcinst -j
odbc_config --odbcini --odbcinstini
默認已經創建了[PostgreSQL]條目。
# Example driver definitions
# 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
數據源名稱為 pgdsn,可自定義其他名稱,注意在后繼配置中引用時保持一致。
PostgreSQL數據庫名為 MYPGDB, 服務器/端口為 192.168.21.125/5432, 用戶名/密碼為 mypguser/oracle。
[pgdsn]
Driver = PostgreSQL
Description = PostgreSQL ODBC Driver
Database = MYPGDB
Servername = 192.168.21.125
Username = mypguser
Password = oracle
Port = 5432
UseDeclareFetch = 1
CommLog = /tmp/pgodbclink.log
Debug = 1
LowerCaseIdentifier = 1
[root@rhel69rac1 ~]# isql pgdsn
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select current_database(),inet_server_port();
+-----------------------------------------------------------------+-----------------+
| current_database | inet_server_port|
+-----------------------------------------------------------------+-----------------+
| MYPGDB | 5432 |
+-----------------------------------------------------------------+-----------------+
SQLRowCount returns -1
1 rows fetched
SQL>
在 $ORACLE_HOME/hs/admin 目錄下創建 init<數據源名>.ora 文件,本測試為 initpgdsn.ora。
HS_FDS_CONNECT_INFO = pgdsn
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
set ODBCINI=/etc/odbc.ini
注意集群環境的監聽文件位置為$GRID_HOME/network/admin/listener.ora。
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.21.13)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC=
(SID_NAME = pgdsn)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/db_1")
(PROGRAM=dg4odbc)
)
)
配置文件為 $ORACLE_HOME/network/admin/tnsnames.ora。
pgdsn =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST = 192.168.21.13)(PORT = 1521))
(CONNECT_DATA=(sid=pgdsn))
(HS=OK)
)
lsnrctl stop
lsnrctl start
SQL> create public database link pglink connect to "mypguser" identified by "oracle" using pgdsn;
注意表名需要添加雙引號。
SQL> select count(*) from "pg_settings"@pglink;
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/130075.html
摘要:摘要第九屆中國數據庫技術大會,阿里云數據庫產品專家蕭少聰帶來以阿里云如何打破遷移上云的壁壘為題的演講。于是,阿里云給出了上面的解決方案。 摘要: 2018第九屆中國數據庫技術大會,阿里云數據庫產品專家蕭少聰帶來以阿里云如何打破Oracle遷移上云的壁壘為題的演講。Oracle是指數據庫管理系統,面對Oracle遷移上云的壁壘,阿里云如何能夠打破它呢?本文提出了Oracle 到云數據庫P...
閱讀 1346·2023-01-11 13:20
閱讀 1684·2023-01-11 13:20
閱讀 1132·2023-01-11 13:20
閱讀 1858·2023-01-11 13:20
閱讀 4100·2023-01-11 13:20
閱讀 2704·2023-01-11 13:20
閱讀 1385·2023-01-11 13:20
閱讀 3597·2023-01-11 13:20