Connect Sybase through the Oracle Transparent gateway

The transparent gateway technology proposed by Oracle can be used to interconnect with many other types of databases, to make connections between different types of data, and to make it easier for users to query. Recently, during the implementation of a company project, developers need to access some of the tables in the Sybase database, thinking about creating an intermediate library, using the Oracle transparent gateway, and creating Dblink to read the data. Due to a number of errors during the installation process, we hereby record the configuration process.

First, the installation of Transparent gateway

Download the required version of the transparent gateway software from the Oracle's official website first. I choose the win32_11gr2_gateways here. The installation process can be found in the web search tutorial, here only two points to note:
(1), transparent gateway installation path do not choose the default, should be placed in the Oracle_home directory (transparent gateway and database on the same server).
(2), the IP address, port, and database name of the heterogeneous database (Sybase) should be prepared before installation.

Second, configure the transparent gateway

After the transparent gateway software is installed, the corresponding configuration file Initdg4sybs.ora is generated in the $oracle_home/dg4sybs/admin directory. Since I am using a Windows Server, the configuration file is C:\app\oracle\product\11.2.0\dbhome_1\dg4sybs\admin\dg4sybs.ora and the file contents are as follows:

# This was a customized agent init file that contains the HS parameters# that's needed for the Database Gateway for Sybas e## 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

The configuration file is automatically generated during the installation of the transparent gateway, in principle without modification, and can be configured for subsequent configuration (you can also create your own transparent Gateway profile Initsid.ora Initdg4sybs.ora as a template). Please also note that C:\app\oracle\product\11.2.0\dbhome_1\dg4sybs\ The other two files under the Admin directory: Listener.ora.sample and tnsnames.ora.sample, you need to understand the content.

Third, add monitoring and TNS configuration

At this time listener.ora.sample and tnsnames.ora.sample These two sample files can help us to complete the configuration file modification, the Listener.ora.sample file "(Sid_desc = ...") Part of the content is placed under the sid_list of the Listener.ora file and saved. Copy the contents of the "Dg4sybs = ..." section in the Tnsnames.ora.sample file to the Tnsnames.ora file and save it. After the configuration is complete, restart the listener.

Iv. creating a dblink to access Sybase

Create Publice database link Dblink_name connect to "username" identified by "password" using ' Dg4sybs ';

There are two points to note when executing this command:

(1), username and password to access the Sybase database.
(2), if you use the transparent gateway default configuration file, then use dg4sybs here, otherwise, use your own defined SID.

V. Test access

C:\>tnsping dg4sybstns Ping Utility for 32-bit windows:version 11.2.0.1.0-production on 1 March-July -2016 12:40:40copyrigh T (c) 1997, Oracle.  All rights reserved. Used parameter file: C:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora used TNSNames Adapter to resolve the alias attempt connection (description= (address= (protocol=tcp) (host=10.10.4.4) (port=1521)) (Connect_data= (SID=DG4SYBS)) (HS=OK ) OK (10 ms) C:\>sqlplus/as Sysdbasql*plus:release 11.2.0.1.0 Production on Wednesday July 12:41:15 2016Copyright (C) 1982 , Oracle.  All rights reserved. Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-productionwith The partitioning, OLAP, Data Mining and Real application testing optionssql> SELECT * FROM [email protected]_link;dummy------X

At this point, all operations completed, configuration success!

Connect Sybase through the Oracle Transparent gateway