How to create spfile from pfile

In this post, we would check out various ways to create spfile from pfile in the Oracle database. First of all, let’s check out the description of both spfile and pfile

Table of Contents

What is pfile

Pfile is a Traditional plain-text parameter file and it does not let you make persistent changes to parameter values. So any alter system set parameter command will not change any variable in the pfile

What is spfile

It is a binary Server parameter file and it let you make persistent changes to individual parameters. When you use a server parameter file, you can specify in an ALTER SYSTEM SET parameter statement that the new parameter value should be persistent

So in short, spfile is more convenient for managing the parameters in the Oracle database. Also, it can be backed up using RMAN

Now let’s see how to create a spfile from pfile.

The general command is given below

So syntax is

create spfile from pfile;

The above command will create a spfile in default location $ORACLE_HOME/dbs. The name will be spfile<ORACLE_SID>.ora

Various options to create spfile from pfile

Creating spfile is a different location than the default location

create spfile ='/tmp/spfile.ora' from pfile;

2. Creating spfile in a different location than the default location from another location of pfile

create spfile ='/tmp/spfile.ora' from pfile='/tmp/pfile.ora';

3. Creating spfile from another location of pfile

create spfile from pfile='/tmp/pfile.ora';

4. Creating spfile from memory parameters

create spfile='/tmp/spfile.ora' from memory;

5. Creating spfile in Oracle ASM

create spfile='+DATA/TEST/spfileTEST.ora' from pfile;

Various options to create pfile from spfile

Creating pfile is a different location than the default location

create pfile ='/tmp/pfile.ora' from spfile;

2. Creating pfile in a different location than the default location from another location of spfile

create pfile ='/tmp/pfile.ora' from spfile='/tmp/spfile.ora';

3. Creating pfile from another location of pfile

create pfile from spfile='/tmp/spfile.ora';

4. Creating pfile from OS spfile

$ cd $ORACLE_HOME/dbs
$ strings spfileTESTD.ora > pfileTEST.ora

How to find if spfile is used by the Oracle database

sqlplus "/ as sysdba"

SELECT name, value FROM v$parameter WHERE name = 'spfile';

or

show parameter spfile

How to change the parameter in spfile

alter system set <parameter> ='value'  scope=spfile;

How to start the Oracle database with spfile

If the spfile exists at the default location, the oracle database will take the spfile while starting the database using the command

startup

If the spfile does not exist at the default location, we can use the below command to start the database using that spfile

startup spfile='/tmp/spfile.ora'

How to create Pfile from Spfile while Instance is down

We do this even when the database is down

sqlplus / as ssysdba
create pfile from spfile;

Hope you like the content for create spfile from pfile in the oracle database. Please do share and provide feedback