Introduction to spfile and pfile in oracle [from henli]

1. Definitions and functions of parameter files
The oracle Database configures the database through a series of parameters. These parameters are displayed in the form of key-value pairs, such:
MAXLOGFILES = 50
BACKGROUND_DUMP_DEST = C: \ DUMP
Here, the parameter name is on the left of the equal sign, and the value corresponding to the parameter value on the right. There are many types of values, such as numbers and strings.

Parameter files are the places where these parameters are stored. oracle reads related configurations from the parameter files at startup.

2. Classification of parameter files
Before 9i, there was only one parameter file. It was in text format and called pfile. In 9i and later versions, a new server parameter file, called spfile, was added, it is in binary format. Both parameter files are used to store parameter configurations for oracle reading, but there are also differences. Note the following:
First, pfile is a text file, and spfile is a binary file;
Second, for parameter configuration, pfile can be opened manually in a text editor, but spfile cannot. It must be modified online through SQL commands after the database is started.
Third, to use pfile to take effect after the configuration change, you must restart the database. The time limit and scope of the spfile configuration can be specified by the SQL command that modifies the parameter and can take effect immediately, or it does not take effect immediately. Of course, some parameter modifications must be restarted before they take effect;
Fourth, you can use SQL commands to create spfile by pfile or pfile by spfile;
Fifth, If you create a database manually instead of using DBCA, you can only define pfile when creating a database. Because it is in text format;
Sixth, the oracle database only uses one parameter file, either pfile or spfile, that is, how to determine which parameter file is currently used by the database? One way is to use create pfile for identification. If the previous use is not spfile, the corresponding format of create pfile will produce an error. The show parameter spfile command is used to display the position of the spfile. If the displayed value is null, pfile is used.

3. parameter file Action Principle
When an oracle instance is started, it reads the configuration in the parameter file. This process is as follows:
You can specify which pfile to start in the database's startup command. However, note that only pfile can be specified, but spfile cannot be specified.
When the startup command without the pfile clause is used, Oracle reads the initialization parameters from the server parameter file (spfile) in the default location specified by the platform. Search for spfile or chuangtong init in Oracle. the order of ora is: in the default location specified by the platform, Oracle first looks for the file named spfile $ ORACLE_SID.ora. If it does not exist, it looks for the spfile. find the init $ ORACLE_SID.ora file.
Under $ ORACLE_BASE \ admin \ db_name \ spfile, you may see a file with the init. ora.1 92003215317] Name like this. This is the initialization parameter file, but it only keeps up with the timestamp. For Oracle920, spfile is started by default, but this spfile is not created out of thin air, but created based on this file. You can remove this long suffix, that is, the standard pfile file.
For Windows NT and Windows 2000, the path is $ ORACLE_HOME \ database \ spfile $ ORACLE_SID.ora.
After the database is started, the parameter configuration value can be obtained by querying the data dictionary v $ parameter.

4. How to modify the parameter file
Manual modification and online modification.
Manual modification is used to modify pfile. You can directly use text editing to open pfile modification. To use the modification to take effect, you must restart the database.
Online modification is performed using the alter system command when the database is running. The command is as follows (for detailed command statements, see the oracle official reference documentation ):
SQL> alter system set job_queue_processed = 50 scope = MEMORY
Note: scope = MEMORY indicates the application range. The values are as follows:
SPFILE: the modification is only valid for SPFILE and does not affect the current instance. You need to restart the database to make the modification take effect;
MEMORY: the modification is only effective for the MEMORY, that is, it is only valid for the current instance and takes effect immediately, but it is not saved to SPFILE. This configuration is lost after the database is restarted;
BOTH: as the name suggests, it includes the above two types. It takes effect immediately and permanently.
Note the following points for the alter system parameter modification command:
First, if the current instance uses pfile instead of spfile, scope = spfile or scope = both will produce an error;
Second, if the instance is started with pfile, the default value of scope is MEMORY. If the instance is started with spfile, the default value is BOTH;
Third, you can use DEFERRED to indicate that the changes are only applicable to future sessions. You can also use COMMENT to write the note, for example, alter system set JOB_QUEUE_PROCESSES = 50 SCOPE = both deferred comment = "COMMENT"
Fourth, the method for deleting a PARAMETER is as follows: alter system set parameter = '';

5. Create a parameter file
For pfile, you can use a text editor to directly edit one or use the create pfile command to CREATE one from spfile, for example, create PFILE = 'C: \ PFILE \ MYPFILE. ORA 'from SPFILE = 'd: \ SPFILE \ MYSPFILE. ORA, or create from the spfile used by the current instance: create pfile = 'C: \ pfile \ mypfile. ora 'from spfile.
The command for creating a spfile is as follows: create spfile from pfile = 'C: \ PFILE \ MYPFILE'