通過透明網(wǎng)關(guān)使Linux上Oralcle鏈接SQL.doc

方法一：注意：確保外部可以通過命令telnet IP Port連通.1. 準(zhǔn)備:一臺(tái)SQLServer主機(jī) OS : windows2003 + SqlServer2000 ip: 10.86.16.27一臺(tái)透明網(wǎng)關(guān)主機(jī) OS : windows2003 ip: 10.86.184.250一臺(tái)Oracle10g數(shù)據(jù)庫(kù) OS: Linux + Oracle 10g ip:10.86.17.1002.在透明網(wǎng)關(guān)主機(jī)上安裝10201_gateways_win32.zip :運(yùn)行Setup進(jìn)行安裝, 選擇oracle transparent gateway for microsoft sql server .并完成安裝.在安裝目錄下D:oracleproduct10.2.0tg_1tg4msqladmin 新建名稱為init.ora的文件 , 可以隨便起名.這里我起名為:inithowey.ora 內(nèi)容如下:10.86.16.27為SQlServer主機(jī)IP .Smartit為SQLServer數(shù)據(jù)庫(kù)名稱:HS_FDS_CONNECT_INFO=SERVER=SERVER=10.86.16.27;DATABASE=SmartITHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVER3.在透明網(wǎng)關(guān)主機(jī)上配置listener.ora , 所在位置如下: D:oracleproduct10.2.0tg_1NETWORKADMIN 內(nèi)容如下:SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(SID_NAME = PLSExtProc)(ORACLE_HOME = D:oracleproduct10.2.0tg_1)(PROGRAM = extproc)(SID_DESC=(SID_NAME=howey)(ORACLE_HOME=D:oracleproduct10.2.0tg_1)(PROGRAM=tg4msql)LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521)(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)4. 啟動(dòng)windows透明網(wǎng)關(guān)監(jiān)聽D:oracleproduct10.2.0tg_1BINlsnrctl startLSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 31-3月 -2009 15:50:14Copyright (c) 1991, 2005, Oracle. All rights reserved.正在啟動(dòng) tnslsnr: 請(qǐng)稍后.TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production系統(tǒng)參數(shù)檔案是 D:oracleproduct10.2.0tg_1networkadminlistener.ora日志信息寫入 D:oracleproduct10.2.0tg_1networkloglistener.log監(jiān)聽之處: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.86.184.250)(PORT=1521)監(jiān)聽之處: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=.pipeEXTPROC0ipc)連線至 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.86.184.250)(PORT=1521)監(jiān)聽器的狀態(tài)-別名 LISTENER版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production啟動(dòng)日期 31-10月 -2011 15:50:15正常運(yùn)作時(shí)間 0 days 0 hr. 0 min. 1 sec追蹤層次 off安管 ON: Local OS AuthenticationSNMP OFF監(jiān)聽器參數(shù)檔案 D:oracleproduct10.2.0tg_1networkadminlistener.ora監(jiān)聽器日志文件 D:oracleproduct10.2.0tg_1networkloglistener.log監(jiān)聽終止點(diǎn)摘要. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.86.184.250)(PORT=1521) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=.pipeEXTPROC0ipc)服務(wù)摘要.服務(wù) PLSExtProc 有 1 個(gè)執(zhí)行處理. 執(zhí)行處理 PLSExtProc, 狀態(tài) UNKNOWN, 有 1 個(gè)此服務(wù)的處理程序.服務(wù) howey 有 1 個(gè)執(zhí)行處理. 執(zhí)行處理 howey, 狀態(tài) UNKNOWN, 有 1 個(gè)此服務(wù)的處理程序.命令順利完成 5在Oracle數(shù)據(jù)庫(kù)上配置tnsname.ora文件:HOWEY =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521)(CONNECT_DATA = (SID = howey)(HS = OK)HOWEY =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521)(CONNECT_DATA = (SID = howey)(HS = OK)測(cè)試連通性:oracleqhdtest admin$ tnsping howeyTNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-OCT-2011 16:54:20Copyright (c) 1997, 2005, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521) (CONNECT_DATA = (SID = howey) (HS = OK)OK (0 msec)6.創(chuàng)建數(shù)據(jù)庫(kù)連接SYSqhdtest:SQL create database link smart connect to sa identified by sasa using howey;7. 在Oracle上測(cè)試數(shù)據(jù)庫(kù)連接:SYSqhdtest:SQL select count(*) from dbo.sysobjectssmart;COUNT(*)-624測(cè)試成功.附過程中出現(xiàn)的一些錯(cuò)誤總結(jié):注意點(diǎn)1：1、SYSqhdtest:SQL select count(*) from dbo.sysobjectssmart_link;select count(*) from dbo.sysobjectssmart_linkERROR at line 1:ORA-28545: error diagnosed by Net8 when connecting to an agentUnable to retrieve text of NETWORK/NCR message 65535ORA-02063: preceding 2 lines from SMART_LINK答案：其實(shí)此錯(cuò)誤是由于Oracle和透明網(wǎng)關(guān)通信出現(xiàn)錯(cuò)誤.可打開透明網(wǎng)關(guān)1521端口解決.并使用tnsping命令驗(yàn)證.如果三者都裝在一臺(tái)機(jī)器上,也一定要保證網(wǎng)絡(luò)連通,因?yàn)檫@樣的訪問必須要通過網(wǎng)關(guān).網(wǎng)上很多關(guān)于此錯(cuò)誤的解決方式:用如下語句建立DBlink, 注:用戶名和密碼要用”雙引號(hào)SYSqhdtest:SQL create database link smart_link connect to sa identified by2、sasa using howey; 其實(shí)只有訪問SQLServer2005時(shí),密碼必須要用雙引號(hào).用戶名不用在訪問SQLServer2000中用戶名,密碼加不加雙引號(hào)沒有任何關(guān)系.注意點(diǎn)2:SYSqhdtest:SQL select count(*) from dbo.sysobjectssmart_link;ERROR at line 1:ORA-02068: following severe error from SMART_LINKORA-03135: connection lost contact最終解決:數(shù)據(jù)庫(kù)的tnsname.ora修改為:HOWEY =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.86.184.250)(PORT = 1521)(CONNECT_DATA = (SID = howey)(HS = OK)由于以上tnsname.ora文件是手工編寫,括號(hào)錯(cuò)誤，導(dǎo)致出現(xiàn)ora-02068 和 03135這2個(gè)找不到原因的ora錯(cuò)誤.注意點(diǎn)3:在訪問SQLServer2005的時(shí)候出現(xiàn)了一個(gè)問題,就是在oracle執(zhí)行查詢SQLServer過程中沒有結(jié)果返回,就是錯(cuò)誤信息也沒有.解決方法：運(yùn)行$ORACLE_HOME/rdbms/admin/caths.sql 腳本. 此腳本為創(chuàng)建HS所需表及視圖表方法二：下載對(duì)應(yīng)版本的Database Gateway，ora 11g linux_x86 32位版的大概440M。然后安裝，一切默認(rèn)設(shè)置，這就不細(xì)說了。然后開始配置，首先是如果你安裝沒有問題會(huì)在你的ora安裝目錄的有dg4msql/admin文件夾，我們需要配置一個(gè)init+SID.ora的文件，其中SID是你將要連接的數(shù)據(jù)庫(kù)的實(shí)例名，自己雖便起一個(gè)只要不和現(xiàn)在有的重就行了。這個(gè)文件有一個(gè)模板，你可以拷一份initdg4msql.ora，然后改一下名和里面的配置就OK了。大致內(nèi)容如下：# that are needed for the Database Gateway for Microsoft SQL Server # HS init parameters#HS_FDS_CONNECT_INFO=192.2.11.188# alternate connect format is hostname/serverinstance/databasenameHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVERHS_FDS_TRACE_LEVEL=DEBUG其中HS_FDS_CONNECT_INFO=192.2.11.188這個(gè)比較重要要。配置這個(gè)錯(cuò)誤會(huì)發(fā)生一個(gè)ORA-28513 internal error in heterogeneous remote agent的錯(cuò)誤，具體這個(gè)參數(shù)的配置要求可以看下載的包里的文檔，說的很細(xì)。第二步配置network/admin目錄下的listener.ora tnsnames.ora兩個(gè)文件，首選配置listener.ora文件，我的文件如下：# listener.ora Network Configuration File: /mnt/sda5/oracle/oracle/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(PROGRAM = tg4msql)(ORACLE_HOME = /mnt/sda5/oracle/oracle)(SID_NAME = mssql)其中，SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(PROGRAM = tg4msql)(ORACLE_HOME = /mnt/sda5/oracle/oracle)(SID_NAME = mssql)這是我們需要添加的。這兩個(gè)參數(shù)：(ORACLE_HOME = /mnt/sda5/oracle/ora