PFILE and SPFILE

How to Change SPFILE Location

Change SPFILE Location

Generally, we don’t change SPFILE location, because it should be found in the default location, $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora any time to startup the instance without any explicit indication.

SQL> startup

Which means, the database will find appropriate SPFILE or PFILE in the default location to startup the instance.

Of course, anything is not impossible if you are asked you to do so. One of my client plans to manage all SPFILE in one location, and consulted me by this matter. Although it sounds unreasonable, I introduce the approach in this post to make it real.

Here are the steps to change SPFILE location:

  1. Backup Original SPFILE
  2. Create SPFILE in New Location
  3. Point to New SPFILE in PFILE

1. Backup Original SPFILE

In case you damage SPFILE during the operation, you should keep a backup of the original SPFILE.

[oracle@test ~]$ cd $ORACLE_HOME/dbs
[oracle@test dbs]$ ll
total 24
-rw-rw----. 1 oracle oinstall 1544 Feb 18 14:20 hc_ora12c1.dat
-rw-r--r--. 1 oracle oinstall 2992 Feb  3  2012 init.ora
-rw-r-----. 1 oracle oinstall   24 Apr 18  2017 lkORA12C
-rw-r-----. 1 oracle oinstall 7680 Apr 18  2017 orapwora12c1
-rw-r-----. 1 oracle oinstall 3584 Feb 18 14:21 spfileora12c1.ora
[oracle@test dbs]$ cp -p ./spfile$ORACLE_SID.ora ./spfile$ORACLE_SID.ora.bak

2. Create SPFILE in New Location

You can always create a new SPFILE to the new location from the current one, no matter whether the instance is running or not.

SQL> create pfile from spfile;

File created.

SQL> create spfile='/spfiles/ORA12C1/spfile@.ora' from pfile;

File created.

Alternatively, if your instance is running, you can also create SPFILE directly from memory. The feature is only for 11g or later.

SQL> create spfile='/spfiles/ORA12C1/spfile@.ora' from memory;

File created.

Or you just simply move the physical file to the new location because the original SPFILE is no longer usable in following steps.

[oracle@test dbs]$ mv ./spfile$ORACLE_SID.ora /spfiles/ORA12C1/

3. Point to New SPFILE

Two tricks to point to the new SPFILE. The first method, we point the new location of SPFILE in PFILE.

[oracle@test dbs]$ vi init$ORACLE_SID.ora
spfile='/spfiles/ORA12C1/spfile@.ora'
[oracle@test dbs]$ sqlplus / as sysdba
...
SQL> startup
...
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /spfiles/ORA12C1/spfileora12c1
                                                 .ora

Since the original SPFILE is missing, the database will turn to PFILE to startup the database. Once PFILE is used, it will redirect the database to the new SPFILE. This approach is based on the order of locating the initialization parameter file by examining file names in a specific order. By the way, if your SPFILE is corrupted or missing for some reasons, there’re still several ways to restore SPFILE.

The second way to play the trick is to make a symbolic link to the new SPFILE.

[oracle@test dbs]$ ln -s /spfiles/ORA12C1/spfile$ORACLE_SID.ora spfile$ORACLE_SID.ora
[oracle@test dbs]$ ll spfile$ORACLE_SID.ora
lrwxrwxrwx. 1 oracle oinstall 34 Feb 20 13:53 spfileora12c1.ora -> /spfiles/ORA12C1/spfileora12c1.ora
[oracle@test dbs]$ sqlplus / as sysdba
...
SQL> startup
...
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 .2/db_1/dbs/spfileora12c1.ora

As we can see, both locations of SPFILE are different.

That’s how we change SFPILE location. If you found no effects on changing new SPFILE, you should know how to make SPFILE back to work.

Further reading: How to Change Control File Location

Leave a Reply

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