Parameter file is one of important component in Oracle Database. This file contains all parameter information that is used in Oracle. There are 2 type of parameter file in Oracle: PFILE and SPFILE.
PFILE is stored in text file format under $ORACLE_HOME/dbs/init$ORACLE_SID.ora. SPFILE is binary file under $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora. We can edit PFILE by using text editor tools but not PFILE.
When starting up database, Oracle will look for parameter file in below sequence:
– If SPFILE is found in $ORACLE_HOME/dbs, database will be started using SPFILE
– If SPFILE is not found, but PFILE is found in $ORACLE_HOME/dbs, database will be started using PFILE
– If both files are not found, database can’t be started
To check if database is started using PFILE or SPFILE, we can check from the SPFILE parameter. If this parameter value is null, it means that database was started using PFILE.
We can update the parameter value from oracle client by using ALTER SYSTEM. There are 3 different scope of parameter change:
MEMORY: The parameter change will only take effect for the life of the instance. Once we restart the database, the change will be reverted
SPFILE: The parameter change will only be made in the SPFILE and take effect only after next instance restart.
BOTH: The parameter change will take place in both MEMORY and SPFILE. The change take effect from the time it changed and even after next instance restart.
When doing alter system scope=both or scope=spfile, we may get ORA-32001: write to SPFILE requested but no SPFILE specified at startup error if we don’t use SPFILE in the current instance.
We can create PFILE from SPFILE and vice versa with below command
SQL> create PFILE from SPFILE;
SQL> create SPFILE from PFILE;
Above command will create a PFILE or SPFILE at $ORACLE_HOME/dbs. If we want them to be created in a custom location and name, we can use below command
SQL> create PFILE='/tmp/pfileDB.ora' from SPFILE;
SQL> create SPFILE='/tmp/spfileDB.ora' from PFILE;