Oracle Pfile SPFile

1. Definition and function of the parameter file
The Oracle database configures the database through a series of parameters. These parameters are expressed in the form of key-value pairs, such as:
Maxlogfiles=50
Background_dump_dest=c:dump
Where the left side of the equals sign is the parameter name, the right side is the value of the corresponding parameter, and there are several types of values, typically such as numbers and strings.

The parameter file is where these parameters are stored, and Oracle reads the relevant configuration from the parameter file at startup.

2. Classification of parameter files
Before 9i, the parameter file only one, it is text format, called Pfile, in 9i and later version, the new server parameter file, called SPFile, it is in binary format. Both of these parameter files are used to store parameter configurations for Oracle to read, but there are also the following differences:
First, Pfile is a text file, SPFile is a binary file;
Second, for the configuration of parameters, Pfile can be directly opened with a text editor manual configuration, and SPFile not, must be started in the database, through the SQL command online modification.
Thirdly, after the Pfile configuration has changed, to use it in effect, the database must be restarted, and the configuration effective time and scope of the SPFile can be specified by the SQL command that modifies the parameters, can take effect immediately, or it may not take effect immediately. Of course, some parameter modification must restart the database to take effect;
Finally, the SQL command can be used by pfile to create the SPFile, or the pfile may be created by SPFile;
If you are creating a database manually instead of through DBCA, you can only define pfile when you start to create the database. Because it is in text format;
The Oracle database uses only one parameter file, either Pfile or SPFile, that is, how can you tell which parameter file the database is currently using? One way is to be able to authenticate through create pfile, if the current use is not spfile, then the corresponding format of Create pfile will produce an error. Another method is the show parameter SPFile command, which is used to display the location of the SPFile, and if the value shown is empty, the pfile is used.

3. The action principle of the parameter file
When the Oracle instance starts, it reads the configuration in the parameter file, which is the process: the startup command of the
database can specify which pfile to start, but note that You can specify only pfile, and you cannot specify SPFile. When using the startup command without the Pfile clause, Oracle will $oracle_home/dbs (Unix/linux platform) from the default location specified by the platform, $ORACLE _home/database (Windows) reads the server The initialization parameters in the parameter file (spfile). The order in which Oracle looks for SPFile or Init.ora is: in the default location specified by the platform, Oracle first looks for files named Spfile$oracle_sid.ora, and if not, find the Spfile.ora file, and if not, find the init$ Oracle_sid.ora file. Under $oracle_base/admin/$db _names/pfile, you will probably see a file like this init.ora.1 92003215317] name, which is the initialization parameter file, just keep up with the timestamp. For Oracle920, the default is to use SPFile to start, but this spfile is not out of thin air, but based on the creation of this file, you can remove the long suffix, is the standard pfile file.
for Windows NT and Windows 2000, the location is: $ORACLE _home/database/spfile$oracle_sid.ora.
After the database is started, the configuration values of the parameters can be obtained by querying the data dictionary v$parameter.

4. How to modify the parameter file
is divided into manual and online modifications.
Manual modification is used to modify pfile and open pfile modification directly with text editing. You must restart the database to use the modification to take effect. The
Online modification is modified with the ALTER SYSTEM command when the database is running, as follows (for a detailed command statement refer to the Oracle's official reference documentation):
Sql>alter System Set job_queue_processed= Scope=memory
Note that scope=memory indicates the scope of the application, with the following values:
SPFILE: The modification is only valid for SPFILE, does not affect the current instance, and requires a restart of the database to take effect;
Memory: Modifications are only valid for memory. This is valid only for the current instance and takes effect immediately, but is not saved to SPFile, the configuration is lost after the database restarts, and
BOTH: As the name implies, contains both of the above, immediately effective and permanent.
for the alter system parameter Modification command, note the following:
First, if the current instance uses pfile instead of SPFile, then scope=spfile or Scope=both will produce an error;
Second, If the instance starts with Pfile, the default value for scope is memory, and if started with SPFile, the default value is BOTH;
Third, you can use deferred to indicate that the modifications are only applicable to future sessions, and you can use comment to write comments. For example: Alter system set JOB_QUEUE_PROCESSES=50 Scope=both DEFERRED comment= "comment"
IV, the method of deleting the parameter is as follows: Alter system set parameter= ' ';

5. Create a parameter file
For PFILE, you can manually edit one directly with a text editor, or you can use the Create PFILE command from SPFile, for example: Create Pfile= ' C:pfilemypfile. ORA ' from spfile= ' D:spfilemyspfile. ORA ', or created from the SPFile used by the current instance: Create Pfile= ' C:pfilemypfile.ora ' from SPFile.
The command to create the SPFILE is as follows: Create SPFILE from pfile= ' C:pfilemypfile '.

Oracle Pfile SPFile