How to know My Database Start with Spfile/Pfile?

There is a parameter spfile which indicate the startup parameter of the database. If database is started with pfile then the parameter spfile contains null value. If database is stated with spfile then the parameter value contains the location of the spfile.

Like, if it is started with spfile then the output will like,

SQL> select decode(value,'','Your Database started with pfile','Your Database started with spfile') from v$parameter where name='spfile';

DECODE(VALUE,'','YOURDATABASESTAR
---------------------------------
Your Database started with spfile

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/spfilearju.ora

If your database is started with pfile, it will show like,
SQL> select decode(value,'','Your Database started with pfile','Your Database started with spfile') from v$parameter where name='spfile';

DECODE(VALUE,'','YOURDATABASESTAR
---------------------------------
Your Database started with pfile

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string


One think you need to remember that there is a parameter named spfile exist in database but no parameter pfile exist in database.

Related Documents:
----------------------------
How Spfile can be Recovered