All language subtitles for [SubtitleTools.com] Parameter File - Learning Oracle 12c [Video]

In this lesson, we'll be
learning about the parameter

file.

And the parameter file is very
important for an Oracle DBA,

because everything in the
database configuration

is controlled by parameters.

So all of those
parameters individually

control different
aspects of the database.

So those parameters can be used
to control the size of memory,

the way the Oracle
optimizer behaves

in certain circumstances,
the number of processes

that we have, the number of
background processes we have.

All of those kinds of things
are controlled by parameters.

Now there's two ways to store
those parameters, an Init

File, or a P-File, we could
call it, or an SP-File,

which we'll take a look at.

So when we talk about
the parameter file,

we need to know
where it's located.

So in Windows, if we have an
Oracle installation on Windows,

we'll find the parameter file
in the Oracle Home directory,

and underneath that, a
folder called Database.

So Oracle Home
Database is where we'd

find the parameter file for an
Oracle installation on Windows.

In Linux, it's similar
but slightly different.

It will be in the
Oracle Home directory,

and then a directory
under that called DBS.

So in Linux, we say that
it's an Oracle Home DBS.

So the two types are the
initialization Parameter File,

or the P-File, and the Server
Parameter File, or the SP-File.

So the P-File is the essentially
the old way that DBA's

modified and used parameters.

So the P-File is text based.

So it's nothing but
a text based file.

You could open it with
Notepad or any text editor.

And what you would see is a
list of parameters, and then

an equal sign, and then the
value for that parameter.

And then that file would be
read during instant startup.

So it's very easy to manage
parameters with a P-File.

You simply open the
file, change the value,

save it and close it.

The problem with the P-File is
that it requires the database

to be restarted.

So anytime you want
to change a parameter,

you have to shut the
database down and restart it.

And when it's restarted,
that P-File, that text file,

will be reread, and
then the database

will be opened with
those parameters.

So Oracle saw that this
was somewhat problematic.

Because in Version
9I, they came up

with the idea of the Server
Parameter File, or SP-File.

The SP-File is a binary file.

It's not a text file.

It's binary, it's
read by Oracle,

and read into the memory.

And it enables us to do
dynamic changes to parameters,

because those parameters
are actually in memory,

at least to some parameters.

It's important to understand
that the SP-File is

a binary file.

So if we opened that file
up with a text editor,

we would see some
parameters and values

that we were familiar with.

We would say that we
could read it, in essence.

But we would also see a lot
of special characters and junk

characters, because
the file is binary.

So it's sort of text
embedded in binary.

It's very important
that a DBA never try

to modify the SP-File directly.

That is to say, to open the
file and actually change

the values in the SP-File,
because that will corrupt

the file and it will
not be able to be used

in order to start the database.

So here we have our Oracle Home.

This is the Oracle Home
directory on my machine.

Yours would be
slightly different.

And so if we want to
see the Parameter File,

we need to look in
Oracle Home Database.

So there's a number
of files here.

But we see one called
SP-FILEORCL.ORA.

And so that's going to be the
default name of the SP-File

for this database.

It's important to understand
that Oracle, when it starts up,

will look for a certain
file in a certain location.

So the first thing it looks
for in the Oracle Home Database

or in Linux DBS Directory,
will be a file called SP-File

and then the name of the
database SID.DOT.ORA.

And so if it can find that file,
it will use those parameters.

It will attempt to
read the SP-File,

and read those into memory
and start the database up.

If it does not find a
file called SP-File,

the name of the SID
or DATABASE.ORA,

then it will look for one called
a NIT, name of the SID.ORA.

And they NITSID.ORA, or in this
case would be a NITORCL.ORA,

is the P-File.

So that's the Initialization
Parameter File.

So by default, it will look
for an SP-File first, which

is the preferred method, because
it allows us to dynamically

change parameters.

And then it will look
for an a Init File.

So there's certain ways
that we can control the use

of the SP-File and the P-File.

One task that a lot
of times we want to do

is write out everything it's in
the SP-File into an Init File

just so we can look at it.

And again, we don't
want to directly operate

on the SP-File, because
it's a binary file

and is easily corrupted.

So let's log into the
database as sysdba,

so an administrative.

We're going to try Create
P-File from SP-File.

So let's see what it did.

And notice that now we have
a file called INITORCL.ORA.

I'm going to open
it with Notepad.

And notice there are a
number of database parameters

here, something
like DB Name equals,

the name of the database.

Open Cursors equal,
Processes equal, all of these

are parameters that
control the database.

Anything not listed in this file
will be simply a default value.

And so Oracle will
use the default value.

So if we need to change a
parameter and it's not here,

we could simply add it.

So adding it in an
Init File just means

adding it to this text file.

But we're going to prefer
to use the SP-File,

because the SP-File will allow
us to be able to dynamically

change some parameters.