PARAMETERS

--------------------------------------------------------------------------------------

BACKGROUND_DUMP_DEST

background_dump_dest specifies the directory (folder) where trace files of background processes are being written. It also specifies the location for the alert.log file.

It is also used for ORA-00600 errors.

Explain BACKGROUND TRACE FILES?

Background trace files are used to log errors that have been encountered by a background process, such are SMON, PMON, DBWn, and other background processes.

These files exist only when an error requires writing to the trace files.You use these files to diagnose and troubleshoot problems.

Naming convention for user trace file: sid_processname_PID.trc

Its location is defined by the BACKGROUND_DUMP_DEST initialization parameter.

What is an Alert_SID.log file?

Each Oracle Instance has an alert log file. If not already created, it is created during instance
startup.

The alert log file should be the first place you look when diagnosing dayto-day operations or errors. The alert log file also contains pointers to trace files for more detailed information.

The alert log file keeps a record of the following information:
• When the database was started or shut down
• A list of all non-default initialization parameters
• The startup of background processes
• The thread being used by the instance
• The log sequence number LGWR is writing to
• Information regarding a log switch
• Creation of tablespaces and undo segments
• Alter statements that have been issued
• Information regarding error messages such as ORA-600 and extent errors

--------------------------------------------------------------------------------------

USER_DUMP_DEST

The value of user_dump_dest specifies the destination where user processes will write trace files.

It is also used for ORA-00600 errors.

What is User Trace File?

User trace files contain statistics for traced SQL statements, which are useful for SQL tuning.

• Naming convention for user trace file: sid_ora_PID.trc (Ex: db01_ora_23845.trc).
• Produced by the user process
• Can be generated by a server process
• Contains user error messages
• Created when a user encounters user session errors
• Location is defined by USER_DUMP_DEST
• Size defined by MAX_DUMP_FILE_SIZE

Enabling or Disabling User Tracing

Session level:
Using the ALTER SESSION command:

ALTER SESSION SET SQL_TRACE = TRUE

Executing DBMS procedure:

dbms_system.SET_SQL_TRACE_IN_SESSION

Instance level
Setting the initialization parameter:

SQL_TRACE = TRUE

Setting SQL_TRACE=TRUE at the instance level will produce a significant volume of trace data.

This option should be used with care.

--------------------------------------------------------------------------------------