Oracle Database Gateways

Oracle Database Gateways address the needs of disparate data access. In a heterogeneously distributed environment, Gateways make it possible to integrate with any number of non-Oracle systems from an Oracle application. They enable integration with data stores such as IBM DB2, Microsoft SQL Server and Excel, transaction managers like IBM CICS and message queuing systems like IBM WebSphere MQ.

Here Oracle gateways is installed to extract the data in the MSSQL SERVER Database from the Oracle Database by Using DBLINK.

–SQLSERVER DATABASE CONFIGURATION AND PARAMETERS

SqlServer Ver         : 2016
Hostname              : dugg-uh-oi-pt
Listener Port          : 1433
Instance name       : MSSQLSERVER
DB Name               : fossil
IP                           : 192.168.1.10

–ORACLE DATABASE CONFIGURATION AND PARAMETERS
Oracle DB ver        : 11g (11.2.0.2.0)
HOSTNAME           : oralinuxfossil01.grgh.oracle-help.com
Listener Port          : 1521
Instance Name      : fossilDVL
DB Name               : fossilDVL
IP                           : 192.168.1.11
ORACLE_HOME     : /Oracle/app/oracle/11gR2/rdbms/11.2.0.2

–ORACLE GATEWAY CONFIGURATION AND PARAMETERS

ORA GATEWAY VER   : 11g
Listener Port               : 1523
ORACLE_HOME         : /Oracle/app/oracle/gateway

Download and Extract linux.x64_11gR2_gateways.zip  from the OTN

Run the ./runInstaller from the Linux Server

[oracle@oralinuxfossil01 admin]$ ./runInstaller

1.Welcome Screen

2. Specify the Oracle Gateway Path and Home Name

3. Select for MSSQL Server

4. Give all the required parameter in the box.

click on Next and it will Install the Oracle Gateways Successfully.

==================================================================================

Configuration in Oracle Gateways Home

  • NETWORK CONFIGURATION FOR ORACLE GATEWAY 

Only LISTENER.ORA file will be configured in oracle gateway home

[oracle@oralinuxfossil01 admin]$ cd /Oracle/app/oracle/gateways/network/admin
[oracle@oralinuxfossil01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /Oracle/app/oracle/gateways/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENERDG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oralinuxfossil01.grgh.oracle-help.com)(PORT = 1523))
)
)

SID_LIST_LISTENERDG=
(SID_LIST=
(SID_DESC=
(SID_NAME=fossil)
(ORACLE_HOME=/Oracle/app/oracle/gateways)
(ENV=”LD_LIBRARY_PATH=/Oracle/app/oracle/gateways/dg4msql/driver/lib:/Oracle/app/oracle/gateways/lib”)
(PROGRAM=dg4msql)
)
)

ADR_BASE_LISTENERDG = /Oracle/app/oracle

[oracle@oralinuxfossil01 admin]$ cd /Oracle/app/oracle/gateways/bin

[oracle@oralinuxfossil01 bin]$ ./lsnrctl start LISTENERDG

  • INITPARAMETER FILE FOR ORACLE GATEWAY , IT WILL BE USED TO CONNECT THE SQLSERVER DATABASE.

Gatway Parameter file name must be same as the given SID in the listener file.

[oracle@oralinuxfossil01 admin]$ cd /Oracle/app/oracle/gateways/dg4msql/admin

[oracle@oralinuxfossil01 admin]$ cat initfossil.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[dugg-uh-oi-pt]:1433//fossil
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

=================================================================================

Configuration in Oracle Database Home

  • NETWORK CONFIGURATION FOR ORACLE GATEWAY
    Only An Entry will be made in TNSNAMES.ORA

[oracle@oralinuxfossil01 admin]$ cd $ORACLE_HOME/network/admin

[oracle@oralinuxfossil01 admin]$ cat listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.11)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

[oracle@oralinuxfossil01 admin]$ cat tnsnames.ora

fossil=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=oralinuxfossil01.grgh.oracle-help.com)(PORT=1523))
(CONNECT_DATA=(SID=fossil))
(HS=OK)
)

check the tnsping output

[oracle@oralinuxfossil01 admin]$ tnsping fossil

TNS Ping Utility for Linux: Version 11.2.0.1 on 27-OCT-2018 14:46:28

Copyright (c) 2001, 2015 Oracle Corporation. All rights reserved.

Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oralinuxfossil01.grgh.oracle-help.com)(PORT=1523)) (CONNECT_DATA=(SID=fossil)) (HS=OK) )
OK (10 msec)

Now all the configurations are completed for Oracle Gateways.

=================================================================================

create a dblink in the oracle database from where the sqlserver database needs to be fetch.

ag33 is the dblink name , which will access the sqlserver from oracle database.

sqluser is the user for connecting the sqlserver database.

fossil123 is the password for the sqlserver user “sqluser” to login into the database.

fossil is the tns service name in the sqlserver.

[oracle@oralinuxfossil01 admin]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 27 15:12:58 2018

Copyright (c) 1982, 2012, Oracle. All rights reserved.

15:23:30 SYS @ Tissot > CREATE PUBLIC DATABASE LINK ag33 CONNECT TO ‘sqluser’ IDENTIFIED BY ‘fossil123’ USING ‘fossil’;

Database link created.

15:23:55 SYS @ Tissot >select sysdate from dual@ag33;

SYSDATE
———
27-OCT-18

=================================================================================

Thanks for reading this article.

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.