Diagnostic Files

Diagnostic files are a means to capture information about the database's activities.

Types:

-- alertSID.log file : Information for day-to-day operation of the database

-- Background trace files : Vital information when background processes, such as SMON, PMON, DBWn, and others fail.

-- User trace files : Vital information for fatal user errors or user forced traced files.

Alert Log File

The alertSID.log file records the commands and Results of major events while the database is operational. 这主要包括Oracle的内部错误、数据块损坏错误、特权用户操作（例如启动例程、关闭例程、备份和恢复等）、数据库物理结构变化等信息。其信息由服务器进程和后台进程DBWR、LGWR写入。

-- Each entry has a time stamp associated with it.

-- The DBA manages the alertSID.log file.

-- Its location is defined by BACKGROUND_DUMP_DEST.

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.

Background Trace Files

-- They are created when a background process encounters an errors

-- Their location is defined by BACKGROUND_DUMP_DEST

User Trace File

-- It can also be generated by a you or a server process.

-- Its location is defined by USER_DUMP_DEST.

-- Its size is defined by MAX_DUMP_FILE_SIZE and defaults to 10M.

Enabling or Disabling User Tracing

User tracing is enabled or disabled at the session or instance level by using the following commands and parameter:

- Session level using the ALTER SESSION command:

-- ALTER SESSION SET SQL_TRACE = TRUE;

- Session level by executing DBMS procedure:

-- dbms_system.SET_SQL_TRACE_IN_SESSION

- Instance level by setting the initialization parameter:

-- SQL_TRACE = TRUE