Performing Oracle User-Managed Backups - OCP: Oracle Database 12c Administrator Certified Professional - page 16

Spfiles are kept, by default, in the $ORACLE_HOME/dbs (Unix) or %ORACLE_HOME%\
database (Windows) directory. The spfile naming convention is sp<oracle_sid>.ora
by default. When the DBA starts the Oracle database/instance, Oracle will first look
for an spfile using the default filename. If one is not found, it will look for a file called
spfile<oracle_sid>.ora in the same default directory. Finally, if no spfile is found,
Oracle will look for a regular pfile as described previously. If no parameter file is found,
then the database will signal an error and the startup will abort. Oracle generally recom-
mends using an SPFILE over a text parameter file.
Note that if you try to start the database from RMAN without an spfile or
pfile available, Oracle will use the default parameter settings and actually
start the database. This does not happen if you try to start the database
from SQL*Plus or OEM. We will discuss RMAN more in Chapter 3, “Config-
uring and Backing Up Using RMAN.”
How Do You Set Parameter Values When Using an Spfile?
To modify a parameter when using an spfile, you use the alter system command. For
example, if you want to change the parameter DB_RECOVERY_FILE_DEST_SIZE , which is a
dynamic parameter (so it can be changed on the fly), you would issue the following alter
system command:
Alter system set db_recovery_file_dest_size=100m;
The previous command only changes the parameter as long as the database instance is
running. The parameter would be reset in this case unless you used the scope=both param-
eter. In this case, the parameter would be changed for the running instance, dynamically,
and also the parameter would be changed in the database spfile so that the change would
persist through an instance restart. Here is an example:
Alter system set db_recovery_file_dest_size=100m scope=both;
As we said earlier, some parameters are not dynamic. In this case, you have to indicate
that you want to change only the parameter file. To do this, use the alter system command
and include the scope=spfile keyword, as shown here:
Alter system set memory_max_target=200m scope=spfile;
In some cases, you may want to change the parameter in just the current instance of
the database, but you will not want that change to persist after the next shutdown. In
this case, use the scope=memory keyword when issuing the alter system command, as
shown here:
Alter system set db_recovery_file_dest_size=100m scope=memory;