everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

DBA Fundamentals

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
<< Previous

Chapter # 05

Next >>


 

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.

 

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.