記一次oracle透過dblink連線mysql實施
本週由於公司一個系統的oracle資料庫需要採集另一個系統mysql資料庫的資料,於是對oracle透過dblink連線mysql進行了研究,測試環境是單例項oracle 10g 64位,折騰了半天搞定了,但是生產環境是10g rac,原本以為會很順利,但是折騰了一天多都沒搞定,問題主要出在監聽上面,原本打算放棄,採用中介軟體連線mysql取資料,但是憑直覺感覺應該是單例項和rac的監聽配置差異造成的,於是在rac節點上單獨建立預設監聽,問題果然解決了。
當配置有問題的時候都會報下面的錯誤:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
Unable to retrieve text of NETWORK/NCR message 65535
遇到的主要問題和解決辦法有:
1、oracle 10g 64位的hsodbc應該有問題,檔案大小為0,執行hsodbc沒有任何資訊;
解決:複製32機器下的hsodbc到$ORACLE_HOME/bin目錄下,替換原hsodbc,注意oracle要有許可權,下載:http://space.itpub.net/?uid-28321441-action-viewspace-itemid-750733
2、rac監聽不能監聽到hsodbc服務;
解決:netca選擇single node configuration建立監聽來監聽hsodbc服務,監聽埠選擇1522;
3、連線mysql查詢資料亂碼。
解決:編輯/etc/odbc.ini,增加
charset =gbk
STMT =SET NAMES 'GBK'
STMT =SET NAMES 'GBK'
其中gbk是MYSQL字符集。
具體實施步驟如下:
--oracle伺服器:
-- 作業系統:Red Hat Enterprise Linux 5.3
-- 資料庫: Oracle RDBMS 10.2.0.3.0
--mysql伺服器:
-- 作業系統:windows server 2003
-- 資料庫: MySQL 5.5
-- 作業系統:windows server 2003
-- 資料庫: MySQL 5.5
單例項操作如下,操作都在oracle伺服器上完成:
--檢查需要的rpm包,需要的包如下,由於hsodbc應該是隻支援32位,所以32位包是必須的:
libtool-ltdl-1.5.22-6.1.i386.rpm
libtool-ltdl-1.5.22-6.1.x86_64.rpm
mysql-5.0.77-3.el5.i386.rpm
mysql-5.0.77-3.el5.x86_64.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.i386.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.x86_64.rpm
perl-DBI-1.52-2.el5.x86_64.rpm
unixODBC-2.2.11-7.1.i386.rpm
unixODBC-2.2.11-7.1.x86_64.rpm
libtool-ltdl-1.5.22-6.1.i386.rpm
libtool-ltdl-1.5.22-6.1.x86_64.rpm
mysql-5.0.77-3.el5.i386.rpm
mysql-5.0.77-3.el5.x86_64.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.i386.rpm
mysql-connector-odbc-3.51.26r1127-1.el5.x86_64.rpm
perl-DBI-1.52-2.el5.x86_64.rpm
unixODBC-2.2.11-7.1.i386.rpm
unixODBC-2.2.11-7.1.x86_64.rpm
上面unixODBC-2.2.11-7.1、mysql-5.0.77-3.el5、mysql-connector-odbc是需要的包,其他是安裝這些包的前提。
安裝完後檢查
# rpm -qa |grep unixODBC
安裝完後檢查
# rpm -qa |grep unixODBC
unixODBC-2.2.11-7.1
unixODBC-2.2.11-7.1
unixODBC-2.2.11-7.1
# rpm -qa |grep mysql
mysql-5.0.45-7.el5
mysql-5.0.45-7.el5
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-5.0.45-7.el5
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-connector-odbc-3.51.26r1127-1.el5
--編輯/etc/odbc.ini
vi /etc/odbc.ini
vi /etc/odbc.ini
#[ODBC Data Sources]
#myodbc3 = MySQL ODBC 3.51 Driver DSN
#myodbc3 = MySQL ODBC 3.51 Driver DSN
[test]
Driver = /usr/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
SERVER = 24.1.20.149
PORT = 3306
USER = root
Password = laopo
Database = teng_push
OPTION = 3
SOCKET =
charset =gbk
STMT =SET NAMES 'GBK'
Driver = /usr/lib/libmyodbc3.so
Description = MySQL ODBC 3.51 Driver DSN
SERVER = 24.1.20.149
PORT = 3306
USER = root
Password = laopo
Database = teng_push
OPTION = 3
SOCKET =
charset =gbk
STMT =SET NAMES 'GBK'
--最後兩個是解決中文亂碼問題,gbk是MYSQL字符集
--切換到oracle使用者
# su - oracle
--編輯環境變數配置檔案,主要是LD_LIBRARY_PATH和最後面兩項
$ vi ~/.bash_profile
# su - oracle
--編輯環境變數配置檔案,主要是LD_LIBRARY_PATH和最後面兩項
$ vi ~/.bash_profile
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
#export LANG=zh_CN.GB18030
export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/bin:/usr/bin:/usr/sbin
:/usr/local/bin:/usr/X11R6/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
#ulimit -u 16384 -n 65536
umask 022
export EDITOR=vi
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=orcl
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
#export LANG=zh_CN.GB18030
export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/bin:/usr/bin:/usr/sbin
:/usr/local/bin:/usr/X11R6/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
#ulimit -u 16384 -n 65536
umask 022
export EDITOR=vi
ODBCINI=/etc/odbc.ini; export ODBCINI
ODBCSYSINI=/etc; export ODBCSYSINI
ODBCSYSINI=/etc; export ODBCSYSINI
--使配置生效
source ~/.bash_profile
source ~/.bash_profile
--檢視odbc版本及引數檔案路徑
$odbcinst -j
$odbcinst -j
unixODBC 2.2.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
USER DATA SOURCES..: /usr/local/etc/odbc.ini
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
USER DATA SOURCES..: /usr/local/etc/odbc.ini
--測試 My SQL ODBC 驅動
$isql test -v(測試前需要將/etc/odbc.ini中的Driver= /usr/lib/libmyodbc3.so 修改成/usr/lib64/libmyodbc3.so,測試完後還原)
isql test oracle manager -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit
--配置 HSODBC 程式
vi $ORACLE_HOME/hs/admin/inittest.ora
vi $ORACLE_HOME/hs/admin/inittest.ora
HS_FDS_CONNECT_INFO = test
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
set DBCINI=/etc/odbc.ini
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME=/usr/lib/libmyodbc3.so
set DBCINI=/etc/odbc.ini
--確認 hsodbc 的配置是否正確,如果正確可以看到版本號
--注意:如果是64位oracle,由於hsodbc應該是隻支援32位,這裡需要複製32位機器上的hsodbc到$ORACLE_HOME/bin下替換原有的hsodbc
--注意:如果是64位oracle,由於hsodbc應該是隻支援32位,這裡需要複製32位機器上的hsodbc到$ORACLE_HOME/bin下替換原有的hsodbc
$ hsodbc
Oracle Corporation --- FRIDAY DEC 06 2012 12:20:44.240
Heterogeneous Agent Release 10.2.0.3.0 - Production Built with
Driver for ODBC
Driver for ODBC
--修改監聽檔案,增加下面服務:
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
)
$ vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
)
)
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = /oracle/product/10.2.0/db_1)
(SID_NAME = test)
(ENVS=LD_LIBRARY_PATH = /oracle/product/10.2.0/db_1/lib32:/usr/lib:/oracle
/product/10.2.0/db_1/lib)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
--修改tnsnames.ora
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID= test)
)
(HS=OK)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID= test)
)
(HS=OK)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
--重啟監聽,要有test服務
$ lsnrctl stop
$ lsnrctl start
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, 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
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, 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
--tnsping測試服務
$ tnsping test
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)(HOST = 24.1.20.30)(PORT = 1521))) (CONNECT_DATA = (SID= test)) (HS=OK))
OK (0 msec)
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =(PROTOCOL = TCP)(HOST = 24.1.20.30)(PORT = 1521))) (CONNECT_DATA = (SID= test)) (HS=OK))
OK (0 msec)
--建立dblink
CREATE PUBLIC DATABASE LINK mysql CONNECT TO "root" IDENTIFIED BY "laopo" USING 'test';
--測試
select * from where "id"=1;
至此,單節點配置完成。
rac環境下每個節點都需要配置,和單例項唯一的區別是監聽的配置,我試著多種方式去配置監聽,但都沒有監聽到hsodbc的test服務,最後透過netca選擇single node configuration建立監聽來監聽test服務才成功,注意監聽埠改成1522
rac環境節點1的監聽檔案如下:
$vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER_RAC01 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER_RAC01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1521)(IP = FIRST))
)
)
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(PROGRAM=hsodbc)
(ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1)
(SID_NAME=test)
(ENVS=LD_LIBRARY_PATH=/opt/ora10g/product/10.2.0/db_1/lib32:/lib:/usr/lib:
/opt/ora10g/product/10.2.0/db_1/lib)
)
)
(SID_LIST =
(SID_DESC =
(PROGRAM=hsodbc)
(ORACLE_HOME=/opt/ora10g/product/10.2.0/db_1)
(SID_NAME=test)
(ENVS=LD_LIBRARY_PATH=/opt/ora10g/product/10.2.0/db_1/lib32:/lib:/usr/lib:
/opt/ora10g/product/10.2.0/db_1/lib)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 24.1.20.246)(PORT = 1522))
)
)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31444259/viewspace-2138959/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7OracleMySql
- 配置Oracle DBlink連線MySQL庫OracleMySql
- 透過Amoeba連線mysql報錯MySql
- mysql dblink 連結mysql庫MySql
- Oracle透過gateway連線sqlserver2008OracleGatewaySQLServer
- 如何透過DBeaver 連線 TDengine?
- mysql 連線oracleMySqlOracle
- Oracle->Mysql dblink 建立詳細過程OracleMySql
- Oracle19c dblink連結mysql8.0OracleMySql
- Oracle建立dblink MySQLOracleMySql
- 記錄一次spark連線mysql遇到的問題SparkMySql
- 求救:jdbc與mysql透過Servlet操作後連線關閉不了JDBCMySqlServlet
- ORACLE通過透明閘道器建dblink連線Postgresql的幾個問題OracleSQL
- 解決不能透過mysql.sock連線MySQL問題的辦法(轉)MySql
- C#/C++ 透過ODBC連線OceanBase Oracle租戶C#C++Oracle
- mysql 通過IP連線MySql
- Oracle DBLink連線數過多的問題(Ora-02020)Oracle
- 記一次實戰滲透
- adb透過wifi連線手機WiFi
- MySQL筆記3——內連線/外連線、多表連線MySql筆記
- oracle通過透明閘道器連線mysql的配置OracleMySql
- WEKA連線MySQL,Oracle,SQLServerMySqlOracleServer
- oracle 觸發器實現禁止在資料庫中建立dblink ---透過觸發器實現Oracle觸發器資料庫
- 通過跳板機連線MySQLMySql
- SSH 透過跳板機連線目標機
- 樹莓派透過nmcli命令連線WIFI樹莓派WiFi
- Oracle 19c透過SCAN連線ORA-12520Oracle
- ODBC客戶端連線ORACLE透過PowerDesigner匯出表ER圖客戶端Oracle
- Ruby實踐—連線MysqlMySql
- MYSQL 連線登入過程分析MySql
- JSP透過JDBC與Oracle相連 (轉)JSJDBCOracle
- 重建控制檔案的一次實施過程
- oracle資料庫透過sqlplus連線的幾種方式介紹Oracle資料庫SQL
- OceanBase學習之路7|透過 MySQL 客戶端連線 OceanBase 租戶MySql客戶端
- powerbuildr中如何使用dblink連線的表UI
- Oracle 透明閘道器連線MySQLOracleMySql
- Oracle 透過透明閘道器 訪問 mysqlOracleMySql
- Oracle/MySQL透過odbc訪問PostgreSQL for LightDBOracleMySql