An SPFILE (Server Parameter File), new to Oracle9i, is a binary file. The file is not meant to be modified manually and must always reside on the server side. After the file is created it is maintained by the Oracle server. If modified manually, the SPFILE is rendered useless. The SPFILE provides the ability to make changes to the database persistent across shutdown and startup. It also provides the ability to self-tune parameter values, which are recorded in the file. RMAN (Recovery Manager) support for backing up the initialization parameter file is possible because the SPFILE resides on the server side. By default, the file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora
Creating an SPFILE
An SPFILE is created from a PFILE file using the CREATE SPFILE command. This command requires the SYSDBA privilege to execute. This command can be executed before or after instance startup.
CREATE SPFILE [='SPFILE-NAME']
FROM PFILE[='PFILE-NAME'];
where:
SPFILE-NAME: Name of the SPFILE to be created
PFILE-NAME: Name of the PFILE being used to create the SPFILE. The PFILE must be available on the server side
Example
CREATE SPFILE = ‘$ORACLE_HOME/dbs/spfileDBA01.ora’
FROM PFILE = ‘$ORACLE_HOME/dbs/initDBA01.ora’;
If the SPFILE-NAME and PFILE-NAME are not included in the syntax, Oracle will use the default PFILE to generate an SPFILE with a system-generated name.
SQL> CREATE SPFILE FROM PFILE;
Using Oracle Enterprise Manager to Create an SPFILE
From the OEM Console:
- Navigate to Instance > Configuration
- Highlight Configuration.
- Select Object > Create spfile from the main menu.
SPFILE Example
The comments specified on the same lines as a parameter setting in the PFILE are maintained in the SPFILE. All other comments are ignored.
Although the text of an SPFILE is easily viewed in UNIX, the SPFILE is binary, and manual modification of the SPFILE will render it unusable. If you need to view the specific contents of an SPFILE or make some modification, export the SPFILE to a PFILE.
* .background_dump_dest= ‘/home/dba01/ADMIN/BDUMP’
*.compatible=’9.2.0′
*.control_files=’/home/dba01/ORADATA/u01/ctrl01.ctl’ *.core_dump_dest= ‘/home/dba01/ADMIN/CDUMP’
*.db_block_size=4096
*.db_name=’dba01′
*.db_domain= ‘world’
*.global_names=TRUE
*.instance_name=’dba01′
*.remote_login_passwordfile=’exclusive’
*.java_pool_size=50000000
*.shared_pool_size=50000000
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS’
Modifying Parameters in SPFILE
The ALTER SYSTEM SET command is used to change the value of instance parameters.
ALTER SYSTEM SET parameter_name = parameter_value
[COMMENT 'text'] [SCOPE = MEMORY|SPFILE|BOTH]
[SID= 'sid'|'*']
where
parameter_name: Name of the parameter to be changed
parameter_value: Value the parameter is being changed to
COMMENT: A comment to be added into the SPFILE next to the parameter being altered
SCOPE: Determines if change should be made in memory, SPFILE, or in both areas
MEMORY: Changes the parameter value only in the currently running instance
SPFILE: Changes the parameter value in the SPFILE only
BOTH: Changes the parameter value in the currently running instance and the SPFILE
SID: Identifies the ORACLE_SID for the SPFILE being used
‘sid’: Specific SID to be used in altering the SPFILE
‘*’: Uses the default SPFILE
Example :
Changing parameter values :
ALTER SYSTEM SET undo_tablespace = UNDO2;
Specifying temporary or persistent changes :
ALTER SYSTEM SET undo_tablespace = UNDO2 SCOPE=BOTH;
Deleting or resetting values :
ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID=’*';
There are several ways to remove a parameter from the SPFILE:
- Set the parameter back to its default value to simulate deleting using ALTER SYSTEM SET.
- Re-create the SPFILE using CREATE SPFILE FROM PFILE.
- Use ALTER SYSTEM RESET to delete the parameter from the SPFILE.
