Pfile and Spfile In Oracle

Parameter File Management

There are two type of parameter file:- pfile and spfile

Pfile à pfile is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi"

SPfile àAn SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM" command.

Note : Default location for both parameter files is $ORACLE_HOME/dbs

To change the parameter value in pfile:-

Dynamic parameter (sga_target)

Sql> alter system set sga_target=3g;

The above parameter value gets changed only in memory, to permanently change do the following:

1.    open the parameter file.

2.    then specify the value in parameter file

Exp.

$cd $ORACLE_HOME/dbs

$vi inittest.ora

Sga_target=3g

:wq

·       The static parameter (sga_max_size) can be changed only in parameter file

And to make the value effective, the instance be bounced back

How to change parameter value  in spfile

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

Memory :- value is Set for the current instance only.

Spfile :- update the SPFILE, the parameter will take effect with next database startup

Both :- value is affect the current instance and persist to the SPFILE.

Dynamic parameter:

SQL>Alter system set sga_target=300m;

SQL>Alter system set sga_target=500m scope=memory;

SQL>Alter system set sga_target=500m scope=both;

Static parameter

SQL>Alter system set sga_max_size=1g scope=spfile

SQL>Alter system set sga_max_size=500m comment=’max size is 1GB’ scope=spfile;

(NOTE: sga_max_size,sga_target,pga_aggregate_target

Instead of this parameter we can use memory_target parameter. This parameter divides ur sga and pga into 60 and 40%) 

Converting between PFILES and SPFILES:

Execute the following commands from a user with SYSDBA or SYSOPER privileges:

SQL> CREATE PFILE FROM SPFILE;

SQL> CREATE SPFILE FROM PFILE;

Views for Parameter File Mgmt:

  -v$parameter,   v$parameter2, v$spparameter