SAPTechno

Note 601157 - Oracle9i: Server Parameter File

Header
Version / Date 35 / 2007-07-02
Priority Recommendations/additional info
Category Upgrade information
Primary Component BC-DB-ORA Oracle
Secondary Components BC-DB-ORA-DBA Database Administration with Oracle

Summary
Symptom


Oracle Server Parameter File (SPFILE)

The Oracle Server Parameter File (SPFILE) is a new feature available as of Oracle Release 9i. This note describes how to use the SPFILE rather than the Oracle parameter file init<SID>.ora (short: init.ora).

The following designations are used in this note:

  • <SID> is the <ORACLE_SID> which is the unique indicator of an Oracle database instance. This is not to be confused with the <SAPSID>, the SAP system ID (unique indicator of an SAP system).
  • init.ora is used as a synonym for the known Oracle parameter file init<SID>.ora.
  • SPFILE is the short form for Server Parameter File.

Oracle profile file, init<SID>.ora and init.ora (short form) are synonyms for the known Oracle parameter file.

SPFILE (short form), server-side parameter file, and sometimes also persistent parameter file are used as synonyms for the server parameter file spfile<SID>.ora.


Reason and Prerequisites



Documentation and migration note for Oracle9i: SPFILE

Solution


Structure of this note

This note is structured as follows:
Support by BR*Tools and SAPDBA
BR*Tools and SPFILE - Contexts and explanations
General Recommendation
SPFILE - Description
SPFILE - Search Sequence
SPFILE - Functions, Prerequisites, Constraints
SPFILE - Advantages
SPFILE - Special Features
SPFILE - Special Features with Oracle Real Application Cluster (RAC)
SPFILE - Migration
SPFILE - Migration (Return path)
SPFILE - Restoring an Inconsistent SPFILE
SPFILE - Administration
      - Default Location and Default Name of the SPFILE
      - Determining the active SPFILE
      - Exporting the SPFILE
       - Displaying the parameter in SPFILE at operating system level
       - Displaying Instance Parameters with SQL*PLUS
       - Setting system parameters in the SPFILE (persistent)
      - Setting system parameters temporarily
      - Resetting system parameters
      - Setting events in the SPFILE
       - Setting underscore parameters

Support by BR*Tools and SAPDBA

Backup and restore of the SPFILE are supported as of BR*Tools 6.20 Patch Level 103.

In addition, BR*Tools 6.40 also provides parameter maintenance for SPFILE with the program BRSPACE (see Note 647697).

Due to improved SPFILE support in BR*Tools (BRSPACE), we recommend that you generally use the latest version of the BR*Tools (6.40 or higher).

BR*Tools and SPFILE - Contexts and explanations

With every run, brbackup, brarchive, and 'brconnect -check' create a new profile file 'init<DBSID>.ora' in the standard directory and under the default name, if a SPFILE exists in the standard directory under the default name regardless of whether this SPFILE is active or not. This process will overwrite a file 'init<DBSID>.ora' that exists there! If you want to keep the last original version of init<DBSID>.ora for the moment, copy it (do not rename it, because certain SAP transactions, or example, ST04, need this file).

A file generated by the BR*Tools can be recognized by the file header:

################################################################
# This init.ora profile is a transparent copy of Oracle spfile.
# It was created by BR*Tools at 2004-03-26 08.53.03.
# Please do not change this file! Maintain only the spfile.
# You can use BRSPACE for the altering of database parameters:
#    brspace -f dbparam
################################################################

When you change a parameter in SPFILE using brspace, the system also automatically creates a new init<SID>.ora from the SPFILE so the contents of SPFILE and init<SID>.ora remain consistent. Certain SAP transactions (CCMS:DB02, DB03, DB26; ST04) still rely on the existence of an init.ora. If the spfile and init<SID>.ora are not kept consistent, these transactions show a status that is out-of-date in comparison with the SPFILE. If no init<SID>.ora exists, no display occurs at all. These SAP transactions only have a display character of lesser importance. Their function has no effect on the operation of the database itself.

Another reason for generating an init<SID>.ora from the SPFILE is brarchive. brarchive does not change the status of the database. To save archives logs if the database is stopped, brarchive must therefore read the init<SID>.ora to determine the archive directory.

General Recommendation

We recommend that you switch to using the SPFILE as part of the database upgrade to Oracle9i.

SPFILE - Description

Until now, initializing parameters have been specified for an Oracle instance in the parameter file known as init.ora on the client side. In most cases, this parameter file is located on the database server. However, this does have to be the case, since the instance can also be started from another (remote) host. The prerequisite for this, however, is that the init.ora file is stored locally on that host. Therefore, the init.ora file may exist several times on several hosts, which complicates the synchronization if a parameter is changed. This problem no longer occurs with a server parameter file.

