Enable JDBC trace for Oracle on the DB service agent.

book

Article ID: 210668

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine

Issue/Introduction

Windows:

java -jar -Xmx1024M -Djava.util.logging.config.file=C:\Automic\Automation.Platform\Agents\sql\bin\OracleTrace.properties -Doracle.jdbc.Trace=true  C:\Automic\Automation.Platform\ServiceManager\bin\..\..\Agents\sql\bin\ucxjsqlx.jar -service -iC:\Automic\Automation.Platform\ServiceManager\bin\..\..\AutomationEngine\bin\ucsrv.ini > C:\Automic\Automation.Platform\Agents\sql\temp\my_trace_file.txt 2>&1

Unix:

 java -jar -Xmx1024M -Djava.util.logging.config.file=/opt/Automic/Automation.Platform/Agents/sql/bin/OracleTrace.properties -Doracle.jdbc.Trace=true -jar /opt/Automic/Automation.Platform/ServiceManager/bin/./../../Agents/sql/bin/ucxjsqlx.jar -service -i/opt/Automic/Automation.Platform/ServiceManager/bin/./../../AutomationEngine/bin/ucsrv.ini > /opt/Automic/Automation.Platform/Agents/sql/temp/my_trace_file.txt 2>&1 &

Content of the file OracleTrace.properties:

#
# set levels
#
.level=ALL
oracle.jdbc.level=ALL
#oracle.jdbc.driver.level=FINEST
#oracle.jdbc.pool.level=SEVERE
#oracle.jdbc.util.level=SEVERE
oracle.sql.level=ALL
#oracle.net.level=FINER
#
# Config handlers
#
oracle.handlers=java.util.logging.ConsoleHandler
java.util.logging.ConsoleHandler.level=ALL
java.util.logging.ConsoleHandler.formatter = \
java.util.logging.SimpleFormatter java.util.logging.SimpleFormatter

Cause

Debugging SQL statements against an Oracle database via JDBC.

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Resolution

In order to create the debug information the diagnosability related jars are needed from Oracle are needed.

ojdbc<version>_g.jar

The prepared statement can then be found in the file called my_trace_file.txt.

Automic variable:

Statement within the trace file:

Mar 16, 2021 9:07:04 PM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINEST: 78969F1C Enter: "SELECT count(*) FROM apps.fnd_concurrent_requests where phase_code <> 'C' and request_id <>? CONNECT BY PRIOR request_id = parent_request_id START WITH request_id = ?"
Mar 16, 2021 9:07:04 PM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINEST: 78969F1C Enter: "SELECT count(*) FROM apps.fnd_concurrent_requests where phase_code <> 'C' and request_id <>? CONNECT BY PRIOR request_id = parent_request_id START WITH request_id = ?", -1, -1

Some Oracle jdbc drivers corrupt the SQL statement, i.e.

Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser computeBasicInfo
FINER: 6B881FC1 Enter: "SELECT count(*) FROM apps.fnd_concurrent_requests where phase_code <> 'C' and request_id <>:1  CONNECT BY PRIOR request_id = parent_request_id START WITH request_id = :2 "
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser computeBasicInfo
FINER: 6B881FC1 Exit [0.047766ms]
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser getTableNames
FINER: 6B881FC1 Enter: 
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser getTableNames
FINER: 6B881FC1 Return: [Ljava.lang.String;@b2f32d2
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser getTableNames
FINER: 6B881FC1 Exit [0.058674ms]
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser getColumnNames
FINER: 6B881FC1 Enter: 
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser getColumnNames
FINER: 6B881FC1 Return: [Ljava.lang.String;@3349541b
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser getColumnNames
FINER: 6B881FC1 Exit [0.026345ms]
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser getParameterMetaDataSql
FINER: 6B881FC1 Return: SELECT aent_request_idTARTITHequest_id FROM appsf
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.OracleParameterMetaDataParser getParameterMetaDataSql
FINER: 6B881FC1 Exit [0.362851ms]
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINE: 1FCDAFCA Public Enter: "SELECT aent_request_idTARTITHequest_id FROM appsf"
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.PhysicalConnection prepareStatement
FINER: 1FCDAFCA Public Enter: "SELECT aent_request_idTARTITHequest_id FROM appsf", -1, -1
Mar 18, 2021 4:53:47 PM oracle.jdbc.driver.PhysicalConnection prepareStatementInternal
FINER: 1FCDAFCA Enter: "SELECT aent_request_idTARTITHequest_id FROM appsf", -1, -1

 

Additional Information

The malformed ojbc.jar is this version:

java -jar ojdbc7_g.jar
Oracle 12.1.0.1.0 JDBC 4.1 debug compiled with JDK7 on Thu_Apr_04_15:44:53_PDT_2013
#Default Connection Properties Resource
#Thu Mar 18 17:19:54 UTC 2021

The working ojbc.jar is this version:

 java -jar ojdbc8_g.jar
Oracle 12.2.0.1.0 JDBC 4.2 debug compiled with javac 1.8.0_91 on Tue_Dec_13_08:42:59_PST_2016
#Default Connection Properties Resource
#Thu Mar 18 17:21:13 UTC 2021

***** JCE UNLIMITED STRENGTH IS INSTALLED ****

 

Attachments