Timings to Backup SPFILE
In case that we need to restore SPFILE to the original state at a later time, we should backup SPFILE in advance. Since SPFILE is pretty small, so any time can be a good time to perform the backup, especially at the following timings.
- Before you change any value of initialization parameters.
- Before you duplicate, build or restore a database from the most current state of the source SPFILE.
- Before you move the database from one place to another. Any loss can be a disaster.
- Before you remove the database completely from the server. Any accident can be a tragedy.
I would say, setting regular backup of SPFILE would be very basic and necessary to cover almost all situations.
Ways to Backup SPFILE
There're several ways to backup SPFILE before you change any initialization parameter.
Make a Copy of SPFILE
Although SPFILE is sometimes changed, it's not often, so making a copy of SPFILE from current state is alright.
Directly Copy
For example, we can issue a cp command to copy it on Linux.
[oracle@test ~]$ cp -p $ORACLE_HOME/dbs/spfileORCLCDB.ora /home/oracle/spfileORCLCDB.ora
[oracle@test ~]$ ll /home/oracle/spfileORCLCDB.ora
-rw-r----- 1 oracle oinstall 3584 Jul 21 2022 /home/oracle/spfileORCLCDB.ora
For Windows, you may also copy it to a safe place.
RAC Database
For RAC database, copying a file from ASM to file system is not difficult, the difficult part is how we restore SPFILE into a RAC database correctly.
Get a Copy by RMAN
If you don't or wouldn't know the location of SPFILE, you may still get a copy of SPFILE through RMAN:
We backup SPFILE. Please note that, this operation may trigger AUTOBACKUP.
RMAN> backup spfile;
Next, we restore it to the desired place immediately. RMAN will automatically choose the most recent backup of SPFILE to restore.
RMAN> restore spfile to '/home/oracle/spfileORCLCDB.ora';
We got a copy of SPFILE through RMAN.
Backup SPFILE as a PFILE
Saving SPFILE as a plain-text PFILE is also a good strategy to preserve the state of SPFILE. Basically, they have the same content but with different forms.
From Default SPFILE
If the instance is running, we can create PFILE from the default SPFILE, Oracle knows where it is.
Create PFILE to the default location from default SPFILE.
SQL> create pfile from spfile;
File created.
It's not difficult to find the default location of SPFILE and PFILE.
Create PFILE to specific location from default SPFILE.
SQL> create pfile='/home/oracle/initORCLCDB.ora' from spfile;
File created.
From Any SPFILE
Create PFILE to specific location from specific SPFILE.
SQL> create pfile='/home/oracle/initORCLCDB.ora' from spfile='/home/oracle/spfileORCLCDB.ora';
File created.
For RAC databases, you may also specify the location of SPFILE.
SQL> create pfile='/home/oracle/initORCLCDB.ora' from spfile='+DATA/ORCLCDB/spfile';
File created.
Backup SPFILE by RMAN
Even though we can backup SPFILE along with the controlfile, we can also backup SPFILE solely.
Backup To Default Destination
RMAN> backup spfile;
The default location is usually the fast recovery area, if it's enabled.
Backup To Specific Destination
RMAN> backup spfile format '/home/oracle/spfile-%U';
...
There're some other ways to set backup locations in RMAN, you may have a look.
Although every AUTOBACKUP contains controlfile and SPFILE at the state of backup, we do recommend that you put the operation in your daily backup.