Configuring ODBC Gateway for SQL Server

Posted: April 9, 2014 in Database Administration, Database Development
Tags:

Greetings, Oracle friends!

In case you were desperate for my next blog post, never fear… I have returned! I had a good week off although I was called in on Thursday. But now, I am officially back!

This week, I am discussing the Oracle ODBC Gateway:

My current company obtains data from a SQL Server 2008 database from a separate company. That data is replicated over to our company on another SQL Server database. The data is then transferred to our Oracle database. It is accessed through our data warehouse and Web Applications. Up until recently, we used a package that transfers the data a thousand rows at a time over two separate connections. Needless to say, it is quite a slow process. One of the developers asked me if I had any suggestions.

My first suggestion was to scrap SQL Server and go with all Oracle.

That suggestion was met with a little bit of laughter ending abruptly when my manager shot back with…

We could always scrap Oracle.

So I took to Twitter and asked a trusted source, @dbakevlar, if she had any suggestions. She leaned over and asked @timothyjgorman. The response I got was Oracle Gateway.

Cool! Oracle Gateway! What’s that?

The rest is history. Tim and I began to look into installing this ODBC Gateway which is included in Oracle 11.2.0.3 with no licensing charge! For those of you looking for a good way to interface with SQL Server, read on!!


The first step is to install the base installation of the DG4MSQL software on the TESTserver…

After the base installation of the DG4MSQL software on the Test server first…

• Source software directory: /u03/app/oracle/stage/11.2.0/gateways
• New ORACLE_HOME: /u03/app/oracle/product/11.2.0/tg_1

The only things that needed to be changed to configure the gateway are…

• Within the RDBMS ORACLE_HOME directory (i.e. “/u01/app/Oracle/product/11.2.0/dbhome_1”) used by the databases (such as ODS_TST, etc)

o In the directory “$ORACLE_HOME/network/admin”, the file “tnsnames.ora” needs the following entry…

database_name, database_url.com =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=url.com)(PORT=####))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)

• Within the gateway ORACLE_HOME (i.e. “/u03/app/oracle/product/11.2.0/tg_1”)…

o In the directory “$ORACLE_HOME/dg4msql/admin”, the file “initdg4msql.ora” needs the following entry…

HS_FDS_CONNECT_INFO=host_name:40501//DATABASENAME

Where:
• Host name is the hostname where the SQL Server instance resides
• Port# is the port on that server
• DATABASENAME is the database

o In the directory “$ORACLE_HOME/network/admin”, the file “listener.ora” needs the following entry…

SID_LIST_LISTENER_DG4MSQL =
(SID_LIST=
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = /u03/app/oracle/product/11.2.0/tg_1)
(ENV = “LD_LIBRARY_PATH=/u03/app/oracle/product/11.2.0/tg_1/dg4msql/driver/lib:/u03/app/oracle/product/11.2.0/tg_1/lib”)
(PROGRAM = dg4msql)
)
)

Where:
• SID_NAME should be something business appropriate but we kept the generic name “dg4msql” for listing purposes.
• ORACLE_HOME is the Oracle Home directory of the gateway software
• ENV needs to be set to the directories “$ORACLE_HOME/dgrmsql/driver/lib” and “$ORACLE_HOME/lib”, but using the full pathnames, as shown
• PROGRAM needs to be “dg4msql” to indicate the gateway executable file in the “$ORACLE_HOME/bin” directory

o When changes are made to the “listener.ora”, the LISTENER_DG4MSQL listener needs to be stopped and restarted…

$ dg4msql
$ lsnrctl stop listener_dg4msql
$ lsnrctl start listener_dg4msql

…another alternative (if an alias named “dg4msql” isn’t yet set up) is…

$ export ORACLE_SID=dg4msql
$ export ORAENV_ASK=NO
$ . oraenv
$ unset ORAENV_ASK
$ lsnrctl stop listener_dg4msql
$ lsnrctl start listener_dg4msql

• For testing, get into one of the database environments and run “tnsping”…

dbtst01:/home/oracle(sid_name)> tnsping <DatabaseName>  **the name used in the “tnsnames.ora” file above

TNS Ping Utility for Linux: Version 11.2.0.3.0 – Production on 07-MAR-2014 16:13:41

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
/u01/app/Oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora   **notice which ORACLE_HOME is in use
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=url.com)(PORT=1527)) (CONNECT_DATA=(SID=dg4msql)) (HS=OK))
OK (0 msec)

 

 


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s