Oracle Concepts - pfile and spfile

Oracle Tips by Burleson Consulting

Introducing the PFILE and SPFILE

Oracle provides two different types of mutually exclusive parameter files that you can use, PFILE and SPFILE. Let’s look at the PFILE and the SPFILE in a bit more detail.

The Oracle PFILE

As we said, 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. Also, 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 (we talked about where ORACLE_HOME was earlier in this book).

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 can not be directly edited. This is because it has a header and footer that contains binary values. Since you can not change a SPFILE directly, Oracle allows you to manage the SPFILE via the alter system command.

That might sound a bit more complex, but it really is no harder than manually changing a PFILE. 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 (we will talk about RMAN a great deal in a later chapter!). Also, SPFILES allow you to make dynamic changes to parameters that are persistent. For example, remember that we said this 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 can not autotune 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.

Remember that 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. 

Note that the SID in initSID.ora will be replaced with the SID of your database as defined during creation.

In addition, you can back up the file directly to the preferred location with the command:

SQL> create pfile=/path/to/backup.ora from spfile;

If the time comes that you must put the SPFILE back into place, you can do so with this command:

SQL>  create spfile from pfile=/path/to/backup.ora

If your database is currently running using the SPFILE, be sure to shut down first so Oracle can replace the file.  As your SPFILE is in use the entire time your database is running, you should never overwrite it during normal operations 

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    Name                                      Null?    Type  ----------------------------------------- -------- -------------  NUM                                                NUMBER  NAME                                               VARCHAR2(80)  TYPE                                               NUMBER  VALUE                                              VARCHAR2(512)  DISPLAY_VALUE                                      VARCHAR2(512)  ISDEFAULT                                          VARCHAR2(9)  ISSES_MODIFIABLE                                   VARCHAR2(5)  ISSYS_MODIFIABLE                                   VARCHAR2(9)  ISINSTANCE_MODIFIABLE                              VARCHAR2(5)  ISMODIFIED                                         VARCHAR2(10)  ISADJUSTED                                         VARCHAR2(5)  ISDEPRECATED                                       VARCHAR2(5)  DESCRIPTION                                        VARCHAR2(255)  UPDATE_COMMENT                                     VARCHAR2(255)  HASH                                               NUMBER     SQL> select name, value from v$parameter where name = 'control_files';   NAME                 VALUE -------------------- ----------------------------------------------- control_files        C:\ORACLE\ORADATA\BOOKTST\BOOKTST\CONTROL01.CTL, C:\ORACLE                      \ORADATA\BOOKTST\BOOKTST\CONTROL02.CTL, C:\ORACLE\ORADATA\                      BOOKTST\BOOKTST\CONTROL03.CTL

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.

SQL> startup pfile=/path/to/pfile/inittestdb.ora

Furthermore, you can create a PFILE that contains nothing but the following line:

SPFILE=/path/to/spfiletestdb.ora

By doing so, we are able to startup using a PFILE in any location we want, but continue to use an SPFILE that can also be in a different location.  This can be very beneficial for those that wish to store their SPFILE in a centralized location, such as a SAN.  Now, let’s take a quick look at redo log administration.

==============================================================

Initialization Parameter files: PFILEs vs. SPFILEs

Submitted by admin on Sun, 2003-08-03 19:29

• RDBMS Server

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

What is the difference between a PFILE and SPFILE:

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.

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

Changing SPFILE parameter values:

While a PFILE can be edited with any text editor, the SPFILE is a binary file. The "ALTER SYSTEM SET" and "ALTER SYSTEM RESET" commands can be used to change parameter values in an SPFILE. Look at these examples:

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET timed_statistics=TRUE
	COMMENT='Changed by Frank on 1 June 2003'
	SCOPE=BOTH
 	SID='*';

The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:

- MEMORY: Set for the current instance only. This is the default behaviour if a PFILE was used at STARTUP.

- SPFILE: update the SPFILE, the parameter will take effect with next database startup

- BOTH: affect the current instance and persist to the SPFILE. This is the default behaviour if an SPFILE was used at STARTUP.
The COMMENT parameter (optional) specifies a user remark.

The SID parameter (optional; only used with RAC) indicates the instance for which the parameter applies (Default is *: all Instances).

Use the following syntax to set parameters that take multiple (a list of) values:

SQL> ALTER SYSTEM SET utl_file_dir='/tmp/','/oradata','/home/' SCOPE=SPFILE;

Use this syntax to set unsupported initialization parameters (obviously only when Oracle Support instructs you to set it):

SQL> ALTER SYSTEM SET "_allow_read_only_corruption"=TRUE SCOPE=SPFILE;

Execute one of the following command to remove a parameter from the SPFILE:

SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;
SQL> ALTER SYSTEM SET timed_statistics = '' SCOPE=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;

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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26464953/viewspace-713256/，如需轉載，請註明出處，否則將追究法律責任。