Instance Monitoring Using Diagnostic Files, Alert Log File, Background Trace File, User Trace File, Enabling/Disabling User Tracing

Hey Folks

We will see here some basic stuff about instance monitoring, that how to monitor the database using Diagnostic files, Alert log file, Background trace file User Trace File, Enabling/ Disabling user tracing.

Monitoring an Instance using Diagnostic files :

• Diagnostic Files 
• Contains Information about significant events encountered
• Used to resolve problems
• Used to better managed the database on a day-to-day  basis
• Several type of diagnostic file exists:
• alertSID.log file
• Background trace files
• User trace files.

Diagnostic files means to capture information about the database's activities. They are also useful tools for you when you are managing an instance.  Several type of files exists. The type of diagnostic file created depends on the problem that occurred or the information that need to be communicated.

• Alert Log File (alertSID.log) : Information for day-to-day operation of the database.
• Background trace files : It Records important Information when background processes, such as SMON, PMON, DBWn, and others fail.
• User Trace File : It is as text file and  It records important information for fatal user errors or user forced to traced files.

 

Alert Log Files :

• alertSID.log files :
• Records the commands
• Records results of major events
• Used for day-to-day operational information
• Used for diagnostic database errors
• Each Entry has a time stamp associated with it
• Must be managed by DBA 
• Location defined by BACKGROUND_DUMP_DEST

Each Oracle instance has an alert log file. If not already created , it is created during instance start up.  The alert log File must be managed by DBA. It continues to grow while the database continues to work. The alert log file should be the first place you look when diagnosing day-to-day operations or errors. The alert log file also contains pointers to trace files for more details information.

The alert log file keeps a records of the fallowing 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 writing to 
• Information regarding a log switch
• Creation of tablespaces and undo segments
• Alter statements that have been issued
• Information regarding error message such as ORA-600 and other ORA- errors and extent errors.

The alertSID.log file location is defined by the BACKGROUND_DUMP_DEST  initialization parameter. 

 

Background Trace Files :

• Background trace files
• Log Errors detected by any background process
• Are used to diagnose and troubleshoot errors.
• Created when a background process encounters an error 
• Location defined by BACKGROUND_DUMP_DEST

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. This files exist only when an error requires writing to the trace files. You use these files to diagnose and troubleshoot problems. Initially when a background trace file is  created it contains  header information indicating the version number of the data server and the operating system.

 Naming convention for user trace file : sid_processname_PID.trc  ( orcl_lgwr_12345.trc).

 Its Location is defined by the BACKGROUND_DUMP_DEST initialization parameter. 

 

User Trace Files :

• User trace files :
• Produced By the user process
• Can be generated by a server process
• Contain Statistics for traced SQL statements
• Contain user error messages
• Created when a user encounters user session errors
• Location is defined by USER_DUMP_DEST
• Size defined by MAX_SUMP_FILE_SIZE

 User trace file contains statistics for traced SQL statements, Which are useful for SQL tuning. In Addition, user trace files contains user error messages.

 Naming convention for user trace file : sid_ora_PID.trc  (orcl_ora_12345.trc)

 Its Location is defined by the USER_DUMP_DEST initialization parameter.

 

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.

 

Thank You !!