Disable all TRACING in 10g Database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Disable all TRACING in 10g Database

Hybrid View

  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Disable all TRACING in 10g Database

    Hi all,

    I'm new to the DBA world, and I've got a 10g database which is generating gigabytes of Tracing Files (*.trc) every day.

    I'd like to be able to disable all tracing for this database. How would I do this, using SQL Plus?

    At this time, using Enterprise Manager is not an option.


    Many Thanks.

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    Sounds like you've got SQL_TRACE set to TRUE at the database level. Check by using SHOW PARAMETER SQL_TRACE in SQLPlus. If its TRUE set it to FALSE

    Code:
    SQL>  sho parameter sql_trace
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    sql_trace                            boolean     TRUE
    SQL>  alter system set sql_trace = false;
    
    System altered.

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    Hi, thanks.

    running SHOW PARAMETER SQL_TRACE tells me that tracing is indeed disabled. I've just found out that the database in question is used for Enterprise Manager.

    The tracing files are deposited into a directory called bdump. Does this make a difference?

    Are there any other parameters I can try?

    Thanks for any help.

  4. #4
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    What platform is the DB running on?

    What are the names of the trace files? Traces in the bdump area are associated with background processes like SMON and PMON etc. The trace file names should have the relevant process name in it e.g. test_smon_14905.trc

    Also, have you checked the alert log for any relevant messages?

  5. #5
    Join Date
    Aug 2007
    Posts
    3
    Hi again,

    We're running 10g on Windows Server 2003.

    Here is a typical name of one of the trace files:

    emrep_j002_2368.trc

    Most follow this format - with the numbers changing sequentially upwards. However, there are 3 files in the same directory -

    emrep_mman_468.trc
    emrep_mmon_2384.trc
    emrep_pmon_1180.trc

    It seems to be the emrep_j002_2368.trc type files which are building up, just today there are hundreds.


    Thanks for your continuing help with this.

  6. #6
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    The j002 ones are from the jobs run via dbms_job as defined by job_queue_processes.

    It sounds to me like you've got some jobs that are submitted via dbms_job (check dba_jobs) that could have tracing turned on as part of the SQL or PL/SQL code.

    Do the trace files contain SQL? (Run tkprof against them and look at the output)

    Search the tkprof output for "trace" or "alter session"

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    It seems it's time to log a service request with Oracle.
    It is not normal to get generated so many mmon, pmon and mman trace files.
    Log a SR and Oracle support will take a look at the trace files and hopefully clarify what's wrong

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    fraze already asked you to check alert.log; what do you see in it?

    Look for ORA- messages.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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