linux_oracle11g使用透明網(wǎng)關連接sqlserver

1、Linux系統(tǒng)oracle 11g數(shù)據(jù)庫使用透明網(wǎng)關（gateway）連接sqlserver數(shù)據(jù)庫本例中sqlserver數(shù)據(jù)庫是192.168.6.199，oracle數(shù)據(jù)庫是192.168.6.231，gateway透明網(wǎng)關是192.168.6.232，oracle和gateway分開部署。1.準備環(huán)境參數(shù)a) 修改主機名rootgateway # vi /etc/sysconfig/networkNETWORKING=yesNETWORKING_IPV6=noHOSTNAME=gatewayrootgateway # vi /etc/hosts127.0.0.1 localhost lo

2、calhost.localdomain localhost4 localhost4.localdomain4:1 localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.6.232 gatewayb) 創(chuàng)建用戶和組、目錄rootgateway # groupadd dbaroot gateway # useradd -m -G dba oracleroot gateway # passwd oracleroot gateway opt# mkdir -p /u01/app/oracleroot gate

3、way opt# chown -R oracle:dba /u01root gateway /# chmod 755 /u01c) 修改oracle用戶環(huán)境變量限制rootxxgsdb # su - oracleoraclexxgsdb $ vi .bash_profile增加以下部分umask 022export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/tg_1export NLS_LANG=AMERICAN_AMERICA.UTF8export NLS_DATE_FORMAT=&qu

4、ot;yyyy-mm-dd hh24:mi:ss"export PATH=.:$PATH:$HOME/bin:$ORACLE_HOME/binexport PATH=$PATH:/usr/bin:/bin:/usr/bin/X11:/usr/local/binexport PATH=$PATH:$ORACLE_BASE/common/oracle/binexport ORACLE_PATH=$PATH:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/adminexport ORACLE_TERM=xtermexport TNS_

5、ADMIN=$ORACLE_HOME/network/adminexport ORA_NLS10=$ORACLE_HOME/nls/dataunset ORA_NLS10export LD_LIBRARY_PATH=$ORACLE_HOME/libexport LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/oracm/libexport LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/lib:/usr/lib:/usr/local/libexport CLASSPATH=$ORACLE_HOME/JREexport CLASSP

6、ATH=$CLASSPATH:$ORACLE_HOME/jlibexport CLASSPATH=$CLASSPATH:$ORACLE_HOME/rdbms/jlibexport CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlibexport THREADS_FLAG=nativeexport TEMP=/tmpexport TMPDIR=/tmpexport PATH=$PATH:$ORACLE_HOME/bind) 關閉iptablesrootxxgsdb /# service iptables stopiptables: Setting chai

7、ns to policy ACCEPT: filter OK iptables: Flushing firewall rules: OK iptables: Unloading modules: OK rootxxgsdb /# rootxxgsdb /# rootxxgsdb /# chkconfig -list iptablesiptables 0:off 1:off 2:on 3:on 4:on 5:on 6:offrootxxgsdb /# chkconfig iptables offrootxxgsdb /# chkconfig -list iptablesiptables 0:of

8、f 1:off 2:off 3:off 4:off 5:off 6:off2.安裝gateway（透明網(wǎng)關）組件a) 下載透明網(wǎng)關 Oralce 11g下透明網(wǎng)管是一個單獨的組件，需要單獨下載安裝，這里下載是64位的，請根據(jù)具體下載32位或64位，下載地址google搜索一下Oracle Database Gateways或者在oralce官網(wǎng)的Oracle Database Software Downloads找。b) 安裝過程（過程中會創(chuàng)建監(jiān)聽）3.修改配置文件a) 修改gateway（透明網(wǎng)關）的initdg4msql.ora安裝完成后會自動生成/u01/app/oracle/produ

9、ct/11.2.0/tg_1/dg4msql/admin/initdg4msql.ora文件，確定文件內(nèi)容HS_FDS_CONNECT_INFO=192.168.6.199:1433/master HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER 192.168.6.199:1433/master為sqlserver數(shù)據(jù)庫的信息b) 修改gateway（透明網(wǎng)關）的監(jiān)聽文件listener.ora修改/u01/app/oracle/prod

10、uct/11.2.0/tg_1/network/admin/listener.ora文件添加以下內(nèi)容SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=dg4msql) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/tg_1) (PROGRAM=dg4msql) ) )#CONNECT_TIMEOUT_LISTENER = 0修改配置文件后使用（lsnrctl reload）重啟監(jiān)聽服務，c) 修改oracle數(shù)據(jù)庫的tnsname.ora配置文件添加以下內(nèi)容dg4msql = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.6.232)(PORT=1521) (CONNECT_DATA=(SID=dg4msql) (HS=OK) )IP地址為gateway（透明網(wǎng)關）所在的服務器4.測試連接a) 創(chuàng)建數(shù)據(jù)庫鏈接在sqlplus中創(chuàng)建數(shù)據(jù)庫鏈接create database link gw connect to sa identified by pas