If we were trying to change parameters in an unfamiliar database that was handed over from other DBA, we have to figure it out whether the database started with SPFILE or PFILE. Moreover, if PFILE is used, you have to know the location of PFILE.
Of course, we can ask the former DBA for the answer, but if we can’t get the correct response, we have to find it out by ourselves.
Find Location of PFILE
The PFILE is usually $ORACLE_HOME/dbs/init$ORACLE_SID.ora by default. But we can’t be sure about it, because the former DBA may specify the PFILE location other than the default one to startup the database. Such as
SQL> startup pfile='/cluster/admin/init$ORACLE_SID.ora';
This is not a conventional path. So we have no idea to guess it right if we were not there. How can we know the location of PFILE in the last startup?
It seems to be a dead end. But wait … Not really! We can check the alert log or trace log to search for the very last startup record. The alert or trace log is usually at background_dump_dest (before 11g) or diagnostic_dest (for 11g onward). This is the most accurate way to check the location of PFILE.
If your database is running but no SPFILE is found, you can create the file from the run-time configuration of current instance.
SQL> create spfile from memory;
Or create from a known and static PFILE.
SQL> create spfile from pfile='/path/to/pfile';
The newly created SPFILE will be at ORACLE_HOME/dbs for Linux/Unix and ORACLE_HOME/database for windows platform.
In RAC environments, it would be more complex to debug the location problem. Please see more information in the following post:
How to Resolve SPFILE Does Not Work