当前位置:Gxlcms > 数据库问题 > 配置Oracle Gateway 12连接到SQL server 2014

配置Oracle Gateway 12连接到SQL server 2014

时间:2021-07-01 10:21:17 帮助过:11人阅读

.0.2 Oracle db: 11.2.0.4 + RHEL6.3 Sqlserver: 2014 + Win2012 如果安装在已经安装Oracle相同的目录下,会收到如下提示,无法继续安装。 [INS-32025] The chosen installation conflicts with software already installed the given Oracle home.

二、安装Oracle gateway

1、准备环境

  1. <code class=" hljs bash">$ unzip linuxamd64_12102_gateways.zip
  2. $ mkdir -p /u01/app/gateway
  3. $ mkdir -p /u01/app/gateway/<span class="hljs-number">12.1</span>
  4. $ cp ~/.bash_profile ~/.bash_profile_gw
  5. $ vim ~/.bash_profile_gw <span class="hljs-comment">###编辑新的bash_profile文件</span>
  6. $ more ~/.bash_profile_gw <span class="hljs-comment">###编辑后如下</span>
  7. <span class="hljs-comment"># .bash_profile</span>
  8. <span class="hljs-comment"># Get the aliases and functions</span>
  9. <span class="hljs-keyword">if</span> [ <span class="hljs-operator">-f</span> ~/.bashrc ]; <span class="hljs-keyword">then</span>
  10. . ~/.bashrc
  11. <span class="hljs-keyword">fi</span>
  12. <span class="hljs-comment"># User specific environment and startup programs</span>
  13. TMP=/tmp; <span class="hljs-keyword">export</span> TMP
  14. TMPDIR=<span class="hljs-variable">$TMP</span>; <span class="hljs-keyword">export</span> TMPDIR
  15. ORACLE_HOSTNAME=wms.ycdata.net; <span class="hljs-keyword">export</span> ORACLE_HOSTNAME
  16. ORACLE_UNQNAME=dg4msql; <span class="hljs-keyword">export</span> ORACLE_UNQNAME
  17. ORACLE_BASE=/u01/app/gateway; <span class="hljs-keyword">export</span> ORACLE_BASE
  18. ORACLE_HOME=<span class="hljs-variable">$ORACLE_BASE</span>/<span class="hljs-number">12.1</span>; <span class="hljs-keyword">export</span> ORACLE_HOME
  19. ORACLE_SID=dg4msql; <span class="hljs-keyword">export</span> ORACLE_SID
  20. PATH=/usr/sbin:<span class="hljs-variable">$PATH</span>; <span class="hljs-keyword">export</span> PATH
  21. PATH=<span class="hljs-variable">$ORACLE_HOME</span>/bin:<span class="hljs-variable">$PATH</span>; <span class="hljs-keyword">export</span> PATH
  22. LD_LIBRARY_PATH=<span class="hljs-variable">$ORACLE_HOME</span>/lib:/lib:/usr/lib; <span class="hljs-keyword">export</span> LD_LIBRARY_PATH
  23. CLASSPATH=<span class="hljs-variable">$ORACLE_HOME</span>/jlib:<span class="hljs-variable">$ORACLE_HOME</span>/rdbms/jlib; <span class="hljs-keyword">export</span> CLASSPATH</code>

2、安装配置gateway

  1. <code class=" hljs avrasm">$ source ~/<span class="hljs-preprocessor">.bash</span>_profile_gw
  2. $ env |grep ORACLE
  3. ORACLE_UNQNAME=dg4msql
  4. ORACLE_SID=dg4msql
  5. ORACLE_BASE=/u01/app/gateway
  6. ORACLE_HOSTNAME=wms<span class="hljs-preprocessor">.ycdata</span><span class="hljs-preprocessor">.net</span>
  7. ORACLE_HOME=/u01/app/gateway/<span class="hljs-number">12.1</span>
  8. $ export DISPLAY=<span class="hljs-number">192.168</span><span class="hljs-number">.21</span><span class="hljs-number">.157</span>:<span class="hljs-number">0.0</span>
  9. $ cd gateways/
  10. $ ./runInstaller
  11. 选择for sql server
  12. Oracle Database Gateway for Microsoft SQL Server
  13. Oracle Database Gateway for ODBC (此项可以用于配置访问mysql)
  14. 输入sqlserver连接信息,也可以后续再配置文件initdg4msql<span class="hljs-preprocessor">.ora</span>中修改
  15. <span class="hljs-number">192.168</span><span class="hljs-number">.21</span><span class="hljs-number">.157</span>
  16. <span class="hljs-number">1433</span>
  17. HQ1636
  18. testdb
  19. 安装完毕后,会提示创建监听器,可以直接创建,也可以在安装完毕后再配置,本文是在安装完毕后,通过netmgr进行配置的。
  20. 在通过netmgr配置时,除了配置监听器地址和端口号之外,还需要配置其他服务项:
  21. Program Name dg4msql
  22. SID dg4msql
  23. Oracle Home Directory /u01/app/gateway/<span class="hljs-number">12.1</span>
  24. 与此同时,也可以通过netmgr配置tnsnames<span class="hljs-preprocessor">.ora</span>
  25. $ cd $ORACLE_HOME/network/admin
  26. $ more listener<span class="hljs-preprocessor">.ora</span>
  27. <span class="hljs-preprocessor"># listener.ora Network Configuration File: /u01/app/gateway/12.1/network/admin/listener.ora</span>
  28. <span class="hljs-preprocessor"># Generated by Oracle configuration tools.</span>
  29. LISTENER_GW =
  30. (DESCRIPTION =
  31. (ADDRESS = (PROTOCOL = TCP)(HOST = wms<span class="hljs-preprocessor">.ycdata</span><span class="hljs-preprocessor">.net</span>)(PORT = <span class="hljs-number">1531</span>))
  32. )
  33. SID_LIST_LISTENER_GW =
  34. (SID_LIST =
  35. (SID_DESC =
  36. (PROGRAM = dg4msql)
  37. (SID_NAME = dg4msql)
  38. (ORACLE_HOME = /u01/app/gateway/<span class="hljs-number">12.1</span>)
  39. )
  40. )
  41. ADR_BASE_LISTENER_GW = /u01/app/gateway
  42. <span class="hljs-preprocessor">###查看配置后的tnsnames.ora</span>
  43. $ more tnsnames<span class="hljs-preprocessor">.ora</span>
  44. <span class="hljs-preprocessor"># tnsnames.ora Network Configuration File: /u01/app/gateway/12.1/network/admin/tnsnames.ora</span>
  45. <span class="hljs-preprocessor"># Generated by Oracle configuration tools.</span>
  46. DG4MSQL =
  47. (DESCRIPTION =
  48. (ADDRESS_LIST =
  49. (ADDRESS = (PROTOCOL = TCP)(HOST = wms<span class="hljs-preprocessor">.ycdata</span><span class="hljs-preprocessor">.net</span>)(PORT = <span class="hljs-number">1531</span>))
  50. )
  51. (CONNECT_DATA =
  52. (SERVER = DEDICATED)
  53. (SERVICE_NAME = dg4msql)
  54. )
  55. )
  56. <span class="hljs-preprocessor">###安装完毕,在gateway相应目录下也有对应的配置样例,如下 </span>
  57. $ cd $ORACLE_HOME/dg4msql/admin
  58. $ ls
  59. dg4msql_cvw<span class="hljs-preprocessor">.sql</span> dg4msql_tx<span class="hljs-preprocessor">.sql</span> initdg4msql<span class="hljs-preprocessor">.ora</span> listener<span class="hljs-preprocessor">.ora</span><span class="hljs-preprocessor">.sample</span> tnsnames<span class="hljs-preprocessor">.ora</span><span class="hljs-preprocessor">.sample</span>
  60. <span class="hljs-preprocessor">###这个文件用于配置连接到sqlserver</span>
  61. $ more initdg4msql<span class="hljs-preprocessor">.ora</span>
  62. HS_FDS_CONNECT_INFO=[<span class="hljs-number">192.168</span><span class="hljs-number">.21</span><span class="hljs-number">.157</span>]:<span class="hljs-number">1433</span>//testdb
  63. <span class="hljs-preprocessor"># alternate connect format is hostname/serverinstance/databasename</span>
  64. HS_FDS_TRACE_LEVEL=OFF
  65. HS_FDS_RECOVERY_ACCOUNT=RECOVER
  66. HS_FDS_RECOVERY_PWD=RECOVER</code>

