How do you switch and create spfile from pfile in 11g,12c,19c

How do you switch and create spfile from pfile| In this article we will discuss ‘how to create pfile from spfile’. Spfile is a binary file.  By default, a new Oracle database will be working on a pfile,  so the spfile must be created from the pfile using SQL command.



How do you switch and create spfile from pfile?


Check spfile exist or not.

SQL> show parameter spfile;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string     
It means no spfile created in your database. Hence, we can create the spfile from pfile.

SQL> show parameter pfile;
NAME                                 TYPE        VALUE
———————————— ———– ——————————
spfile                               string      /data05/

Create spfile on default location ($ORACLE_HOME/dbs)

SQL> create spfile from pfile;
File created

Create spfile on non default location.

SQL>Crete spfile ‘/data02/prod/spfilePROD.ora’ from pfile;

Create spfile from non default pfile location.

SQL>Create spfile from pfile ‘‘/data02/prod/initPROD.ora’;
Now we can switch the database from pfile to spfile;
SQL> shutdown immediate
SQL> startup spfile; 


SQL> startup; (by default database will start using spfile , if spfile exist on default location)
In case of non default location.
SQL> startup spfile=‘/data02/prod/spfilePROD.ora’;
  • You may check another similar post, click here.

Leave a Comment