11 Replies Latest reply: Sep 11, 2013 10:34 AM by gbite RSS

    trace files filling up on server

    gbite

      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

          >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

            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

              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

                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

                  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

                    does LOGON trigger exist?

                    • 7. Re: trace files filling up on server
                      gbite

                      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

                        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

                          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 .

                            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

                              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