Saturday, 17 June 2017

SPFILE and PFILE in Oracle Database

Parameter File:-A parameter file is a file that contains a list of initialization parameters and a value for each parameter.

Types of Parameter File:- Oracle support below 2 types of parameter files

Server Parameter Files (SPFILE) :- This is a Binary File and it is default.
Parameter Files (PFILE)- This is a Text File.

When the Oracle instance started, first it looks for Parameter file in $ORACLE_HOME/dbs directory in below order:-
      1.  spfile${ORACLE_SID}.ora   (SPFILE = Server Parameter File)
      2.  spfile.ora                (SPFILE) 
      3.  init${ORACLE_SID}.ora     (PFILE)
      4.  init.ora                  (PFILE)

Oracle first look for a SPFILE and after that a PFILE. 

Advantages of SPFILE:-

1) No need to restart the database in order to have a parameter changed and the new value stored in the SPFILE
2) Reduce human errors Parameters are checked before changes are accepted
3) An SPFILE can be backed-up with RMAN while PFILE can’t backed-up by RMAN

By following query we can check that which file is using by database.

SELECT DECODE (value, NULL, 'PFILE', 'SPFILE') "Init File Type"
             FROM v_$parameter WHERE name = 'spfile';

Switch from SPFILE to PFILE:
     2)  Backup and delete SPFILE
     3)  Restart the instance

Switch from PFILE to SPFILE :
     2)  Restart the instance

Note :- PFILE will be in the same directory but system will start from SPFILE as it is default.

Changing the SPFILE parameter values:-

  ALTER SYSTEM SET timed_statistics = TRUE SCOPE = BOTH;

Note :- 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: Change value in SPFILE, the parameter will take effect with next database startup
- BOTH: affect the current instance and change the value in current SPFILE. This is the default behavior if an SPFILE was used at STARTUP.

Creating SPFILE to PFILE and vice-versa:-

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

Oracle 11g provides a new feature to create PFILE or SPFILE using the current values in the Oracle Memory (active Instance).

This will be useful if you delete the spfile or pfile by mistake.

SQL>create pfile from memory
File created.

pfile will create in the $ORACLE_HOME/dbs directory. You can specify the location while creating the pfile.

SQL> create pfile='/home/oracle/scott/initprod.ora' from memory;

File created.


Similarly, we can create SPFILE from memory using the below mentioned command.

You need to have SYSDBA or SYSOPER role to execute above statements.

1 comment:

  1. Thanks for the given information you are providing content very usefull to users.
    Learn How to Work With Ref Cursor in ORACLE 12C For Beginner Actual cost of this course is 9,999/- But now Kuncham Software Solutions Pvt ltd is giving you Free access. Limited period offer Hurry-up Enroll Now at Udemy.

    Hurry-Up Enroll Now and get Free Access to the Entire Course


Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...