This discussion is archived
11 Replies Latest reply: Sep 11, 2013 8:34 AM by gbite RSS

trace files filling up on server

gbite Newbie
Currently Being Moderated

11.1.0.7 - 64 bit on Solaris

 

It appears tracing has been enabled on our database in the last one week as the trace directory is getting filled up.

 

However this is what it's showing:

 

SQL> show parameter trace

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_trace                    integer           0

sec_protocol_error_trace_action string          TRACE

sql_trace                                boolean          FALSE

trace_enabled                        boolean           TRUE

tracefile_identifier                     string

 

It seems 11g have trace_enabled set to true by default.

 

My question is how do I find who's session is being traced?

 

Thanks

  • 1. Re: trace files filling up on server
    sb92075 Guru
    Currently Being Moderated

    >My question is how do I find who's session is being traced?

    every  session is being traced.

  • 2. Re: trace files filling up on server
    gbite Newbie
    Currently Being Moderated

    Can I get the list of sessions that are being traced.

    How do I switch the tracing off?  I can say that trace _enabled has always been set to TRUE and the directory was not filling up until a week ago

  • 3. Re: trace files filling up on server
    sb92075 Guru
    Currently Being Moderated

    gbite wrote:

     

    Can I get the list of sessions that are being traced.

    How do I switch the tracing off?  I can say that trace _enabled has always been set to TRUE and the directory was not filling up until a week ago

     

    ALTER SYSTEM SET SQL_TRACE=FALSE SCOPE=SPFILE;

    # then restart the DB

  • 4. Re: trace files filling up on server
    gbite Newbie
    Currently Being Moderated

    Above, I already stated that sql_trace=false

    But on that note can the above be scop=both and will I still have to restart the DB since both means memory and spfile?

  • 5. Re: trace files filling up on server
    TGG Newbie
    Currently Being Moderated

    gbite wrote:

     

    Above, I already stated that sql_trace=false

    But on that note can the above be scop=both and will I still have to restart the DB since both means memory and spfile?

    No you didn't. You stated that sql_trace had always been set to TRUE.

  • 6. Re: trace files filling up on server
    sb92075 Guru
    Currently Being Moderated

    does LOGON trigger exist?

  • 7. Re: trace files filling up on server
    gbite Newbie
    Currently Being Moderated

    not sure about logon trigger but I don't think so. There are 2 trace parameter and copying but I wrote above:

     

    sql_trace            boolean          FALSE

    trace_enabled     boolean           TRUE

     

    I have read somewhere that for 11g the second is always set to true by default

  • 8. Re: trace files filling up on server
    Mark D Powell Guru
    Currently Being Moderated

    TGG, the OP posted the database parameters related to trace and sql_trace clearly shows as FALSE.

     

    gbite, I would suggest looking at the content of some of the trace files.  We have experienced more than one platform version specific trace file generation issue which caused hundreds of useless trace files to be generated.  If the files contain the same messages you can then search Oracle support for a known problem which might have a solution available.  If all the traces just appear to be regular session sql traces then the suggestion to look to see if any database event logon triggers exist and turn trace on.

     

    Removing the trace files at the OS level should be an option in the meantime.

     

    HTH -- Mark D Powell --

  • 9. Re: trace files filling up on server
    gbite Newbie
    Currently Being Moderated

    Thanks.

    My colleague said during some reported performance issue last week, he thinks using Grid control 11g he enabled tracing for a session he thinks was responsible for high % of database activity and I reckon that the trace files started been written then. Since this was probably enabled through greed control and it was last week, how can I get the user for which tracing was enabled?

     

    Since this is likely to be session tracing, should restarting the database stop the tracing?

  • 10. Re: trace files filling up on server
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    select sql_trace from v$session;

     

    it's DISABLED by default - if it's been chanegd you can find it that way.

     

    Otherwise the trace file should have the unix pid in it - you can link this back to v$session by joining to v$process.

     

    Something like

     

    select sid from v$session where paddr = (select addr from v$process where spid=pid from trace file)

     

    Cheers,

    Harry

  • 11. Re: trace files filling up on server
    gbite Newbie
    Currently Being Moderated

    Thanks to everyone for your inputs.

    This is solved now by disabling tracing from the Top Consumer tab of Grid Control (GC) 11g as this was where it appears to be enabled in the first place.

    It was enabled for top module which was the main application module and it was writing multiple trace files every minute.

     

    This would have been very difficult to solve outside GC from where it was enabled and it is just a click to accomplish on GC.

     

    Thanks All

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points