How to trace Oracle heterogeneous gateway for ODBC

Tracing Oracle heterogeneous gateway for ODBC

Enabling heterogeneous services tracing

To enable HS gateway tracing set HS_FDS_TRACE_LEVEL initialization parameter to DEBUG:


cd $ORACLE_HOME\hs\admin 

Set HS_FDS_TRACE_LEVEL to DEBUG in init<HS-SID>.ora file:

type initSQLSRV1.ora
#
# HS odbc access to sales SQL Server
#
HS_FDS_CONNECT_INFO = SQLSRV1
HS_FDS_TRACE_LEVEL = DEBUG

Restart HS listener so that new parameter value takes effect


C:\> lsnrctl reload LISTENERHS

Check trace file in $ORACLE_HOME\hs\trace directory

Now whey an SQL is issued accessing remote SQL Server, trace files will be generated in $ORACLE_HOME\hs\trace directory. Tracing slows HS connection down dramatically so do trace only when necessary.


cd %ORACLE_HOME%\hs\trace

E:\app\oracle\product\12.2.0\dbhome_1\hs\trace>dir
 Volume in drive E has no label.
 Volume Serial Number is 307B-C197

 Directory of E:\app\oracle\product\12.2.0\dbhome_1\hs\trace

03/10/2019  11:19 AM    <DIR>          .
03/10/2019  11:19 AM    <DIR>          ..
03/10/2019  11:21 AM            20,363 SQLSRV1_agt_6752.trc
               1 File(s)         20,363 bytes
               2 Dir(s)  903,214,813,184 bytes free

E:\app\oracle\product\12.2.0\dbhome_1\hs\trace>