oracle配置透明網(wǎng)關(guān)連接SQLServer的流程

1、一、 安裝環(huán)境1. 當(dāng)前使用數(shù)據(jù)庫操作系統(tǒng)：Windows Server 2008 x64數(shù)據(jù)庫版本：oracle 10g 10204數(shù)據(jù)庫實(shí)例：CRDS2. 目標(biāo)數(shù)據(jù)庫操作系統(tǒng)：Windows Server 2008 x64數(shù)據(jù)庫版本：SQLServer2008數(shù)據(jù)庫實(shí)例：IA3. 透明網(wǎng)關(guān)操作系統(tǒng)：Windows Server 2003數(shù)據(jù)庫版本：oracle 9i 92010二、 安裝流程1. Transparent Gateway for SQL Server安裝從Oracle 9i數(shù)據(jù)庫安裝光盤setup.exe安裝,選擇安裝客戶端安裝類型選擇：管理員安裝完客戶端后，重新運(yùn)行setu

2、p.exe，安裝產(chǎn)品選擇Oracle 9i Database 9.2.0.1.0, 安裝類型選擇"自定義",安裝組件選擇Oracle Net Services和Oracle Transparent Gateways, 并在此項下選擇Oracle Transparent Gateway for Microsoft SQL Server, 安裝過程中可以不設(shè)置連接到SQL Server的信息.如下圖所示：以下的IP地址及數(shù)據(jù)庫以實(shí)際環(huán)境為準(zhǔn)2. 修改透明網(wǎng)關(guān)配置文件編輯%ORACLE_HOME%tg4msqladmininit%ORACLE_SID%.ora, 該文件包含了TG

3、 for SQL Server的配置信息, 其中%ORACLE_SID%是給TG的"SID", 默認(rèn)為tg4msql. 修改文件中的行：其中SERVER后為SQL Server所在的服務(wù)器名稱或ip地址,Database為連接到的數(shù)據(jù)庫名稱。 當(dāng)然，如果在SQL Server服務(wù)器上，有多個sql server實(shí)例，則使用服務(wù)器名實(shí)例名的方式設(shè)置上面的SERVER值，如：HS_FDS_CONNECT_INFO="SERVER=192.168.3.60IA;DATABASE=FCDB_BOND"。3. 配置透明網(wǎng)關(guān)監(jiān)聽編輯%ORACLE_HOME%netw

4、orkadminlistener.ora, 編輯對應(yīng)listener的SID_LIST%ORACLE_SID%必須為第二布中設(shè)置的SID, 默認(rèn)值為tg4msql. 修改listener.ora文件后需重啟listener使修改生效.如果沒有l(wèi)istener.ora文件，請用Net Configuration Assistant或Net Manager新建一個LISTENER.以上操作都是在Transparent Gateway所在機(jī)器上。在當(dāng)前使用數(shù)據(jù)庫服務(wù)器上：編輯需要連接到透明網(wǎng)關(guān)的Oracle Server的$ORACLE_HOMEnetworkadmintnsnames.ora, 例

5、如:HOST指向Transparent Gateway所在的機(jī)器的IP，Service_name為第3步中的SID_NAME。 使用tnsping測試服務(wù)名是否連通，命令：tnsping tg4msql。如果不通，請檢查防火墻及網(wǎng)絡(luò)配置。5. 在Oracle數(shù)據(jù)庫中建立連接, 指向TG for SQL Server其中tg4msql是tnsnames中建立的連接字符串。6. 確保CATHS.sql已被運(yùn)行。以SYS用戶連接上去，查看有沒有SYS.HS_FDS_CLASS，如果沒有，運(yùn)行$ORACLE_HOME/RDBMS"ADMIN"CATHS.SQL。7. 測試在目標(biāo)數(shù)據(jù)

6、庫中建立表t，插入幾條記錄如運(yùn)行select * from tfcdb。三、 本次配置文件透明網(wǎng)關(guān)當(dāng)前使用數(shù)據(jù)庫當(dāng)前使用數(shù)據(jù)庫四、 問題解決及注意事項【錯誤信息】【錯誤處理】訪問dblink時說明:這是基于網(wǎng)關(guān)的方式，因此不能在斷開網(wǎng)絡(luò)的情況下本地的oracle連接本地的SQLServer?！惧e誤信息】【錯誤處理】在建立DBlink時，SQL Server的用戶名和密碼必須小寫，而且要加雙引號【錯誤信息】【錯誤處理】以SYS運(yùn)行exec dbms_hs.replace_base_caps(531, 531, 'First/Last function');【錯誤信息】【錯誤處理】