SPFILE - Search Sequence
Oracle searches for the parameter file in the platform-dependent default location (Unix: $ORACLE_HOME/dbs; Windows: %ORACLE_HOME%\DATABASE) using the following names and in the specified sequence:
1. spfile<SID>.ora
2. spfile.ora
3. init<SID>.ora

SPFILE - Functions, Prerequisites, Constraints

The SPFILE is managed by the Oracle server. New entries or parameter changes are performed using the command 'ALTER SYSTEM SET...'. In the scope clause, you specify whether the change should be temporary (scope = memory, valid until the next instance restart) or whether it should be saved permanently in the SPFILE (scope = spfile, only effective after the instance is restarted). The default setting is scope=both (valid immediately and permanently, even after an instance restart). You can also delete a parameter entry from the SPFILE again using 'ALTER SYSTEM RESET...'.

A SPFILE can generate from an existing init.ora with the CREATE SPFILE command, regardless of the instance status (nomount, mount, open). The active SPFILE of an instance is the one that has been used to start the instance.

SPFILE - Advantages

The main advantages of an SPFILE are described below:

  • Administration through the Oracle server (for example, with SQL*PLUS) using SQL commands.
  • Dynamic changes to the system configuration with 'ALTER SYSTEM SET' are by default written simultaneously to the SPFILE and are therefore retained after the instance is restarted. Consequently, unlike the classic init.ora, you do not need to maintain the init.ora file after you change a parameter using 'ALTER SYSTEM SET'.
  • Starting an Oracle instance remotely without a local copy of the init.ora.
  • If an instance is started, you can use the instance information to determine which SPFILE was used to start it. This is not possible if the instance has been started with a standard init.ora file.
  • If parameter changes are made in the SPFILE with BRSPACE, a history of the parameter changes is recorded in the relevant BRSPACE logs. The usual manual maintenance of parameter changes as a comment in the init.ora is not required in this case.

As a result of the advantages already mentioned, the SPFILE already forms the basis for future enhancements of the Oracle database in the area of Self-administration and Automatic Tuning (Oracle 10 g).

SPFILE - Special Features

Note the following points when you use an SPFILE:

  • An SPFILE is a binary file. The contents can be displayed with a normal editor. However, write access corrupts the SPFILE. If the SPFILE is corrupted, the instance cannot be started or a current instance terminates.
  • SYSOPER or SYSDBA authorization is required for working with the SPFILE.
  • If an instance was started with an SPFILE and this SPFILE was deleted by mistake, parameter changes can no longer be written permanently to the SPFILE. You can no longer execute 'ALTER SYSTEM SET' commands with SCOPE=SPFILE or SCOPE=BOTH. Error message:
       ORA-27041: unable to open file
       OSD-04002: unable to open file

The configuration option provided by Oracle (SPFILE in a non-standard location or with a non-standard name) is not supported in the SAP environment. For a non-standard SPFILE to be found automatically by Oracle, you would have to make following entry as the only entry in the init<SID>.ora:

  spfile=<path to non-standard-spfile>

This configuration is incompatible with the use of BR*Tools.

SPFILE - Special Features with Oracle Real Application Cluster (RAC)

In an RAC environment, a single SPFILE can be used for all RAC instances. Since this SPFILE must be accessible to all RAC instances, it must be located on the cluster file system (shared disk). See Note 621293.

The following configuration recommendation applies to SAP on Oracle RAC:

  • Perform Oracle-RAC installations only with SPFILE
  • SPFILE on shared disk (prerequisite: Cluster File System CFS)
    This allows all RAC instances to have read and write access to the SPFILE.
  • Name of the SPFILE: spfile.ora
    This allows all RAC instances to recognize and use a single SPFILE. This differs from single instance systems, where the naming convention spfile<SID>.ora applies.
SPFILE - Migration

Before the SPFILE mechanism is activated in a system, you must ensure that the installed versions of BR*Tools or SAPDBA support this feature (see above).

Important: If an SPFILE is generated from the current parameter file, comments are then transferred to the SPFILE if they belong to a parameter, so if they correspond to the following form:

<parameter_name> = <value> # <Comment for this parameter>

For example:
undo_management = AUTO # Automatic Undo activated by Larry

Pure comment lines are not transferred. Therefore, we recommend that you check the validity and usefulness of existing comments before a migration to SPFILE to see whether they are to be copied into spfile or not.

The following steps activate the SPFILE mechanism:
1. Create SPFILE
     SQL>connect / as sysdba
     SQL>create spfile from pfile;
This command creates a SPFILE at the platform-dependent default position with the default name spfile<SID>.ora.

2. Copy the old parameter file as a backup and save it under a different name:

