Oracle 10 Gb spfile and pfile

Starting from Oracle 9i, Oracle uses spfile by default to start the database. spfile must be created by pfile. The new spfile will take effect the next time the database is started.

Pfile is the Initialization parameter file (Initialization Parameters Files). Before Oracle 9i, Oracle stores the Initialization Parameters through pfile. The file is a text file and can be manually modified.

Spfile is the Server Parameters Files, which is introduced after Oracle 9i. It is a data file and cannot be modified manually.

Before Oracle9i, you must manually modify the pfile file to modify the initialization parameters. When the instance is started, the initialization parameters will be read from the pfile. Therefore, restarting the instance will make the modification take effect. After spfile is introduced, you can directly use the command to modify the parameter (alter system). The dynamic parameter modification can take effect directly and the scope of the parameter can be set at the same time, the scope of the static parameter must be spfile, which takes effect only after restart.

1. [Oracle @ oracle pfile] $ file init. ora.7302011151541
2. Init. ora.7302011151541: ASCII text

1. [Oracle @ oracle dbs] $ file spfileTESTDB. ora
2. SpfileTESTDB. ora: data

[oracle@oracle dbs]$ file spfileTESTDB.ora spfileTESTDB.ora: data

1. SQL> select * from v $ version;
3. BANNER
4. --------------------------------------------------------------------------------
5. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bi
6. PL/SQL Release 10.2.0.1.0-Production
7. CORE 10.2.0.1.0 Production
8. TNS for Linux: Version 10.2.0.1.0-Production
9. NLSRTL Version 10.2.0.1.0-Production

The file directories are:

Pfile:

/Home/oracle/admin/TESTDB/pfile/init. ora.6112012111012

Spfile:

/Home/oracle/product/10.2.0/db_1/dbs/spfileTESTDB. ora

1. If the spfile is corrupted and does not exist, restart the database and the spfile cannot be found.

1. SQL> startup
2. ORA-01078: failure in processing system parameters
3. LRM-00109: cocould not open parameter file
4. '/Home/oracle/product/10.2.0/db_1/dbs/initTESTDB. ora'

In this case, you need to create a spfile through pfile (note that the spfile file cannot be re-created while the system is running normally, this method can also be used to check whether the database is started through the spfile file --!) :

1. ERROR at line 1:
2. ORA-32002: cannot create SPFILE already being used by the instance

 

1. SQL> create spfile from pfile =
2. '/Home/oracle/admin/TESTDB/pfile/init. ora.7302011151541 ';
4. File created.

And restart the database.

By default, spfile is created to the default directory of the system.

1. Unix: $ ORACLE_HOME/dbs
2. Windows: $ ORACLE_HOME \ database

Note:

1. Create a pfile through the spfile File

Create pfile [= 'pfile-name'] from spfile [= 'spfile-name'];

2. Create a spfile file through the pfile File

Create spfile [= 'spfile-name'] from pfile [= 'pfile-name'];

3. Modify dynamic parameters through the spfile File

Alter system set parameter = Value scope = memory | spfile | both

Memory: only changes the current instance and becomes invalid after the database is restarted.

Spfile: only changes the spfile settings, does not change the current instance settings, takes effect after the database is restarted

Both: changes the spfile and instance settings at the same time. The current change takes effect immediately and remains valid after restart.

4. When you start up nomount, you need to read spfile or pfile to coexist. spfile takes precedence.