step by step to read oracle DB data from MSSQL server using linked server in MSSQL database

 Linked Server in MS SQL Server

-----------------------------------

Source : MS SQL 2019 EE on Windows 22

Target : Oracle 19c R2 EE on Linux 8 - IP - 192.168.56.74 Port- 1583 ServiceName- ORA_DB_19c

Pre-Req:

-----------

a. You must have Oracle client installed on MSSQL server.

b. Working Network Connection between both Servers.

   (If required create exception in Firewall.)

c. Remote (Oracle) DB credentials.

d. Proper TNS entry for remote (Oracle) DB.

Steps:

---------

1. Create User in Remote DB Server

Create user oracle identified by oracle123;

grant create session to oracle;

2. Provide Required Permission to User as per your need

grant select any table to oracle;

grant select any dictionary to oracle;

3. Create TNS Entry in Oracle Client TNSNAMES.ora in Source Server (MS SQL Server)

ORA_DB_19c=(DESCRIPTION=

(CONNECT_DATA=

(SERVICE_NAME=ORA_DB_19c))

(ADDRESS=

(PROTOCOL=TCP)(HOST=12.16.53.12)(PORT=1583)

)

)

4. Create Linked Server Using SSMS

You Need 3 Values : 

a. LINKED SERVER NAME -- Any Name you want to use , Just Like any DBLink. --ORACLE_DB

b. Product Name -- Oracle

c. Data Source -- TNS Service Name of Oracle DB

d. Fill Credentials of Remote DB in Security Section --oracle DB user name & password

5. Enable AllowInProcess in SQL Server

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

6. Test Linked Server

7. Fetch records from Oracle DB using Linked Server in MS SQL Server

ORACLE_DB---Linked server name

SELECT * FROM OPENQUERY([ORACLE_DB] , 'select name,open_mode,host_name from v$database,v$instance') ;