$>cp $ORACLE_HOME/dbs/init<SID>.ora $ORACLE_HOME/dbs/init<SID>.ora.SAVE

3.  Restart the database instance and check whether the SPFILE is active
    SQL>connect / as sysdba
    SQL>shutdown
     SQL>startup
     SQL>show parameter spfile

SPFILE - Migration (Return path)

The section below describes the return path from the SPFILE to the classic init<SID>.ora. We assume that an SPFILE exists in the standard location and the init<SID>.ora is also to be created in the standard location. Therefore, the instance is started with a normal 'startup' command. Execute the following steps on the database server:
1. Create PFILE
   SQL>connect / as sysdba
   SQL>create pfile from spfile;
2. Delete or rename the old SPFILE at the standard position
3.  Start and check the database instance
    SQL>connect / as sysdba
    SQL>shutdown
    SQL>startup
    SQL>show parameter spfile
The parameter 'spfile' should now be an empty string. The SPFILE is no longer active.

SPFILE - Restoring an Inconsistent SPFILE

If an invalid parameter value (for example, db_cache_size too large) has been written to the SPFILE by mistake - for example, due to a typing error - and the mistake is not spotted immediately, the system recognizes this error when it reads the SPFILE the next time you try to restart the instance and you cannot restart the instance. The start procedure terminates with an error message referring to the incorrect parameter.

To start the instance, you can either access a backup of the SPFILE or generate a temporary standard text parameter file from the current, inconsistent SPFILE. Correct the incorrect parameter value in this text file. Then recreate an SPFILE from the corrected parameter. To do this, proceed as follows:

1. Logon with SYSDBA or SYSOPER authorization
   SQL>connect / as sysdba
2. Create the parameter file
   SQL>create pfile='temp_init.ora' from spfile;
3. Correct the incorrect parameter in the created parameter file
4. Create SPFILE (at the standard position)
   SQL>create spfile from pfile='temp_init.ora'
5. Start the instance (using the corrected spfile)
   SQL>startup

SPFILE - Administration

Default Location and Default Name of the SPFILE

Unix platforms: $ORACLE_HOME/dbs/init<SID>.ora
Windows platforms: %ORACLE_HOME%\database\INIT<SID>.ORA

Algorithm used to search for the correct parameter file in the default location:
1. spfile<SID>.ora
2. spfile.ora
3. init<SID>.ora

Determining the active SPFILE

The following sources allow you to determine which SPFILE was used to start the instance:
Option 1: SQL>show parameter spfile
An empty string means that the instance was started with a standard init.ora. Otherwise, the SPFILE was used with the displayed path.
Option 2: Alert Log
If an SPFILE other than the SPFILE of the standard location was used, this is also shown in the Oracle Alert Log: When the instance is started, the system parameters that do not have default values are also listed.

Exporting the SPFILE

You can export the contents of an SPFILE, in particular the active SPFILE, at any time, regardless of the status of the instance, to a normal init.ora text file (standard Oracle parameter file). Reasons for an export include:

  • Regular backups of the SPFILE content
  • Diagnosis/listing of the set parameters
  • for changing parameters manually using export/change/SPFILE (for instance, in the case of an inconsistent SPFILE)

You can also use the parameter file created to start the instance by means of the PFILE option.

Syntax:
SQL command variant 1:
   SQL>create pfile from spfile;
SQL command variant 2:
   SQL>create pfile='<path_pfile>' from spfile;
SQL command variant 3:
  SQL>create pfile='<path_pfile>' from
       from  spfile='<path_spfile>';

Note that Oracle overwrites an existing file without issuing a warning. If no explicit path is specified, Oracle generates the init<SID>.ora using the Oracle default path (see above).

If a path is specified, it must exist. If the path does not exist, the system issued the following error messages:
  ORA-01078: failure in processing system parameters
  ORA-27040: skgfrcre: create error, unable to create file
  OSD-04002: unable to open file
  O/S-Error: (OS 3) The system cannot find the path specified.

Displaying the parameter in SPFILE at operating system level

The following alternatives exist for displaying the parameter set in SPFILE, its values and (if available) the comment for a parameter value:
Option 1: with a normal text editor
CAUTION: Writing with a normal editor corrupts the data in the SPFILE and therefore renders it unusable.
Option 2: 'strings' command
On UNIX platforms only: strings command
  $strings SPFILE<SID>.ORA | more
Displaying specific parameters in SPFILE:
  $strings SPFILE<>SID>.ORA | more | grep <parameter_name>

Displaying Instance Parameters with SQL*PLUS

