All language subtitles for [SubtitleTools.com] Alert Log And Trace Files - Learning Oracle 12c [Video]

In this lesson, we'll
learn a little bit

about the alert log
and the trace files.

So these are
informational files that

are going to have primarily
error information, or problem

information, although the
alert log will give us

a lot of supplemental
information, as well.

So the two types of
log files that we

have are the alert log
and the trace file.

The alert log is
going to be something

that the DBA does interface
with an awful lot.

For most DBAs, this
is their first stop

when they think there's
a problem in the database

because it's going to record
all of that type of information

and some informational
stuff, as well, information

about the startup of the
database, about the shut down,

those kinds of things.

Trace files, on
the other hand, are

going to be more
difficult to interpret

and are usually
for Oracle Support.

So the alert log, as we
said, is the primary source

of problem information.

The alert log is
primarily a text file,

although it does have a file
that exists as XML, as well.

So ordinarily when we would
look at the alert log,

we might just go
out to the alert log

itself and open it up in
a notepad or a text editor

and then just read it and
its information that it has.

Starting in 11g, version
11g, we were also

able to do this through
the ADRCI, which

is a command-line tool that
allows us to do a lot of things

that have to do with the alert
log and trace files, as well.

Trace files, however, are
purely diagnostic information.

They are mostly unreadable
except by Oracle Support,

and they have programs
that can parse them and get

the relevant
information out of them.

But we do need to know
about the trace files

because in a serious
problem situation,

Oracle Support will
request those files.

So we need to know at least a
little bit about where they are

and the information
that they contain.

So here we are in the
Oracle base directory.

So that'll be, in my
case, E:/app/sries.

Now, we know that our home
is under product and then

several directory
layers underneath that,

but we have a directory called
diag in the Oracle base,

and this is where the diagnostic
information is located,

and it is very extensive.

So let's drill down and
get to the alert log

and the trace files.

So I'm going to click on rdbms
and the name of the database,

name of the database
again, and then

directory trace, not the
directory alert, the directory

trace.

So right here at the top,
we have a file called

alert_orcl.log, and that'll be
the default name of the alert

log.

It'll begin with
alert, and it will have

the name of the database in it.

I'm going to click
that and bring that up.

So this can look very confusing,
and it is not important

that you understand what every
line of the alert log means.

It is something you
can get a little more

familiar with with
experience, but we can kind of

outline a few things
in the alert log

as far as the type of
information that it contains.

So the first entry
here has a date

and starting the
Oracle instance.

And so then it's
going through all

of the process of
starting it up.

Now, there's a lot
of things in here

that we wouldn't understand,
but there's information

about processors and such.

But notice that we have a
section here, system parameters

with non-default values.

And so we have a number of
different processes here,

and they have values
that aren't the default.

So this can be
important sometimes when

you are changing parameters
and you think that you maybe

didn't do it right,
that you didn't

get the expected behavior.

You can come back
to the alert log

and look and say, well,
what parameter values did

it start up with?

So after it has
that information--

this is all, again,
during the nomount state--

it starts the process
PMON, MMAN, some processes

that we would recognize,
database writer,

the log writer, the
checkpoint, system monitor,

all of those processes, and
then gives us information

about what happens after that.

So notice that this is a
CREATE DATABASE statement.

So after the instance was
started, a CREATE DATABASE.

And actually, this database
that I'm working on was created.

And here's the actual
SQL that was used.

So that's the kind
of information

that we can see here.

I wanted to make a point
about a particular line

in the alert log, and
that'll be the one that says,

beginning crash recovery of,
usually in a single instance,

one threads.

And that's the line that
you see in the alert log

whenever instance
recovery is occurring.

So the database was shut down
ungracefully through an abort,

through a power off on the
system, something where

a shutdown immediate
didn't occur to take down

the database gracefully.

And then an instance recovery
has to occur after that.

If we had backup information,
it would be in here, as well,

as well as recovery
information if we

did any database recovery.

So the alert log is just
a wealth of information.

It just takes some time
and practice and research

to find the information
that you're looking at.

Next will be the trace
files, and the trace files

come in two different
flavors, if you will.

They'll be the trace
files that are associated

with a background
process and then trace

files that are associated
with a user process.

So for instance, let's
find a trace file

with a background process
that we'd be familiar with,

something like the
checkpoint process.

So we see here
that there will be

the name of the database, the
process name, ckpt, and then

a number.

But we have a trc and a trm.

The trms are normally going
to be binary type or XML type

information that are
read by that ADR CI tool.

So if we want to read them
in their natural text state,

we always look at the .trc.

So if I click that--

and this is going
to have information

that is generally unreadable
to most DBAs, if not all.

This is information that's used
primarily by Oracle Support.

But we can see that the trace
file is associated with a given

background process.

So if we were to go
a little further,

here's the dbw0 process,
which is the database writer.

And so we can see a message here
about an action that it did.

Another example would be maybe
the log writer process, lgwr.

We created two redo
writer workers--

so just a lot of
informational messages.

But if anything occurs
in the realm of an error,

that process
information is going

to be written to a
trace file, as well.

So things like memory dumps,
crash dumps, core dumps,

all of that type of
information will be at least

alluded to in trace
files, as well.

So it's important to at least
know about their existence.