Thursday, January 21, 2016

RDBMS-- Oracle to SQLSERVER dblink

When we talk about creating a Dblink from an Oracle Database (Oracle 11g or above) To MSSQL server, we actually talk about dg4odbc.
Well, creating a database link from an Oracle Database to a Microsoft Sql Server Database is based on the binary named DG4ODBC.(Database Gateway For ODBC) DG4ODBC is a new program that replaces hsodbc, which was used in earlier Oracle Database releases.
Actually, DG4ODBC is not the only thing required for this operation. There are linux drivers(I m talking about Oracle On Linux env bytheway, as %99 of the Oracle Environments that I have dealed with were  running on Linux), driver manager, oracle clients and driver managers that makes an Oracle to Sql Server dblink available to function.
The full path of reaching an Sql Server databae from an Oracle Instance is as follows:
Suppose we are using sqlplus to query the sql server;

"SQL*Plus -> Oracle Client -> DG4ODBC instance -> unixODBC -> ODBC driver -> Database"

So what we do here is ; we execute our query in a query tool such as sqlplus.
This tool used oracle client and when it sees the dblink, it executes the DG4ODBC program via the Oracle Database listener.
DG4ODBC passes the work to te Linux ODBC Driver Manager
Linux ODBC Driveer manager triggers the MS SQL Server ODBC driver to be taken to the stage and the MS Sql ODBC Driver of Linux connects to the Sql Server database and our query executed from Oracle Database gets data from Sql Server.

So as you may imagine , all of these components except the sqlplus and oracle client(there are already configured and nothing needs to be done for them) needs a proper configuration for this.

What we do to configure?

1) We first install a Microsoft Sql Server Driver to Linux OS where our Oracle database is running.
Then we add sql server related configuration to the odbc.ini as shown in the example below;
Note that: we specify the driver that comes with the ms sql server driver installation with the "Driver=" setting.

[root@ermanserver]# cat /etc/odbc.ini
[SQLSERVER_ODBC_DSN]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Server=10.54.54.12
Database=ERMANSQL
User=ermanuser
Password=ermanpass
QuotedId=YES
AnsiNPW=YES
Threading=1
UsageCount=1

Then we add the sql server driver related information to the odbcinst.ini as shown in the example below;
Note that: the installation driver may do this automatically, but still it is good to be checked

[root@ermanserver]# cat /etc/odbcinst.ini
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbc.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbc.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1
[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc3_r.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc3_r.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1
[PostgreSQL64]
Description=ODBC for PostgreSQL (64 bit)
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS64.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS64.so
FileUsage=1
[MySQL64]
Description=ODBC for MySQL (64 bit)
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS64.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS64.so
FileUsage=1
[ODBC Driver 11 for SQL Server]  ----> "here is our driver information"
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1

Next, we check the sqlserver connection and see if it can be done using our newly installed driver and its configuration.
isql utility can be used here.

[root@ermanserver ]# . /root/unixODBC-2.3.0/exe/isql -v SQLSERVER_ODBC_DSN ermanuser ermanpassword
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

After seeing the SQL> prompt, we can say that our work with Linux configuration files are finished.

We jump to Oracle Database home and create an .ora file in $ORACLE_HOME/hs/admin directory.
Note that: the file name should be in the following format init<DSN_NAME_WE_HAVE_CONFIGURED_IN_ODBCINSTFILE>.ora

[oracle@ermanserver ~]$ cd $ORACLE_HOME/hs/admin

[oracle@ermanserver admin]$ ls
initSQLSERVER_ODBC_DSN.ora

The init file is important as we decleare lots of heterogenous parameters in this file.
The most important setting is the HS_FDS_SHAREABLE_NAME setting, as we set the Linux's ODBC driver manager related System object file(/usr/lib64/libodbc.so) here. Driver manager will choose our driver when it is needed.

[oracle@ermanserver admin]$ cat initSQLSERVER_ODBC_DSN.ora
HS_FDS_CONNECT_INFO=SQLSERVER_ODBC_DSN
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_FDS_TRACE_LEVEL=255
HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
HS_NLS_LENGTH_SEMANTICS = CHAR
HS_LANGUAGE=AMERICAN_AMERICA.WEISO8859P2
HS_NLS_NCHAR = UCS2
set ODBCINI=/etc/odbc.ini

After configuring the init file, we configure our listener.ora and tnsnames.ora files
Note that : we have used the IFILE here because this example was done on an autoconfig enabled EBS environment and that's why we didnt want our newly added tns entry to be overwritten by a future autoconfig run.
Also note that: we set the hostname and port of our local Oracle Database environment, as we want the clients to reach the listener and then the listener to execute the DG4ODBC program to connect them internally to sqlserver.

[oracle@ermanserver admin]$ cat /u01/app/oracle/product/11.20/dbhome_1/network/admin/PROD_ermanserver/PROD_ermanserver.ora

SQLSERVER =
    (DESCRIPTION =
       (ADDRESS =
          (PROTOCOL = tcp)
          (HOST = ermanserver)
          (PORT = 1523))
       (CONNECT_DATA =
          (SID = SQLSERVER_ODBC_DSN ))
       (HS = OK)
    )

In listener.ora file, we also use the database host and port because of the same reason.
Note that: all the settings related to odbc are important here. (PROGRAM, ENVS=LD_LIBRARY_PATH etc..)
Bytheway, we have added our sqlserver related configuration directly to the listener.ora here, you can add it to the ifile of the listener :) to prevent it getting overwritten by autoconfig. :)

