Modifying the SPFILE using tools other than Oracle Enterprise Manager or SQL*Plus can corrupt the file and prevent database startup. To repair the file, you might be required to create a PFILE and then regenerate the SPFILE.
The examples in this section appear in ASCII text although the SPFILE is a binary file. Assume that you start an instance with an SPFILE containing the following entries:
The value before the period (.) in an SPFILE entry identifies the instance to which the particular parameter value belongs. When an asterisk (*) precedes the period, the value is applied to all instances that do not have a subsequent, individual value listed in the SPFILE.
For the instance with the Oracle system identifier (SID)
OPEN_CURSORS parameter is set to
1000 even though it has a database-wide setting of
500. Parameter file entries that have the asterisk (*) wildcard character only affect the instances without an instance-specific entry. This gives you control over parameter settings for instance
prod1. These two types of settings can appear in any order in the parameter file.
If another DBA runs the following statement, then Oracle Database updates the setting on all instances except the instance with SID
ALTER SYSTEM SET OPEN_CURSORS=1500 sid='*' SCOPE=SPFILE;
The SPFILE now has the following entries for
Run the following statement to reset
OPEN_CURSORS to its default value for all instances except
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE;
The SPFILE now has just the following entry for
Run the following statement to reset the
OPEN_CURSORS parameter to its default value for instance
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE SID='prod1';