The Geek Diary

CONCEPTS | BASICS | HOWTO

How to Modify spfile in Oracle Database

By

SPFILE: The Server Parameter File

Beginning with Oracle9i, Oracle introduced the concept of the server parameter file. This file, known as SPFILE , differs from the standard INIT.ORA file in a number of ways:

  • It is a binary file, rather than a text-based file.
  • It is stored on the server, rather than on a client machine.
  • It can maintain changes to parameter values over the shutdown and startup of an instance.

This last point is the important part of SPFILE. If your database is running under Oracle9i, any changes you make to configuration parameters via the ALTER SYSTEM statement will be saved as part of the permanent configuration file. That means that if you change any of your database parameter values for tuning purposes, you won’t also have to change one or more INIT.ORA files so that the new values will persist. You also have the option of making dynamic changes to parameters without making them a part of the SPFILE.

How to Modify the Content of an SPFILE File

In order to modify the spfile in oracle database, do one of the following:

1. Use the ALTER SYSTEM command to modify the SPFILE currently in use

1. To determine if an spfile is being used:

SQL> select value from v$parameter where name='spfile'

2. To modify the contents of the spfile:

SQL> alter system set undo_management=auto scope=spfile;
System altered.

This sets undo_management to auto.

SQL> alter system set resource_manager_plan='' scope=spfile; 
System altered.

This sets the resource_manager_plan parameter to null.

NOTE: Parameters starting with an underscore must be enclosed with “” (double quotes) to prevent an ORA-00911: invalid character

2. Use the export method.

1. Export the SPFILE to a PFILE. The resulting PFILE will be an editable ASCII file. Examples:

SQL> create pfile [= 'pfile_name' ] from spfile [= 'spfile_name'];
SQL> create pfile from spfile;

The PFILE is created in directory $ORACLE_HOME/dbs and named init[SID].ora.

SQL> create pfile = '/testcasesADC/137483.1/initV1023U.ora' from spfile;

The PFILE is created as ‘/testcasesADC/137483.1/initV1023U.ora’ 

SQL> create pfile = '/testcasesADC/137483.1/initV1023U_test.ora' from spfile = '/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora';

The PFILE is created as ‘/testcasesADC/137483.1/initV1023U_test.ora’ and the location of the spfile was explicit.

2. Modify the PFILE parameter value with a text editor and save the file:

undo_management=auto
resource_manager_plan=''

3. Recreate the SPFILE from the modified PFILE:

– If the instance is down:

SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';

– If the instance is up and you try to recreate the spfile the database was started with a naming conflicts occurs. You will recieve error ORA-32002. 

SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';

ERROR at line 1:
ORA-32002: cannot create SPFILE already being used by the instance

To overcome this error specify a different file name and then rename the spfile once the instance is shutdown:

SQL> create spfile='/usupport/64bit/app/oracle/product/10.2.0.3/dbs/spfileV1023U_new.ora' from pfile='/testcasesADC/137483.1/initV1023U_test.ora';

You May Also Like