Databaselink from Oracle to MS SQL database (heterogeneous database connections)

14 Feb

You have to do the following (Windows2008R2 is the OS on both of the servers: Oracle server (11G) and MS SQL server 2012):

    1. Install Oracle ODBC driver on the Oracle database server where the instance(database) you are using to access the remote MS SQL server database
    2. Use “ODBC Data Source Administrator” to create “System DSN” for the connection:
      • Choose the SQL Server driver
      • Supply the name for the connection (ODBCNAME)
      • Supply the sql server (and or with instance name like sqlserver1\database1
      • Choose either Windows authentication or create a user in that database to with read access in MS SQL (SQL_USER with db_datareader)
      • Choose default database to access
      • Finish and test the connection
    3. Run this command to check for global names paremeter in Oracle is set to false:
      • show parameter global
    4. Create initODBCNAME.ora in $ORACLE_HOME$\dbhome_1\hs\admin
      • HS_FDS_CONNECT_INFO=ODBCNAME
    5. Modify the listener.ora
      • for Oracle 11g: (SID_DESC = (SID_NAME = ODBCNAME) (ORACLE_HOME = E:\Oracle\product\11.2.0\dbhome_1) (PROGRAM = dg4odbc))
      • for Oracle 10g: (SID_DESC = (SID_NAME = ODBCNAME) (ORACLE_HOME = E:\Oracle\product\10.2.0\dbhome_1) (PROGRAM = hsodbc))
    6. Modify the tnsnames.ora
      • ODBCNAME.WORLD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLESERVER)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ODBCNAME) ) (HS=OK) )
    7. Restart the listener.ora (through services or through the command line)
    8. Create database link in Oracle database
      • CREATE PUBLIC DATABASE LINK “ODBCNAME.WORLD” CONNECT TO “SQL_USER” IDENTIFIED BY “XXX” USING ‘ODBCNAME.WORLD’;
    9. Test run a sql query which uses the database linke e.g
      • select sysdate from dual@ODBCNAME.WORLD

Other useful links:

http://dbaforums.org/oracle/lofiversion/index.php?t21694.html

http://www.dba-oracle.com/t_heterogeneous_database_connections_sql_server.htm

http://www.orafaq.com/forum/t/171463/0/

http://docs.oracle.com/cd/B28359_01/gateways.111/b31042/configodbc.htm

http://www.datadirect.com/resources/odbc/oracle-heterogeneous/common-error.html

http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm

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

%d bloggers like this: