In yesterday I was asked about the possibility of Oracle Database generate a trace file of 122 Gb only cause this is work in archivelog mode.
My answer was “not”, it is one process totally independent of other. Maybe this trace was generated by DBA monitoring some user session and forgot DBMS_MONITOR tracing for ever.
Let me clarify more about traces…
Background processes and server processes may write a trace file, but it is in specific areas on your Oracle Database server, this trace files help Database Administrators to find and solve problems and several info about his databases, open TARs with Oracle Support.
1) BACKGROUND_DUMP_DEST initialization parameter
The path where background processes dump traces, generally defined as equivalent path that $ORACLE_BASE/admin/$ORACLE_SID/bdump and where we find files named by Oracle background processes:
logwriter (LGWR) generate one trace orcl_lgwr_402.trc
Instance Monitor (SMON) generate one trace orcl_smon_406.trc
2) USER_DUMP_DEST initialization parameter
The path where server processes dump traces, generally defined as equivalent path that $ORACLE_BASE/admin/$ORACLE_SID/udump and where we find files named by Oracle server processes.
orcl_ora_16028.trc
orcl_ora_71.trc
This traces may be started by “DBMS_MONITOR” and “DBMS_SESSION” by DBA or via “ALTER SESSION SQL_TRACE TRUE;”
Be careful with SQL_TRACE = TRUE, it can cause negative performance impact.
3) Alert_
The “alert” as called by DBA is a lead trace of database. This file is a very especial file with chronological logs and error messages like ORA-600, block corruption errors (ORA-1578), and deadlock errors (ORA-60). Also logs of startup, shutdown, drop, create and alter on database structures as create tablespace command.
When your database is with some strange problem, error messages displayed on EM Console take a careful look on alert.log
Alert filename sample:
alert_orcl.log
The alert is generated under BACKGROUND_DUMP_DEST directory
4) Manage size of trace files
It is recommendable limit size of trace files, this objective can be achieved by initialization parameter MAX_DUMP_FILE_SIZE, on out of box configuration is set to UNLIMITED, the value used by this parameter is in number of Operating System blocks. Choose value most adequate to your environment.
This parameter can control size of all trace file except alert.log size.
For more information read official documentation http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/manproc.htm#sthref729
Ps.: The name of trace file are specific for each environment, the names showed on this post was examples of Oracle database running at Solaris SPARC 64bits.