Oracle gateway与多数据源SqlServer配置

一、静默安装

首先，上Oracle官网下载Gateway安装包，地址：https://edelivery.oracle.com/osdc/faces/SoftwareDelivery 找自己对应的操作系统及数据库版本下载，上传至数据库服务器.

cd response
vim tg.rsp
主要修改 以下内容
UNIX_GROUP_NAME="oinstall"                  #用户组名称
FROM_LOCATION="../stage/products.xml"      #可加可不加
ORACLE_HOME="/u01/app/gateway/product/11.2.0/dbhome_1"  #安装目录，我这里把gateway和oracle分开了
ORACLE_BASE="/u01/app/oracle"           #oracle base目录
ORACLE_HOME_NAME="oraDg11g_home1"    #自己起个名
oracle.tg:DEPENDENCY_LIST={"oracle.rdbms.tg4msql:11.2.0.1.0","oracle.rdbms.hsodbc:11.2.0.1.0"} #这里选择装sqlserver 和 odbc
oracle.rdbms.tg4msql:sl_returnVal={"192.168.1.66","1433","MSSQLSERVER","test"} #这里是sqlserver的配置

执行完就Ok,安装成功，接下来开始配置~

二、透明网关配置

1.找到gateway安装目录，进入dg4msql/admin,查看sqlserver连接配置有没有错
cd /u01/app/gateway/product/11.2.0/dbhome_1/dg4msql/admin
cat initdg4msql.ora

HS_FDS_CONNECT_INFO=[192.168.1.66]:1433//test   #这里默认生成就长这样，暂时先别改，曾经在windows下这样配报错，后来改成HS_FDS_CONNECT_INFO=192.168.1.41//zlpt_export这种才好使，但是这次linux下原装的才好使~不知为毛，诡异
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2、修改监听配置文件，因为我这里gateway和oracle在一台机器上，使用原来的监听就行，不用再创建一个新监听，改配置之前记得备份，养成好习惯
cd /u01/app/gateway/product/11.2.0/dbhome_1/network/admin
cp listener.ora listener.ora.bak
vim listener.ora

#orcl是原有实例，dg4msql是透明网关配置，注意路径分别是各自的home,dg4msql使用PROGRAM
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_NAME=ORCL)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         (SID_NAME=orcl)
      )

      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=/u01/app/gateway/product/11.2.0/dbhome_1)
         (PROGRAM=dg4msql)
      )
  )
ADR_BASE_LISTENER = /u01/app/oracle

3.修改tnsnames.ora文件，同样改之前先备份
cp tnsnames.ora tnsnames.ora.bak
vim tnsnames.ora

``
#本机ip+监听的端口`

dg4msql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.134)(PORT=1521))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  )
4.重启监听lsnrctl stop lsnrctl start,可以看到orcl和dg4msql都正常启动

5、使用tnsping测试一下tnsping  dg4msql

6、创建dblink进行测试

create public database link test_lk connect to sa identified by "password" using 'dg4msql';

select * from t_test@test_lk

三、多个SqlServer数据源配置

1.回到gateway安装目录下，将initdg4msql.ora拷贝一份initdg4msql2.ora,并修改其数据连接配置(别问我为啥起个2的名称~oracle官网也这么起的…)

 cd /u01/app/gateway/product/11.2.0/dbhome_1/dg4msql/admin
  #将配置文件copy一份
  cp initdg4msql.ora initdg4msql2.ora
  vim initdg4msql2.ora
  #然后修改其中的sqlserver数据库链接
  HS_FDS_CONNECT_INFO=[192.168.1.81]:1488//rent
2.修改oracle监听文件，增加dg4msql2的配置

cd /u01/app/gateway/product/11.2.0/dbhome_1/network/admin
vim listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (GLOBAL_NAME=ORCL)
         (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
         (SID_NAME=orcl)
      )

      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=/u01/app/gateway/product/11.2.0/dbhome_1)
         (PROGRAM=dg4msql)
      )
      
      (SID_DESC=
         (SID_NAME=dg4msql2)
         (ORACLE_HOME=/u01/app/gateway/product/11.2.0/dbhome_1)
         (PROGRAM=dg4msql)
      )

  )
ADR_BASE_LISTENER = /u01/app/oracle
3、修改tnsnames.ora，增加dg4msql2配置
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
dg4msql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.134)(PORT=1521))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  )

dg4msql2  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.134)(PORT=1521))
    (CONNECT_DATA=(SID=dg4msql2))
    (HS=OK)
  )
4、重启监听，可以看到orcl,dg4msql,dg4msql2都已正常启动…
5、创建dblink进行测试
create public database link rent81_lk connect to sa identified by "password" using 'dg4msql2';
select * from t_test@test_lk
select * from active_step@rent81_lk