Where is Oracle SPFILE Location

  • by

Here is a quick glance at the default locations of SPFILE and PFILE in different platforms. For instance, Linux and Windows.

Linux/Unix

SPFILE

<ORACLE_HOME>/dbs/spfile<ORACLE_SID>.ora

PFILE

<ORACLE_HOME>/dbs/init<ORACLE_SID>.ora

Windows

Please note that, for Windows, directory name and filename are case-insensitive.

SPFILE

<ORACLE_HOME>\database\spfile<ORACLE_SID>.ora

PFILE

<ORACLE_BASE>\admin\<ORACLE_SID>\pfile\init.ora.<n>

There's no typos in the above path. The default location of PFILE on Windows truly depends on ORACLE_BASE. As you can see, they're somewhat different on path between SPFILE and PFILE on Windows.

Create Parameter Files

CREATE PFILE FROM SPFILE

If you create the parameter file without specifying any location, the online SPFILE used by a running instance or the default SPFILE will be used to create PFILE to its default locations.

SQL> CREATE PFILE FROM SPFILE;

File created.

Please note that, if SPFILE is not at the default location, you might get ORA-01565: error in identifying file '?=/dbs/spfile@.ora'

Or vice versa.

CREATE SPFILE FROM PFILE

SQL> CREATE SPFILE FROM PFILE;

File created.

Oracle use the default files to operate with.

ASM

Normally, we don't directly use PFILE to startup a RAC database, because PFILE is just a pointer to the location of SPFILE for a RAC node. Here we focus only on SPFILE below.

The location of SPFILE may be different from version to version.

OMF

+<DISK_GROUP>/<DB_UNIQUE_NAME>/PARAMETERFILE/spfile.<m>.<n>

Non-OMF

+<DISK_GROUP>/<DB_UNIQUE_NAME>/spfile<DB_UNIQUE_NAME>.ora;

Where is SPFILE

Show Parameter Spfile

If you want to know the current SPFILE location, you can query the instance.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/spfileORCLCDB.or
                                                 a

Find PFILE

In some situations, DBA use a PFILE with non-conventional path to startup the database. You need more skills to find the location of PFILE.

Change Location of SPFILE

Normally, parameter files should stay where they are, but the location of SPFILE can be changed somehow, as long as you know how to do it.

Leave a Reply

Your email address will not be published. Required fields are marked *