linux系统oracle11g使用透明网关连接sqlserver.docx

linux系统oracle 11g数据库使用透明网关（gateway）连接sqlserver数据库本例中sqlserver数据库是192.168.6.199，oracle数据库是192.168.6.231，gateway透明网关是192.168.6.232，oracle和gateway分开部署。1.准备环境参数a) 修改主机名rootgateway # vi /etc/sysconfig/networknetworking=yesnetworking_ipv6=nohostname=gatewayrootgateway # vi /etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4:1 localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.6.232 gatewayb) 创建用户和组、目录rootgateway # groupadd dbaroot gateway # useradd -m -g dba oracleroot gateway # passwd oracleroot gateway opt# mkdir -p /u01/app/oracleroot gateway opt# chown -r oracle:dba /u01root gateway /# chmod 755 /u01c) 修改oracle用户环境变量限制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=yyyy-mm-dd hh24:mi:ssexport 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_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 classpath=$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 chains 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:off 1:off 2:off 3:off 4:off 5:off 6:off2.安装gateway（透明网关）组件a) 下载透明网关 oralce 11g下透明网管是一个单独的组件，需要单独下载安装，这里下载是64位的，请根据具体下载32位或64位，下载地址google搜索一下oracle database gateways或者在oralce官网的oracle database software downloads找。b) 安装过程（过程中会创建监听）3.修改配置文件a) 修改gateway（透明网关）的initdg4msql.ora安装完成后会自动生成/u01/app/oracle/product/11.2.0/tg_1/dg4msql/admin/initdg4msql.ora文件，确定文件内容hs_fds_connect_info=192.168.6.199:1433/masterhs_fds_trace_level=offhs_fds_recovery_account=recoverhs_fds_recovery_pwd=recover192.168.6.199:1433/master为sqlserver数据库的信息b) 修改gateway（透明网关）的监听文件listener.ora修改/u01/app/oracle/product/11.2.0/tg_1/network/admin/listener.ora文件添加以下内容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）重启监听服务，c) 修改oracle数据库的tnsname.ora配置文件添加以下内容dg4msql = (description= (address=(protocol=tcp)(host=192.168.6.232)(port=1521) (connect_data=(sid=dg4msql) (hs=ok) )ip地址为gateway（透明网关）所在的服务器4.测试连接a) 创建数据库链接在sqlplus中创建数据库链接create database link gw connect to sa identifie