SPFILEs represent a fundamental change in the way Oracle accesses and maintains parameter settings for the instance. An SPFILE eliminates the two serious issues associated with legacy parameter files:
•\ It stops the proliferation of parameter files: An SPFILE is always stored on the database server; the SPFILE must exist on the server machine itself and can’t be located on the client machine. This makes it practical to have a single source of “truth” with regard to parameter settings.
•\ It removes the need (in fact, it removes the ability) to manually maintain parameter files outside of the database using a text editor: The ALTER SYSTEM command lets you write values directly into the SPFILE. Administrators no longer have to find and maintain all of the parameter files by hand.
The naming convention for this file by default is
$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora (UNIX/Linux environment variable)
%ORACLE_HOME/database/spfile%ORACLE_SID%.ora (Windows environment variable)
I strongly recommend using the default location; doing otherwise defeats the simplicity SPFILEs represent. When an SPFILE is in its default location, everything is more or less done for you. Moving the SPFILE to a nondefault location means you have to tell Oracle where to find the SPFILE, leading to the original problems of legacy parameter files all over again!
Note In Oracle RAC environments, the spfile is usually located on shared ASM disks in a directory like +DATA//PARAMETERFILE. You view the location via the SRVCTL utility.
Converting toSPFILEs
Suppose you have a database that is using a legacy parameter file. The move to an SPFILE is quite simple—you use the CREATE SPFILE command.
Note You can also use a “reverse” command to create a parameter file (PFILE) from an SPFILE. I’ll explain shortly why you might want to do that.
So, assuming you have an init.ora parameter file and that init.ora parameter file is in the default location on the server, you simply issue the CREATE SPFILE command and restart your server instance. You need to be connected to the root container as a privileged account to perform this task:
$ sqlplus / as sysdba
SQL> show parameter spfile;
NAME TYPE VALUE
spfile string
SQL> create spfile from pfile;
File created.
SQL> startup force;
ORACLE instance started.
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
spfile string /opt/oracle/product/21c/
dbhome_1/dbs/spfileCDB.ora
To recap, we used the SHOW PARAMETER command here to show that initially we were not using an SPFILE, but after we created one and restarted the instance, we were using one and it had the default name.
Note In a clustered environment, using Oracle RAC, all instances share the same SPFILE, so this process of converting over to an SPFILE from a PFILE should be done in a controlled fashion. The single SPFILE can contain all of the parameter settings, even instance-specific settings, but you’ll have to merge all of the necessary parameter files into a single PFILE using the format that follows.
In a RAC environment, in order to convert from individual PFILEs to an SPFILE shared by all, you’d merge your individual PFILEs into a single file resembling this:
*.cluster_database=true
*.control_files=’+DATA/CDB/CONTROLFILE/current.261.1064287219′,’+RECO/CDB/
CONTROLFILE/current.256.1064287219′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_name=’cdb’
*.db_recovery_file_dest=’+RECO’
*.db_recovery_file_dest_size=12207m
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdbXDB)’
*.enable_pluggable_database=true
cdb1.instance_number=1
cdb2.instance_number=2
*.local_listener=’-oraagent-dummy-‘
*.nls_language=’AMERICAN’
*.nls_territory=’AMERICA’
*.pga_aggregate_target=256m
*.remote_login_passwordfile=’exclusive’
*.sga_target=2000m
cdb2.thread=2
cdb1.thread=1
cdb1.undo_tablespace=’UNDOTBS1′
cdb2.undo_tablespace=’UNDOTBS2′
That is, parameter settings that are common to all instances in the cluster would start with the “*.” string. Parameter settings that are specific to a single instance, such as the INSTANCE_NUMBER and the THREAD of redo to be used, are prefixed with the instance name (the Oracle SID). In the preceding example
•\ The PFILE would be for a two-node cluster with instances named CDB1 and CDB2.
•\ The *.db_name = ‘CDB’ assignment indicates that all instances using this SPFILE will be mounting a database named CDB.
•\ cdb1.undo_tablespace=’UNDOTBS1′ indicates that the instance named CDB1 will use that specific undo tablespace, and so on.
