Today we are going to have look on the topic of “PFile and SPFile”. This is the most important element of the Oracle database. Being Oracle DBA we must aware about “PFile and SPFile”. This file plays an important role in Oracle database.
Let’s have look at the technical definition of “PFile and SPFile”. It will be useful for all who are beginners to Oracle Database journey.
What is PFILE?
PFILE stands for the parameter file. It is a text file which can be modified by a text editor. With the help, PFILE Oracle server start an Oracle Instance.
What is SPFILE?
SPFILE stands for the server parameter file. It is a binary file which can not be modified by a text editor. It is created from PFILE.
We use these parameters for the view of all information about “PFile and SPFile”
The “SHOW PARAMETERS” command from SQL*Plus (i.e.: SHOW PARAMETERS timed_statistics)
- V$PARAMETER view – it displays the currently in effect parameter values
- V$PARAMETER2 view – it displays the currently in effect parameter values, but “List Values” are shown in multiple rows
- V$SPPARAMETER view -it displays the current contents of the server parameter file.
Now we are going to have to look How the Oracle Instance is initialized?
Whenever the Oracle instance starts, first it looks to the $ORACLE_HOME/dbs (UNIX, Linux) or $ORACLE_HOME/database (Windows) directory for the following files (in this order):
- spfileSID.ora (SPFILE)
- Default SPFILE (SPFILE)
- initSID.ora (PFILE)
- Default PFILE (PFILE)
SPFILE have its own advantages which are mention below:-
- 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: Parameters are checked before changes are accepted
- An SPFILE can be backed-up with RMAN (RMAN cannot backup PFILEs)
How could we switch from SPFILE to PFILE and vice-versa?
- Switch from SPFILE to PFILE:
- CREATE PFILE FROM SPFILE;
- Backup and delete SPFILE
- Restart the instance
- Switch from PFILE to SPFILE:
- CREATE SPFILE FROM PFILE=’Location of the PFILE’;
- Restart the instance (the PFILE will be in the same directory but will not be used. SPFILE will be used instead)
Converting SPFILE to PFILE and vice-versa
This could be done in order to have a backup in the other format or to change the initialization file for the database instance.
Thank you all readers for giving there valuable time for explore new things.
Stay tuned for More articles on Oracle RAC
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Telegram Channel : https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
