In Oracle : PFILE and SPFILE initialization files in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> PFILE and SPFILE initialization files in Oracle

 

 PFILE and SPFILE initialization files in Oracle

 

 

Here are the main topics for this article:

How the Oracle Instance is initialized

 

When the Oracle instance start, first it looks to the $ORACLE_HOME/dbs (UNIX, Linux) or ORACLE_HOME/database (Windows) directory for the following files (in this order): 

      1.  spfile${ORACLE_SID}.ora    (SPFILE = Server Parameter File)

      2.  spfile.ora                                  (SPFILE)  

      3.  init${ORACLE_SID}.ora        (PFILE) 

      4.  init.ora                                      (PFILE) 

 

The first found file is used for the instance initialization. So, Oracle first look for a SPFILE and after that after a PFILE. PFILE is a text file, SPFILE is a binary file which cannot be modified using a text editor.  

  

 

SPFILE advantages

 

Here are the SPFILE advantages:

  1. No need to restart the database in order to have a parameter changed and the new value stored in the initialization file

  2. Reduce human errors: Parameters are checked before changes are accepted

  3. An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)

 


What is my database using SPFILE or PFILE ?

 

The following select has the response:

 

             SELECT DECODE (value, NULL, 'PFILE', 'SPFILE') "Init File Type"

             FROM sys.v_$parameter WHERE name = 'spfile';

 

 

How could I switch from SPFILE to PFILE and vice-versa ?

 

Switch from SPFILE to PFILE:

     1)  CREATE PFILE FROM SPFILE;

     2)  Backup and delete SPFILE

     3)  Restart the instance

 

Switch from PFILE to SPFILE :

     1)  CREATE SPFILE FROM PFILE

     2)  Restart the instance (the PFILE will be in the same directory but will not be used. SPFILE will be used instead)  

 

 

Changing the SPFILE parameter values

 

                 ALTER SYSTEM SET timed_statistics = TRUE

                          COMMENT = 'Changed by Paul on January 1st 2008'

                          SCOPE = BOTH

                          SID = '*'

 

The SCOPE parameter can be set to SPFILE, MEMORY or BOTH:

- MEMORY: Set for the current instance only. This is the default behavior if a PFILE was used at STARTUP.
- SPFILE: update the SPFILE, the parameter will take effect with next database startup
- BOTH: affect the current instance and persist to the SPFILE. This is the default behavior if an SPFILE was used at STARTUP. 

 

The COMMENT parameter (optional) specifies a user remark.

The SID parameter (optional; only used with RAC) indicates the instance for which the parameter applies. (Default is *: all Instances).

 

Converting SPFILE to PFILE and vice-versa

 

This could be done in order to have a backup in the other format or to change the initialization file for the database instance. 

 

            CREATE PFILE FROM SPFILE;

            CREATE SPFILE FROM PFILE;

            CREATE SPFILE = '/oradata/spfileORCL.ora' FROM PFILE = '/oradata/initORCL.ora' ;

 

 

Viewing parameter values

 

1)   Using SHOW PARAMETERS:     SHOW PARAMETERS sga_max

 

2)   Querying the following views:

        

        a) V$PARAMETER: display the currently in effect parameter values

        b) $SPPARAMETER: display the current contents of the server parameter file.

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> PFILE and SPFILE initialization files in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.