Skip to content

Where is Oracle PFILE Location

  • by

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 default location of parameter files.

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 in Linux 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 about what's going on. How can we know the location of PFILE in the last startup?

If you are thinking about showing parameter to check the information, you will be disappointed.

SQL> show parameter pfile;

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

Nothing here.

Solution

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. This is the most accurate way to check the location of PFILE. If you don't know where to find your alert log, you may check this post: Where is the alert log.

How to Create SPFILE

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:
Why SPFILE Does Not Work in RAC

Leave a Reply

Your email address will not be published.