Too many trace files generation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Too many trace files generation

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hello Guys:

    Every minute I am generating about 10 trace files in my udump directory since yesterday afternoon.

    I TKPROFed a few file but does not make much sense, to know how to stop trace files getting generated..

    An average trace file is like this.. when i checked randomly for the last few hours

    TKPROF: Release 8.1.7.1.0 - Production on Fri Aug 16 10:29:28 2002

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    Trace file: ././ora_48758_odbmsprd.trc
    Sort options: default

    ********************************************************************************
    count = number of times OCI procedure was executed
    cpu = cpu time in seconds executing
    elapsed = elapsed time in seconds executing
    disk = number of physical reads of buffers from disk
    query = number of buffers gotten for consistent read
    current = number of buffers gotten in current mode (usually for update)
    rows = number of rows processed by the fetch or execute call
    0 statements EXPLAINed in this session.
    ********************************************************************************
    Trace file: ././ora_48758_odbmsprd.trc
    Trace file compatibility: 8.00.04
    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.
    22 lines in trace file.


    Thanks, Sashi

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    if you've set tracing on at the database level they're probably from your background processes

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    Hello Pando:

    I did implement or try to execute a trace using logon trigger The trc files are indicating some one has and does not have the permissions to do
    so. See the following notes:

    From the alert log:

    Errors in file /home/oracle/admin/ODBMSPRD/udump/ora_60654_odbmsprd.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01031: insufficient privileges
    ORA-06512: at line 4
    Fri Aug 16 10:20:36 2002


    From udump:

    PROD box [AIM]: more ora_61266_odbmsprd.trc
    Dump file /home/oracle/admin/ODBMSPRD/udump/ora_61266_odbmsprd.trc
    Oracle8i Enterprise Edition Release 8.1.7.1.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.1.0 - Production
    ORACLE_HOME = /home/oracle/product/8.1.7
    System name: AIX
    Node name: CG_JDA1
    Release: 3
    Version: 4
    Machine: 0007904F4C00
    Instance name: ODBMSPRD
    Redo thread mounted by this instance: 1
    Oracle process number: 71
    Unix process pid: 61266, image: oracle@CG_JDA1 (TNS V1-V3)

    *** SESSION ID:(330.524) 2002-08-16 10:20:45.951
    Skipped error 604 during the execution of COTPROD.TRACE
    *** 2002-08-16 10:20:45.964
    ksedmp: internal or fatal error
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01031: insufficient privileges
    ORA-06512: at line 4

    Remember from my previous note, I created a logon trigger as "non-SYS user" by the time you came back to say that should be created as "SYS" user..

    This is generating tonnes of trc files in udump and in bdump alert.log files entries..

    We have bounced the db for cold backup yday and I have also dropped the trigger this morning as well...

    Please suggest..

    Thanks, ST2000


  4. #4
    Join Date
    Oct 2000
    Posts
    449
    OK. The tracing stopped..

    Just had to log in and drop the trigger...

    Only thing left is to clean up the mess in udump and bdump.

    Can anyone tell, how to delete all tracefiles after yesterday 4:00 pm or say.. delete all trace files that has

    0 statements explained in this session..

    All trace files that are dumb have this statement at the end of them and I guess there are many many files like this..

    Thanks, Sashi

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I guess you have not dropped the trigger created by non-sys user

  6. #6
    Join Date
    Oct 2000
    Posts
    449
    I did drop the trigger Pando and I have also mentioned that in my mail...After that the generation of trc files stopped.. There are about 9000 files (trc files) got created since yday 4:30 pm and till today 10:30 am (time I dropped the trigger).

    Now I am just cleaning up the trc files..

    How can I drop the trc files created between this time only..

    Secondly, I requested for someone to help with the command with "0 statements explained".. please ignore that since those are from TKPROFed trace files and not from trace files..

    Thanks, ST2000

  7. #7
    Join Date
    Oct 2000
    Posts
    449
    I just dropped all the trace files in the last 24 hours..

    Thanks, ST2000

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what i meant is the trace files are generated is because you have not dropped the trigger not owned by SYS

    because sys wont give insufficient privilege error since it has explicit alter session and not through a role

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width