|
Reading the ALERT file
Introduction
As a DBA, you
are responsible for reading the ALERT file
once in a while to be informed of any
unknown problems with the database such as,
not enough space in the rollback segment or
the maximum extent reached in a table. Your
job�s responsibilities dictate that you
should at least be informed of the following
basic fundamental subjects:
Reading the
ALERT file
Information:
The location
of the ALERT file
The date and
time the database started
Oracle version
Operating
System information
Number of
processes
Shared pool
size
Location of
control files
Block size
Processes:
Process ID
DB writer
process (DBWn)
LOG writer
process(LGWR), etc.
Redo Log file
archive information
Frequency of
the checkpoint process
ERROR location
Looking at the
backup controlfile with the TRACE option
Hands-on
In this
exercise, you will learn how to read the
ALERT file. We will also look at the trace
file when we use the ALTER DATABASE BACKUP
CONTROLFILE command with the TRACE option.
View Alert
file
Open an alert file. It is located at
$ORACLE_BASE/admin/<database
name>/ bdump.
Top Part of
Alert file
The following
is a example of top part of the ALERT file.
Pay careful attention to the following
example of top part of the ALERT file. It
shows the location of the ALERT file, the
date and time the database started, Oracle
version, and some information about the
Operating System.
/*
Dump file c:i.LOG
Fri Mar 29 11:50:55 2002
ORACLE V9.0.1.1.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 2, CPU
type 586
Starting up ORACLE RDBMS Version: 9.0.1.1.1
*/
Check for any
of the system parameters
We can use the ALERT file, to check for any
of the system parameters. Remember, the
default parameter values are not included in
the ALERT file. The following is an example
of the system parameters included in the
ALERT file. You can see the number of
processes, the shared pool size, along with
the location of control files, and the block
size, etc.
/*
System parameters with non-default values:
processes = 150
shared_pool_size = 46137344
large_pool_size = 1048576
java_pool_size = 33554432
control_files = c:i.CTL,
c:i.CTL,
c:i.CTL
db_block_size = 4096
db_cache_size = 33554432
compatible = 9.0.0
remote_login_passwordfile= EXCLUSIVE
instance_name = school
dispatchers = (PROTOCOL=TCP)(SER=MODOSE),
(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer),
(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)
background_dump_dest = c:i
user_dump_dest = c:i
core_dump_dest = c:i
sort_area_size = 524288
db_name = school
open_cursors = 300
*/
Check for
Background Process
We can also use the ALERT file, to check the
process ID that was assigned to a background
process, such as the DB writer process (DBWn),
the LOG writer process(LGWR), etc. This
generated list indicates that the background
processes running in the database are
successfully loaded with their associated
Process ID (PID) number.
/*
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
*/
Archived
information
In using the ALERT file, we can also find
the date and time that a Redo Log file was
successfully archived or was not able to be
archived. The following example shows the
Online redo log group #1 was successfully
archived. We will learn about the Online
Redo Log file and archiving in the next few
Hands-On exercises.
/*
Thu Jul 18 13:05:32 2002
ARC0: Beginning to archive log 1 thread 1
sequence 200
*/
Check the
Frequency of the checkpoint process
The ALERT
file, can also be used to check the
frequency of the checkpoint process. The
following example shows that the checkpoint
process has problems when attempting to
complete it's task. We will learn about the
checkpoint process in the next few Hands-On
exercises.
/*
Thu Jul 18 13:05:28 2002
Thread 1 cannot allocate new log, sequence
201
Checkpoint not complete
Current log# 1 seq# 200 mem# 0:
C:_1_YMCCM600.LOG
*/
System Error Messages
In using the
ALERT file, we can locate system error
messages and more. The following sample
indicates that there is a problem with the
DB Writer, and it will tell us that we can
find out more about the problem in the
schoolDBW0.TRC trace file.
/*
Fri Jun 21 13:27:56 2002
Errors in file c:i.TRC:
ORA-01157: cannot identify/lock data file 11
- see DBWR trace file
ORA-01110: data file 11: 'C:DEPT30TS_01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the
path specified.
*/
Now, let's look at the backup controlfile
which was created with the TRACE option. The
reason we may want to backup the controlfile
with the TRACE option is if we lost all of
the controlfile(s), we can use the trace
file to rebuild it. It is a good idea to
always have a backup of the controlfile with
the TRACE option when you change the
database structure. The following is an
example of the backup of a controlfile using
the TRACE option.
/*
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "YOURDATABASENAME"
NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 452
LOGFILE
GROUP 4 'C:I_01.LOG' SIZE 500K,
GROUP 5 'C:I_02.LOG' SIZE 500K
DATAFILE
'C:I.DBF',
'C:I.DBF',
'C:I.DBF',
CHARACTER SET WE8MSWIN1252
*/
Questions:
Q: What is the
ALERT file in an Oracle database?
Q: What type
of information does the ALERT file contain?
Q: What does
the Top Part of ALERT file contain?
Q: What type
of system parameters does the ALTERT file
show?
Q: Describe
the background processes in the ALERT file.
Q: Describe
the archived information in the ALERT file.
Q: Describe
the checkpoint process activities messages
in the ALERT file.
Q: Describe
the System Error Messages in the ALERT file.
|
