Connect to Sybase through Oracle transparent gateway

Connect to Sybase through Oracle transparent gateway

The transparent gateway technology proposed by Oracle can be used to interconnect with other types of databases to establish connections between different types of data, which is convenient for users to query. Recently, during the implementation of a project in the company, developers need to access some tables in the Sybase Database, and they want to create an intermediate database, use the Oracle transparent gateway, and create a dblink to read data. As many errors have occurred during the installation process, we hereby record the configuration process.

Oracle 11g transparent gateway connected to sybase Database

1. Install transparent gateway

Download the required version of transparent gateway software from the official Oracle website. Here I use win32_11gR2_gateways. For more information about the installation process, see the web search tutorial. Here, we only need to note the following:
(1) do not select the default installation path for transparent gateway, but place it in the ORACLE_HOME directory (the transparent gateway and database are on the same server ).
(2) Prepare the IP address, port, and Database Name of the heterogeneous database (Sybase) before installation.

Ii. Configure transparent gateway

After the transparent gateway software is installed, the corresponding configuration file initdg4sybs. ora is generated in the $ ORACLE_HOME/dg4sybs/admin directory. Because I am using a Windows server, the configuration file is C: \ app \ oracle \ product \ 11.2.0 \ dbhome_1 \ dg4sybs \ admin \ dg4sybs. ora. The file content is as follows:

12345678910

# This is a customized agent init file that contains the HS parameters# that are needed for the Database Gateway for Sybase ## HS init parameters#HS_FDS_CONNECT_INFO=[198.168.1.11]:7000/HNXSHS_FDS_TRACE_LEVEL=OFFHS_FDS_RECOVERY_ACCOUNT=RECOVERHS_FDS_RECOVERY_PWD=RECOVER

This configuration file is automatically generated during the installation of transparent gateway. In principle, you do not need to modify it. You can perform subsequent configuration based on this file (or you can use initdg4sybs. ora creates its own transparent gateway configuration file initsid. ora ). Note the other two files in the C: \ app \ oracle \ product \ 11.2.0 \ dbhome_1 \ dg4sybs \ admin directory: listener. ora. sample and tnsnames. ora. sample. You must understand the content.

3. Add listeners and TNS configurations

Listener. ora. sample and tnsnames. ora. the two sample files can help us easily modify the configuration file and change the listener. ora. in the sample file, "(SID_DESC =... "Put some content in listener. save the settings in the SID_LIST file of ora. Copy the content of "dg4sybs =..." in the tnsnames. ora. sample file to the tnsnames. ora file and save it. After the configuration is complete, restart the listener.

4. Create a DBLINK to access Sybase

1	create publice database link dblink_name connect to "username" identified by "password" using 'dg4sybs';

Note the following two points when executing this command:

(1) The user name is the user name and password used to access the sybase Database.
(2) If the default configuration file of transparent gateway is used, dg4sybs will be used here. Otherwise, your own SID will be used.

V. Test Access

1234567891011121314151617181920212223242526272829303132

C:\>tnsping dg4sybs TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 13-7-2016 12:40:40 Copyright (c) 1997, 2010, Oracle.  All rights reserved. Used parameter files:C:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.oraAlias resolved using TNSNAMES AdapterTry to connect (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp) (HOST = 10.10.4.4) (PORT = 1521) (CONNECT_DATA=(SID=dg4sybs)) (HS=OK))OK (10 ms) C:\>sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Wednesday, September 16, July 13 12:41:15, 2016 Copyright (c) 1982, 2010, Oracle.  All rights reserved.Connect:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from dual@hnxs_link; DUMMY------X

At this point, all operations are completed and the configuration is successful!