三、测试gateway

  1. <code class=" hljs vhdl">$ lsnrctl start LISTENER_GW
  2. LSNRCTL <span class="hljs-keyword">for</span> Linux: Version <span class="hljs-number">12.1</span><span class="hljs-number">.0</span><span class="hljs-number">.2</span><span class="hljs-number">.0</span> - Production <span class="hljs-keyword">on</span> <span class="hljs-number">08</span>-JAN-<span class="hljs-number">2016</span> <span class="hljs-number">18</span>:<span class="hljs-number">03</span>:<span class="hljs-number">03</span>
  3. Copyright (c) <span class="hljs-number">1991</span>, <span class="hljs-number">2014</span>, Oracle. <span class="hljs-keyword">All</span> rights reserved.
  4. Starting /u01/app/gateway/<span class="hljs-number">12.1</span>/bin/tnslsnr: please <span class="hljs-keyword">wait</span>...
  5. TNSLSNR <span class="hljs-keyword">for</span> Linux: Version <span class="hljs-number">12.1</span><span class="hljs-number">.0</span><span class="hljs-number">.2</span><span class="hljs-number">.0</span> - Production
  6. System parameter <span class="hljs-keyword">file</span> <span class="hljs-keyword">is</span> /u01/app/gateway/<span class="hljs-number">12.1</span>/network/admin/listener.ora
  7. Log messages written <span class="hljs-keyword">to</span> /u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xml
  8. Listening <span class="hljs-keyword">on</span>: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wms.ycdata.net)(<span class="hljs-keyword">PORT</span>=<span class="hljs-number">1531</span>)))
  9. Connecting <span class="hljs-keyword">to</span> (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wms.ycdata.net)(<span class="hljs-keyword">PORT</span>=<span class="hljs-number">1531</span>)))
  10. STATUS <span class="hljs-keyword">of</span> the LISTENER
  11. <span class="hljs-comment">------------------------</span>
  12. <span class="hljs-keyword">Alias</span> LISTENER_GW
  13. Version TNSLSNR <span class="hljs-keyword">for</span> Linux: Version <span class="hljs-number">12.1</span><span class="hljs-number">.0</span><span class="hljs-number">.2</span><span class="hljs-number">.0</span> - Production
  14. Start Date <span class="hljs-number">08</span>-JAN-<span class="hljs-number">2016</span> <span class="hljs-number">18</span>:<span class="hljs-number">03</span>:<span class="hljs-number">03</span>
  15. Uptime <span class="hljs-number">0</span> days <span class="hljs-number">0</span> hr. <span class="hljs-number">0</span> min. <span class="hljs-number">0</span> sec <span class="hljs-comment">--Author : Leshami</span>
  16. Trace Level off <span class="hljs-comment">--Blog : http://blog.csdn.net/leshami </span>
  17. Security <span class="hljs-keyword">ON</span>: Local OS Authentication
  18. SNMP OFF
  19. Listener Parameter <span class="hljs-keyword">File</span> /u01/app/gateway/<span class="hljs-number">12.1</span>/network/admin/listener.ora
  20. Listener Log <span class="hljs-keyword">File</span> /u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xml
  21. Listening Endpoints Summary...
  22. (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wms.ycdata.net)(<span class="hljs-keyword">PORT</span>=<span class="hljs-number">1531</span>)))
  23. Services Summary...
  24. Service <span class="hljs-string">"dg4msql"</span> has <span class="hljs-number">1</span> instance(s).
  25. Instance <span class="hljs-string">"dg4msql"</span>, status UNKNOWN, has <span class="hljs-number">1</span> handler(s) <span class="hljs-keyword">for</span> this service...
  26. The command completed successfully
  27. $ tnsping DG4MSQL
  28. TNS Ping Utility <span class="hljs-keyword">for</span> Linux: Version <span class="hljs-number">12.1</span><span class="hljs-number">.0</span><span class="hljs-number">.2</span><span class="hljs-number">.0</span> - Production <span class="hljs-keyword">on</span> <span class="hljs-number">08</span>-JAN-<span class="hljs-number">2016</span> <span class="hljs-number">18</span>:<span class="hljs-number">29</span>:<span class="hljs-number">51</span>
  29. Copyright (c) <span class="hljs-number">1997</span>, <span class="hljs-number">2014</span>, Oracle. <span class="hljs-keyword">All</span> rights reserved.
  30. Used parameter files:
  31. /u01/app/gateway/<span class="hljs-number">12.1</span>/network/admin/sqlnet.ora
  32. Used TNSNAMES adapter <span class="hljs-keyword">to</span> resolve the <span class="hljs-keyword">alias</span>
  33. Attempting <span class="hljs-keyword">to</span> contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = wms.ycdata.net)(<span class="hljs-keyword">PORT</span> = <span class="hljs-number">1531</span>)))
  34. (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg4msql)))
  35. OK (<span class="hljs-number">0</span> msec)
  36. $ sqlplus WMS_USER/xxx@WMSSERVER
  37. SQL> show user;
  38. USER <span class="hljs-keyword">is</span> <span class="hljs-string">"WMS_USER"</span>
  39. SQL> create public database link mssql connect <span class="hljs-keyword">to</span> robin identified by <span class="hljs-string">"xxx"</span> using <span class="hljs-attribute">‘dg4msql</span>‘;
  40. SQL> <span class="hljs-keyword">select</span> * from tt@dg4msql;
  41. <span class="hljs-keyword">select</span> * from tt@dg4msql
  42. *
  43. ERROR at line <span class="hljs-number">1</span>:
  44. ORA-<span class="hljs-number">28546</span>: connection initialization failed, probable Net8 admin error
  45. ORA-<span class="hljs-number">02063</span>: preceding line from DG4MSQL
  46. 调整DG4MSQL配置,增加(HS=OK)项
  47. DG4MSQL =
  48. (DESCRIPTION =
  49. (ADDRESS_LIST =
  50. (ADDRESS = (PROTOCOL = TCP)(HOST = wms.ycdata.net)(<span class="hljs-keyword">PORT</span> = <span class="hljs-number">1531</span>))
  51. )
  52. (CONNECT_DATA =
  53. (SERVER = DEDICATED)
  54. (SID = dg4msql)
  55. )
  56. (HS=OK)
  57. )
  58. ###再次测试
  59. SQL> <span class="hljs-keyword">select</span> * from tt@dg4msql;
  60. id
  61. <span class="hljs-comment">----------</span>
  62. <span class="hljs-number">1</span></code>

四、简化管理

由于Oracle gateway安装时使用了不同的Oracle Home,因此在启动gateway监听时,需要切换环境变量。因此可以直接将gateway 下的监听器内容复制到Oracle Home下listener.ora文件中,同时也复制DG4MSQL至Oracle Home下的tnsnames.ora文件中,省去环境切换的麻烦。

五、更多参考

How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install (Doc ID 562509.1)
ORA-28500 SQLSTATE 8001 When I Select Via DG4MSQL (Doc ID 868672.1)

六、连接过程图(参考其他大湿)

技术分享

配置Oracle Gateway 12连接到SQL server 2014

标签:

人气教程排行