DBA_Oracle PFile and SPFile file management and usage (CASE) (reference parameter Pfile/SPfile when data is started), spfilepfile

DBA_Oracle PFile and SPFile file management and usage (CASE) (reference parameter Pfile/SPfile when data is started), spfilepfile

2014-08-25 BaoXinjian

I. Summary

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

They are loaded when the database instance is started, it determines the physical structure of the database, memory, database restrictions, a large number of default values of the system, various physical properties of the database, specified Database Control File Names and paths, and other information, is an important file for database design and performance tuning.

 

1. Concept

Initialize parameter Files (Initialization Parameters Files). Before Oracle 9i, ORACLE used PFILE to store Initialization Parameters. The file is a text file.

Server Parameter Files. Since Oracle 9i, Oracle introduces the SPFILE file, which is in binary format and cannot be modified manually.

 

2. Modify three modes of the spfile Parameter

• Scope = both immediately and permanently effective (default mode)
• Scope = spfile takes effect only after it is started next time
• Scope = memory takes effect immediately but becomes invalid upon next Startup

 

3. How to view the directory location of SPFILE and PFILE

Method 1. view the 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 is started from SPFILE or PFILE.

Method 1: query the dynamic view V $ PARAMETER. If the VALUE is not empty, SPFILE is enabled; otherwise, PFILE is used.

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

Method 2: view the show parameter command

SQL> show parameter spfile;

Method 3: In the v $ spparameter view, if a 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 is defined in the spfile file and displayed in the dynamic performance view.

 

Ii. Differences between PFile and SPFile

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

You can use a text editor to open the PFILE for manual configuration, but not SPFILE. You can only use SQL commands to modify it online.

The differences between the two can be seen from the operating system. The initialization parameter file is an ASCII text file, and the SPFILE is a data file.

2: The modification of SPFILE can be modified online through the SQL command, without manual modification. All changes to dynamic parameters can take effect immediately, and the modification of PFILE must be restarted to take effect.

3: manually create a database instead of using DBCA. You can only define PFILE when creating a database.

After the database is created, use PFILE to define the SPFILE.

 

Iii. Enabling parameter file sequence in Oracle

Step1. spfile <sid>. ora

Default Unix/Linux storage directory $ ORACLE_HOME/dbs/

Step2. spfile. ora

Default Unix/Linux storage directory $ ORACLE_HOME/dbs/

Step3. init <sid>. ora

Default Unix/Linux storage directory $ ORACLE_HOME/dbs/

 

4. Case-create a PFile and start it. Then copy it to SPFile and start it again.

1. Create a PFile

2. create a SPFile through PFile <create spfile from pfile>

3. view the SPFile content, v $ spparameter

 

********************Author: Bao Xin********************

Which of the following parameter files is used to start a database under sqlplus? Pfile or spfile?

For Versions later than 9i, spfile is started by default. The default storage location is $ ORACLE_HOME/dbs (unix) or $ ORACLE_HOME \ database. The default name is spfilesid. ora, where sid is the sid of the database.
But you can also start the database by creating a pfile file.
Startup pfile = '$ ORACLE_HOME/dbs/pfilesid. ora;
In this case, the implicit parameter needs to be modified;

Check whether the current database uses spfile or pfile as follows:
Log on to the database as sysdba and run the show parameter spfile command. If there is a value, spfile is used; otherwise, pfile is used;

SQL> conn/as sysdba
Connected.
SQL> show parameter spfile;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Spfile string? /Dbs/spfile @. ora
SQL>

How can I determine whether spfile or pfile is used for database startup?

I suddenly thought of this problem and made a test.
SQL> startup
-- Operation (the operation is the following method and can be used to determine whether pfile is started)
SQL> create pfile = '/home/oracle/init. ora' from spfile;
SQL> shutdown immediate
SQL> startup pfile =/home/oracle/init. ora
-- Operation (the operation is the following method and can be used to determine whether pfile is started)
------------------------------------------------------------ Method 1: query the v $ parameter dynamic view. If the query result is empty, pfile is started, and spfile is vice versa.
Select name, value from v $ parameter where name = 'spfile ';
---------------------------------------------------------- Method 2 use the show command. If the query result is empty, pfile is started, and spfile is vice versa.
Show parameter spfile
------------------------------------------------------------ Method 3 novelty view v $ spparameter. If the return value is 0, pfile is enabled. Otherwise, spfile is enabled.
Select count (*) from v $ spparameter where value is not null;
------------------------------------------------------------ Method 4 or v $ spparameter view. If true is returned, pfile is enabled, and spfile is vice versa.
Select isspecified, count (*) from v $ spparameter group by isspecified;
------------------------------------------------------------ Method 5 use the following statement to directly judge
Select decode (count (*), 1, 'spfile', 'pfile') USED
From v $ spparameter where rownum = 1 and isspecified = 'true'; USED ------ pfile ------------------------------------------------------------
The method is similar. Learn from each other.