静默安装Oracle gateway与多数据源SqlServer配置
前言
最近，单位生产环境Oracle要与SqlServer数据库进行数据同步，由于生产环境Oracle没有图形化界面，遂采用静默安装方式，但是度娘一圈没看到靠谱的Gateway 静默安装方案，上Oracle官网翻了一下文档，也没讲的太详细，无奈~开始自己研究之，将整个安装配置过程分享给大家，希望对大家有点用.
一、静默安装
首先，上Oracle官网下载Gateway安装包，地址：https://edelivery.oracle.com/osdc/faces/SoftwareDelivery 找自己对应的操作系统及数据库版本下载，上传至数据库服务器，解压之~过程省略。
下面上正菜：
- *解压安装包： unzip V17533-01.zip ，得到gateways目录，进入之~
- 修改静默安装配置文件:
cd response
vim tg.rsp
主要修改 以下内容，修改时注意格式和自己本地的路径，话说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的配置
- 回到上层目录，使用Oracle用户开始静默安装，注意这里的tg.rsp路径最好写绝对路径，相对路径丫报错
./runInstaller -silent -noconfig -responseFile /soft/gateways/response/tg.rsp
安装前oracle会自动进行相关检查，如果条件不符会给出提示，按报错编码度娘吧~~
安装过程如下：
[oracle@localhost gateways]$ ./runInstaller -silent -noconfig -responseFile /soft/gateways/response/tg.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 410305 MB Passed
Checking swap space: must be greater than 150 MB. Actual 20390 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-05-03_08-52-09PM. Please wait ...[oracle@localhost gateways]$ Oracle Universal Installer, Version 11.2.0.1.0 Production
Copyright (C) 1999, 2009, Oracle. All rights reserved.
You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2022-05-03_08-52-09PM.log
.................................................................................................... 100% Done.
Loading Product Information
.................................................................................................... 100% Done.
Analyzing dependencies
........................................................................................................................ 100% Done.
-----------------------------------------------------------------------------
Summary
Global Settings
Source: /soft/gateways/stage/products.xml
Oracle Home: /u01/app/gateway/product/11.2.0/dbhome_1 (oraDg11g_home1)
Installation Type: Custom
Product Languages
English
Space Requirements
/ Required 1.36GB (includes 132MB temporary) : Available 400.36GB
New Installations (67 products)
Oracle Database Gateways 11.2.0.1.0
Oracle Database Gateway for Microsoft SQL Server 11.2.0.1.0
Oracle Database Gateway for ODBC 11.2.0.1.0
Oracle Net Listener 11.2.0.1.0
Oracle Internet Directory Client 11.2.0.1.0
SQL*Plus 11.2.0.1.0
Oracle Netca Client 11.2.0.1.0
Oracle Net 11.2.0.1.0
Oracle Database Utilities 11.2.0.1.0
Generic Connectivity Common Files 11.2.0.1.0
Oracle Recovery Manager 11.2.0.1.0
Oracle JDBC/THIN Interfaces 11.2.0.1.0
Assistant Common Files 11.2.0.1.0
Installation Common Files 11.2.0.1.0
Required Support Files 11.2.0.1.0
Parser Generator Required Support Files 11.2.0.1.0
Agent Required Support Files 10.2.0.4.2
RDBMS Required Support Files 11.2.0.1.0
RDBMS Required Support Files for Instant Client 11.2.0.1.0
XDK Required Support Files 11.2.0.1.0
SQL*Plus Required Support Files 11.2.0.1.0
HAS Common Files 11.2.0.1.0
Buildtools Common Files 11.2.0.1.0
Oracle LDAP administration 11.2.0.1.0
Oracle Globalization Support 11.2.0.1.0
Platform Required Support Files 11.2.0.1.0
Enterprise Manager plugin Common Files 11.2.0.1.0
Oracle Locale Builder 11.2.0.1.0
Oracle Clusterware RDBMS Files 11.2.0.1.0
Cluster Verification Utility Common Files 11.2.0.1.0
Oracle Wallet Manager 11.2.0.1.0
Oracle Security Developer Tools 11.2.0.1.0
XML Parser for Java 11.2.0.1.0
Enterprise Manager Minimal Integration 11.2.0.1.0
Oracle Notification Service 11.2.0.0.0
Oracle Database User Interface 2.2.13.0.0
Oracle Net Required Support Files 11.2.0.1.0
SQL*Plus Files for Instant Client 11.2.0.1.0
SSL Required Support Files for InstantClient 11.2.0.1.0
Oracle Help For Java 4.2.9.0.0
Oracle Java Client 11.2.0.1.0
Oracle JDBC/OCI Instant Client 11.2.0.1.0
Oracle Globalization Support 11.2.0.1.0
Secure Socket Layer 11.2.0.1.0
LDAP Required Support Files 11.2.0.1.0
Oracle JFC Extended Windowing Toolkit 4.2.36.0.0
Oracle Ice Browser 5.2.3.6.0
Oracle Code Editor 1.2.1.0.0I
Perl Interpreter 5.10.0.0.1
Perl Modules 5.10.0.0.1
Expat libraries 2.0.1.0.1
Oracle Extended Windowing Toolkit 3.4.47.0.0
Oracle Core Required Support Files 11.2.0.1.0
Bali Share 1.1.18.0.0
Oracle RAC Required Support Files-HAS 11.2.0.1.0
Precompiler Required Support Files 11.2.0.1.0
Deinstallation Tool 11.2.0.1.0
Enterprise Manager Common Files 10.2.0.4.2
Oracle RAC Deconfiguration 11.2.0.1.0
Oracle DBCA Deconfiguration 11.2.0.1.0
Oracle Database Deconfiguration 11.2.0.1.0
Enterprise Manager Common Core Files 10.2.0.4.2
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Oracle Universal Installer 11.2.0.1.0
Oracle One-Off Patch Installer 11.2.0.0.2
Installer SDK Component 11.2.0.1.0
Sun JDK 1.5.0.17.0
-----------------------------------------------------------------------------
Installation in progress (Tuesday, May 3, 2022 8:52:19 PM SGT)
............................................................... 19% Done.
............................................................... 38% Done.
............................................................... 57% Done.
................................................. 72% Done.
Install successful
Linking in progress (Tuesday, May 3, 2022 8:52:39 PM SGT)
Link successful
Setup in progress (Tuesday, May 3, 2022 8:52:51 PM SGT)
................ 100% Done.
Setup successful
End of install phases.(Tuesday, May 3, 2022 8:53:41 PM SGT)
WARNING:
The following configuration scripts need to be executed as the "root" user.
/u01/app/gateway/product/11.2.0/dbhome_1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
Starting to execute configuration assistants
The following configuration assistants have not been run. This can happen for following reasons - either root.sh is to be run before config or Oracle Universal Installer was invoked with the -noConfig option.
--------------------------------------
The "/u01/app/gateway/product/11.2.0/dbhome_1/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
The "/u01/app/gateway/product/11.2.0/dbhome_1/cfgtoollogs/configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
--------------------------------------
The installation of Oracle Database Gateways was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-05-03_08-52-09PM.log' for more details.
- 看到successful说明安装成功，按照提示用root用户执行sh脚本
/u01/app/gateway/product/11.2.0/dbhome_1/root.sh
执行完就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都正常启动
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-MAY-2022 21:48:22
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 03-MAY-2022 21:48:22
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
5、使用tnsping测试一下tnsping dg4msql
6、创建dblink进行测试
#这一步是最容易报错的，出错时如果报ora28500什么的可以尝试修改initdg4msql.ora里的sqlserver配置来进行调试
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都已正常启动…
[oracle@localhost admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 03-MAY-2022 22:42:06
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 03-MAY-2022 22:42:06
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "dg4msql" has 1 instance(s).
Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
Service "dg4msql2" has 1 instance(s).
Instance "dg4msql2", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
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
可以看到两个dblink均能查询成功，至此大功告成！
