Hands-On 04 (Reading the ALERT file)

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

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

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 04 (Reading the ALERT file)
-- Preparation
set echo on
connect system/manager@school as sysdba
SET linesize 1000 pagesize 55

pause

--Start

CLEAR SCR
-- 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.

pause
pause


CLEAR SCR
-- Now, 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.

pause

/*
Dump file c:\oracle\ora9i\admin\school\bdump\schoolALRT.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
*/

pause

CLEAR SCR
-- 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.

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

pause

/*
System parameters with non-default values:
processes = 150
shared_pool_size = 46137344
large_pool_size = 1048576
java_pool_size = 33554432
control_files = c:\ora9i\oradata\school\CONTROL01.CTL,
c:\ora9i\oradata\school\CONTROL02.CTL,
c:\ora9i\oradata\school\CONTROL03.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:\ora9i\admin\school\bdump
user_dump_dest = c:\ora9i\admin\school\udump
core_dump_dest = c:\ora9i\admin\school\cdump
sort_area_size = 524288
db_name = school
open_cursors = 300
*/

pause

CLEAR SCR
-- 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.

pause

/*
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
*/

pause

CLEAR SCR
-- 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.

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

pause

/*
Thu Jul 18 13:05:32 2002
ARC0: Beginning to archive log 1 thread 1 sequence 200
*/

pause

CLEAR SCR
-- The ALERT file, can also be used to check the frequency of the checkpoint
-- process.

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

pause

/*
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:\MYLOGS\ORA_1_YMCCM600.LOG
*/

pause

CLEAR SCR
-- In using the ALERT file, we can locate system error messages and more.

-- This example indicates that there is a problem with the 
-- DB Writer, and we can find out more about the problem
-- in the schoolDBW0.TRC trace file.

pause

/*
Fri Jun 21 13:27:56 2002
Errors in file c:\oracle\ora9i\admin\school\bdump\schoolDBW0.TRC:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'C:\DISK4DEPT30\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.
*/

pause

CLEAR SCR
-- 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.

-- This is an example of the backup of a controlfile using the TRACE option.

pause

/*
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SCHOOL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 452
LOGFILE
GROUP 4 'C:\ORA9I\DATABASE\MYLOGS_01.LOG' SIZE 500K,
GROUP 5 'C:\ORA9I\DATABASE\MYLOGS_02.LOG' SIZE 500K
DATAFILE
'C:\ORA9I\ORADATA\SCHOOL3\SYSTEM01.DBF',
'C:\ORA9I\ORADATA\SCHOOL3\UNDOTBS01.DBF',
'C:\ORA9I\ORADATA\SCHOOL3\INDX01.DBF',
CHARACTER SET WE8MSWIN1252
*/

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause