Mar 22, 2021
Unsetting Values in SPFILEs- Files
The next question that arises is, how do we unset a value that we previously set? In other words, we don’t want that parameter setting in our SPFILE anymore. Since we can’t edit the file using a text editor, how do we accomplish that? This, too, is done via the ALTER SYSTEM command, but using the RESET clause:
Alter system reset parameter sid=’sid|*’
So, for example, if we wanted to remove the sort_area_size parameter, to allow it to assume the default value we specified previously, we could do so as follows:
SQL> alter system reset sort_area_size scope=spfile ; System altered.
The sort_area_size is removed from the SPFILE, which you can verify by issuing the following:
SQL> create pfile=’/tmp/pfile.tst’ from spfile; File created.
You can then review the contents of /tmp/pfile.tst, which will be generated on the database server. You’ll find the sort_area_size parameter does not exist in the parameter file anymore.
Creating PFILEs from SPFILEs
The CREATE PFILE…FROM SPFILE command we just saw is the opposite of CREATE SPFILE. It takes the binary SPFILE and creates a plain text file from it—one that can be edited in any text editor and subsequently used to start up the database. You might use this command for at least two things on a regular basis:
•\ To create a one-time parameter file with some special settings, to start up the database for maintenance. So, you’d issue CREATE PFILE…
FROM SPFILE and edit the resulting text PFILE, modifying the required settings. You’d then start the database, using the PFILE= option to specify your PFILE instead of the SPFILE. After you finished, you’d just start up normally without specifying the PFILE=, and the database would use the SPFILE.
•\ To maintain a history of commented changes. In the past, many DBAs heavily commented their parameter files with a change history. If they changed the size of the SGA 20 times, for example, they would have 20 comments in front of the sga_target init.ora parameter setting, stating the date and reason for making the change. The SPFILE does not support this, but you can achieve the same effect if you get into the habit of doing the following:
SQL> connect / as sysdba
Connected.
SQL> create pfile=’init_10_feb_2021_CDB.ora’ from spfile; File created.
SQL> alter system set pga_aggregate_target=512m comment = ‘Changed 10-feb-
2021, AWR recommendation’;System altered.
In this way, your history will be saved in the series of parameter files over time.
Fixing Corrupted SPFILEs
The last question that comes up with regard to SPFILEs is, “SPFILEs are binary files, so what happens if one gets corrupted and the database won’t start? At least the init.ora file was just text, so we could edit it and fix it.” Well, SPFILEs shouldn’t go corrupt any more than should a datafile, redo log file, control file, and so forth. However, in the event one does—or if you have set a value in your SPFILE that does not allow the database to start—you have a couple of options.
First, the amount of binary data in the SPFILE is very small. If you are on a UNIX/ Linux platform, a simple strings command will extract all of your settings:
$ strings $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
*.audit_sys_operations=false
*.audit_trail=’none’
*.commit_logging=’batch’
*.commit_wait=’nowait’…
On Windows, simply open the file with write.exe (WordPad). WordPad will display all of the clear text in the file, and by simply cutting and pasting into init. ora, you can create a PFILE to use to start your instance.
In the event that the SPFILE has just “gone missing” (for whatever reason—not that I’ve seen an SPFILE disappear), you can also resurrect the information for your parameter file from the database’s alert log (more on the alert log shortly). Every time you start the database, the alert log will contain a section like this:
System parameters with non-default values:
processes = 300
nls_language = “AMERICAN”
nls_territory = “AMERICA”
filesystemio_options = “setall”
sga_target = 2152M
control_files = “/opt/oracle/oradata/CDB/control01.ctl”
db_block_size = 8192
From this section, you can easily create a PFILE to be converted into a new SPFILE using the CREATE SPFILE command.
More Details