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.

URL:- https://youtu.be/G5eE79poQzU

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/11.2.0.4/dbs/spfilePROD.ora

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; 

or

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’;