Pfile VS Spfile (MOS Note 249664.1), pfilespfile

Pfile VS Spfile (MOS Note 249664.1), pfilespfile

For the original article, see:Http://www.cnblogs.com/haoxiaoyu/p/4308483.html, Better format, more conducive to reading ~~~

 

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

Until Oracle 8i DBAs have been using a text file called the pfile (parameter file) to store the database initialization parameters. --- use the text parameter file before 8i

The pfile is read at instance startup time to get specific instance characteristics. Any changes made the pfile wocould only take effect when the database is restarted.

However, parameters that were dynamically alterable cocould be changed using the appropriate alter system or alter session statement, which wocould take effect immediately.

As of Oracle9i, new feature called the spfile (server parameter file ). the spfile is a binary file that contains the same information as the old pfile. -- 9i introduction and service parameter file, binary type

The spfile is a server-side initialization parameter file; parameters stored in this file are persistent guest SS database startups.

This makes all the changes made to the instance using the alter system statement persistent. Oracle requires that you start an instance for the first time using the pfile and then create the spfile.

The server parameter file (also called SPFILE) is in a single location where all the necessary parameters are defined and stored. The defined parameter values are applicable for all the instances in the cluster.

The SPFILE permits dynamic changes without requiring you to bring down the instance.

You can still use the client side parameter file to manage parameter settings in Real Application Clusters; however, administrative convenience is sacrifle iced and the advantage of dynamic change is lost.

By default, if you do not specify PFILE in your STARTUP command, Oracle will use a server parameter file.--- Spfile is used by default when the database is started.


Server parameter file (SPFILE)--- Service parameter file
======================================

A server parameter file is basically a repository for initialization parameters.

Initialization parameters stored in a SPFILE are persistent, meaning any parameter changes made while an instance is running can persist between SS instance shutdown and startup.

In this way, all the initialization parameters manually updated by alter system set commands become persistent.

It also provides a basis for the Oracle database server to self-tune.

Another advantage, maid for multi-instance RAC systems, is that a single copy of the parameter file can be used by all instances. even though a single file is used to specify parameters, it has different format styles to support both the common values for all instances, as well as the specific values for an individual instance.

A server parameter file is initially built from the traditional text initialization parameter file, usingCreate SPFILE Statement. It is a binary file that cannot be browsed or edited with a text editor.

Oracle provides other interfaces for viewing and modifying parameter settings. at system startup, the default behavior of the STARTUP command is to read a SPFILE to obtain initialization parameter settings. if the STARTUP command doesn't have a PFILE clause, it reads the SPFILE from a location
Specified by the operating system.

If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command.


Setting the server parameter file values --- set THE value of THE PARAMETER FILE
========================================================== =

Use the SID designator to set instance-specific parameter values in the server parameter file.

For settings when SS the database, use a '*', and for a specific instance, set the prefix with SID as indicated below. the database range uses "*", and the instance range uses "SID."

*. OPEN_CURSORS = 400 # For database-wide setting
RACDB1.OPEN _ CURSORS = 800 # For RACDB1 instance

Note that even though open_cursors is set at 400 for all instances in the first entry, the value of 800 remains in effect for the SID 'racdb1'. --- equivalent to the proximity principle, with different priorities

Some initialization parameters are dynamic since they can be modified using the alter session or alter system statement while an instance is running. Use the following syntax to dynamically alter
Initialization parameters:

Alter session set parameter_name = value
Alter system set parameter_name = value [DEFERRED]

Use the SET clause of the alter system statement to set or change initialization parameter values. Additionally, the SCOPE clause specifies the scope of a change as described below:

SCOPE = SPFILE ---- for all dynamic and static parameters, this statement takes effect after the next restart

(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart .)

SCOPE = MEMORY ---- adjust the dynamic parameters and expire upon next restart

(For dynamic parameters, changes are applied in memory only. No static parameter change is allowed .)

SCOPE = BOTH --- valid for dynamic parameters, BOTH current and restart take effect. You can specify the DEFERRED parameter, and the delay takes effect.

For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameter change is allowed .)

For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is valid only for future sessions.

HERE ARE A FEW EXAMPLESExamples
======================================

The following statement affects all instances. However, the values are only valid for the current instances, they are not written to binary SPFILE.

Alter system set OPEN_CURSORS = 500 SID = '*' SCOPE = MEMORY;

The next statement resets the value for the instance 'racdb1 '.
At this point, the database-wide setting becomes valid tive for SID of RACDB1.

Alter system reset OPEN_CURSORS SCOPE = SPFILE sid = 'racdb1 ';

To reset a parameter to its default value throughout the cluster database, use the command:

Alter system reset OPEN_CURSORS SCOPE = SPFILE sid = '*';


Creating a server parameter file --- create A PARAMETER FILE

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

The server parameter file is initially created from a text initialization parameter file (init. ora ).

It must be created prior to its use in the STARTUP command.
The create SPFILE statement is used to create a server parameter file.

The following example creates a server parameter file from an initialization parameter file.

Create spfile from pfile = '/u01/oracle/product/920/dbs/initRAC1.ora ';

Below is another example that extends strates creating a server parameter file and supplying a name.

Create spfile = '/u01/oracle/product/920/dbs/racdb_spfile.ora'
From pfile = '/u01/oracle/product/920/dbs/init. ora ';

Exporting the server parameter file --- export THE PARAMETER FILE
==========================================

We can export the server parameter file to create a traditional text initialization parameter file.

This wocould be useful for :( function)
1) Creating backups of the server parameter file. -- backup
2) For diagnostic purposes to list all of the parameter values currently used by an instance. Diagnose
3) Modifying the server parameter file by first exporting it, editing the output file, and then recreating it. external editing

The following example creates a text initialization parameter file from the server parameter file:

Create pfile from spfile;

The example below creates a text initialization parameter file from a server parameter file, where the names of the files are specified:

Create pfile = '/u01/oracle/product/920/dbs/racdb_init.ora'
From spfile = '/u01/oracle/product/dbs/racdb_spfile.ora ';

Refer to 'oracle 9i Database Reference 'for all the parameters that can be changed with an alter system command...

Is my database using spfile?

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

Am I using spfile or pfile?

The following query will let you know ..

1) SQL> SELECT name, value FROM v ParameterWHEREname = 'spfile ′; NAMEVALUE − − spfile/fsys1/oracle/product/9.2.0/spfileTEST. ora2) SQL> showparameterspfile; Thev Spparameter view
The contents of the SPFILE can be obtained fromV $ SPPARAMETERView:The modified static parameters can be found in this view before the database is restarted.

SQL> ALTER SYSTEM SET timed_statistics=FALSE SCOPE=SPFILE;System altered.SQL> SELECT name,value FROM v$parameter WHERE name='timed_statistics';NAME VALUE-------------------- ---------------------timed_statistics TRUESQL> SELECT name,value FROM v$spparameter WHERE name='timed_statistics';NAME VALUE-------------------- ---------------------timed_statistics FALSERELATED DOCUMENTS-----------------Oracle 9, Oracle 10g