Environment
Details:
Operating Environment: Windows Server 2012
R2 Evolution Version
Database Environment: Microsoft Sql Server
2008 R2 32bit/64Bit
Oracle Database Environment:
OS Linux 7.5 64bit With EBS R12 (12.1.3) and RDBMS 12.1.0.2.0 EE
Step – 1
A-
Installation
of Oracle Gateways (Heterogenic Services) 11.2.0.4.0 64bit
Patch No: (p13390677_112040_MSWIN-x86-64_5of7)
Note:
Oracle Gateways certified version
for 64bit-ODBC and SQL-Server including Operating System.
Starting with 11R2g
Oracle now provides a Database Gateway for ODBC for 64-bit Windows operating
systems, which can be used to connect to any third party database using a
suitable third party 64-bit ODBC driver.
Please be aware:
- DG4ODBC is first ported to 64bit Windows
platforms starting with 11.2 and it is NOT supported to use a 32bit DG4ODBC on
a 64bit Windows operating system nor can you use a 32bit ODBC driver with a
64bit DG4ODBC.
The gateway is certified also for older Oracle
releases 9.2.0.8, 10.1.0.5, or 10.2.0.3. But please be aware those pre-11g
Oracle databases require a patch to work properly with V11 Gateways.
The patch can be found on My Oracle Support by
performing a simple search under the 'Patches & Updates' tab at the top of
the page. Choose 'Simple Search’, enter 5965763 in the block for the patch
number, and select the appropriate platform from the Platform or Language list.
This compatibility patch is already included in
the 10.2.0.4 patch-sets.
B-
Click Run
Button for Installation.
C-
Installer is automatic checking UI Pre-req
D-
Click Next
Button
E-
Click Browse...
If you want to change the installation directory location if it is your need.
Otherwise Click Next Button to
Continue.
F-
Before Checking Product-Specific
Prerequisite Click Next Button
G-
Check
In (√) Two
Production for Database Gateways
1-
Oracle
Database Gateway for Microsoft SQL Server 11.2.0.4.0
2-
Oracle
Database Gateway for ODBC 11.2.0.4.0
Then Click Next Button
H-
Loading Java Development Kit for
Installation
I-
You will write of your Microsoft Sql-Server
2008 information there like this.
1-
Sql
Server Database Server Host Name: GTECHPC ( This is host name of Microsoft
Sql Server)
2-
Sql
Server Instance Name: ORCL (This is Microsoft SQL-Server Instance name you
can get from Sql-Server)
3-
Sql
Server Database Name: Northwind (This is your created database there you
will store our business data as well
4-
All Information of Microsoft Sql Server
Should be correct because it will create internal connectivity link with ODBC
Then Click Next Button
J-
Processing Precompiler Support Files…
K-
Click Install
Button for Installation
L- Installation Continue…
M-
At the end we will be creating LISTENER and TNS just for Name of Listener
and tns because both has not certified for Oracle Gateways (HS) after creating
we will delete all information in listener and tns because of some (HS) miss
information has miss.
N- I am
Creating listener with the name of LISTENERDG4ODBC and TNS for DG4ODBC.
O-
Click Yes
button for Finished Installation.
P-
After Installation we will check dg4odbc and
dg4msql is available in Oracle Gateways ORACLE_HOME/Bin
Q-
Listener Service add in Services.msc Like
below:
Create ODBC for Connectivity
Step – 2
A- Click
ODBC Data Sources (64-bit)
B- Click
System DNS Tab and Click Add Button
C- Click SQL Server Line and Click Finish Button
D- You
will write and select correct information for DNS Configuration
1- Name: dg4odbc
2-
Description:
dg4odbc
3-
Server:
GTECHPC\ORCL
Then Click Next
E- Go
with default configuration and Click Next
Button
F- Check In (√) Change the default database to: and
Select Northwind Custom Database for
Default and Business Data. Then Click Next
Button
G- Go
with default configuration and Click Finish
Button
H- Click
Test Data Source Button for Testing
of ODBC DSN
I- Click
OK Button
J- Click
OK Button
K- Click
OK for Final Close
Heterogenic Service
Configuration on Oracle Gateways
Step – 3
A- Create and Change init<SID>.ora File
1- Create
init<SID>.ora file
2- Location
“D:\product\11.2.0\tg_1\hs\admin “
3- If
your DSN-ODBC name is dg4odbc so you will create initdg4odbc.ora file in above location
like:
4- Change
information in initialization initdg4odbc.ora
file
D:\product\11.2.0\tg_1\hs\admin\
initdg4odbc.ora
# 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 = <odbc
data_source_name>
HS_FDS_TRACE_LEVEL =
<trace_level>
#
# Environment variables
required for the non-Oracle system
#
#set
<envvar>=<value>
Change like this: first, copy both parameters, commend off then paste and
change the values with save.
D:\product\11.2.0\tg_1\hs\admin\
initdg4odbc.ora
# 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 = <odbc data_source_name>
#HS_FDS_TRACE_LEVEL = <trace_level>
HS_FDS_CONNECT_INFO
= dg4odbc
HS_FDS_TRACE_LEVEL
= OFF
#
# Environment variables
required for the non-Oracle system
#
#set
<envvar>=<value>
B- You
will remove old Information in listener.ora
and recreate the listener with new (PROGRAM)
with services.msc name.
1- Old Listener Values.
2- Location: D:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora
# This is a sample listener.ora
that contains the NET8 parameters that are
# needed to connect to an HS
Agent
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\product\11.2.0\tg_1)
(PROGRAM=dg4odbc)
)
)
#CONNECT_TIMEOUT_LISTENER = 0
3- You will have add new listener information
as per Configuration Requirement like this:
1- As
per services.msc Registration listener name : LISTENERDG4ODBC
2-
Add Hostname of Sql Server : GTECHPC.oracle.com
3- Add
Port for Oracle Database : 1521
4- Add
SID_NAME this is DSN-ODBC Name Here: dg4odbc
5- Add
PROGRAM which is you are used for Connectivity (It will same DSN-ODBC) : dg4odbc
# Listener.ora Network Configuration File:
D:\product\11.2.0\tg_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENERDG4ODBC
=
(ADDRESS_LIST=
(ADDRESS= (PROTOCOL=tcp)
(HOST=GTECHPC.oracle.com)
(PORT=1521))
)
SID_LIST_LISTENERdg4odbc=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\product\11.2.0\tg_1)
(PROGRAM=dg4odbc)
)
)
#CONNECT_TIMEOUT_LISTENERdg4odbc = 0
C- You
will remove old Information in tnsnames.ora
and recreate the TNS with new (HS) Prameter.
1- Old tnsname.ora values:
2- Location: D:\product\11.2.0\tg_1\NETWORK\ADMIN\
tnsnames.ora.
# tnsnames.ora Network Configuration File:
D:\product\11.2.0\tg_1\network\admin
dg4msql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
3- You will have add new listener information
as per Configuration Requirement like this:
1-
Write of your tnsname which is DSN-ODBC Name
Here: dg4odbc
2-
Add Hostname of Sql Server : GTECHPC.oracle.com
3-
Add Port for Oracle Database : 1521
4- Add
CONNECT_DATA this is DSN-ODBC Name Here: dg4odbc
5- Add
HS Parameter which is you are used for Connectivity: OK
# tnsnames.ora Network Configuration File:
D:\product\11.2.0\tg_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
dg4odbc
=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=GTECHPC.oracle.com)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
D- You
will start listener and check all Services
that is depended on listener
#lsnrctl start LISTENERDG4ODBC
#lsnrctl Status LISTENERDG4ODBC
#tnsping dg4odbc
Oracle Database DB Link
Configuration with Oracle Gateways (Heterogenic Service)
Step – 4
A- Add Oracle Gateways tnsname service in Oracle Database where you want to create db link for accessing Microsoft
SQL-Server.
B- Source you Database environment
[oracle@dbsc ~]$ source /u02/oracle/db/tech_st/12.1.0/DUMM_dbsc.env
C- Open
your tnsname.ora and you will add there Oracle Gateways services
for connectivity with Microsoft SQL Server and test tsnping is working fine.
[oracle@dbsc ~]$ cd $TNS_ADMIN
[oracle@dbsc DUMM_dbsc]$ vi tnsnames.ora
dg4odbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=GTECHPC.oracle.com)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
[oracle@dbsc ~]$ tnsping dg4odbc
TNS Ping Utility for Linux: Version 12.1.0.2.0 -
Production on 26-SEP-2019 11:10:07
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u02/oracle/db/tech_st/12.1.0/network/admin/DUMM_dbsc/sqlnet_ifile.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=GTECHPC.oracle.com)(PORT=1521)) (CONNECT_DATA=(SID=dg4odbc)) (HS=OK))
OK
(10 msec)
[oracle@dbsc ~]$
D- You
will Connect with SYS AS SYSDBA for Creating DB Link and Create DB link with “sa” sysadmin user in SQL-Server and furqan
is local user in SQL-Server
[oracle@dbsc DUMM_dbsc]$ sqlplus / as sysdba
SQL> Create public database link dg4odbc connect to "sa"
identified by "Password123" using 'dg4odbc';
SQL> Create public database link furqan connect to "furqan"
identified by "Oracle_12" using 'dg4odbc';
SQL>
desc "dbo"."Employees"@dg4odbc;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
EmployeeID NOT NULL
NUMBER(10)
LastName NOT NULL
NVARCHAR2(20)
FirstName NOT NULL
NVARCHAR2(10)
Title
NVARCHAR2(30)
TitleOfCourtesy NVARCHAR2(25)
BirthDate DATE
HireDate DATE
Address
NVARCHAR2(60)
City
NVARCHAR2(15)
Region
NVARCHAR2(15)
PostalCode
NVARCHAR2(10)
Country
NVARCHAR2(15)
HomePhone
NVARCHAR2(24)
Extension
NVARCHAR2(4)
Photo
LONG RAW
ReportsTo
NUMBER(10)
PhotoPath
NVARCHAR2(255)
Salary
NUMBER(10)
SQL>
select "EmployeeID","LastName","FirstName" from
"dbo"."Employees"@dg4odbc;
EmployeeID
LastName
FirstName
----------
---------------------------------------- --------------------
1 Davolio Nancy
2 Fuller ali
3 Leverling Janet
4 Peacock Margaret
5 Buchanan Steven
6 Suyama Michael
7 King Robert
8 Callahan Laura
9 Dodsworth Anne
12 Majid Mansoor
13 asif khan
21 rashif ayaz
22 ali raza kashif
23 khan ali asif
24 kashif ali
25 khan ali asif
26 ali jan noman
27 ali jan noman
30 qureshi asif
31 zafar kashif
32 ali kashif
33 erre ali
34 Khan Ali
23
rows selected.
2092
rows selected.
SQL>
select * from "sys"."all_objects"@dg4odbc
2092
rows selected.
SQL>
col OWNER format a10
SQL>
col DB_LINK format a30
SQL>
col USERNAME format a10
SQL>
col HOST format a10
SQL>
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links where DB_LINK like
('%DG4ODBC%');
OWNER DB_LINK USERNAME HOST
----------
------------------------------ ---------- ----------
PUBLIC DG4ODBC.ORACLE.COM sa dg4odbc
SQL>
Source: SR/Internal Research
