You Asked

My goal is to enable logging and tracing for the oracle database using the oracle client and npm package oracledb.

I want to check logs for any errors and want to trace slow queries or queries for which a timeout error has occurred or any deadlock that occurs.

So far I'm successfully able to enable logging and tracing for the oracle database client and it's working only when I use sqlplus CLI but when it comes to the oracledb npm package it's not working.

I have created sqlnet.ora in $ORACLE_HOME/network/admin directory, and then pasted this content to enable logging and tracing for oracle-client.

LOG_DIRECTORY_CLIENT=C:\Oracle\instantclient_21_3\network\admin\cl-log
LOG_DIRECTORY_SERVER=C:\Oracle\instantclient_21_3\network\admin\srv-log
LOG_FILE_CLIENT=client
LOG_FILE_SERVER=svr.log
TRACE_LEVEL_CLIENT=ADMIN
DIAG_ADR_ENABLED=ON
ADR_BASE=C:\Oracle\instantclient_21_3\network\admin\log-adr

And as far as the implementation of oracledb with NodeJs is concerned I create connection pools using oracledb.createPool({...config}) and then when I need to query into DB I use oracledb.getPool('poolName').execute(query).

To me, the error is that it creates random

.trc

files and does not put logs to the sqlnet.log file only when using the oraceldb npm package.

and Connor said...

You need to let the oracledb module know that you are picking up sqlnet.ora entries etc, eg

const fs = require('fs');
const oracledb = require('oracledb');
oracledb.initOracleClient({libDir: 'C:\\oracle\\instantclient_21_03', configDir: 'C:\\oracle\\instantclient_21_03\\network\admin'});

I added that to my oracledb initialisation along with sqlnet.ora entries similar to yours

LOG_DIRECTORY_CLIENT=C:\oracle\instantclient_21_03\network\admin\log
LOG_DIRECTORY_SERVER=C:\oracle\instantclient_21_03\network\admin\log
LOG_FILE_CLIENT=client
LOG_FILE_SERVER=svr.log
TRACE_LEVEL_CLIENT=ADMIN
DIAG_ADR_ENABLED=ON
ADR_BASE=C:\oracle\instantclient_21_03\network\admin\log

, and got this under my log area

C:\oracle\instantclient_21_03\network\admin\log>dir /s /b
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\alert
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\cdump
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\incident
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\incpkg
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\lck
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\log
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\metadata
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\metadata_dgif
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\metadata_pv
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\stage
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\sweep
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\alert\log.xml
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\lck\AM_1096102193_3488045378.lck
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\lck\AM_1096102262_3454819329.lck
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\lck\AM_1744845641_3861997533.lck
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\lck\AM_3216668543_3129272988.lck
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\log\attention
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\log\debug
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\log\test
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\metadata\ADR_CONTROL.AMS
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\metadata\ADR_INVALIDATION.AMS
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\metadata\INC_METER_IMPT_DEF.AMS
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\metadata\INC_METER_PK_IMPTS.AMS
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_11520.trc
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_11520.trm
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_22704.trc
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_22704.trm
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_23056.trc
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_23056.trm
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_36428.trc
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_36428.trm
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_39772.trc
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\ora_39176_39772.trm
C:\oracle\instantclient_21_03\network\admin\log\oradiag_connor\diag\clients\user_connor\host_2576541751_120\trace\sqlnet.log

Good info here

https://oracle.github.io/node-oracledb/INSTALL.html

Is this answer out of date? If it is, please let us know via a Comment