Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 17 April 2012

Trace and Alert Logs

Trace Files
A trace file is created each time an Oracle instance starts or an unexpected event occurs in a user process or background process. The name of the trace file includes the instance name, the process name, and the Oracle process number. The file Extension or file type is usually TRC, and, if different, is noted in your operating System-specific Oracle documentation. The contents of the trace file may include dumps of the system global area, process global area, supervisor stack, and registers.
Two initialization parameters are used to specify the location of the trace files.
The BACKGROUND_DUMP_DEST specifies the location of trace files created by the Oracle background processes PMON, DBWR, LGWR and SMON.
The USER_DUMP_DEST specifies the location of trace files created by user processes such as SQL*Loader or Pro*C.
Major error shows in trace file is :
*** 2002.
*** SESSION ID:(18.1395) 2002.
*** 2002.
ksedmp: internal or fatal error
The Alert file also describes the location of trace files generated when internal errors occur. You may need to format the trace file before using it to diagnose problems. To format a trace file, use the DUMPFMT utility, which is available on most systems and is described in your operating system-specific Oracle documentation. Oracle Support Services may ask you for a formatted trace file to help solve a problem.
The Alert File
The Alert file is a log file that records information about internal errors and administrative activities, such as backups. When an internal error occurs, the message is sent to the terminal screen as well as written to the Alert file.
Oracle also writes additional information about internal errors to the Alert file, such as the location and name of any trace files generated because of the error. The name of the Alert file is operating system-specific. The location of the Alert file is the same as the location of the background process trace files. This location is specified by the BACKGROUND_DUMP_DEST.
The Alert file also records information about administrative activities, such as backups and archiving online redo log files.
Monitoring Errors with Trace Files and the Alert Log
Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, and other information is for Oracle Support Services. Trace file information is also used to tune applications and instances.
Alert.log is a text file that can be opened with any text editor. The location can be determined by:
SQL>Select value from v$parameter where name = 'background_dump_dest';
If the background_dump_dest parameter is not specified, Oracle will write the alert. log into the $ORACLE_HOME/RDBMS/trace directory.
Common messages in the Alert log
ARCx: Media recovery disabled
This message will be written into the Alert. Log if the arch process is started with the database being in noarchive log mode.
Thread 1 cannot allocate new log, sequence 1558 Checkpoint not complete
This error message is written into the alert.log if a checkpoint cannot write all dirty db blocks to the online redo log. Usually, this message is a sign that the size of the redo logs is too small or that there should be more of them.
All internal Errors: ORA-00600, Block Corruption Errors: ORA-01578 and deadlock errors ORA-00060 that occur recorded in alert Log file.
Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements are recorded in alert Log file.
Messages and Errors relating to the functions of shared server and dispatcher processes
Errors occurring during the automatic refresh of a Materialized view
The values of all initialization parameters that had nondefault values at the time the database and instance start
Rotating the alert logs
Within Oracle, it is perfectly possible to delete, or Rename, the alert.log, if desired (for example, if it reaches a certain size). Oracle simply recreates a new alert.log the next time it writes to it.
Linux  has an utility called logrotate to automate that task.

How to Write own messages into the alert log
sys.dbms_system.ksdwrt(2, 'My own message');
Reading the alert log through an external table
Here is a procedure which creates an external table that can be used to read the alert.log.
SQL>Create or replace directory ext_dir as 'C:\oracle1/rene/ext_dir';
SQL>Grant read, write on directory ext_dir to rene;
Create Table ext_table_csv (
  i   Number,
  n   Varchar2(20),
  m   Varchar2(20)
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field values are null
  location ('file.csv')
reject limit unlimited;

create table ext_table_fixed (
   field_1 char(4),
   field_2 char(30)
organization external (
   type       oracle_loader
   default directory ext_dir
   access parameters (
     records delimited by newline
     fields (
       field_1 position(1: 4) char( 4),
       field_2 position(5:30) char(30)
  location ('file')
reject limit unlimited;
The following ksh/awk script will gather the portion of the alert log file that was either written today or yesterday.
printf "Enter Destination File: "
read dest
files="/*/u??/oradata/$ORACLE_SID/oralogs -name alert_$ORACLE_SID.log"
for file in $(find $files)
awk '
BEGIN {Flg=0}
NR==1 {R=split(DATE,TMP," ")
NF==5 && $2==Month && ($3==(NoDayM) || $3==(NoDayM)) {Flg=1}
Flg==1 {print $0}
' DATE="$DT" $file | awk '
/^ORA-/ || /cannot/ {print DATE print $0}
NF==5 {DATE=$0}
' >> $dest
exit 0
We know that in linux 'grep' will used to search the file. But if you want only search the part of the alert log that has been added to since the last time you search then you can use the 'dd' command to achieve this, as the following script demonstrates. (It also keeps the alert log down to a manageable size).

if [ -f $ALERT_LOG.prev ] ; then
   PREVSIZE=`ls -l $ALERT_LOG.prev | awk '{print $5}'`
   dd if=$ALERT_LOG of=/tmp/alert.log bs=$PREVSIZE skip=1

if [ "`grep 'ORA-' $CHECKFILE`" ] ; then
   (error has occurred)
   send $CHECKFILE as email or whatever
   (no errors found)

if [ $PREVSIZE -gt $MAXSIZE ] ; then
  tail -$LINES_KEPT $ALERT_LOG > $
  mv $ $ALERT_LOG

Controlling the Size of Trace Files
You can control the maximum size of all trace files (excluding the alert log) using the initialization parameter MAX_DUMP_FILE_SIZE, which limits the file to the specified number of operating system blocks.
To control the size of an alert log, you must manually delete the file when you no longer need it. Otherwise the database continues to append to the file. Although you can make an archive copy of this file before delete.
Using TKPROF to Format the Contents of Trace File
TKPROF C:\oracle1\admin\orcl3\bdump\ orcl3_lgwr_1612.trc OUTPUT.TXT
TKPROF C:\oracle1\admin\orcl3\bdump\ orcl3_lgwr_1612.trc orcl3_lgwr_1612.prf SORT= (PRSDSK, EXEDSK, FCHDSK) PRINT = 10
Sample Output TKPROF:
TKPROF: Release - Production on Sat Dec 24 11:07:43 2011
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Trace file: C:\oracle1\admin\orcl3\bdump\orcl3_lgwr_1504.TRC
Sort options: default
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
Trace file: C:\oracle1\admin\orcl3\bdump\orcl3_lgwr_1504.TRC
Trace file compatibility: 9.00.01
Sort options: default
       1  session in tracefile.
       0  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       0  SQL statements in trace file.
       0  unique SQL statements in trace file.
      20  lines in trace file.
Note: You can concatenate Trace to file to generate report.
Controlling When Oracle Database Writes to Trace Files
Trace files are written on behalf of server processes whenever critical errors occur. Additionally, setting the initialization parameter SQL_TRACE = TRUE causes the SQL trace facility to generate performance statistics for the processing of all SQL statements for an instance and write them to the Automatic Diagnostic Repository.


  1. Did you know that you can earn cash by locking premium pages of your blog / site?
    Simply join Mgcash and run their Content Locking tool.