Initialization Parameter File (Oracle Database) PFILE, SPFILE

Initialization Parameter File :

In order to start an instance and open the database, you must connect as SYSDBA and enter the STARTUP command. The Oracle Server will then read the Initialization Parameter file  and prepare the instance according to the Initialization Parameter contained within the file.

Some Specific Important Facts about Initialization Parameter file :

• Entries are specific to the Instance being Started
• Two types of Parameters :
• Explicit : Having an entry in the Initialization parameter file
• Implicit : NO Entry within the file, But Assuming the Oracle Default Value.
• Multiple Initialization parameter file can exists
• Changes to entries in the file take effect based on the type of initialization parameter file used :
• Static Parameter File, PFILE
• Persistent Server Parameter File, SPFILE

To Start an Instance the Oracle server reads the Initialization Parameter File. Two Types of Initialization Parameter files exists :

• Staitic Parameter File, PFILE, Commonly referred to as initSID.ora.
• Persistent Server Parameter File , SPFILE , commonly Referred to as spfileSID.ora

Initialization Parameter contents :

• A list of Instance Parameters
• The name of the Database the instance is associated with
• Allocation for memory structures of the System Global Area (SGA)
• What to do with Filled online redo Log Files.
• The names and  location of control files
•  Information About UNDO segments.

PFILE :  initSID.ora

The PFILE is a text file that can be Maintained using a standard operating system editor. The PFILE is read only during instance startup. If the file is modified , the instance must be shut down and restarted in order to make the new parameter values effective.

Characteristics of PFILE :

• Text File
  
• Modified With an operating system editor
• Modification made manually
• Changes take Effect on the next startup
• Only opened during Instance Start UP
• Default Location is $ORACLE_HOME/dbs

By default, The PFILE located in the $ORACLE_HOME/dbs directory and name initSID.ora.

Creating a PFILE :

How to Create PFILE ?

• PFILE created from a sample init.ora file
• Sample Installed by the Oracle Universal Installer
• Copy Sample using operating system copy command
• Uniquely Identified by Database SID

           cp init.ora $ORACLE_HOME/dbs/initdba01.ora

• Modify the initSID.ora
• Edit the Parameters
• Specific to Database needs

A sample init.ora file is created by the Universal Installer during installation. This Sample init.ora file can be used to create an instance specific initSID.ora. A text editor can be used to modify the parameter within the initSID.ora file.

PFILE Example : Here we will review the example of init.ora file that how it looks like and what parameter are added by default. Below is the my Test system default init.ora file.

cat init.ora

# Example INIT.ORA file
.
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

Now Let's see if Need to add a parameter manually in PFILE  then We need to review below set of rules that we have to fallow it.

• Specify the values in the fallowing format like Keyword = value .
• The server has a default value for each parameter. This Value may be operating system Dependent, depending on the parameter.
• Parameter can be specified in any order, although there are some exceptions
  
• Comment line begin with the # Symbol.
• Enclose parameters in double quotation marks to include character literals.
• Additional file can be included with the keyword IFILE.
• If case is the significant for the operating system , then it is also significant in filenames.
• Multiple values are enclosed in parentheses and separated by commas.

Note : The Best Practice is ,For Addition of Parameter in PFILE , First List the Parameter alphabetically or Group them by functionality and add it in PFILE.  The PFILE varies from Instance to Instance and does not necessarily look like the example given above.

SPFILE : spfileSID.ora :

An SPFILE  (" Introduce in Oracle 9i ") is a binary File. This file is not meant to be modified manually and must always reside on the server side. After the file is created it is maintained by the oracle server. If Modified manually, This makes SPFILE useless.  

The SPFILE provides the ability to make changes to database persistent across Shutdown and Startup. It also provide the ability to self-tune parameter values, which are recorded in the file.

RMAN Support for backing UP the Initialization Parameter file is possible because the SPFILE resides on server side. By Default,  the files is located in  " $ORACLE_HOME/dbs "  and has a default name in the format of spfileSID.ora.

