pfile and spfile in oracle database

Pfile and spfile both of them are a part of the oracle database through which the parameters which is required to run a database or to get the optimal database performance can be set. However, both of them has some differences.

 Ø  A pfile is a text file however a spfile is a binary file.

 Ø  If we use the pfile then each and every time we’ve to modify the pfile manually whenever we need to add or modify any parameter.

Ø Spfile is a binary file and we can change the parameter through command line.

Ø There are 2 types of parameters in pfile/spfile i.e. static parameter and dynamic parameter. Dynamic parameter doesn’t require to restart the database however static parameter require database restart.

Ø Spfile has more priority than pfile. It’s always recommended to use spfile for the database.

             How to check if the parameter is Dynamic/Static

SQL> set lines 300

SQL> col ISSES_MODIFIABLE for a30

SQL> col ISSYS_MODIFIABLE for a30

SQL> col ISPDB_MODIFIABLE for a30

SQL> col ISINSTANCE_MODIFIABLE for a30

SQL> col name for a30

SQL> col value for a40

SQL> select name,value,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISPDB_MODIFIABLE,ISINSTANCE_MODIFIABLE from v$parameter where name in('processes','job_queue_processes');

 NAME                       VALUE                            ISSES_MODIFIABLE           ISSYS_MODIFIABLE          ISPDB_MODIFIABLE               ISINSTANCE_MODIFIABLE

------------------------------ ---------------------------------------- -----------

processes                  320                              FALSE                      FALSE                    FALSE                    FALSE

job_queue_processes        80                               FALSE                      IMMEDIATE                TRUE                     TRUE

 

NOTE: if ISINSTANCE_MODIFIABLE value is showing as TRUE then it means the value can be change dynamically otherwise the parameter is static parameter and it require database bounce.

SQL> alter system set job_queue_processes=100 scope=both sid='*';

System altered.

SQL> show parameter job_queue_processes

NAME                                            TYPE      VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer   100

SQL> alter system set processes=350 scope=both sid='*';

alter system set processes=350 scope=both sid='*'

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

NOTE: Since the processes parameter is a STATIC parameter, so when we try to modify it dynamically it’s giving error. So we’ve to use scope=spfile and then restart the database to set the parameter with a new value.

SQL> alter system set processes=350 scope=spfile sid='*';

System altered.

[oracle@oratest1 ~]$ srvctl stop database -d ORA_PRIM

[oracle@oratest1 ~]$ srvctl start database -d ORA_PRIM

[oracle@oratest1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 29 19:07:06 2024

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter processes;

NAME                                            TYPE      VALUE

------------------------------------ ----------- ------------------------------

processes                             integer   350