You can use the performance view V$SPPARAMETER to display the contents of the active SPFILE.

  • Which parameters are set explicitly in the SPFILE?
    SQL>select name, value from v$spparameter where isspecified = 'TRUE'
  • Which parameters are not specified in the SPFILE?
    SQL>select name, value from v$spparameter where isspecified = 'FALSE'
  • Which current session parameters deviate from the settings of the system-wide parameters stored in the SPFILE?
    SQL>select sp.name NAME, sp.value SYSVALUE, p.value SESVALUE
        from v$spparameter sp, v$parameter p
        where p.name = sp.name and
              p.value <> sp.value;
  • Displaying the parameters set in SPFILE with comments
    SQL>select name, value, update_comment
        from v$spparameter
        where update_comment <> ' ';
Setting system parameters in the SPFILE (persistent)

Whether or not a parameter change is stored in the SPFILE is specified using the SCOPE clause of the 'ALTER SYSTEM SET' command.

  • SCOPE=BOTH (default):
    Change both in the SPFILE and in the current instance (not permitted for static parameters).
  • SCOPE=MEMORY:
    The change is valid for the current instance, but not persistent in the SPFILE. Not permitted for static parameters.
  • SCOPE=SPFILE:
    the change is stored persistently in the SPFILE and activated with the next restart. This is the only option for changing static parameters.

General syntax:
SQL>ALTER SYSTEM SET <parameter_name>=<value>
    COMMENT = 'comment explaining why this parameter was changed>
    SCOPE = {BOTH|SPFILE|MEMORY}
    SID   = '<SID>|'*'>;

The parameters that can be changed dynamically are:
SQL>select name, value, issys_modifiable
    from v$parameter
    where issys_modifiable = 'IMMEDIATE';
For these parameters, you can specify scope = memory, scope = both or scope = spfile.

The following parameters are static:
SQL>select name, value, issys_modifiable
    from v$parameter
    where issys_modifiable = 'FALSE' or issys_modifiable = 'DEFERRED';
These parameters can only be changed in the SPFILE. To activate them, you have to rstarte the instance.

Setting system parameters temporarily

If you want to test the effect of a changed dynamic parameter first, you can execute the change with 'ALTER SYSTEM SET' without making the change persistent in the SPFILE. To do this, you must specify the scope clause.

Resetting System Parameters

You can delete parameter entries from the SPFILE using the following command:
SQL>ALTER SYSTEM RESET <parameter_name> scope = spfile sid = '*';

Setting Events in the SPFILE

Explanation with an example:
Syntax in init<SID>.ora:
# events set for debug tracing of control and rollback
event = '10325 trace name context forever, level 10'
event = '10015 trace name context forever, level 1'

SQL syntax for SPFILE:

The following syntax is ideal: This method sets line breaks between the events.

ALTER SYSTEM SET
EVENT = 'Event 1',
        'Event 2',
        'Event 3',
        'Event n'
COMMENT = 'debug trace events for bug ...'
SCOPE = SPFILE;

The following syntactic alternative is more critical and is therefore not recommended:
ALTER SYSTEM SET
  EVENT='10325 trace name context forever,
  level 10:10015 trace name context forever, level  1'
  COMMENT='debug tracing of control and rollback'
  SCOPE=SPFILE;

If you use this syntax, you must make absolutely sure that no line breaks occur in the event text. Even though this does not cause an error when you set the events, the SPFILE is corrupted when you delete the event parameters because not all lines are deleted correctly. (See Oracle Enhancement Request 2946487). Therefore it is better not to use this syntax.
If the different events are separated by ':', then these should be in one line:
ALTER SYSTEM SET
  EVENT='Event1:Event2:Event3' COMMENT = 'comment' SCOPE = SPFILE;
This prevents the SPFILE from being corrupted when the event parameters are deleted.

You must restart the instance for the change to become effective.

To set new events, remove individual events from the list, or to make changes to an event from the list, you must reset the complete list and restart the instance.

All events are removed from the SPFILE using the following command:
SQL>ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*';
With a RAC configuration, you must replace '*' with the instance name.

Setting Underscore Parameters

You must set the underscore parameters in inverted commas:
SQL>alter system set "_push_join_predicate"=false scope=spfile;
System altered.
SQL>alter system reset "_push_join_predicate"
    scope = spfile sid = '*';
System altered.

Otherwise, you get the following error message:
SQL>alter system set _push_join_predicate=false scope=spfile;
ERROR at line 1:   ORA-00911: invalid character

Related Notes
1178409ORA-32021: parameter value longer than 255 characters
948197Merge fix for DBMS_STATS package on Oracle 9.2.x and 10.2.x
848708Oracle Database Configuration for NWDI server
830576Parameter recommendations for Oracle 10g
773173Using Oracle Fail Safe cluster on Windows with SPFILE
647697BRSPACE - New tool for Oracle database administration
598678Oracle 9i: New functions
596423Events and SPFILE