Shulou(Shulou.com)06/01 Report--

1. Summary

The parameter file in ORACLE is an operating system file that contains a series of parameters and their corresponding values, which can be divided into two types of PFile and SPFile.

They are loaded when the database instance is started, and determine the physical structure of the database, memory, the limitation of the database, a large number of default values of the system, various physical properties of the database, specifying database control file names and paths, and so on. it is an important file for database design and performance tuning.

Concept

Initialize the parameter file (Initialization Parameters Files). Before Oracle 9i, ORACLE has been using PFILE to store initialization parameters, which is a text file.

Server parameter file (Server Parameter Files), starting with Oracle 9i, Oracle introduced the SPFILE file, which is in binary format and cannot be modified manually.

Three modes for modifying spfile parameters

Scope=both takes effect immediately and permanently (default mode)

Scope=spfile will not take effect until the next start.

Scope=memory takes effect immediately but expires the next time it starts

How to view the directory location of SPFILE and PFILE

Method 1. View dynamic View

SELECT NAME, VALUE, DISPLAY_VALUE FROM V$PARAMETER WHERE NAME = 'spfile'

Method 2. View parameters

SQL > show parameter spfile

SQL > show parameter pfile

Determine whether the database starts from SPFILE or PFILE

Method 1: query the dynamic view V$PARAMETER. If the value is not empty, it is started by SPFILE, otherwise it is PFILE.

SELECT name, value, display_value FROM v$parameter WHERE name = 'spfile'

Method 2:SHOW PARAMETER command to view

SQL > show parameter spfile

Method 3: through the v$spparameter view, if the query returns a value of 0, it indicates that you are using pfile, otherwise it indicates that you are using spfile

SQL > SELECT COUNT (1) FROM v$spparameter WHERE value is not null

The value of this v$spparameter comes from the spfile file definition and is displayed in the dynamic performance view

II. The difference between PFile and SPFile

1:PFILE is a text file, while SPFILE is in binary format.

PFILE files can be manually configured with a text editor, while SPFILE can only be modified online through the SQL command. You can see the difference between the two from the operating system. The initialization parameter file is an ASCII text file and SPFILE is a data file.

The modification of 2:SPFILE can be modified online through the SQL command, no longer need to be modified manually, and all changes to dynamic parameters can take effect immediately, while the modification of PFILE must restart the instance to take effect.

3: if you create the database manually instead of through DBCA, you can only define PFILE when you start creating the database.

Define SPFILE through PFILE after the database has been created

3. Oracle enables the order of parameter files

Step1. Spfile.ora

Unix/Linux defaults to the directory $ORACLE_HOME/dbs/

Step2. Spfile.ora

Unix/Linux defaults to the directory $ORACLE_HOME/dbs/

Step3. Init.ora

Unix/Linux defaults to the directory $ORACLE_HOME/dbs/

Using the BOTH option is actually equivalent to an ALTER SYSTEM statement with no parameters

Select name,value,ISSYS_MODIFIABLE from v$parameter

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.