安装目录: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=OFF
HS_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 Agent
LISTENER =
(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
# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
dg4msql =
(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 Agent
dg4msql =
(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\log
SID_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名
create public database link mssqldb connect to "sa" identified by "admin" using 'dg4msql1';
其中“sa”、“admin”分别为访问SqlServer数据库用户名和密码,“dg4msql1”为之前配置的SID名