7、【錯誤信息】【錯誤處理】以上信息表示由于SQlServer使用了"僅 Windows"的身份驗(yàn)證方式，因此用戶無法使用SQLServer的登錄帳戶（例如 sa )進(jìn)行連接，解決方法如下設(shè)置允許SQLServer身份登錄 (基本上這個很有用)操作步驟：1.在企業(yè)管理器中，展開"SQL Server組"，鼠標(biāo)右鍵點(diǎn)擊SQL Server服務(wù)器的名稱2.選擇"屬性"3.再選擇"安全性"選項卡4.在"身份驗(yàn)證"下，選擇"SQL Server和Windows"5.確定,并重新啟動SQL

8、 Server服務(wù)。一定要重新啟動sql服務(wù)，關(guān)閉sql，重新進(jìn)入，并不是重新啟動sql服務(wù)?！惧e誤信息】【錯誤處理】【以下內(nèi)容為實(shí)踐過程中從網(wǎng)上拷貝下來的資料】【報錯信息處理】Common Errors and Solutions Associated with Heterogeneous Services and GenericConnectivityThe following list contains some of the most common errors associated with setting up Heterogeneous Services and Generic

9、Connectivity.ORA-28509: unable to establish a connection to non-Oracle systemORA-02063: preceding line from HSCause: This indicates a problem with the Oracle configuration files.Action:Make sure the HOST parameter in the tnsnames.ora file is correct Make sure the PORT number is correct Make sure the

10、 SID name is correct in both the TNSNAMES.ORA and LISTENER.ORA ORA-02068: following severe error from HSORA-03114: not connected to ORACLECause: This indicates the required syntax for the TNSNAMES.ORA file is not present. Action: (HS=OK) has to be added to the tnsnames.ora file in the DESCRIPTION se

11、ction. ORA-02068: following severe error from HSORA-28511: lost RPC connection to heterogeneous remote agent using %tns_address% Cause: The listener is unable to spawn the HS agent or the agent cannot find the ODBC lib directory.Action: The PROGRAM line in the listener.ora file is incorrect or not s

12、pecified. Make sure LD_LIBRARY_PATH includes the $ODBC_HOME/lib directory. If not, set LD_LIBRARY_PATH and restart the listener.ORA-28500: connection from ORACLE to a non-Oracle system returned this message:Transparent gateway for ODBCH001 The environment variable is not set.ORA-02063: preceding 2 l

13、ines from HSCause: Incorrect parameter settings in the HS init.ora file.Action: Set HS_FDS_CONNECT_INFO in the HS init.ora file to the data source name located in the odbc.ini file.Example: HS_FDS_CONNECT_INFO = MS_SQLServer Wire Protocol Make sure the HS init.ora file exists in the $ORACLE_HOME/hs/

14、admin directory and has the same name as the SID in the LISTENER.ORA.ORA-28500: connection from ORACLE to a non-Oracle system returned this message:Transparent gateway for ODBCH001 The environment variable is not set.ORA-02063: preceding 2 lines from HSCause: Incorrect parameter settings in the HS i

15、nit.ora file.Action: Set HS_FDS_SHAREABLE_NAME to the full path plus filename to the libodbc.so file.ORA-28500: connection from ORACLE to a non-Oracle system returned this message:Transparent gateway for ODBCDRV_InitTdp:(SQL State: 01000; SQL Code: 0)ORA-02063: preceding 2 lines from HSCause: The HS

16、 agent cannot find the odbc.ini file.Action: The ODBCINI variable is not set in the HS init.ora file and needs to be set.ORA-00942: table or view does not exist Transparent gateway for ODBCDRV_OpenTable:DATADIRECTODBC SQL Server DriverSQL ServerInvalid object name '%table%'. SQL State: S0002

17、; SQL Code: 208)ORA-02063: preceding 2 lines from HSCause: The data source in the odbc.ini file has incorrect database information.Action: Consult the Connect for ODBC Reference Guide on how to set the parameters for your datasource.ORA-28500: connection from ORACLE to a non-Oracle system returned t

18、his message:Transparent gateway for ODBCDRV_InitTdp: DATADIRECTODBC SQL ServerDriverlibssclient15General network error. Check your network documentation.(SQL State: 08001; SQL Code: 11)ORA-02063: preceding 2 lines from HSCause: There is a problem at the network layer communicating with the foreign d

19、ata source. Action: Make sure the destination host or IP address and port number are correct for the data source in the odbc.ini file.ORA-28500: connection from ORACLE to a non-Oracle system returned this message:Transparent gateway for ODBCDRV_InitTdp: DATADIRECTODBC SQL Server DriverSQL Server Login failed(SQL State: 28000; SQL Code: 4002)ORA-02063: preceding 3 lines from HSTESTCause: The Oracle database link created for the foreign datasource has either no credentials or incorrect credentials.Action: Recreate the Oracle database link with the proper username and password. Also