Parameter file (PFILE/SPFILE)

After starting the instance we cannot change the value of the parameter using pfile. If we want to change we need to restart the database again.

If we want to change the parameter without rebooting the database. We have to create a server parameter file called spfile

SPFILE:

A SPFILE, Server Parameter File, is a server managed binary file that Oracle uses to hold persistent initialization parameters. If a parameter is to be changed have to use ALTER SYSTEM SET command. Oracle will apply this parameter change to the current value in SPFILE. Oracle by default starts DB with SPFILE if not found starts with PFILE. 

Spfile will be created automatically when database created with DBCA tool. 

From pfile we can create spfile.

SPFILE’s provide the following advantages over PFILE’s

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

·      Reduce human errors. The SPFILE is maintained by the server. Parameters are checked before changes are accepted.

·      Easy to find - stored in a central location.

This spfile provides 3 options to update the database instance.

           a) Scope=memory

           b) Scope=spfile

           c) Scope=both

a) Scope=memory:-

This option provides to allow the change in the system global area. Till the instance is available. These changes will be removed once instance is restarted.

b) Scope=spfile :-

This parameter is set then these changes will effect after restarting the database instance.Oracle writes these changes in to the spfile which is available in the harddisk.

c) Scope=both :-

This option provides to allow the changes in the current instance and even after restart the database instance.

Dynamically Changeable Parameters

You can modify certain parameters dynamically using the command:

ALTER SYSTEM SET parameter = value;

You can also set the parameters for a user session using the command:

ALTER SESSION SET parameter = value;

NOTE: Many of them can be modified dynamically, and can take on new settings. These new settings do not reset when the database is restarted.

You can refer to the V$PARAMETER table to determine if a parameter is changeable at either the session or database wide level. The columns of primary interest are isses_modifiable and issys_modifiable.

SQL> select ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE from  V$PARAMETER where name='shared_pool_size';

ISSES ISSYS_MOD ISINS

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

FALSE IMMEDIATE TRUE

The isses_modifiable column indicates if a user who has the ALTER SESSION privilege can change the init.ora parameter for his or her session. If this column is TRUE, then the user can do so; otherwise, the column is FALSE.

The issys_modifiable column indicates if someone with ALTER SYSTEM privileges can change a parameter. It has three statuses:

IMMEDIATE which indicates that the parameter is changeable and that the change takes effect immediately

FALSE which means that the parameter is not changeable dynamically

DEFERRED which indicates that the parameter is changeable but that it will not take effect until your next session

Demos on pfile & spfile:

$sqlplus / as sysdba

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE

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

shared_pool_size                     big integer 95 M

$vi $ORACLE_HOME/dbs/initorcl.ora

Shared_pool_size=95M

Shared_pool_size=105M

:wq

$exit

Shutdown and restart

sys>startup 

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE

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

shared_pool_size                     big integer 105 M

How to create spfile from pfile:-

$sqlplus / as sysdba

SQL> create spfile from pfile;

File created.

cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ ls

hc_orcl.dat  init.ora  initorcl.ora  lkORCL  spfileorcl.ora

Note: spfile provide more security & more performance to the DB instance with compare to pfile.

Restart database so that database will start with spfile.

If we have both pfile and spfile, DB will be started with spfile.

Restart database now.

SQL>SHOW PARAMETER SPFILE

Above command will show spfile name and path.

Now try changing parameters with spfile.

How to set shared_pool_size with scope=memory :

SQL> alter system set shared_pool_size=120m scope=memory;

System altered.

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE

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

shared_pool_size                     big integer 120M

sys>startup

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE

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

shared_pool_size                     big integer 105 M

How to set shared_pool_size with scope=spfile :

SQL> alter system set shared_pool_size=140m scope=spfile;

System altered.

SQL> startup

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE

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

shared_pool_size                     big integer 140M

How to set shared_pool_size with scope=both :

sys >alter system set shared_pool_size =160m scope=both;

sys >show parameter shared_pool_size;

sys >startup

sys>show parameter shared_pool_size

How to create pfile from spfile:

$cd  $ORACLE_HOME/dbs

rm –rf initorcl.oa

sys>create pfile from spfile;

$cd  $ORACLE_HOME/dbs

$initorcl.ora

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

CREATE PFILE FROM SPFILE = '/u01/spfileORCL.ora’;

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

CREATE PFILE = '$ORACLE_HOME/dbs/my_pfile.ora' FROM SPFILE = '$ORACLE_HOME/dbs/my_spfile.ora';

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