Disable all TRACING in 10g Database
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.
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
SQL> sho parameter sql_trace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_trace boolean TRUE
SQL> alter system set sql_trace = false;
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.
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?
We're running 10g on Windows Server 2003.
Here is a typical name of one of the trace files:
Most follow this format - with the numbers changing sequentially upwards. However, there are 3 files in the same directory -
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.
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"
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
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.
Click Here to Expand Forum to Full Width