All language subtitles for [SubtitleTools.com] The Database - Core Datafiles - Learning Oracle 12c [Video]

In this lesson,
we want to examine

the core datafiles that
are a part of the Oracle

architecture.

The datafiles align with the
database portion of the RDBMS.

So if we say that
the RDBMS is composed

of an instance and a
database, the datafiles

align with the database portion.

We can better understand
the datafiles in the Oracle

database by looking at the
concepts of transient data

versus persistent data.

So transient data is data
that is lost, generally,

when a power outage occurs.

So if you remove power
from the computer source,

then transient data is lost.

So transient data is
data that we normally

associate with memory.

So if we have data
stored in memory,

in RAM in a computer
system, we remove the power

or turn the system
off, that data is lost.

However, persistent
data is different.

Persistent data remains
stored even after the power is

removed, the system is shut
down, or any of those types

of situations.

So persistent data is what we
associate with data on disk,

and that's what we get when
we look at our core datafiles

in Oracle.

Most datafiles that
we'll discuss map

to something called
a tablespace,

and a tablespace is
just a logical name

for a physical file.

So for instance, a
datafile might be

c:/oradata/orcl/data01.dbf.

So that means we could
go out to the C drive,

into that directory
structure, and actually

see a data01.dbf file, and
that is the datafile itself.

But always naming
the datafile whenever

you interact with
it is cumbersome,

to be able to have to put the
full path and datafile name.

So Oracle enables us to
assign a logical name

to that physical
file, and that's

what's known as a tablespace.

Tablespace can map to
one or more datafiles.

So now when someone
says, I want to create

a table in a certain
place, they can say,

I want to create a table
in the data tablespace,

rather than saying
I want to create

a table on this datafile.

The first of our core datafiles
is really the most important,

and that's called
the control file.

The control file is essentially
the brain of the database.

The control file knows
where every other file

in the database is located.

It has information on the
state of the database.

It's probably the file that gets
written to the most frequently

in an Oracle database, because
its information is constantly

being updated.

One of the many things that
the control files store

is something called the
system change number, or SCN.

When Oracle was created, it was
created with high performance

large databases in mind.

And so rather than using
the actual server time

to count the passage of
time, it uses something

called a system change number.

And the system change
number, or SCN,

is just a number
that gets incremented

every time a change
occurs in the database.

And the system change
number is primarily

used for recovery of
an Oracle database.

So it's really important that
the brain, the control file,

knows what that system change
number is at all times.

The control file is
so important that we

use something called
multiplexing when we're

dealing with a control file.

If we have a single control file
and we lose that control file,

then the database cannot open,
because it does not know what

the state of the database is.

It doesn't know where
all the files are.

So we're in effect lost if
we lose the control file.

Now, there are ways to
recover it from the backup.

We're in a lot of trouble if
we lose the control files.

So we do something called
multiplexing, and multiplexing

simply means putting more
than one copy of the control

file in your database.

Oracle, at the very
least, recommends

that you have two copies of
the control file in an Oracle

database-- preferably
three and preferably

on different physical media.

So if you have a C drive,
a D drive, and an E drive

on the server that hosts
your Oracle database,

it would be best
to put a control

file on each one of those, with
a multiplexing level of three.

The next core datafile
is the redo log file.

It's also called the online
log in Oracle documentation.

It records database changes.

So every time a change
occurs in the database--

say, through an insert
statement, an update statement,

or a delete, the creation of
a table, dropping a table.

Any of those things constitutes
change in the database,

and that must be recorded.

And really, the reason that
we record those changes

is simply for
recovery scenarios,

where we can use
it to roll forward

a database from a backup.

Start at the backup, and
then you apply the changes,

rolling forward the database
until you come to the place

where it was during the failure.

Redo logs can actually
be overwritten.

When we do configure
our database in a way

that they're overwritten, we
don't have full recoverability.

That's why Oracle offers
other options as well,

to be able to have
full recoverability.

The redo log files are
also very important.

So we use something
similar to control file

multiplexing with them.

And rather, it's
called duplexing.

So duplexing is the act of
making a copy of the redo log,

so that there is always
two copies of the redo log

file at all times.

Next is the archive log file,
also called the offline log.

So we mentioned, when
we discussed redo logs,

that it was possible to
configure the database in such

a way that the redo logs
would be overwritten.

That's not a desirable state.

So Oracle provides something
called archive log mode that

allows for full recoverability.

So the archive logs are
copies of the redo logs

that are written out.

So that if a redo
log is overwritten,

it doesn't matter because
the archive log is already

in place.

Next to the core datafiles
is our first datafile,

the way we defined it
in our first slide,

and that would be the system
tablespace, or the system

datafile.

So if the control file is
the brain of the database,

we might think of system
as the heart of it,

because it contains
the data dictionary.

The data dictionary is
a unique set of metadata

that Oracle maintains
about itself.

So the data dictionary
has all the information

about the database and
makes it accessible to those

who know how to use it
using querying methods.

So we can query the
database to find out

things about how many tables
they are, what their names are,

are there sequences in the
database, what's the last time

that a certain session
connected to the database--

any of those types of things.

So system holds the
data dictionary.

It is always datafile
number one in the database.

So it is one of the
few mandatory datafiles

that you have to
have in the database,

and it's always the
first one created.

Next is the SYSAUX tablespace,
or SYSAUX datafile.

When Oracle 10g came
out, Oracle included

something called the Automatic
Workload Repository, or AWR.

The AWR is essentially a
second data dictionary,

but it's dedicated to
performance metrics.

So it is an entire set
of tables and views

that contain performance
metric information.

And it makes it
incredibly useful,

because you can query
the Automatic Workload

Repository to find
out performance

problems in the
database, and it's

the SYSAUX tablespace,
or the SYSAUX datafile,

that contains that information.

Next are temporary data files,
what we call temp files.

Any time that sorting
operations are

running in the PGA, the
program global area,

it's always possible that
there won't be enough memory

to do the entire sort.

If that occurs, it
doesn't cause an error.

What it does
instead is write out

the data from memory in
the PGA out to segments

in temporary data
files, or temp files.

So Oracle writes
temporary segments

into the temp files that
can be reconstructed

whenever the query is
complete and the data is

to be returned to the user.

Temp files also store temporary
tables, a feature of Oracle

that allows us to create
a table that is temporary.

It only lives for the period
that a session is connected,

or only lives during
a certain transaction,

and then it's gone.

So that information is
actually stored in temp files.

Next is undo datafiles.

When an Oracle performs a
transaction in the database--

let's say we're updating
a column in a table--

rules of transactions say that
Oracle has to save that data.

The original image of the
data, or before image,

has to be saved
somewhere in order

to be able to roll
back the transaction

in the event of a rollback
command being executed.

So if we do a transaction
and it's the wrong thing,

we are allowed to roll
back the transaction.

So in order to do that, the
data has to be stored somewhere,

and that's the purpose
of an undo datafile,

storing before images of data.

Lastly are the
non-specialized datafiles,

and these are the ones that
we're most familiar with.

These data files
hold application data

in the form of tables.

So everything up to this point
is essentially infrastructure.

The non-specialized
datafiles are the datafiles

and the tablespaces
that we create

that actually hold table data.

So the maximum
size of a datafile

is 4,194,304 times the
size of a database block,

which might generally in
default be an 8K block.

So 8K block times that
value would give us

a maximum size, for
instance, of 32 gig

for a non-specialized datafile.