How to Change SPFILE Location in Oracle

  • by

In this post, I will talked about how to change SPFILE location in a single-instance database and in a RAC database.

  1. Change SPFILE Location of a Single-Instance Database
  2. Change SPFILE Location of a RAC Database

ALTER SYSTEM SET SPFILE

Before reading, you have to know that there's no ALTER SYSTEM SET SPFILE in Oracle, because you can never write the location of SPFILE into SPFILE. It makes no sense.

A. Change SPFILE Location of a Single-Instance Database

Generally, we don't change SPFILE location of a single-instance database, because it should be found in the default location, $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora any time for starting up 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 some approaches 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.
SQL> shutdown immediate;
...

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.

Restore SPFILE from Backup

We can also use Recovery Manager (RMAN) to have a copy of current SPFILE.

RMAN> backup spfile;
...
RMAN> restore spfile to '/spfiles/ORA12C1/spfile@.ora';
...

RMAN will pick the latest backup of SPFILE to restore, no matter the backup is from AUTOBACKUP or a manual backup.

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 for later steps.

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

It'd better to move it offline.

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

3. Point to New SPFILE

Can we just specify the location of SPFILE when we startup a database? No, we can't. There's no STARTUP SPFILE option. That's why we're going to talk about how to point to the new location of SPFILE in this section.

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 default SPFILE is missing, the database will turn to the default 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.

By the way, is it possible to see a blank or empty value returned by SHOW PARAMETER SPFILE?

B. Change SPFILE Location of a RAC Database

Usually, we put SPFILE in Automatic Storage Management (ASM), a shared storage for RAC databases to access. More specifically, we put it in one of disk groups based on ASM.

There're several ways to change SPFILE location of a RAC database.

  1. Symbolic Link
  2. Recovery Manager (RMAN)
  3. Parameter File (PFILE)

Using Symbolic Link to change SPFILE location does not move the actual file, we just do a trick to make it look like changed.

Let's see current SPFILE first.

[oracle@primary01 ~]$ srvctl config database -d orclcdb
...
Spfile: +DATA/ORCLCDB/PARAMETERFILE/spfile.275.1053776653
...
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is running on node primary01
Instance ORCLCDB2 is running on node primary02
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Check SPFILE location in the instance.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ORCLCDB/PARAMETERFILE/sp
                                                 file.275.1053776653

It's +DATA/ORCLCDB/PARAMETERFILE/spfile.275.1053776653, my goal is to switch SPFILE to +DATA/ORCLCDB/spfile.

Here we use mkalias, a command of ASMCMD to make a symbolic link to the original SPFILE file.

[grid@primary01 ~]$ asmcmd mkalias +DATA/ORCLCDB/PARAMETERFILE/spfile.275.1053776653 +DATA/ORCLCDB/spfile

Then check the new link.

[grid@primary01 ~]$ asmcmd ls -l +DATA/ORCLCDB/spfile
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   DEC 30 14:00:00  N    spfile => +DATA/ORCLCDB/PARAMETERFILE/spfile.275.1053776653

Next, we configure the new SPFILE into the database.

[oracle@primary01 ~]$ srvctl modify database -d orclcdb -spfile +DATA/ORCLCDB/spfile
[oracle@primary01 ~]$ srvctl config database -d orclcdb
...
Spfile: +DATA/ORCLCDB/spfile

Perform a restart to make it work.

[oracle@primary01 ~]$ srvctl stop database -d orclcdb
[oracle@primary01 ~]$ srvctl start database -d orclcdb

Check SPFILE location again in the instance.

SQL> conn / as sysdba
Connected.
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ORCLCDB/spfile

This is the same process as I talked in How to Revert OMF Spfile and Password File in RAC.

2. Use Recovery Manager (RMAN)

First of all, we created a new directory for the new home of SPFILE if necessary.

[grid@primary01 ~]$ asmcmd mkdir +DATA/TESTCDB

Check SPFILE location in configuration.

[oracle@primary01 ~]$ srvctl status database -d testcdb
Instance TESTCDB1 is running on node primary01
Instance TESTCDB2 is running on node primary02
[oracle@primary01 ~]$ srvctl config database -d testcdb
...
Spfile: +DATA/ORCLCDB/spfile

Check SPFILE location in the instance.

SQL> conn / as sysdba
Connected.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ORCLCDB/spfile

It's +DATA/ORCLCDB/spfile, my goal is to switch SPFILE to +DATA/TESTCDB/spfile.

Backup current SPFILE.

RMAN> backup spfile;
...

Then we restore it to the destination we want.

RMAN> restore spfile to '+DATA/TESTCDB/spfile';
...

Let's check the actual file in the disk group.

[grid@primary01 ~]$ asmcmd ls -l +DATA/TESTCDB/spfile
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   DEC 30 13:00:00  N    spfile => +DATA/TESTCDB/PARAMETERFILE/spfile.303.1060523781

Next, we configure the new SPFILE into the database.

[oracle@primary01 ~]$ srvctl modify database -d testcdb -spfile +DATA/TESTCDB/spfile
[oracle@primary01 ~]$ srvctl config database -d testcdb
...
Spfile: +DATA/TESTCDB/spfile

Perform a restart to make it work.

[oracle@primary01 ~]$ srvctl stop database -d testcdb
[oracle@primary01 ~]$ srvctl start database -d testcdb

Check SPFILE location again in the instance.

SQL> conn / as sysdba
Connected.
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/TESTCDB/spfile

As you can see, using RMAN to have a copy at a different directory is pretty easy.

3. Use Parameter File (PFILE)

Let's see current SPFILE first.

[oracle@primary01 ~]$ srvctl config database -d orclcdb
...
Spfile: +DATA/ORCLCDB/PARAMETERFILE/spfile.275.1053776653
...
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is running on node primary01
Instance ORCLCDB2 is running on node primary02
[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Create a PFILE for later use.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ORCLCDB/PARAMETERFILE/sp
                                                 file.275.1053776653
SQL> create pfile='/home/oracle/pfile' from spfile;

File created.

SQL> exit

Stop the database.

[oracle@primary01 ~]$ srvctl stop database -d orclcdb
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is not running on node primary01
Instance ORCLCDB2 is not running on node primary02

Then disable the database from the cluster.

[oracle@primary01 ~]$ srvctl disable database -d orclcdb

We startup the database to NOMOUNT with the PFILE.

[oracle@primary01 ~]$ sqlplus / as sysdba
...
SQL> startup nomount pfile='/home/oracle/pfile';
ORACLE instance started.
...

Create a new SPFILE for the database from PFILE, then we close the database.

SQL> create spfile='+DATA/ORCLCDB/spfile' from pfile='/home/oracle/pfile';

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit

Change SPFILE location by modifying the attribute in SRVCTL.

[oracle@primary01 ~]$ srvctl modify database -d orclcdb -spfile '+DATA/ORCLCDB/spfile'

Let's check the current location of SPFILE.

[oracle@primary01 ~]$ srvctl config database -d orclcdb
...
Spfile: +DATA/ORCLCDB/spfile
...

We get the database back to the cluster.

[oracle@primary01 ~]$ srvctl enable database -d orclcdb

Optionally, you can point the location of SPFILE in PFILE on both nodes.

[oracle@primary01 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
spfile='+DATA/ORCLCDB/spfile'
[oracle@primary02 ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
spfile='+DATA/ORCLCDB/spfile'

We can startup the database with the new SPFILE now.

[oracle@primary01 ~]$ srvctl start database -d orclcdb
[oracle@primary01 ~]$ srvctl status database -d orclcdb
Instance ORCLCDB1 is running on node primary01
Instance ORCLCDB2 is running on node primary02

Check the SPFILE location.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ORCLCDB/spfile

As you can see, the procedure to change the location of a SPFILE in a RAC database is a little complicated than in a single-instance database.

If you found no effects on changing new SPFILE, maybe you did it in the wrong way. Please check how to make SPFILE back to work for more clues.

Leave a Reply

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