Create and
Configure database link from Oracle to Non-Oracle database
By Donatien MBADI OUM, OCP, OCE, Oracle Instructor
1. Introduction to
Oracle Database Gateway for ODBC
Oracle Database Gateways provide the ability to transparently access
data residing in a non-Oracle system from an Oracle environment. The following
sections briefly cover Heterogeneous Services, the technology that the Oracle
Database Gateway for ODBC is based on.
1.1.
Overview of Oracle Database Gateways
Gateway technology is composed of two
parts: a component that has the generic technology to connect to a non-Oracle
system, which is common to all the non-Oracle systems, called Heterogeneous
Services, and a component that is specific to the non-Oracle system that the
gateway connects to. Heterogeneous Services, in conjunction with the Oracle
Database Gateways, enable transparent access to non-Oracle systems from an
Oracle environment.
Heterogeneous data access is a problem that affects a
lot of companies. Many companies run several different database systems. Each
of these systems stores data and has a set of applications that run against it.
Consolidating this data in one database system is often hard - in large part
because many of the applications that run against one database may not have an
equivalent that runs against another. Until migration to one consolidated
database system is feasible, it is necessary for the various heterogeneous
database systems to interoperate.
Oracle Database Gateways provide the ability to
transparently access data residing in a non-Oracle system from an Oracle
environment. This transparency eliminates the need for application developers
to customize their applications to access data from different non-Oracle
systems, thus decreasing development efforts and increasing the mobility of the
application. Applications can be developed using a consistent Oracle interface
for both Oracle and non-Oracle systems.
1.2.
Oracle
Database Gateway for ODBC Architecture
The gateway works with an ODBC driver to access the
non-Oracle data store using Oracle Database Gateway for ODBC. The driver that
you use must be on the same machine as the gateway. The non-Oracle system can
reside on the same machine as the Oracle database or on a different machine.
The gateway can be installed on the machine running
the non-Oracle system, the machine running the Oracle database or on a third
machine as a standalone. Each configuration has its advantages and
disadvantages. The considerations when determining where to install the gateway
are network traffic, operating system platform availability, hardware resources
and storage.
The ODBC driver may require
non-Oracle client libraries even if the non-Oracle database is located on the
same machine.
This tutorial shows how to integrate
remote non-oracle data with Oracle by using DG4ODBC. It can be useful using Microsoft
SQL Server, MySQL, PostgreSQL database with an ODBC driver.
Oracle’s heterogeneous connectivity
solution enables client applications to take advantage of Oracle’s underlying
security features. For example, you secure the Oracle® database connection with
Oracle Advanced Security. In this tutorial, we are using the PostgreSQL as
non-oracle database and we are using the Windows gateway server.
Our architecture is as below:
In this configuration:
A
client connects to the Oracle database through Oracle Net.
The
Heterogeneous Services component of the Oracle database connects through Oracle
Net to the gateway.
The
gateway communicates with the following non-Oracle components:
An
ODBC driver manager
An
ODBC driver
Each
user session receives its own dedicated agent process spawned by the first use
in that user session of the database link to the non-Oracle system. The agent
process ends when the user session ends.
2. Implementation
steps
2.1.
Create an ODBC on Gateway
Machine
- Open the ODBC tool for
PostgreSQL (ODBC for PostgreSQL must be first installed on the Gateway Server)
- Provide PostgreSQL Database
Name, IP/Hostname, Port Number and Credential and test the connection to
PostgreSQL Database
2.2. Create a Gateway
Init File
On
the Oracle gateway server, log in using an account that belongs to the
Administrators group. Make a copy of the template gateway init file,
initdg4odbc.ora. The template file is stored in the ORACLE_HOME\hs\admin
folder.
Every
instance using DG4ODBC for Windows needs a separate init*.ora file. For this
tutorial, we copied the template init file, initdg4odbc.ora, to initreporting.ora,
a naming convention that identified our target database. The init file
references the System ODBC data source for the target database. For example:
# This is a sample agent init file that
contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = my_system_odbc_dsn
HS_FDS_TRACE_LEVEL = 0
#HS_FDS_SUPPORT_STATISTICS=FALSE
#
# Environment variables required for the
non-Oracle system
#
#set <envvar>=<value>
|
Parameter |
Description |
|
HS_FDS_CONNECT_INFO |
|
|
HS_FDS_TRACE_LEVEL |
Specifies the level of tracing. You should generally
leave this commented out, as tracing can degrade performance. However, if you
are having problems, HS_FDS_TRACE_LEVEL may be set: |
|
HS_FDS_TRACE_LEVEL = Debug |
|
|
This setting generates a log file for each SQL*Plus
connection. Gateway log files are stored in the ORACLE_HOME\hs\trace
folder. For example, ORACLE_HOME\hs\trace\FB_agt_1516.trc. |
|
|
HS_FDS_SUPPORT_STATISTICS |
If you want to use multiple active statements and
the ODBC driver supports this feature (which, for example, the Firebird ODBC
driver does), leave HS_FDS_SUPPORT_STATISTICS commented out: |
|
#HS_FDS_SUPPORT_STATISTICS = FALSE |
|
|
|
|
|
If you do not want to use multiple active statements
or the ODBC driver does not support this feature, un-comment the
HS_FDS_SUPPORT_STATISTICS setting by deleting the # character from the start
of the line. |
2.3. Edit listener.ora
The Oracle
Listener listens for incoming requests from the Oracle database. For the Oracle
Listener to listen for DG4ODBC, information about DG4ODBC must be added to the
Oracle Listener configuration file, ORACLE_HOME\network\admin\listener.ora.
Configure
the Listener on Gateway Server
1.
Open the LISTERNER.ORA file
(Located on $ORACLE_HOME/network/admin)
2.
Modify the listener.ora file
as below :
LISTENER_your_SID =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.xxx.xxx.xxx)(PORT=your_listener_port))
)
SID_LIST_LISTENER_your_SID =
(SID_LIST=
(SID_DESC
=
(SID_NAME
= your_SID)
(ORACLE_HOME
= YOUR_ORACLE_HOME)
(PROGRAM
= dg4odbc))
)
|
Variable |
Value |
|
SID_NAME |
The name of the gateway init file without the init prefix or ora
extension. For example, we named the init file initfb, and so used fb as the
SID_NAME. |
|
ORACLE_HOME |
The location of your Oracle home directory. |
|
PROGRAM |
dg4odbc |
|
|
|
|
This is the executable name of the Windows Oracle® Database Gateway for
ODBC. |
3.
Start the listener:
2.4. Edit tnsnames.ora
The final Oracle file to edit is ORACLE_HOME\network\admin\tnsnames.ora.
You need to add a connect descriptor for the gateway. The connect descriptor
identifies the Oracle server to attach to and the SID_NAME to use. For example:
fb_connection= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=gateway_host)(PORT=5424)) (CONNECT_DATA= (SID=fb)) (HS=OK) )Where:
|
Variable |
Value |
|
fb_connection |
The connect descriptor for DG4ODBC for Windows. You
include this entry when creating the database link with SQL*Plus. |
|
|
|
|
Reference the SID_NAME you created for DG40DBC in
the connect descriptor. For example, "fb_connection". Do
this so that your tnsnames.ora and listener.ora entries can be attributed to
each other. |
|
|
ADDRESS |
This is the entry for your Oracle® server. For example: |
|
|
|
|
ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=1521) |
|
|
|
|
|
CONNECT_DATA |
This must specify the SID_NAME entry you created in
listener.ora. For example: |
|
|
|
|
CONNECT_DATA=(SID=fb) |
|
|
|
|
|
HS=OK |
Specifies that this connect descriptor connects to a
non-Oracle system. |
Save the file when
you have entered this information.
Use tnsping
<alias> to check that you can connect to the new service. If tnsping
succeeds, you will get a message similar to:
tnsping fb TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 31-JUL-2021 17:19:20Copyright (c) 1997, 2007, Oracle. All rights reserved. Used parameter files:d:\product\11.2.0\db_1\network\admin\sqlnet.ora Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxxx)(PORT=5424)) (CONNECT_DATA= (SID=fb_connection)) (HS=OK))OK (10 msec)If tnsping successfully connects, you can now create a database instance
and try to connect to it.
2.5. Create a Database
Link
To access an ODBC data source through
DG4ODBC for Windows, you need to create a database link. To do this, connect to
SQL*Plus as if connecting to your regular database. At the SQL prompt, create a
new database link. For example:
CREATE PUBLIC DATABASE LINK hslink CONNECT TO"dbuser" IDENTIFIED BY "dbpassword" using 'fb_connection';The previous command creates an Oracle® database link named hslink that
references the tnsnames.ora entry you
created for DG4ODBC for Windows. Note that when
creating the database link, the database user and password need to be supplied.
"dbuser" and "dbpassword" must be a valid Firebird
user name and password.
'fb_connection' is the tnsnames.ora entry you created to identify
DG4ODBC for Windows and must be enclosed in single quotes.
After creating the database link, try verifying the connection to the
ODBC data source by running a SQL query. For example:
select * from “remote_table”@hslink;
