Oracle Gateway 12c Installation Steps
From the software directory run the setup.exe file as an administrator. Then complete the below 8 steps wizard.
ODBC System DSN Configuration to connect to Microsoft SQL Server database
On the server create a new ODBC System DSN Name as below to connect to Microsoft SQL Server Database and test the connectivity.
Oracle Gateway 12c 12.1.0.2 Configuration
a. Update the Oracle sqlnet.ora, tnsnames.ora & listener.ora files as part of gateway configuration.
b. Create a new initSQLServerSID.ora file as below,
c. Start the Listener service on the Gateway server,
d. Update the C:\Windows\System32\drivers\etc\hosts file on the Gateway server and add the below entry,
127.0.0.1 localhost
e. Verify the successful TNSPING for the SQL Server's TNSENTRY that you did in the above tnsnames.ora file.
Configuration on Oracle Database Server to connect to SQL Server via the new Oracle 12c Gateway
Perform the below steps on any oracle database server from where you want to access data from Microsoft SQL Server database via the new Oracle Gateway 12c server,
a. Add the TNSENRY of Gateway server on the Oracle Database Server,
XXMYSQLDB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=MYGATEWAYSRV)(PORT=1521))
(CONNECT_DATA=(SID=XXMYSQLDB))
(HS=OK)
)
b. Now create a database link object to connect to the SQL Server database.
CREATE PUBLIC DATABASE LINK MYDBLINK
CONNECT TO <SQL Server DB Username>
IDENTIFIED BY <SQL Server DB User password>
USING 'XXMYSQLDB';
c. Now you can run the below query from Oracle database to get the result from SQL Server XXMYSQLDB Database.
SELECT * FROM ALL_TABLES@MYDBLINK;
For any queries please don't hesitate to contact me on samiora@gmail.com
