Create Trace for Wallet / Sessions / adrci

 

Generate a Level 16 sqlnet trace for the failing connection.

 To generate the trace files following parameters needs to be added in the sqlnet.ora file of the database.

TRACE_UNIQUE_CLIENT = ON
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = Client_Trace
TRACE_TIMESTAMP_CLIENT = ON
DIAG_ADR_ENABLED=OFF
TRACE_DIRECTORY_CLIENT = <directory where the trace file will be generated>

Reproduce the error. It will generate a trace file in the location specified by TRACE_DIRECTORY_CLIENT. Once the trace file is generated, comment the tracing parameters in the sqlnet.ora file.

Generate SQL Trace

alter session set tracefile_identifier='SRDC_10937';

alter session set events '10937 trace name context forever, level 4';

select utl_http.request('https://xxxx',null,'file:C:\wallet','Oracle123') from dual;

alter system set events '10937 trace name context off';

Generate Trace for A Session using Session#

Enable Trace using SID,Serial#

begin
  dbms_monitor.session_trace_enable (
    session_id => <SID>, 
    serial_num => <serial#>, 
    waits      => true, 
    binds      => true,
    plan_stat  => 'all_executions');
end;

Find out the Trace file Location for the session

select
   r.value                                ||'\diag\rdbms\'||
   sys_context('USERENV','DB_NAME')       ||'\'||
   sys_context('USERENV','INSTANCE_NAME') ||'\trace\'||
   sys_context('USERENV','DB_NAME')       ||'_ora_'||p.spid||'.trc'
   as tracefile_name
from v$session s, v$parameter r, v$process p
where r.name = 'diagnostic_dest'
and s.sid = &1
and p.addr = s.paddr;

Generate tkprof output 

tkprof D:\APP\DBADMIN\diag\rdbms\test\test\trace\test_ora_21376.trc test_ora_21376.out sys=no waits=yes aggregate=no width=180

Once tracing is done, you can disable the trace using below SQL

exec dbms_monitor.SESSION_TRACE_DISABLE(SID,Serial#);

ADRCI

	adrci
		ADRCI: Release 12.2.0.1.0 - Production on Sun Oct 1 11:11:19 2023
		Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
		ADR base = "D:\oracle\app\dev_u"

	adrci> set homepath diag\rdbms\devdb\devdb

	adrci> show incident
	ADR Home = D:\oracle\app\dev_u\diag\rdbms\devdb\devdb:
	*************************************************************************
	INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                            
	-------------------- ----------------------------------------------------------- ----------------------------------------
	887599               ORA 4036                                                                    2022-10-05 14:14:36.278000 +03:00      
    887448               ORA 6544 [pevm_peruws_callback-1]                           2022-10-05 14:52:04.510000 +03:00      
	974403               ORA 700 [kskvmstatact: excessive swapping observed]         2023-02-02 10:57:49.761000 +03:00      
	50 rows fetched (*** more available ***)


	--> generate adrci trace for specifi Issue

	adrci> show incident -p "problem_key='ORA 600'"
	ADR Home = D:\oracle\app\dev_u\diag\rdbms\devdb\devdb:
	*************************************************************************
	INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                            
	-------------------- ----------------------------------------------------------- ----------------------------------------
	1015838              ORA 600                                                     2023-09-28 22:03:22.450000 +03:00      
	1015798              ORA 600                                                     2023-09-29 22:03:15.787000 +03:00      
	1015842              ORA 600                                                     2023-09-29 23:01:05.411000 +03:00      
	9 rows fetched

	--> Create adrci trace for incident.

	adrci> ips pack incident 1024753 in D:\Software\tt