Configure Oracle Gateway 12 to connect to SQL Server 2014

Configure Oracle Gateway 12 to connect to SQL Server 2014

In recent work, we need to connect to SQL Server 2014 based on Oracle. We can configure Gateway to implement this function. This Gateway is actually implemented through dblink. That is, the SQL Server is simulated into a remote Oracle instance, which is received and forwarded by the Gateway. This article briefly describes the configuration process.

I. Introduction to the installation environment

Gateway: 12.1.0.2Oracle db: 11.2.0.4 + RHEL6.3Sqlserver: 2014 + Win2012 if you have installed it in the same directory as Oracle, you will receive the following prompt and cannot continue the installation. [INS-32025] The chosen installation conflicts with software alreadyinstalled the given Oracle home.

2. Install Oracle gateway1 and 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 ### edit the new bash_profile File $ more ~ /. Bash_profile_gw ### edit the file as follows #. bash_profile # Get the aliases and functionsif [-f ~ /. Bashrc]; then .~ /. Bashrcfi # User specific environment and startup programsTMP =/tmp; export tmpdir = $ TMP; export external = External; export external = dg4msql; export ORACLE_UNQNAMEORACLE_BASE =/u01/app/gateway; export ORACLE_BASEORACLE_HOME = $ ORACLE_BASE/12.1; export ORACLE_HOMEORACLE_SID = dg4msql; export ORACLE_SIDPATH =/usr/sbin: $ PATH; export PATHPATH = $ ORACLE_HOME/bin: $ PATH; export PATHLD_LIBRARY_PATH = $ ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATHCLASSPATH = $ ORACLE_HOME/jlib: $ ORACLE_HOME/rdbms/jlib; export CLASSPATH

2. install and configure 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.21.157: 0.0 $ cd always ways/$. /runInstaller select for SQL server Oracle Database Gateway for Microsoft SQL Server Oracle Database Gateway for ODBC (this option can be used to configure access to mysql) to enter the sqlserver connection information, or you can configure the file initdg4msql later. modify 192.168.2 in ora After 1.1571433HQ1636testdb is installed, you will be prompted to create a listener, which can be directly created or configured after installation. This article uses netmgr for configuration after installation. When configuring through netmgr, in addition to configuring the listener address and port number, you also need to configure other service items: Program Name dg4msqlSID dg4msqlOracle Home Directory/u01/app/gateway/12.1, you can also configure tnsnames through netmgr. 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_HOME =/u01/app/gateway/12.1) ADR_BASE_LISTENER_GW =/u01/app/gateway ### view the configured 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) ### after the installation is complete, the corresponding configuration samples are available in the corresponding gateway directory, as shown in the following $ cd $ ORACLE_HOME/dg4msql/admin $ lsdg4msql_cv1_ SQL dg4msql_tx. SQL. ora listener. ora. sample tnsnames. ora. sample ### this file is used to configure connection to sqlserver $ more initdg4msql. oraHS_FDS_CONNECT_INFO = [192.168.21.157]: 1433 // testdb # alternate connect format is hostname/serverinstance/region = OFFHS_FDS_RECOVERY_ACCOUNT = RECOVERHS_FDS_RECOVERY_PWD = RECOVER

3. Test gateway

$ Lsnrctl start LISTENER_GWLSNRCTL for Linux: Version 12.1.0.2.0-Production on 08-JAN-2016 18: 03: 03 Copyright (c) 1991,201 4, Oracle. all rights reserved. starting/u01/app/gateway/12.1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.1.0.2.0-ProductionSystem parameter file 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 of the LISTENER ------------------------ Alias LISTENER_GWVersion TNSLSNR for Linux: Version 12.1.0.2.0-ProductionStart Date 08-JAN-2016 18: 03: 03 Uptime 0 days 0 hr. 0 min. 0 sec -- Author: LeshamiTrace Level off -- Blog: http://blog.csdn.net/leshami Security ON: Local OS AuthenticationSNMP OFFListener Parameter File/u01/app/gateway/12.1/network/admin/listener. oraListener Log File/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" has 1 instance (s ). instance "dg4msql", status UNKNOWN, has 1 handler (s) for this service... the command completed successfully $ tnsping DG4MSQLTNS Ping Utility for Linux: Version 12.1.0.2.0-Production on 08-JAN-2016 18: 29: 51 Copyright (c) 1997,201 4, Oracle. all rights reserved. used parameter files:/u01/app/gateway/12.1/network/admin/sqlnet. oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = wms.ycdata.net) (PORT = 1531 ))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg4msql) OK (0 msec) $ sqlplus WMS_USER/xxx @ WMSSERVERSQL> show user; USER is "WMS_USER" SQL> create public database link mssql connect to robin identified by "xxx" using 'dg4msql'; SQL> select * from tt @ dg4msql; select * from tt @ dg4msql * ERROR at line 1: ORA-28546: connection initialization failed, probable Net8 admin errorORA-02063: preceding line from DG4MSQL adjust DG4MSQL configuration, add (HS = OK) item DG4MSQL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = wms.ycdata.net) (PORT = 1531) (CONNECT_DATA = (SERVER = DEDICATED) (SID = dg4msql) (HS = OK) ### test SQL again> select * from tt @ dg4msql; id ---------- 1

Iv. simplified management

Since Oracle gateway uses different Oracle Home during installation, you must switch the environment variable when starting the gateway listener. Therefore, you can directly copy the listener content under gateway to the listener. ora file under Oracle Home, and copy DG4MSQL to the tnsnames. ora file under Oracle Home, saving the trouble of Environment switching.

V. More references

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)

6. Connection Process Diagram (refer to other big wet charts)

This article permanently updates the link address: