Dba_oracle PFile and SPFile file management and use (case) (set in reference parameters Pfile/spfile data startup)

2014-08-25 Baoxinjian

I. Summary

The parameter file in Oracle is an operating system file that contains a series of parameters and the corresponding values of the parameters, and can be divided into two types Pfile and SPFile.

They are loaded at the start of the database instance, which determines the physical structure of the database, memory, database constraints and the system of a large number of default values, the various physical properties of the database, the specified database control file name and path information, is the database design and performance tuning of important files.

1. Concept

Initialization parameters file (initialization Parameters files), Oracle 9i, Oracle has been using Pfile method to store initialization parameters, which is a text file.

Server Parameter files, starting with Oracle 9i, Oracle introduced the SPFile file, which is in binary format and cannot be modified manually.

2. Modify the three modes of the SPFile parameter

• Scope=both Immediate and permanent (default mode)
• Scope=spfile the next boot will take effect
• Scope=memory takes effect immediately but fails on next boot

3. 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

4. Determine whether the database starts from SPFile or pfile

Method 1: Query The dynamic view V$parameter, if the value is non-null, it is spfile started, otherwise it is pfile.

SELECT name, value, Display_value from v$parameter WHERE name = ' SPFile ';

Method 2:show parameter Command view

Sql> Show parameter SPFile;

Method 3: Through the V$spparameter view, if the query returns a value of 0, indicating that you are using pfile, otherwise you are using SPFile

Sql> SELECT COUNT (1) from V$spparameter WHERE value was not null;

The value of this v$spparameter is 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, and SPFile is in binary format.

Pfile files can be manually configured with a text editor, but not SPFile, and can only be modified online via SQL commands.

From the operating system can see the difference between the two, the initialization parameter file is an ASCII text file, spfile as a data file.

2:spfile changes can be modified online through the SQL command, no longer need to be manually modified, for dynamic parameters all changes can take effect immediately, and pfile modification must restart the instance to take effect.

3: Create a database manually instead of through DBCA, you can only define pfile when you start to create a database.

After the database is created, it is defined by Pfile SPFile

Iii. Oracle Enable parameter file order

Step1. Spfile<sid>.ora

Unix/linux Default storage directory $ORACLE _home/dbs/

Step2. Spfile.ora

Unix/linux Default storage directory $ORACLE _home/dbs/

Step3. Init<sid>.ora

Unix/linux Default storage directory $ORACLE _home/dbs/

Iv. Case-Create a pfile, and start, after copy for SPFile start again

1. Establish Pfile

2. Establishment of SPFile <create SPFile from pfile> via Pfile

3. View SPFile content, V$spparameter

Abalone New ********************

Dba_oracle PFile and SPFile file management and use (case) (set in reference parameters Pfile/spfile data startup)