|
“In order for
people to be happy, sometimes they have to take risks. It's true
these risks can put them in danger of being hurt.” Meg Cabot,
The Boy Next Door, 2002 |
Read
first then play the video:
DBA004(VIDEO)-Reading
the ALERT file
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 "SCHOOL" 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
*/
|
“"Knowledge
speaks, but wisdom listens." - Jimi Hendrix |
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.
|