Characteristics of SPFILE :

• Binary File
• Maintained by the Oracle Server
• Always resides on the server side
• Ability to make changes across shut down and start up
• Can Self-Tune parameter value
• Can have Recovery manager Support backing Up to the initialization parameter file.

Creating an SPFILE :

• Created from a PFILE file.
  

CREATE SPFILE = '$ORACLE_HOME/dbs/spfileORCL.ora'  FROM PFILE = '$ORACLE_HOME/dbs/initORCL.ora ';

Where

- SPFILE-NAME : SPFILE to be created

- PFILE- NAME :  PFILE creating the SPFILE

• Can be executed before OR after instance start up.

An SPFILE is created from PFILE using  " CREATE SPFILE " Command. This Command requires the SYSDBA previlege to execute. This command can be executed before OR after instance start UP.

Syntax : 

CREATE SPFILE = ' SPFILE - NAME ' FROM PFILE = 'PFILE-NAME';

Where

- SPFILE-NAME : Name of the SPFILE to be created

- PFILE-NAME :Name of the PFILE being used  to create the SPFILE. The PFILE Must be available on the server side

If the SPFILE-NAME  and PFILE-NAME are not included in the syntax, Oracle will use the default PFILE to generate an SPFILE with a system generated name.

SQL>  CREATE SPFILE FROM PFILE;

The contents of an SPFILE can be exported into a PFILE.

SQL> CREATE PFILE FROM SPFILE;

The PFILE is created as a text file on the server side. This command can be exeuted either before or after instance start up. This Provides an easy way to view the SPFILE and make modifications by :

• Exporting the SPFILE to a PFILE
• Editing the PFILE
• Re-creating the SPFILE form edited PFILE

Exporting an SPFILE to a PFILE can also serve as another alternative to creating a backup of the server parameter file.

Note : With Oracle9i Onwards Relase of oracle , RMAN can also back up server parameter file.

v$SPPARAMETER  :

By Using this VIEW, There are several options for Viewing the parameter settings within the SPFILE.  V$SPPARAMETER is another source for presenting and viewing Contents of the SPFILE.

Modifying Parameters In SPFILE :

• Changing Parameter values

ALTER SYSTEM SET undo_tablespace = UNDO2;

• Specifying temporary or persistent cahnges

ALTER SYSTEM SET undo_tablespace = UNDO2  SCOPE=BOTH

• Deleting or Resetting values

ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='*';

The ALTER SYSTEM SET Command used to change the value of instance Parameters.

ALTER SYSTEM SET  parameter_name = parameter_value [Comment 'text'] [SCOPE = MEMORY | SPFILE | BOTH] [SID = 'sid'|'*']

Where ,

Parameter_name : Name of the Parameter to be changed

Parameter_value : Value the parameter is being changed to

COMMENT : A comment to be added into the SPFILE next to the parameter being altered.

SCOPE : Determines if change should be made in memory, SPFILE , OR in both areas

MEMORY : Changes the parameter value only in currently running instance

SPFILE : Cahnges the parameter value in SPFILE only

BOTH : Changes the parameter value in the currently running Instance and the SPFILE

SID: Identifies the ORACLE_SID for the SPFILE being used

'sid' : Specific SID to be used in altering the SPFILE

'*' : Uses the Default SPFILE

The ALTER SYSTEM RESET Command is used to delete or Revert to the default value.

ALTER SYSTEM REST parameter_value [SCOPE = MEMORY | SPFILE | BOTH] [SID = 'sid' | '*']

Example :

SQL> ALTER SYSTEM RESET undo_suppress_errors SCOPE = BOTH  SID = 'ORCL';

There are several ways to remove a parameter from SPFILE :

• Set the parameter back to its default value to simulate deleting using ALTER SYSTEM SET .
  
• Re-Create the SPFILE using  CREATE SPFILE FROM PFILE.
  
• Use ALTER SYSTEM RESET to delete the parameter from the SPFILE.
  

Thank You !!