What is the difference between Pfile and Spfile?
What is the difference between Pfile and Spfile?
SPFILE is a binary file that contains the same information as the old PFILE. SPFILE permits dynamic changes without requiring you to restart that instance. If you choose to use the traditional text initialization parameter file (PFILE), you must specify the PFILE clause when issuing the STARTUP command.
What is Pfile Spfile?
A PFILE is a static, client-side text file that must be updated with a standard text editor like “notepad” or “vi”. ORA file. 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 SET” command.
How do I use Spfile instead of Pfile?
Create spfile from pfile
- Search for the spfile$ORACLE_SID. ora file in the default location,
- Look for the spfile. ora; and.
- Search for the pfile by name init$ORACLE_SID. ora.
How do I change from Pfile to Spfile?
A SPFILE is created from a PFILE using one of the following commands. SQL> CREATE SPFILE FROM PFILE = ‘$ORACLE_HOME/dbs/my_pfile. ora’; SQL> CREATE SPFILE = ‘$ORACLE_HOME/dbs/my_spfile. ora’ FROM PFILE = ‘$ORACLE_HOME/dbs/my_pfile.
How do I create a pfile using Spfile?
How to Create Pfile from Spfile
- As sysdba execute following command.
- SQL> create pfile=’/tmp/initOrcl.ora’ from spfile;
- You can also create the pfile directly from memory.
How to check if pfile or spfile is used?
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.
What are the parameters of a pfile file?
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 : spfile was introduced starting from oracle 9i, untill that time text based pfile was used to store database initialization parameters. spfile can not be edited using a text editor.
Can a pfile be stored in a different location?
By doing so, we are able to startup using a PFILE in any location we want, but continue to use an SPFILE that can also be in a different location. This can be very beneficial for those that wish to store their SPFILE in a centralized location, such as a SAN.
Where do I Find my pfile in Oracle?
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.