[oracle@ermanserver admin]$ cat listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ermanserver)(PORT = 1523))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = PROD)
    )
        (SID_DESC =
          (SID_NAME = SQLSERVER_ODBC_DSN)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
          (PROGRAM = /u01/app/oracle/product/11.2.0/dbhome_1/bin/dg4odbc)
          (ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0:/usr/lib64:/u01/app/oracle/product/11.2.0/dbhome_1/lib)
       )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
LOG_DIRECTORY_LISTENER = /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
LOG_FILE_LISTENER = LISTENER
TRACE_DIRECTORY_LISTENER = /u01/app/oracle/product/11.2.0/dbhome_1/network/admin
TRACE_FILE_LISTENER = LISTENER
ADMIN_RESTRICTIONS_LISTENER = OFF
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF

IFILE=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener_ifile.ora

That's all :) We reload our listener and start using our dblink by  creating a dblink with the following syntax.

CREATE DATABASE LINK MYSQLSERVER CONNECT TO ermanuser identified by ermanpass
USING 'SQLSERVER';

Example query select * from "dbo"."tablename"@SQLSERVER

Well... We have reached the end of this post. Note that: The configuration files used in this post in taken from a real system. So this is a working example . I hope you find it useful.

10 comments :

  1. Hi, I created a ODBC also I'm trying to be used by the Concurrent Request, but can't get it working... I have a 1 server DB where the ODBC is configured and a Apps Server. Is there are some way to allow the Apps Server to use the dblink??? currently when trying to do it the same query that run ok in DB, I'm getting the message: TNS:could not resolve the connect identifier specified message.

    Any idea????

    ReplyDelete
  2. From apps server connect to the database, and from inside the database/using the db session, use the db link.
    use the same db link owner to connect to the database. (if your db link is not a public one)

    Note that: ODBC data source and db link are two different thing.

    ReplyDelete
    Replies
    1. Hi,

      Thanks for the update. I tried to connect from the Application Server to the DB using the APPS user the same as I'm doing in the Database Server and have the same error...

      Delete
  3. It is impossible. If you connect to the database using the db link owner schema and use the dblink from that session, then you should be able to use it without any problems.

    If you cant even use that dblink from a database connection (connect using toad or sqlplus and check), then your db link is not configured properly.

    ReplyDelete
  4. Hi,

    I can connect from the Database Server fine... I know my dblink to sqlserver is working, but connecting from Apps Server by sqlplus with the same apps user is not working

    ReplyDelete
  5. it s not normal, send me screenshots. email: earslan@partnera.com.tr

    ReplyDelete
  6. Hi,
    Just got the issue solved. I had to created the dblink using the full '(DESCRIPTION=(ADDRESS=...)'
    Tested from Application Server and from Concurrent Manager and is working.
    Thanks for your help.

    ReplyDelete
  7. I guess that, your local test was not done using the listener. You were making a local connection / not via the listener, right?
    You were doing sqlplus apps/apps (this is fully local, not like concurrent managers connections), rather than sqlplus
    apps/apps@DBNAME(this is via listener, like concurrent managers connections)

    If that 's the case, it is normal altough I have said that it is not normal.

    The location of the Oracle Net files for the origin or source database is not the same when you log in via the listener versus when you log in locally. So the tnsnames.ora file that is referenced for a remote connection may not be correct.

    So, your fix is correct, however you can add your tns to the sqlnet files that are used by the session connected via the listener and thus you can still use the tns alias (rather than using a direct definition like Description=address... and son) for the dblink definition.

    Oracle Support note:
    Database Link Accessed Remotly Fails with ORA-12154, Locally Works (Doc ID 1593961.1)

    ReplyDelete
  8. Hello. Informative blog. It is really useful. I am a student and I love software. I always read blogs posted on ORACALE, MYSQL & SQL. I have gone through a website named as www.dbload.com which provides good information on software. Have a look. You may also get help.

    ReplyDelete
  9. :) Thanks Roberto

    ReplyDelete

If you will ask a question, please don't comment here..

For your questions, please create an issue into my forum.

Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html

Register and create an issue in the related category.
I will support you from there.