===========Oracle Database Gateways=======



To access non Oracle DB systems we need to use Oracle Heterogeneous Services.


Oracle Database Gateways Provide Heterogeneous data access.


Oracle Database Gateways provide the ability to transparently access data residing
in a non Oracle systems from an Oracle Environment


Steps:


Download and install oracle database gateway.
[ Ссылка ]


----done


Create ODBC data source.
---name mytestodbc


Configure initdg4odbc.ora file in oracle gateway home.

---gateway home : C:\app\tg\Ekramul\product\19.0.0\tghome_1\hs\admin
copy initdg4odbc.ora and past with a new name


HS_FDS_CONNECT_INFO = mytestodbc
HS_FDS_TRACE_LEVEL = OFF


Configure listener.ora in oracle gateway home.
---add below lines


SID_LIST_LISTENER_ODBC =
(SID_LIST =
(SID_DESC =
(SID_NAME = mytestodbc)
(ORACLE_HOME = C:\app\tg\Ekramul\product\19.0.0\tghome_1)
(PROGRAM = dg4odbc)
)
)


Configure tnsname.ora in oracle database home.

---oracle 19c database tns file
---add below lines

mytestodbc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-53PA6MR)(PORT = 1525))
(CONNECT_DATA =(SID=mytestodbc))
(HS=OK)
)


now restart services

lsnrctl reload
or from services


now check from cmd by tnsping

My 19c database tns name is : orcl
My 19c gateway database tns name is : mytestodbc


tnsping orcl
tnsping mytestodbc


export environment
set oracle_home=E:\app\19c
set oracle_sid=orcl




Create database link to connect with MSSQL server.
sqlplus / as sysdba


create database link mylink01 connect to "sa" identified by "Asdf123#" using 'mytestodbc';


select * from test_tbl@mylink01;

свернуть