配置gateways从oracle连sql server 2005

环境：

虚拟机：系统 winxp 32  ip:192.168.2.222

Database :   端口:1521

        win32_11gR2_database  安装目录：d:\app\ORACLE\product\11.2.0\dbhome_1

Gateways:    端口:1522

        win32_11gR2_gateways  安装目录：d:\product\11.2.0\tg_1

主机：  系统 win7 64  ip:192.168.2.121

Sqlserver 2005:

        1.SQLEXPR_CHS.EXE(配置工具)                 

        2.SQLServer2005_SSMSEE_x64.msi  (SQL Server Managerment Studio Express)               

        3.SQLServer2005SP3-KB955706-x64-CHS.exe (x64补丁包)   

        4.SQLServer2005SP3-KB955706-x86-CHS.exe (x86补丁包)

配置：

一、配置gateways的实例，这里的实例下面会用在database的tnsnames.ora中。

进入D:\product\11.2.0\tg_1\dg4msql\admin，配置initdg4msql.ora文件如下

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server


#
# HS init parameters
#
HS_FDS_CONNECT_INFO=192.168.2.121:1433//master
HS_FDS_TRACE_LEVEL=ODBC
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

其中，HS_FDS_CONNECT_INFO的配置格式，下面是官方文档所示

HS_FDS_CONNECT_INFO= host_name/[instance_name][/database_name]

Where:

Variable	Description
host_name	is the host name or IP address of the machine hosting the SQL Server database.
instance_name	is the instance of SQL Server running on the machine.
database_name	is the SQL Server Database database name.

Both instance_name and database_name are optional. If instance_name is omitted and database_name is provided, the slash (/) is required. This can be shown as follows:

HS_FDS_CONNECT_INFO= host_name//database_name

二、配置gateways的listener.ora

进入D:\product\11.2.0\tg_1\NETWORK\ADMIN配置listener.ora,这个文件的范本是D:\product\11.2.0\tg_1\dg4msql\admin\listener.ora.sample，我们稍加修改就可以，配置后的listener.ora如下：

# 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=
         (SID_NAME=dg4msql) 
         (ORACLE_HOME=D:\product\11.2.0\tg_1)
         (PROGRAM=dg4msql)
      )
  )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 2a6b83753b05412)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )


ADR_BASE_LISTENER = D:\product\11.2.0\tg_1

官方文档的描述：

To direct the Oracle Net Listener to start the gateway in response to incoming connection requests, add an entry to the listener.ora file.

Note:

You must use the same SID value in the listener.ora file and the  tnsnames.ora file which will be configured in the next step.

SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC= 
         (SID_NAME=gateway_sid)  ---注意这点的注释
         (ORACLE_HOME=oracle_home_directory)
         (PROGRAM=dg4msql)  ---注意这点的注释
      )
   )

Where:

Variable	Description
gateway_sid	specifies the SID of the gateway and matches the gateway SID specified in the connect descriptor entry in thetnsnames.ora file.
oracle_home_directory	specifies the Oracle home directory where the gateway resides.-----------------注意这一点
dg4msql	specifies the executable name of the Oracle Database Gateway for SQL Server.

配置后重启gateways监听

You must stop and restart the Oracle Net Listener to initiate the new settings, as follows:

1. From the Start menu, select Settings, Control Panel and then select Services.

2. Select the Oracle Net Listener service for the gateway.

3. If the service is already running, click Stop to stop it.

4. Click Start to start or restart the service.

三、配置database的tnsnames.ora

进入D:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN配置tnsnames.ora,添加：

dg4msql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.222)(PORT=1522))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  ) 

官方文档对于这部分的解释：

Edit the tnsnames.ora file to add a connect descriptor for the gateway. The following is a syntax of the Oracle Net entry using the TCP/IP protocol:

connect_descriptor=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=TCP)
         (HOST=host_name) ---注意这点的注释
         (PORT=port_number)
      )
      (CONNECT_DATA=
         (SID=gateway_sid))---注意这点的注释
      (HS=OK))

Where:

Variable	Description
connect_descriptor	is the description of the object to connect to as specified when creating the database link, such as dg4msql. Check the sqlnet.ora file in the Oracle database's ORACLE_HOME for the following lines: names.directory_path = (TNSNAMES, HOSTNAME) names.default_domain = world name.default_zone = world Note: If the Oracle database is on Microsoft Windows, the file is ORACLE_HOME\network\admin\sqlnet.ora. If the sqlnet.ora file has these lines, connect_descriptor must end with the extension .world.
TCP	is the TCP protocol used for TCP/IP connections.
host_name	specifies the machine where the gateway is running.
port_number	matches the port number used by the Oracle Net Listener that is listening for the gateway. The Oracle Net Listener's port number can be found in the listener.ora file used by the Oracle Net Listener. See "Syntax of listener.ora File Entries".
gateway_sid	specifies the SID of the gateway and matches the SID specified in the listener.ora file of the Oracle Net Listener that is listening for the gateway. See"Configure Oracle Net Listener for the Gateway" for more information.
(HS=OK)	specifies that this connect descriptor connects to a non-Oracle system.

四、创建DBlink

CREATE PUBLIC DATABASE LINK ora_sql CONNECT TO
"sa" IDENTIFIED BY "123456" USING '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.2.222)(PORT=1522))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  )';

五、配置SQL SERVER 2005的端口（此步是在用透明网关DBLINK查询sqlserver数据库报ORA-28500错误的时候使用）

如果报错

1.查看1433端口是否打开

netstat -a

如果没有

2.打开sql server 的SQL Server Configuration Manager

1）.配置sql server 2005 网络管理/sqlexpress的协议

    协议：全部选“是”

    Ip 地址：将TCP动态端口全被设为“空”，TCP端口全部设成：1433 

2）.配置sql native client配置/客户端协议

    TCP/IP设为：已启用

    协议：默认端口设为 1433，已启用设为：是