What is a parameter file
Parameter file is a text or binary to store the database initialization parameters. The oracle instance reads the parameter file during startup which are then used to control the behavior of database instance and many other aspects as well. Such as : memory allocation (SGA and PGA), startup of optional background processes, Setting of NLS parameters etc. There are 2 types of parameter files, namely :
1. pfile (parameter file) – older way [ not recommended by oracle ]
2. spfile (server parameter file) – newer way [ recommended by oracle ]
spfile was introduced starting from oracle 9i, untill that time text based pfile was used to store database initialization parameters.
pfile V/s spfile
| pfile | spfile |
|---|---|
| Text file | Binary file |
| Parameters in pfile can be edited using any text editor | spfile can not be edited using a text editor. Instead it can only be altered using the “”ALTER SYSTEM”” command |
| Default location of pfile – $ORACLE_HOME/dbs/init[SID].ora where [SID] – is the name of the instance. | Default location of spfile – $ORACLE_HOME/dbs/spfile[SID].ora where [SID] – is the name of the instance. |
| The RMAN utility can not take backup of a pfile | The RMAN utility can take backup of a spfile. |
How to check if SPFILE or PFILE is used
Run the below command against the database you want to check :
SQL> show parameter spfile
If the query returns no rows – pfile is used. If the query returns any value with a filename(with its path) then SPFILE is used by current running instance. For example:
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string ?/dbs/spfile@.ora
The above example shows that spfile used by the running instance.
How to set parameters when using spfile
The basic syntax for altering the parameters using spfile is :
SQL> alter system set [parameter=value] sid='[sid|*]' scope=[memory|spfile|both];
The SCOPE clause specifies the scope of a change for static and dynamic parameters as described below:
| SCOPE | Allowed parameter changes | Description |
|---|---|---|
| SPFILE | static and dynamic | changes are recorded in the spfile, to be given effect in the next restart |
| MEMORY | dynamic | changes are applied in memory only |
| BOTH | dynamic | changes are applied in both the server parameter file and memory |
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.