How to create spfile from pfile

Last updated on May 16th, 2019 at 04:13 am

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

What is pfile: Pfile is a Traditional plain-text parameter files and it do 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 binary Server parameter files 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 Oracle database.Also it can be backed up using RMAN

Now lets see how to create spfile from pfile.

The general command is given below

So syntax is

create spfile from pfile;

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

Various option to create spfile from pfile

1.Creating spfile is different location then the default location

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

2.Creating spfile is different location then 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';
  1. Creating spfile from memory parameters
create spfile='/tmp/spfile.ora' from memory;
  1. Creating spfile in Oracle ASM
create spfile='+DATA/TEST/spfileTEST.ora' from pfile;

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 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, oracle database will take spfile while starting the database using command

startup

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

startup spfile='/tmp/spfile.ora'

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

Related links

alter system switch logfile v/s archive log current

Oracle Shutdown steps decoded

How to easily find the Oracle database startup and shutdown time using sqlplus

How to use Oracle SQL*Plus

Leave a Reply