Configuring Oracle Gateway 12 to connect to SQL Server 2014

Recent work requires Oracle-based connectivity to SQLserver2014, and we can implement this by configuring the gateway. The essence of this gateway is achieved through Dblink. That is, SQL Server is modeled as a remote Oracle instance, which is handled by the gateway to receive, forward, and so on. This article briefly describes its configuration process.

I. Introduction of installation Environment

12.1.0.211.2.0.4 + RHEL6.32014 + Win2012如果安装在已经安装Oracle相同的目录下，会收到如下提示，无法继续安装。[INS-32025withthegiven Oracle home.

Second, install Oracle Gateway1, prepare the environment

$ unzip Linuxamd64_12102_gateways.zip $ mkdir-p/u01/app/gateway$ mkdir-p/u01/app/gateway/12.1$ cp ~/.bash_profile ~/.bash_profile_gw$ vim ~/.BASH_PROFILE_GW# # #编辑新的bash_profile文件$ more ~/.BASH_PROFILE_GW# # #编辑后如下#. Bash_profile# Get The aliases and functionsif[- F~/.BASHRC]; Then. ~/.bashrcfi# User specific environment and startup programstmp=/tmp;ExportTmptmpdir=$TMP;ExportTmpdiroracle_hostname=wms.ycdata.net;ExportOracle_hostnameoracle_unqname=dg4msql;ExportOracle_unqnameoracle_base=/u01/app/gateway;ExportOracle_baseoracle_home=$ORACLE _base/12.1;ExportOracle_homeoracle_sid=dg4msql;ExportOracle_sidpath=/usr/sbin:$PATH;ExportPathpath=$ORACLE _home/bin:$PATH;ExportPathld_library_path=$ORACLE _home/lib:/lib:/usr/lib;ExportLd_library_pathclasspath=$ORACLE _home/jlib:$ORACLE _home/rdbms/jlib;ExportCLASSPATH

2. Installation and Configuration Gateway

$ source ~/. Bash_profile_gw$ Env |grep oracleoracle_unqname=dg4msqloracle_sid=dg4msqloracle_base=/u01/app/gatewayoracle_hostname= Wms. Ycdata. NEToracle_home=/u01/app/gateway/12.1$ Export display=192.168.. 157:0.0$ cd gateways/$./runinstaller Select for SQL Server Oracle database Gateway for Microsoft SQL Server Oracle Database Gate The-for-ODBC (this entry can be used to configure access to MySQL) input SQL Server connection information, or you can follow the configuration file Initdg4msql. OraModified in192.168.. 1571433Hq1636testdb after installation, you will be prompted to create a listener, can be directly created, or can be configured after installation, this article is installed after the installation, through the Netmgr configuration. In addition to configuring the listener address and port number, additional service items need to be configured when the Netmgr configuration is configured: program Name dg4msqlsid dg4msqloracle Home Direct ory/u01/app/gateway/12.1At the same time, the Netmgr can also be configured with TNSNames. Ora$ cd $ORACLE _home/network/admin$ more Listener. Ora # Listener.ora Network Configuration File:/u01/app/gateway/12.1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER_GW = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = WMS. Ycdata. NET) (PORT =1531)) SID_LIST_LISTENER_GW = (Sid_list = (Sid_desc = (program = dg4msql) (Sid_name = Dg4msql) (oracle_ho ME =/u01/app/gateway/12.1))) ADR_BASE_LISTENER_GW =/u01/app/gateway# # #查看配置后的tnsnames. Ora$ more TNSNames. Ora # Tnsnames.ora Network Configuration File:/u01/app/gateway/12.1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.Dg4msql = (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = WMS. Ycdata. NET) (PORT =1531))) (Connect_data = (SERVER = dedicated) (service_name = Dg4msql)))# # #安装完毕, there are corresponding configuration examples in the corresponding directory of the gateway, as follows$ cd $ORACLE _home/dg4msql/admin$ LSDG4MSQL_CVW. SQLDg4msql_tx. SQLInitdg4msql. OraListener. Ora. SampleTNSNames. Ora. Sample# # #这个文件用于配置连接到sqlserver$ more Initdg4msql. Orahs_fds_connect_info=[192.168.. 157]:1433TestDB# Alternate Connect format is Hostname/serverinstance/databasenameHs_fds_trace_level=offhs_fds_recovery_account=recoverhs_fds_recovery_pwd=recover

Third, Test gateway

$ lsnrctl Start Listener_gwlsnrctl forLinux:version12.1. 0. 2. 0-Production on  ,-jan- .  -:Geneva:GenevaCopyright (c)1991, the, Oracle. AllRights reserved. starting/u01/app/gateway/12.1/bin/tnslsnr:pleasewait... Tnslsnr forLinux:version12.1. 0. 2. 0-Productionsystem parameterfile  is/u01/app/gateway/12.1/network/admin/listener.oralog messages written to/u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xmllistening on: (Description= (Address= (protocol=tcp) (host=wms.ycdata.net) (PORT=1531)) connecting to(Description= (address= (protocol=tcp) (host=wms.ycdata.net) (PORT=1531)) STATUS ofThe LISTENER------------------------AliasListener_gwversion Tnslsnr forLinux:version12.1. 0. 2. 0-Productionstart Date ,-jan- .  -:Geneva:GenevaUptime0Days0hr.0Min.0Sec--author:leshamiTrace level off--blog:http://blog.csdn.net/leshamiSecurity on: Local OS authenticationsnmp Offlistener ParameterFile/u01/app/gateway/12.1/network/admin/listener.oralistener LogFile/u01/app/gateway/diag/tnslsnr/wms/listener_gw/alert/log.xmllistening Endpoints Summary ... (Description= (address= (protocol=tcp) (host=wms.ycdata.net) (PORT=1531)) Services Summary ... Service"Dg4msql"Has1Instance (s). Instance"Dg4msql", Status UNKNOWN, has1Handler (s) forThis service ... The command completed successfully$ tnsping dg4msqltns Ping Utility forLinux:version12.1. 0. 2. 0-Production on  ,-jan- .  -: in:WuyiCopyright (c)1997, the, Oracle. AllRights reserved. Used parameter files:/u01/app/gateway/12.1/network/admin/sqlnet.oraused TNSNames Adapter toResolve thealiasAttempting toContact (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = wms.ycdata.net) (PORT=1531))) (Connect_data = (SERVER = dedicated) (service_name = Dg4msql)) OK (0msec) $ sqlplus wms_user/[email protected]sql> show USER; USER is "Wms_user"sql> Create public database link MSSQL Connect toRobin identified by"XXX"Using' Dg4msql‘; Sql>Select* FROM [email protected];Select* FROM [email protected] *error on line1: ora-28546: Connection initialization failed, probable NET8 admin errorora-02063: Preceding line from Dg4msql adjust Dg4msql configuration, add (hs=ok) Entry Dg4msql = (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = wms.ycdata.net) (PORT=1531))) (Connect_data = (SERVER = dedicated) (SID = Dg4msql)) (Hs=ok)) # # #再次测试SQL >Select* FROM [email protected]; Id----------         1

Iv. Simplified Management

Because Oracle Gateway is installed with a different Oracle Home, you need to switch environment variables when you start the Gateway listener. So you can directly copy the listener content under the gateway to Oracle home Listener.ora file, and also copy the Dg4msql to the Tnsnames.ora file under Oracle home, eliminating the hassle of environment switching.

Five, more reference

How to Configure dg4msql (Oracle Database Gateway for MS sql Server) 64bit Unix OS (Linux, Solaris, Aix,hp-ux) post instal L (Doc ID 562509.1)
ORA-28500 SQLSTATE 8001 when I Select Via dg4msql (Doc ID 868672.1)

Vi. Connection process diagram (refer to other large wet)

Configuring Oracle Gateway 12 to connect to SQL Server 2014