Oracle gateways连接SqlServer

安装目录：D:\app\Administrator\product\11.2.0\tg_1

注意：1.要与客户端、数据库在同一路径下

注意：2.如果oracle与gateways在同一台电脑上需要更改gateways的端口号1522，oracle已经占用了1521.（必须保证gateways的监听可以起来）

GateWays配置：

initdg4msql.ora文件(D:\app\Administrator\product\11.2.0\tg_1\dg4msql\admin)

HS_FDS_CONNECT_INFO="192.168.43.64;database=ClinicalNursing" （注意版本，sql2008与sql2000不一样，ClinicalNuring为sqlserver中的数据库名称）HS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=testa（sqlserver用户名）HS_FDS_RECOVERY_PWD=qazwsx（sqlserver密码）

listener.ora.sample文件（D:\app\Administrator\product\11.2.0\tg_1\dg4msql\admin）

# This is a sample listener.ora that contains the NET8 parameters that are# needed to connect to an HS AgentLISTENER = (ADDRESS_LIST=      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) )SID_LIST_LISTENER=  (SID_LIST=      (SID_DESC=         (SID_NAME=dg4msql)         (ORACLE_HOME=D:\app\Administrator\product\11.2.0\tg_1)         (PROGRAM=dg4msql)      )  )#CONNECT_TIMEOUT_LISTENER = 0 

tnsnames.ora.sample文件(D:\app\Administrator\product\11.2.0\tg_1\dg4msql\admin)

# This is a sample tnsnames.ora that contains the NET8 parameters that are# needed to connect to an HS Agentdg4msql  =  (DESCRIPTION=    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))    (CONNECT_DATA=(SID=dg4msql))    (HS=OK)  ) 

GateWays监听配置：

listener.ora文件（D:\app\Administrator\product\11.2.0\tg_1\NETWORK\ADMIN）

# listener.ora Network Configuration File: D:\product\11.2.0\tg_1\network\admin\listener.ora# Generated by Oracle configuration tools. SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =     (PROGRAM = dg4msql)     (SID_NAME = ClinicalNursing)     (ORACLE_HOME = D:\app\Administrator\product\11.2.0\tg_1)    )  )     LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1522))      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))    )  ) ADR_BASE_LISTENER = D:\app\Administrator\product\11.2.0\tg_1

tnsnames.ora文件（D:\app\Administrator\product\11.2.0\tg_1\NETWORK\ADMIN）

# This is a sample tnsnames.ora that contains the NET8 parameters that are# needed to connect to an HS Agentdg4msql  =  (DESCRIPTION=(ADDRESS_LIST =    (ADDRESS=(PROTOCOL = TCP)(HOST=localhost)(PORT=1522)))    (CONNECT_DATA=(SERVICE_NAME=ClinicalNursing))    (HS=OK)  ) 

oracle配置

LISTENER.ORA文件（D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN）

SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC =      (SID_NAME = orcl)      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")    )  )LISTENER112 =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = LS--20161128SCU)(PORT = 1522))    )    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))    )  )LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = IPC)(KEY = orcl))    )    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = LS--20161128SCU)(PORT = 1521))    )  )ADR_BASE_LISTENER = D:\app\Administrator\product\11.2.0\dbhome_1\logSID_LIST_LISTENER112 =  (SID_LIST =    (SID_DESC =      (PROGRAM = dg4msql)      (SID_NAME = ClinicalNursing)      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\tg_1)    )  )ADR_BASE_LISTENER112 = D:\app\Administrator\product\11.2.0\tg_1\log

tnsnames.ora文件（D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN）

# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora# Generated by Oracle configuration tools.HIS =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.116)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )HMS =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )ORCL =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))    (ADDRESS = (PROTOCOL = TCP)(HOST = LS--20161128SCU)(PORT = 1521))    (CONNECT_DATA =      (SERVICE_NAME = orcl)    )  )CLINICALNURSING =  (DESCRIPTION =    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.43.243)(PORT=1522))    (CONNECT_DATA=(SID=ClinicalNursing))    (HS=OK)  )LISTENER_ORCL =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

理论上完成以上所有配置既可以访问sql server2008了，实际使用当中还要进行如下配置

以上配置完毕后，重新启动下监听服务。再进入Oracle数据库创建DBLink，
create public database link mssqldb connect to "sa" identified by "admin" using 'dg4msql1';
其中“sa”、“admin”分别为访问SqlServer数据库用户名和密码，“dg4msql1”为之前配置的SID名