Managing the Instance by Monitoring the Diagnostic Files

Oracle's diagnostic files are a means to capture vital information that concerns significant events encountered while the instance is operational. These files are used to resolve problems with the instance and to better manage the efficient operation of the database.

Several types of diagnostic files exist in connection with the database. Different types of diagnostic files exist for different types of problems that occur and need their information disseminated.

Alert Log

The alertSID.log file contains information about the day-to-day operation of the database. Oracle writes to the alert log everything that happens when the database is successfully started and shut down. This includes the time and status of all log switches, and any information concerning a tablespace or data file alteration. Any time a trace file is written, it is referenced in the alertSID.log file. Each entry has a time stamp associated with it. This makes it easier to determine what happened when the database was started or shut down. The DBA manages the alertSID.log file in the location specified by the BACKGROUND_DUMP_DEST initialization file parameter. The default location on the Unix system is $ORACLE_HOME/rdbms/log.

If this file does not exist when the instance starts, Oracle automatically creates it. The alert log should always be the first place the DBA looks if there is a need to diagnose any operational errors that occur in the database.

Within the alertSID.log file, you can find the following information:

• The date and time of all database startup and shutdown operations

• All nondefault initialization parameters for the database

• The startup of the background processes

• The log sequence number that log writer is writing and the time of the writing

• Creation of any tablespace

• Creation and deletion of undo segments

• Alter statements that have changed the state of the database (data files, tablespaces, system, or database)

• Information connected to critical error messages in the database

Background Trace Files

Background trace files contain vital information that gets written any time background processes (system monitor, process monitor, log writer, archiver, and others) fail. These files are used to diagnose and troubleshoot errors and are created only whenever any background process encounters errors that require writing out information to the files. These files contain header information indicating the version number of the database server and versions of the operating systems.

The background trace files are located, naturally, in the file system designated by the BACKGROND_DUMP_DESTINATION and have the naming convention sid_processname_process-id.trc (for example, mydb1_pmon_12345.trc).

User Trace Files

User trace files contain important information for fatal user errors or user forced trace files. These files contain the SQL statement that was running when the errors were encountered, statistics for the statement, the user running the query (the Oracle user as well as the OS user) errored, and the error encountered. User trace files can be created deliberately, too, as a way of helping the DBA to troubleshoot performance issues occurring in the database.

User trace files are located in the file system designated by the USER_DUMP_DEST initialization parameter and has its maximum size defined by the MAX_DUMP_FILE_SIZE parameter (if left unset, it defaults to 10 megabytes).

The naming convention for the user trace file is sid_ora_process-id.trc (for example, mydb1_ora_654321.trc).

User tracing is enabled or disabled either at the session level or the instance level by setting any of the following commands or parameters:

• ALTER SESSION Allows you to set the tracing to enabled or disabled at the session level.

  • ALTER SESSION SET SQL_TRACE = TRUE

  • ALTER SESSION SET SQL_TRACE = FALSE

• DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION The DBMS_SYSTEM package has as one of its procedures the SET_SQL_TRACE_IN_SESSION.

• SQL_TRACE You can set the user trace to enabled or disabled at the instance level by setting the initialization parameter SQL_TRACE to trUE or FALSE.

  • SQL_TRACE = TRUE

  • SQL_TRACE = FALSE