How do you find wheather the instance was started with pfile or spfile

Thhere are 3 different ways :-

1) SELECT name, value FROM v$parameter WHERE name = 'spfile'; //This query will return NULL if you are using PFILE

2) SHOW PARAMETER spfile // This query will returns NULL in the value column if you are using pfile and not spfile

3) SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; // if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
By Default oracle will look into the default location depends on the o/s. Like in unix, oracle will check in $oracle_home/dbs directory and on windows it will check in oracle_home/database directory, and the content of pfile is just text based, but spfile content is in binary format, that is understandable by oracle very well.

Also oracle server always check the spfile or pfile with these sequence :-

SPFILE.ORA
SPFILE.ORA
PFILE.ORA
PFILE.ORA
  • Interview Candidate
  • 3
  • 2634

Showing Answers 1 - 3 of 3 Answers

LazyDog

  • Apr 25th, 2006
 

Go to OEM => Databases => ... => Instance => Configuration, in "Database and Instance Information" section, the last row shows spfile

  Was this answer useful?  Yes

cmanne

  • Sep 2nd, 2009
 

show parameter spfile;

 

if the output will be similar to the following, then it means that the instance is using SPFILE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /usr/local/oracle/*****.ora


  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions