ALTER SYSTEM SET SPFILE
Before reading, you have to know that there's no ALTER SYSTEM SET SPFILE statement in Oracle, because we can never write the location of SPFILE into current SPFILE. It makes no sense. All we can do is to change SPFILE physical location for the database.
In this post, I will talk about how to change SPFILE location in a single-instance database and in a RAC database.
For 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 were 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:
- Backup Original SPFILE
- Create SPFILE in New Location
- Point to New SPFILE in PFILE
Backup Original SPFILE
In case you damage SPFILE during the operation, you should keep a copy 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
Create SPFILE in New Location
There're several choices 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, as long as you specified its location.
SQL> create pfile from spfile;
File created.
SQL> create spfile='/spfiles/ORA12C1/[email protected]' 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/[email protected]' from memory;
File created.
SQL> shutdown immediate;
...
Please note that, if you're in a cluster database (RAC), don't create PFILE or SPFILE from memory, because it will give a pfile with only current 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/[email protected]';
...
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.
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/[email protected]'
[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 a trick is to make a symbolic link for 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?
For 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.
Use a Symbolic Link
Using Symbolic Link in ASMCMD to change SPFILE location does not move the actual file, we just do a trick to make it look like changed. For more details, you may refer to: How Srvctl Modify Database SPFILE.
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 by RMAN.
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.
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
We backup SPFILE by creating 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.
Thanks again, useful post.
I’m glad it’s useful.