时间:2021-07-01 10:21:17 帮助过:11人阅读
- <code class=" hljs bash">$ unzip linuxamd64_12102_gateways.zip
- $ mkdir -p /u01/app/gateway
- $ mkdir -p /u01/app/gateway/<span class="hljs-number">12.1</span>
- $ cp ~/.bash_profile ~/.bash_profile_gw
- $ vim ~/.bash_profile_gw <span class="hljs-comment">###编辑新的bash_profile文件</span>
- $ more ~/.bash_profile_gw <span class="hljs-comment">###编辑后如下</span>
- <span class="hljs-comment"># .bash_profile</span>
- <span class="hljs-comment"># Get the aliases and functions</span>
- <span class="hljs-keyword">if</span> [ <span class="hljs-operator">-f</span> ~/.bashrc ]; <span class="hljs-keyword">then</span>
- . ~/.bashrc
- <span class="hljs-keyword">fi</span>
- <span class="hljs-comment"># User specific environment and startup programs</span>
- TMP=/tmp; <span class="hljs-keyword">export</span> TMP
- TMPDIR=<span class="hljs-variable">$TMP</span>; <span class="hljs-keyword">export</span> TMPDIR
- ORACLE_HOSTNAME=wms.ycdata.net; <span class="hljs-keyword">export</span> ORACLE_HOSTNAME
- ORACLE_UNQNAME=dg4msql; <span class="hljs-keyword">export</span> ORACLE_UNQNAME
- ORACLE_BASE=/u01/app/gateway; <span class="hljs-keyword">export</span> ORACLE_BASE
- ORACLE_HOME=<span class="hljs-variable">$ORACLE_BASE</span>/<span class="hljs-number">12.1</span>; <span class="hljs-keyword">export</span> ORACLE_HOME
- ORACLE_SID=dg4msql; <span class="hljs-keyword">export</span> ORACLE_SID
- PATH=/usr/sbin:<span class="hljs-variable">$PATH</span>; <span class="hljs-keyword">export</span> PATH
- PATH=<span class="hljs-variable">$ORACLE_HOME</span>/bin:<span class="hljs-variable">$PATH</span>; <span class="hljs-keyword">export</span> PATH
- LD_LIBRARY_PATH=<span class="hljs-variable">$ORACLE_HOME</span>/lib:/lib:/usr/lib; <span class="hljs-keyword">export</span> LD_LIBRARY_PATH
- 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>
- <code class=" hljs avrasm">$ source ~/<span class="hljs-preprocessor">.bash</span>_profile_gw
- $ env |grep ORACLE
- ORACLE_UNQNAME=dg4msql
- ORACLE_SID=dg4msql
- ORACLE_BASE=/u01/app/gateway
- ORACLE_HOSTNAME=wms<span class="hljs-preprocessor">.ycdata</span><span class="hljs-preprocessor">.net</span>
- ORACLE_HOME=/u01/app/gateway/<span class="hljs-number">12.1</span>
- $ 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>
- $ cd gateways/
- $ ./runInstaller
- 选择for sql server
- Oracle Database Gateway for Microsoft SQL Server
- Oracle Database Gateway for ODBC (此项可以用于配置访问mysql)
- 输入sqlserver连接信息,也可以后续再配置文件initdg4msql<span class="hljs-preprocessor">.ora</span>中修改
- <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>
- HQ1636
- testdb
- 安装完毕后,会提示创建监听器,可以直接创建,也可以在安装完毕后再配置,本文是在安装完毕后,通过netmgr进行配置的。
- 在通过netmgr配置时,除了配置监听器地址和端口号之外,还需要配置其他服务项:
- Program Name dg4msql
- SID dg4msql
- Oracle Home Directory /u01/app/gateway/<span class="hljs-number">12.1</span>
- 与此同时,也可以通过netmgr配置tnsnames<span class="hljs-preprocessor">.ora</span>
- $ cd $ORACLE_HOME/network/admin
- $ more listener<span class="hljs-preprocessor">.ora</span>
- <span class="hljs-preprocessor"># listener.ora Network Configuration File: /u01/app/gateway/12.1/network/admin/listener.ora</span>
- <span class="hljs-preprocessor"># Generated by Oracle configuration tools.</span>
- LISTENER_GW =
- (DESCRIPTION =
- (ADDRESS = (PROTOCOL = TCP)(HOST = wms<span class="hljs-preprocessor">.ycdata</span><span class="hljs-preprocessor">.net</span>)(PORT = <span class="hljs-number">1531</span>))
- )
- SID_LIST_LISTENER_GW =
- (SID_LIST =
- (SID_DESC =
- (PROGRAM = dg4msql)
- (SID_NAME = dg4msql)
- (ORACLE_HOME = /u01/app/gateway/<span class="hljs-number">12.1</span>)
- )
- )
- ADR_BASE_LISTENER_GW = /u01/app/gateway
- <span class="hljs-preprocessor">###查看配置后的tnsnames.ora</span>
- $ more tnsnames<span class="hljs-preprocessor">.ora</span>
- <span class="hljs-preprocessor"># tnsnames.ora Network Configuration File: /u01/app/gateway/12.1/network/admin/tnsnames.ora</span>
- <span class="hljs-preprocessor"># Generated by Oracle configuration tools.</span>
- DG4MSQL =
- (DESCRIPTION =
- (ADDRESS_LIST =
- (ADDRESS = (PROTOCOL = TCP)(HOST = wms<span class="hljs-preprocessor">.ycdata</span><span class="hljs-preprocessor">.net</span>)(PORT = <span class="hljs-number">1531</span>))
- )
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SERVICE_NAME = dg4msql)
- )
- )
- <span class="hljs-preprocessor">###安装完毕,在gateway相应目录下也有对应的配置样例,如下 </span>
- $ cd $ORACLE_HOME/dg4msql/admin
- $ ls
- 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>
- <span class="hljs-preprocessor">###这个文件用于配置连接到sqlserver</span>
- $ more initdg4msql<span class="hljs-preprocessor">.ora</span>
- 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
- <span class="hljs-preprocessor"># alternate connect format is hostname/serverinstance/databasename</span>
- HS_FDS_TRACE_LEVEL=OFF
- HS_FDS_RECOVERY_ACCOUNT=RECOVER
- HS_FDS_RECOVERY_PWD=RECOVER</code>
- <code class=" hljs vhdl">$ lsnrctl start LISTENER_GW
- 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>
- Copyright (c) <span class="hljs-number">1991</span>, <span class="hljs-number">2014</span>, Oracle. <span class="hljs-keyword">All</span> rights reserved.
- Starting /u01/app/gateway/<span class="hljs-number">12.1</span>/bin/tnslsnr: please <span class="hljs-keyword">wait</span>...
- 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
- 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
- Log messages written <span class="hljs-keyword">to</span> /u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xml
- 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>)))
- 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>)))
- STATUS <span class="hljs-keyword">of</span> the LISTENER
- <span class="hljs-comment">------------------------</span>
- <span class="hljs-keyword">Alias</span> LISTENER_GW
- 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
- 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>
- 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>
- Trace Level off <span class="hljs-comment">--Blog : http://blog.csdn.net/leshami </span>
- Security <span class="hljs-keyword">ON</span>: Local OS Authentication
- SNMP OFF
- Listener Parameter <span class="hljs-keyword">File</span> /u01/app/gateway/<span class="hljs-number">12.1</span>/network/admin/listener.ora
- Listener Log <span class="hljs-keyword">File</span> /u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xml
- Listening Endpoints Summary...
- (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wms.ycdata.net)(<span class="hljs-keyword">PORT</span>=<span class="hljs-number">1531</span>)))
- Services Summary...
- Service <span class="hljs-string">"dg4msql"</span> has <span class="hljs-number">1</span> instance(s).
- 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...
- The command completed successfully
- $ tnsping DG4MSQL
- 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>
- Copyright (c) <span class="hljs-number">1997</span>, <span class="hljs-number">2014</span>, Oracle. <span class="hljs-keyword">All</span> rights reserved.
- Used parameter files:
- /u01/app/gateway/<span class="hljs-number">12.1</span>/network/admin/sqlnet.ora
- Used TNSNAMES adapter <span class="hljs-keyword">to</span> resolve the <span class="hljs-keyword">alias</span>
- 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>)))
- (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg4msql)))
- OK (<span class="hljs-number">0</span> msec)
- $ sqlplus WMS_USER/xxx@WMSSERVER
- SQL> show user;
- USER <span class="hljs-keyword">is</span> <span class="hljs-string">"WMS_USER"</span>
- 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>‘;
- SQL> <span class="hljs-keyword">select</span> * from tt@dg4msql;
- <span class="hljs-keyword">select</span> * from tt@dg4msql
- *
- ERROR at line <span class="hljs-number">1</span>:
- ORA-<span class="hljs-number">28546</span>: connection initialization failed, probable Net8 admin error
- ORA-<span class="hljs-number">02063</span>: preceding line from DG4MSQL
- 调整DG4MSQL配置,增加(HS=OK)项
- DG4MSQL =
- (DESCRIPTION =
- (ADDRESS_LIST =
- (ADDRESS = (PROTOCOL = TCP)(HOST = wms.ycdata.net)(<span class="hljs-keyword">PORT</span> = <span class="hljs-number">1531</span>))
- )
- (CONNECT_DATA =
- (SERVER = DEDICATED)
- (SID = dg4msql)
- )
- (HS=OK)
- )
- ###再次测试
- SQL> <span class="hljs-keyword">select</span> * from tt@dg4msql;
- id
- <span class="hljs-comment">----------</span>
- <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
标签: