Tracing Enabled for Sessions After Instance Restart

12022010

February 12, 2010

An recent thread in the comp.databases.oracle.server Usenet group brought up an interesting question. What would cause a 10046 trace at level 12 and and errorstack trace at level 3 for event 21700 to be enabled for sessions every time the original poster bounced the Oracle XE instance? Is this possibly a sign that someone modified an initialization parameter? Did someone modify the application to enabling tracing to log files? Is it a bug in Oracle XE?

Fortunately, the OP provided a portion of one of the trace files, as shown below:

The number following dep= in the trace file is a significant clue. SQL statements that are submitted by client application code will appear in 10046 trace files with dep= set to 0, so that probably rules out a change to the application code, unless of course the application is executing a PL/SQL block. The discrete ALTER SESSION statements in the trace file that are wrapped in EXECUTE IMMEDIATE statements probably rules out the possibility of a parameter that was modified in the spfile/pfile and probably also rules out the possibilty of a bug in Oracle XE. So, what might the next step be to find the cause of the trace files?

Another possibility is either a LOGON or STARTUP trigger that is enabling the trace. The OP mentioned in the thread that he had enabled tracing earlier to troubleshoot a problem. The tracing is apparently enabled in a PL/SQL procedure or anonymous PL/SQL block based on the value of dep= and the EXECUTE IMMEDIATE calls, so either a LOGON or STARTUP trigger is a possibility. To test this possibility, you might execute a SQL statement like this:

If the above returned no rows, there is still a very small chance that a logon trigger or startup trigger is owned by a user other than SYS, so it might make sense to repeat the SQL statement without the OWNER=’SYS’ predicate in the WHERE clause. In my case the SQL statement returned three rows – two logon triggers and one startup trigger. The logon trigger name LOGON_CAPTURE_10046 is a significant indicator that a trigger is present that will enable a 10046 trace when the user logs into the database – it helps to use a consistent and descriptive naming convention.

The OP did not show the output of the above SQL statement, but did state that he found and removed a logon trigger that enabled the two traces. If such a trigger is found, and you know that the trigger does not belong in the database, the trigger may be removed by connecting to the database as the SYS user and dropping the trigger:

Actions

Information

5 responses

Oh, that one was simple :) Just yesterday I have the same issue on my test 11.2.0.1 database – 10046 was on and I couldn’t disable it. I’ve tried:
1) 10046 off
2) dbms_session.session_trace_disable
3) oradebug 10046 trace name context off
and nothing helped. I thought maybe SQL_TRACE is not only deprecated, but has a bug which prevents it from turning off. It turned out dbms_monitor.database_trace_enable can’t be overridden on the session level, which is strange.

That is a great idea. Initially, when I read your comment I thought that reviewing DBA_SOURCE would be a very time consuming task – in part because of the number of different ways that a 10046 trace could be enabled. However, the portion of the trace file posted by the OP provided text that could be used to search the DBA_SOURCE view: “alter session set events ‘10046 trace name context forever, level 12′” – so searching DBA_SOURCE probably would not have been too difficult.

I think that I might have found something interesting that is related to this blog article: Doc ID 556756.1, “SQL TRACE GENERATING TO TRACE FILES NEVER STOPS”

Just out of curiosity, has anyone noticed that if a 10046 trace is enabled system-wide (ALTER SYSTEM or DBMS_MONITOR.DATABASE_TRACE_ENABLE) that the trace cannot be disabled for certain sessions (likely background processes)? I thought that I encountered this problem the last time I tested system-wide 10046 traces, and I thought that I recalled reading that this was a problem that could only be resolved by bouncing the database.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: