Introducing the
PFILE and SPFILE
Oracle provides two different types of
mutually exclusive parameter files that you can use,
a)
PFILE and b) SPFILE (Server Parameter File)
When an
Oracle Instance is started, the characteristics of the Instance are established
by parameters specified within the initialization parameter file. These
initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are
available in Oracle 9i and above. All prior releases of Oracle are using
PFILEs.
SPFILEs
provide the following advantages over PFILEs:
Ø An SPFILE can be
backed-up with RMAN (RMAN cannot backup PFILEs)
Ø Reduce human errors. The
SPFILE is maintained by the server. Parameters are checked before changes are
accepted.
Ø Eliminate configuration
problems (no need to have a local PFILE if you want to start Oracle from a
remote machine)
Ø Easy to find - stored in
a central location
The Oracle PFILE
Ø
The
parameters are stored in either a PFILE or an SPFILE.
Ø
The
PFILE is a text-based file, and the “init.ora” file has been around for over a
decade.
Ø
Inside
the PFILE are a number of database settings called parameters.
Ø
These
parameters help the Oracle programs know how to start.
Ø The parameters tell the Oracle programs how
much memory to allocate, where to put files related to the database and where
certain database files already exist.
Ø As the PFILE is text based, one can edit it
in an editor like vi on UNIX or Notepad on Windows.
Ø When you have changed it, you need to make
sure you save your changes to disk before you exit the editor.
Ø Make sure you save it as a plain text file,
since some editors (like Microsoft Word) can save documents in special formats
that Oracle would not be able to read.
Ø Depending on which operating system you are
running on, your PFILE is located by default in the ORACLE_HOME\database (usually the case on Windows) or ORACLE_HOME\dbs directory for most
other platforms (Linux/Unix).
Ø If you are using a PFILE, it takes on the
form of initSID.ora, meaning the file will use the ORACLE_SID you defined when
you created the database.
Ø If
your SID is called testdb, the resulting PFILE should be called inittestdb.ora
The Oracle SPFILE
Ø The
SPFILE is different from the PFILE in that it cannot be directly edited.
Ø This is because it has a header and footer
that contain binary values.
Ø Since you cannot change a SPFILE directly,
Oracle allows you to manage the SPFILE via the alter system command
Ø For using an SPFILE, you can reap great
benefits.
Ø It can be backed up by RMAN (Oracle’s backup
and recovery software) every time a change is made or when the database is
backed up, which means it’s easier to recover
Ø SPFILES allow you to make dynamic changes to
parameters that are persistent.
Ø Database parameter change was not persistent
if we were using PFILES
Ø Alter system set
db_recovery_file_dest_size=10g
Ø If we were using SPFILES the parameter
would keep the same value, even after a database restart.
Ø This means you only have to change the parameter
value in one place, and that you can forget having to change it in the PFILE of
the database.
Ø One of the most important benefits of the
SPFILE is that Oracle has introduced many automatic tuning features into the
core of the database.
Ø Without an SPFILE, Oracle cannot auto tune
your database.
Ø An SPFILE uses the same formatting for
its file name as the PFILE, except the word spfile replaces init.
Ø For instance, if your ORACLE_SID is testdb,
the resulting spfile would be called spfiletestdb.ora.
Administering the PFILE and SPFILE
Ø As a DBA the main thing you need to
worry about with the SPFILE and PFILES are backing them up.
Ø You can use RMAN to backup an SPFILE,
or back them up yourself.
Ø A PFILE is simply a text based file,
which means you can copy it to another directory without affecting the Oracle
instance. This is the easiest way to backup a PFILE.
Ø To back up an SPFILE, you will first want
to convert it to a PFILE.
You can do this with the following syntax
SQL>
create pfile from spfile;
Ø This will create a PFILE named initSID.ora
in your $ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix)
directory.
Ø The SID in initSID.ora will be replaced
with the SID of your database as defined during creation.
Ø You can back up the file directly to
the preferred location with the command.
Syntax:
SQL>
create pfile=/path/to/backup.ora from spfile;
Eg:
SQL>create
pfile=’/u01/app/oracle/oradata/HRDEV/backup.ora’ from spfile;
Ø If the time comes that you must put the
SPFILE back into place, you can do so with this command:
Syntax:
SQL> create spfile from
pfile=/path/to/backup.ora;
Ex:
SQL>create
spfile=’/u01/app/oracle/oradata/HRDEV/backupsp.ora’ from pfile;
Ø You can use the V$PARAMETER dynamic
view to see the current setting of the different database parameters.
Ø In this example, we use the DESC
SQL*Plus command to describe the V$PARAMETER view, and we then query the
V$PARAMETER view to see the value of the control_file parameter setting:
SQL> desc v$parameter
You
may also use the shortcut “show parameter” command. For instance:
SQL> show parameter control_files;
The
Parameter File at Startup Time
Ø Oracle prefers the use of an SPFILE to a PFILE.
Ø When you startup your Oracle database, Oracle will scan the contents of your parameter directory ($ORACLE_HOME/database on Windows or the Linux directory name $ORACLE_HOME/dbs), searching in the following order
* spfileSID.ora
*
spfile.ora
*
initSID.ora
*
init.ora
If
the directory contains none of the above, then the startup will fail.
Alternatively,
you can tell Oracle where to find a PFILE if you store it in a different
location.
Before
doing this we have to shutdown the database.
SQL>shutdown
immediate;
Syntax:
SQL>
startup pfile=/path/to/pfile/inittestdb.ora
Eg:
SQL>startup
pfile=’/u01/app/oracle/product/10.2.0/db_1/dbs/initHRDEV.ora’;
Oracle spfile example
The Oracle spfile is a binary representation of the text based
init.ora file. By default, a new Oracle9i database will be working on a
pfile, so the spfile must be created from the pfile at the SQL prompt.
The spfile is created using the CREATE SPFILE statement; this
requires connecting as SYSDBA.
Connect system/manager as sysdba;
CREATE SPFILE FROM PFILE;
This command creates a spfile in a non-default location
($ORACLE_HOME/database). However, you can fully-qualify the path name
is the “create spfile” statement:
CREATE SPFILE='/u01/admin/prod/pfile/file_mydb.ora'
FROM
PFILE=/u01/admin/prod/pfile/initprod.ora';
Warning - After a spfile is created, when you
bounce the database you may encounter an error. To get around this, you have
to reconnect as SYSDBA and use the STARTUP command.
The addition of the spfile has changed the search path for the
Oracle startup deck. Oracle9i now uses the following path:
Specifying SCOPE in parameters
Once you have a spfile, you can change any initialization
parameter with the “alter system” command. However, there is an
important SCOPE parameter that you need to understand.
The SCOPE parameter has three values MEMORY, SPFILE and BOTH.
Let’s look at an example of each:
Alter system set db_2k_cache_size=100m
SCOPE=SPFILE;
If you want to make a change to a parameter in the spfile
without affecting the current instance, you can do so using the SCOPE=SPFILE
option of the ALTER SYSTEM statement. This is useful when you want to make a
change starting from the next startup and not for the current instance.
Alter system set db_2k_cache_size=100m
SCOPE=MEMORY;
In the example above, the SCOPE=MEMORY tells Oracle9i to make
the change for the life of the instance, and to change it back to the default
value the next time the database is bounced.
Alter system set db_2k_cache_size=100m
SCOPE=BOTH;
When you specify SCOPE=BOTH, the change will be made immediately,
and Oracle will also make the change permanent, even after the database is
bounced.
How
to set sga_max_size using spfile?
SQL>alter system set sga_max_size =400m SCOPE=SPFILE;
How
to start with pfile?
SQL>startup
pfile=$ORACLE_HOME/dbs/init$control1.ctl;
|
A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.
An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.
How
will I know if my database is using a PFILE or SPFILE?
Execute
the following query to see if your database was started with a PFILE or SPFILE:
SQL> SELECT DECODE (value,
NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name =
'spfile';
You can
also use the V$SPPARAMETER view to check if you are using a PFILE or not: if
the "value" column is NULL for all parameters, you are using a PFILE.
SQL> select name,value from V$parameter where name like 'spfile' order by name;
NAME VALUE
--------------------------------------------------------------------------------
Spfile
No value is displayed because database is not using spfile it is using pfile. It means database is using pfile for startup.
SQL> select name,value from V$parameter where name like 'spfile' order by name;
NAME VALUE
--------------------------------------------------------------------------------
Spfile D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILESTAN.ORA
It gives path of spfile file which is getting used by database for starting up. It means my databaseis using spfile.
SQL> select name,value from V$parameter where name like 'spfile' order by name;
NAME VALUE
--------------------------------------------------------------------------------
Spfile
No value is displayed because database is not using spfile it is using pfile. It means database is using pfile for startup.
SQL> select name,value from V$parameter where name like 'spfile' order by name;
NAME VALUE
--------------------------------------------------------------------------------
Spfile D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SPFILESTAN.ORA
It gives path of spfile file which is getting used by database for starting up. It means my databaseis using spfile.
Viewing
Parameters Settings:
One can
view parameter values using one of the following methods (regardless if they
were set via PFILE or SPFILE):
Ø The "SHOW
PARAMETERS" command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)
Ø V$PARAMETER view -
display the currently in effect parameter values
Ø V$PARAMETER2 view -
display the currently in effect parameter values, but "List Values"
are shown in multiple rows
Ø V$SPPARAMETER view -
display the current contents of the server parameter file.
Starting
a database with a PFILE or SPFILE:
Oracle
searches for a suitable initialization parameter file in the following order:
Ø Try to use the
spfile${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database
(Windows)
Ø Try to use the spfile.ora
file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database (Windows)
Ø Try to use the
init${ORACLE_SID}.ora file in $ORACLE_HOME/dbs (Unix) or ORACLE_HOME/database
(Windows)
One can
override the default location by specifying the PFILE parameter at database
startup:
SQL>
STARTUP PFILE='/oradata/spfileORCL.ora'
Note
that there is not an equivalent "STARTUP SPFILE=" command. One can
only use the above option with SPFILE's if the PFILE you point to (in the
example above), contains a single 'SPFILE=' parameter pointing to the SPFILE
that should be used. Example:
SPFILE=/path/to/spfile
Converting
between PFILES and SPFILES:
One can
easily migrate from a PFILE to SPFILE or vice versa. Execute the following
commands from a user with SYSDBA or SYSOPER privileges:
SQL> CREATE PFILE FROM
SPFILE;
SQL> CREATE SPFILE FROM PFILE;
Note:
Without creating a database we can create spfile but pfile is must.
$sqlplus
“/as sysdba”
SQL>create
spfile from pfile; (Here pfile is must)
SQL>startup
<enter> (It will read spfile for starting).
One can
also specify a non-default location for either (or both) the PFILE and SPFILE
parameters. Look at this example:
SQL>
CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';
Here is
an alternative procedure for changing SPFILE parameter values using the above
method:
Ø Export the SPFILE with:
CREATE PFILE=‘pfilename’ FROM SPFILE = ‘spfilename’;
Ø Edit the resulting PFILE
with a text editor
Ø Shutdown and startup the
database with the PFILE option: STARTUP PFILE=filename
Ø Recreate the SPFILE with:
CREATE SPFILE=‘spfilename’ FROM PFILE=‘pfilename’;
Ø On the next startup, use
STARTUP without the PFILE parameter and the new SPFILE will be used
Parameter File
Backups:RMAN (Oracle's Recovery Manager) will backup the SPFILE with the database control file if setting "CONFIGURE CONTROLFILE AUTOBACKUP" is ON (the default is OFF). PFILEs cannot be backed-up with RMAN. Look at this example:
RMAN>
CONFIGURE CONTROLFILE AUTOBACKUP ON;Use the following RMAN command to restore an SPFILE:
RMAN>
RESTORE CONTROLFILE FROM AUTOBACKUP;
Creating a Parameter File: Example
The
following example creates a text parameter file my_init.ora from a binary
server parameter file production.ora:
CREATE PFILE = 'my_init.ora' FROM
SPFILE = 's_params.ora';
Practical:
$sqlplus ‘/as sysdba’
SQL>startup<enter>
SQL>sho parameter spfile;
None
SQL>alter system set
sga_max_size=250m;
Here specified initiated
parameter cannot be modified.
Once we bounce the database
server, then the new value will effect.
SQL>shutdown <enter>
SQL>startup<enter>
SQL>sho parameter
sga_max_size;
Name Type VALUE
-------- -------------- --------------
Sga_max_size big integer 250m
SQL>create spfile from pfile;
SQL>show parameter spfile;
SQL>startup force;
SQL>show parameter spfile;
SQL>startup force
pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora
SQL>sho parameter spfile;
Name Type Value
------ ------------ ------------------
Pfile string Null
Null means here I am using
pfile.
Static parameters can be
effected when scope=pfile;
SQL>alter system set
sga_max_size=250m scope=spfile;
This will effect after startup
force command.
SQL>startup force;
SQL>sho parameter
sga_max_size;
SQL>startup force
pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora
SQL>sho parameter
sga_max_size;
SQL>startup force;
No comments:
Post a Comment