How to Change SPFILE Location

  • by

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 an appropriate SPFILE or PFILE in the default location to startup the instance.

Of course, anything is possible if you are asked to do so. One of my client plans to manage all SPFILE in one location, and consulted me on 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

There’re several ways that can create a new SPFILE.

Create SPFILE from PFILE

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.

Please note that, the @ sign in the statement stands for $ORACLE_SID. We usually use some symbols or signs as shorthands to represent some variables in Oracle.

Create SPFILE from MEMORY

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.

Please note that, if you’re in a cluster database (RAC), don’t use this method, because it will give a pfile with single-instance parameters.

Move SPFILE to New Location

Of course, you can also 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/

Since the only file we need is PFILE, please remove the original SPFILE if any.

3. Point to New SPFILE

Two tricks to point to the new SPFILE.

Breadcrumbs in PFILE

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 reason, there’re still several ways to restore SPFILE.

Use a Symbolic Link

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 SPFILE locations between two methods 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 *