You have to do the following (Windows2008R2 is the OS on both of the servers: Oracle server (11G) and MS SQL server 2012):
- Install Oracle ODBC driver on the Oracle database server where the instance(database) you are using to access the remote MS SQL server database
- 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
- Run this command to check for global names paremeter in Oracle is set to false:
- Create initODBCNAME.ora in $ORACLE_HOME$\dbhome_1\hs\admin
- HS_FDS_CONNECT_INFO=ODBCNAME
- 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))
- Modify the tnsnames.ora
- ODBCNAME.WORLD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLESERVER)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ODBCNAME) ) (HS=OK) )
- Restart the listener.ora (through services or through the command line)
- Create database link in Oracle database
- CREATE PUBLIC DATABASE LINK “ODBCNAME.WORLD” CONNECT TO “SQL_USER” IDENTIFIED BY “XXX” USING ‘ODBCNAME.WORLD’;
- 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
Tags: db link, HS, MSSQL, odbc, oracle