Coskan’s Approach to Oracle

June 3, 2009

Too many trace files on 11G

Filed under: Tips — coskan @ 9:30 am

On 11G user and system traces are on the same directory (%DIAGNOSTIC_DEST%\diag\rdbms\SID\trace on my machine) and automatic health monitoring  looks like a bit more active than 10G. On 10G I wasn’t seeing automatic trace generation for user sessions, however on 11.1.0.6 Oracle nearly creates trace for every session and  it realy drives me mad when I am working on 10046 traces.  I checked unsupported parameters and I found _disable_health_check parameter. When you set this parameter to TRUE a Oracle stops generating trace files for every user session. Parameter needs restart of database.

It looks like there are 32 other diagnostic related parameters on 11G but _disable_health_check
was enough to solve my problem.

I can see this parameter is available on 10GR2 and 11GR1 but not available at least on 9.2.0.4

!!!!! As you see, it is unsupported dont use it on any production system without asking Oracle Support.

About these ads

12 Comments »

  1. What were the names of the trace files?
    Have you found a way of inhibiting trm files yet in 11g. It is rather frustrating generating twice the files you need and we are not likely to use the XML format.

    Comment by John Hallas — June 3, 2009 @ 3:31 pm

    • They are all trace and trm files but at least after changing this parameter I dont get auto traces for every user session. Background session trace still continues with boh trm and trc files.

      TRM files are more than annoying but I have no solution for them. I think They should at least put them to separate directory. It is very hard to find your file in this new architecture unless you have a tracefile identifier.

      Comment by coskan — June 3, 2009 @ 3:37 pm

  2. Trm files are yet another thing to make something simple more complex :-). First alert log gets changed to XML and to read it, comes a magical util ADRCI and then comes these TRM files which according to oracle are used to search the actual trace files! Huh? Why not let the trace be, well, just good-ol trace only?

    Ok rant over :-) .

    Aman….

    Comment by Aman.... — July 14, 2009 @ 6:31 pm

    • Magical steps to Self managed database :))

      Comment by coskan — July 14, 2009 @ 9:22 pm

  3. I’ve upgraded our 10.2.0.4 RAC environments to 11.1.0.7.10 on two environments and 11.2.0.3 on one environment and I am really tired of all the .trm and .trc files as well. If something actually goes wrong and .trc and .cdmp that we actually need start to get written, you will find out rather quickly that your database environment will run out of disk and crash. I had this problem and could not delete as fast as they were being written, ran out of space and crashed the instance. We’ve been at 11g for about a month now and after zipping all the .trc, .trm and cdmp files (have to retain them for at least a year) the “trace” directory alone is over 200M zipped. 99% of which is garbage. And of course it is a much easier path to view the trace files “/opt/grid/app/oracle/diag/rdbms///trace” vs. “/opt/oracle/admin//bdump”

    Comment by Todd Wolfe — April 26, 2012 @ 11:00 am

  4. I am facing the same problem but it is little more complicated ….. i am having trouble with trace files on only one node …. i have a 2 node production server …
    the details are as such IBM power 7servers and oracle version 11.2.0.3 RAC

    The issue is that on node 2 the trace files are generating on a huge level and i have to manually delete them every 2 hours other wise the space is filling up fast. And to add to my misery the trace files are generating in two separate locations

    One location is the default oracle location folder which has a space of 200 gb and it is some how manageable by adrci

    but the main issue with me is that some fiels are getting created int he /var folder under /tmp/oradiag_oracle forlder also
    and this /var folder is only 5 gb
    if i ignore it for one hour the space reaches 100%

    i am trying to find what traces are enabled on the system so i can disable any unwanted traces.

    Can anyone help me by telling me how to check for enable tracing events and how to disable them manually

    Thank you .

    Comment by Aun shakeel — January 16, 2013 @ 7:10 am

    • Couple things. First, look at the alert_.log file that is writing so much trace and see if there is actually a problem going on. One time my Jr. DBA was running a load script and the device had run out of space. The disk filled up, the load couldn’t continue and the database trace files started writing and very soon thereafter crashed the database. Second, consider relocating the diagnostic_dest to a place with more disk space. Third, once that is done, you can look into creating nightly cron jobs (or whatever you prefer) to zip and relocate the trace files or if you aren’t required to retain a years worth of logs, you can just write the script to delete anything older than x-amount of hours/days. If you have oracle support, open up a Service Request (SR) with them and see if they give you a solution. If they do, make sure you reply here :)

      Comment by wolfee — January 16, 2013 @ 1:20 pm

      • thanks for your reply …
        i would like to mention here that my problem is not the generation of the trace files.
        my problem is generation of log files in two different places simultaneously specially in the /var filesystem
        all my oracle parameters point the trace location to the oracle folder which has more than sufficient space.
        but for some reasons the files are also generation in a filesystem that is only 5 gb.
        and i want to stop that particularly.
        any ideas on that.

        Comment by Aun Shakeel — January 16, 2013 @ 2:39 pm

  5. in oracle 11g db 11.2.0.3.. the trace file *.trc and *.trm generating very unusually.For each 1 hr it is genetating around 50 files.Can any one hav the solution for tis issue.

    Comment by saleem khan — March 16, 2014 @ 5:42 am

    • I run a cron job periodically to purge these files that are a couple of days old using the ORACLE O/S user and command:
      $ORACLE_HOME/bin/adrci exec=”set homepath diag/rdbms//;purge -age 1440″

      Comment by Phil — March 22, 2014 @ 2:43 pm

      • Thanks man…saved my bacon.

        Comment by CaptainPackers — April 8, 2014 @ 6:22 pm

  6. Traces can be enabled by (dbms_monitor.serv_mod_act_trace_enable and dbms_monitor.session_trace_enable) even if you set sql_trace to false, event 10046 to false.
    To verify if traces are enabled or not query “dba_enabled_traces” dictionary view, if you your query return row(s) then you must have trace(s) enabled.

    To disable server level trace execute:
    exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(‘SYS$USERS’,DBMS_MONITOR.ALL_MODULES,DBMS_MONITOR.ALL_ACTIONS);

    To disable client traces execute:
    exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(”); —- primary_id is the value of column PRIMARY_ID in dba_enabled_traces where trace_type =CLIENT_ID

    Hope this will help

    Comment by Atif — June 3, 2014 @ 9:42 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 206 other followers

%d bloggers like this: