Oracle 通過透明閘道器訪問mysql配置步驟
Oracle 通過透明閘道器訪問mysql配置步驟
一、當前環境
1. OS
more /etc/redhat-release
Red Hat Enterprise Linux Server release 5.1 (Tikanga)
2. Oracle
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
3. Mysql
5.1.46-community-log MySQL Community Server
4. IP
|
機器名 |
IP |
資料庫 |
A |
s1.db.sns.mdc.139.com |
192.168.2.235 |
Oracle |
B |
backup.139.com |
192.168.2.23 |
mysql |
二、所需的包
mysql-connector-odbc-5.1.8-1.rhel5.x86_64.rpm
unixODBC-2.2.11-7.1.x86_64.rpm
三、配置過程
1. 在機器A上安裝相關包
rpm –ivh mysql-connector-odbc-5.1.8-1.rhel5.x86_64.rpm
yum install unixODBC
2. 在機器B上建立mysql使用者並授權
grant select on user_global.* to my23_3322@192.168.2.235 identified by 'my23_3322';
3. 配置機器A上的 /etc/odbc.ini檔案,新增如下內容
[my23_3322]
Driver = /usr/lib64/libmyodbc5.so
Server = 192.168.2.23
User = my23_3322
Password = my23_3322
Port = 3322
database = user_global
其中,my23_3322 為服務名,後面將會用到
/usr/lib64/libmyodbc5.so 為odbc 驅動
192.168.2.23 為要訪問mysql 所在IP
my23_3322 登陸mysql 伺服器使用者名稱
3322 為mysql 資料庫埠
4. 在機器A使用isql 命令進行測試
isql my23_3322
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
能夠連線上表示ODBC配置成功
下面進行oracle 方面的配置,如無特殊說明均在機器A上配置
5. 在/opt/oracle/11.2/db_1/hs/admin/目錄下,配置init my23_3322.ora檔案, init my23_3322.ora命名方式為init+服務名+.ora。新增如下內容
# HS init parameters
#
HS_FDS_CONNECT_INFO = my23_3322
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc5.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_SQLLEN_INTERPRETATION=32
HS_LONG_PIECE_TRANSFER_SIZE=1258291
# ODBC specific environment variables
#
#set DBCINI=
set DBCINI=/etc/odbc.ini
HS_FDS_CONNECT_INFO為服務名,與前面保持一致
HS_FDS_TRACE_LEVEL 為日誌跟蹤級別,不使用時可以設為OFF
HS_FDS_SHAREABLE_NAME 為ODBC驅動
HS_LANGUAGE 為 Provides Heterogeneous Services with character set, language, and territory information of the non-Oracle data source. The value must use the following format:language[_territory.character_set] 。為異構伺服器的字符集,語言
HS_FDS_SQLLEN_INTERPRETATION 允許64bit 驅動可以使用32bit 標準,具體解釋可以參考oracle 文件ID 554409.1
6. 修改tnsnames.ora 檔案,新增如下內容
my23_3322 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = s1.db.sns.mdc.139.com)(PORT = 1521))
(CONNECT_DATA =
(SID = my23_3322)
)
(hs=ok)
)
其中SID為前面定義的服務名
HOST為本機的機器名或者IP
PORT為本機的資料庫監聽器埠
7. 修改listener.ora 檔案,新增如下內容
(SID_DESC =
(SID_NAME = my23_3322)
(ORACLE_HOME = /opt/oracle/11.2/db_1)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/opt/oracle/11.2/db_1/lib:/opt/oracle/11.2/db_1/odbc/lib)
)
SID_NAME為服務名
PROGRAM 為使用dg4odbc 驅動
8. 重新啟動listener ,基本內容如下:
lsnrctl status
Service "my23_3322" has 1 instance(s).
Instance "my23_3322", status UNKNOWN, has 1 handler(s) for this service...
如果看到my23_3322 這個服務,則表示listener.ora 配置成功
9. 建立oracle 中建立資料庫連結
sqlplus scott/tiger
create database link ln_my23_3322 connect to "my23_3322" identified by "my23_3322" using 'my23_3322';
10. 測試
SQL> select count(1) from tmp_reg_ip@ln_my23_3322;
COUNT(1)
----------
3812
Elapsed: 00:00:00.07
SQL> select * from dual@ln_my23_3322;
D
-
X
四、安裝過程中遇到的問題
1. 配置完成後,執行SQL報如下錯誤
SQL> select * from dual@ln_my23_3322; select * from dual@ln_my23_3322 * ERROR at line 1: ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=s1.db.sns.mdc.139.com)(PORT=1521)) (CONNECT_DATA=(SID=my23_3322))) ORA-02063: preceding line from LN_MY23_3322 Process ID: 18585 Session ID: 96 Serial number: 207
在initmy23_3322.ora檔案中新增如下引數
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
參考Oracle ID 555355.1
2. 從異種資料庫拷貝大資料量時,報錯如下
insert into auth_user select * from auth_user@ln_my23_3322
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 19180
Session ID: 96 Serial number: 227
Alert 檔案有如下報錯:
ORA-07445: exception encountered: core dump [npixfc()+243] [SIGSEGV] [ADDR:0x7FFF086B7C08] [PC:0x59BAB0D] [Address not mapped to object] []
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=s1.db.sns.mdc.139.com)(PORT=1521))(CONNECT_DATA=(SID=my23_3322)))
ORA-02063: preceding line from LN_MY23_3322
Incident details in: /opt/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_13360/ora11g_ora_17512_i13360.trc
現象為伺服器記憶體(4G)被吃光,資料庫分配2G記憶體。機器記憶體有限無法再提供更多的記憶體,這可能是個BUG,暫沒有找到解決辦法,鬱悶。。。。。。
五、透明閘道器有如下限制
1. BLOB and CLOB data cannot be read by pass-through queries
2. Updates or deletes that include unsupported functions within a WHERE clause are not allowed
3. Does not support stored procedures
4. Cannot participate in distributed transactions; they support single-site transactions only
5. Does not support multithreaded agents
6. Does not support updating LONG columns with bind variables
7. Does not support rowids
8. COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors
9. UPDATE and DELETE statements with the WHERE CURRENT OF clause are not supported by the gateway because they rely on the Oracle implementation.
10. The gateway does not support the CONNECT BY clause in a SELECT statement.
11. The Oracle ROWID implementation is not supported.
12. EXPLAIN PLAN Statement
13. The gateway does not support the START WITH condition and NOWAIT in a SELECT statement.
六、參考文件
Support.oracle.com
Database Gateway Installation and Configuration Guide for AIX 5L Based Systems (64-Bit), HP-UX Itanium, Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64
Oracle Database Gateway for ODBC User’s Guide
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/21601207/viewspace-709366/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 通過透明閘道器訪問mysqlOracleMySql
- Oracle 透過透明閘道器 訪問 mysqlOracleMySql
- Oracle 11.2.0.4 透過透明閘道器訪問mysql 8.0.16OracleMySql
- Oracle透明閘道器訪問MySQL資料庫OracleMySql資料庫
- Oracle透明閘道器訪問SQLServer資料庫OracleSQLServer資料庫
- ORACLE通過透明閘道器建dblink連線Postgresql的幾個問題OracleSQL
- 10g透明閘道器訪問sqlserverSQLServer
- 【磐維資料庫】Oracle(透明閘道器)訪問磐維資料庫(PanWeiDB)資料庫Oracle
- 安全閘道器 透明加解密解密
- apache虛擬機器配置步驟和修改訪問埠Apache虛擬機
- Oracle/MySQL透過odbc訪問PostgreSQL for LightDBOracleMySql
- Modbus轉Ethernet IP閘道器模組與匯川PLC通訊在閘道器配置軟體中的配置
- win10系統如何設定預設閘道器_win10預設閘道器設定步驟Win10
- 30分鐘通過Kong實現.NET閘道器
- 不設定預設閘道器,可訪問網路
- vnc viewer透過外網訪問,vnc viewer透過外網訪問8個步驟VNCView
- 服務閘道器過濾器過濾器
- 多網路卡 配置多閘道器
- 微服務6:通訊之閘道器微服務
- 八步部署NGINX Plus API閘道器NginxAPI
- PLC透過Profinet轉Modbus閘道器與流量計通訊案例
- Modbus TCP轉Profinet閘道器配置案例TCP
- Mysql通過ibd檔案恢復資料的步驟詳解MySql
- 通過配置tomcat在瀏覽器訪問非專案中的檔案Tomcat瀏覽器
- 解決Oracle死鎖問題步驟Oracle
- Oracle 11g dataguard 配置簡約步驟Oracle
- Janusec應用安全閘道器(WAF閘道器)
- Ceph物件閘道器,多區域閘道器物件
- 智慧儀表透過Modbus轉Profinet閘道器與PLC通訊方案
- 【BUILD_ORACLE】Oracle RAC配置ASM Filter Driver(ASMFD)(二)詳細配置步驟UIOracleASMFilter
- 日均數十億訪問量!解讀個推API閘道器高能演進API
- 在 k8s 中通過 Ingress 配置域名訪問K8S
- 【PRODUCE】Oracle 通過儲存過程限制使用者訪問表資料Oracle儲存過程
- 變頻器透過Modbus轉Profinet閘道器連線電機與PLC通訊
- Jaspersoft?Studio新增mysql資料庫配置步驟MySql資料庫
- 什麼是閘道器?閘道器的作用是什麼,閘道器的作用詳解
- 閘道器GatewayGateway
- gateway 閘道器Gateway
- 通過Postman實現API閘道器的請求籤名與除錯PostmanAPI除錯