Original subtitles

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.