iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 05

“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:

   DBA-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.