7 Replies Latest reply on Oct 21, 2010 12:54 PM by Salman Qureshi

    11gR2 trace file

    790438
      Hi all,

      In my production database the trace file is occupying over 40gb of space. I can delete this file (adrci), but why they occupy so much space, one file factor_ora_1880.trc (about 4Gb).
      In alert log for this trace file:
      Non critical error ORA-00001 caught while writing to trace file "...\trace\factor_ora_1880.trc"
      Error message: OSD-00002: additional error information
      O/S-Error: (OS 87) The parameter is incorrect.
      Writing to the above trace file is disabled for now on...

      P.S. Oracle Database 11g 11.2.0.1.0 (Patch 10155838)
      MS Windows 2003 ServerR2 x64


      Thanks.
        • 1. Re: 11gR2 trace file
          Oviwan
          you can set the limit for a tracefile with MAX_DUMP_FILE_SIZE

          check this:
          http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams135.htm#CHDIABEI
          • 2. Re: 11gR2 trace file
            Salman Qureshi
            hi,
            Can you check if you have enabled tracing for some session. It seems that some session is being traced. Paste output of the following
            select sid,username from v$session where sql_trace <> 'DISABLED';
            
            
            select a.sid,a.serial#,a.program,a.username,a.machine from v$session a, v$process b where a.paddr=b.addr and b.spid=1880;
            Also paste here the value of parameter sql_trace
            show parameter sql_trace
            Salman
            • 3. Re: 11gR2 trace file
              790438
              No problem to set MAX_DUMP_FILE_SIZE , but how will it affect trace files for errors (Perhaps there is not a complete record of information in the trace file).
              • 4. Re: 11gR2 trace file
                790438
                Hi, Salman Qureshi
                show parameter sql_trace;
                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- -----
                sql_trace                            boolean     FALSE
                select sid,username from v$session where sql_trace ='DISABLED'; 
                empty
                select sid,username from v$session where sql_trace != 'DISABLED';
                show all current connection client and abckground processes
                • 5. Re: 11gR2 trace file
                  Salman Qureshi
                  what was output of following?
                  select a.sid,a.serial#,a.program,a.username,a.machine from v$session a, v$process b where a.paddr=b.addr and b.spid=1880;
                   
                  if no rows returned, then your session has exitted and size growth of this file would have stopped. You can try tkprof so see what is inside this file. IF nothing, then you would need to contac oracle support to check why this much big file was generated

                  Salman
                  1 person found this helpful
                  • 6. Re: 11gR2 trace file
                    790438
                    select a.sid,a.serial#,a.program,a.username,a.machine from v$session a, v$process b where a.paddr=b.addr and b.spid=1880;
                    empty
                    Thanks for help, now I will write in support and try to analyze trace files )
                    Trace file: factor_ora_1880.trc
                    Sort options: default
                    
                    Trace file: factor_ora_1880.trc
                    Trace file compatibility: 11.1.0.7
                    Sort options: default
                    
                           1  session in tracefile.
                           0  user  SQL statements in trace file.
                           0  internal SQL statements in trace file.
                           0  SQL statements in trace file.
                           0  unique SQL statements in trace file.
                     102378631  lines in trace file.
                           0  elapsed seconds in trace file.
                    • 7. Re: 11gR2 trace file
                      Salman Qureshi
                      It does not seem to be a sesison trace actually, right decision to contact oracle