Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
In SQL Server I can create a view that is a join between two tables that are in completely different servers using Linked Server. If I change one of the servers from SQL Server to be Oracle, can I still do the same?
Here is how I created a database link so that I could connect to SQL Server from my Oracle database.
My existing environment:
a Windows server running Oracle 12c with Oracle Database Gateway for ODBC component installed. (To check, look on the Oracle DB server. If %ORACLE_HOME%\BIN\dg4odbc.exe exists, Database Gateway is probably installed already.) Oracle instance is operational and fully functional in our organization, so important listener.ora and tnsnames.ora files are configured correctly. I ran Select * from v$parameter where name = 'global_names' and confirmed that the value was false.
a separate Windows server running Microsoft SQL Server. This is what I wanted to connect to from my Oracle DB. I had a SQL Server authentication login (username/password) configured for use when connecting from Oracle, and made sure it had the necessary access to the SQL Server databases I want to query.
On Windows server hosting Oracle database, I opened up ODBC (64-bit) and created a System DSN pointing to my Microsoft SQL Server. I tested it and connectivity was successful. (TIP: make sure to set the database you want to connect to as the default database in this DSN connection, as it maywill be the only one you can access from your Oracle link, if you have multiple databases.)
I created a file called init?????.ora in the directory %ORACLE_HOME%\hs\admin\, where ????? is the name of the System DSN I created in Step 1. This file contains the following lines:
Edited my listener.ora file located at %ORACLE_HOME%\NETWORK\ADMIN\. There were already a bunch of SID_DESC items nested within (SID_LIST_LISTENER = (SID_LIST = ... We need to add one (properly nested) for our ODBC System DSN:
(PROGRAM = dg4odbc)
(ORACLE_HOME = <path to your ORACLE_HOME>)
(SID_NAME = <System DSN name>)
Created an entry in my tnsnames.ora file (located in same directory as listener.ora):
<Whatever name we give this connection>=
(CONNECT_DATA=(SID=<System DSN name>))
In SQL*Plus or SQL Developer, I created a database link. This is where I entered the credentials needed to connect to the target SQL Server:
CREATE PUBLIC DATABASE LINK <Name of DB Link>
CONNECT TO "user" IDENTIFIED BY "password"
USING '<Name we gave the connection in tnsnames.ora>';
Opened an elevated command prompt and restarted the Oracle listener service by running lsnrctl stop and then lsnrctl start (shouldn't affect connected users...)
Opened up SQL Developer and tested it out by running a query against the now linked SQL Server database (as limited by the access of the account used to access it).
--Basic ODBC connectivity test:
select * from dual@<Name we gave link in Step 5>
SELECT * FROM SomeTable@<Name we gave link in Step 5>;
SELECT * FROM <optional schema.>SomeTable@<Name we gave link in Step 5>;