Oracle’s SPFILE – Part 2

The first part of this series
introduced the System Parameter file and its attributes. Below are some
practical examples of working with the SPFILE.

Examples of using SPFILE

The examples below were tried on Oracle 10g (10.1.0.2) on
Linux platform (FC1). This will behave the same way on other platforms as well.

Creating an SPFILE.

The actual file names can also be specified in the create
command.

SQL> create spfile from pfile;

File created.

Creating PFILE from SPFILE.

The actual file names can also be specified in the create
command. This option is also known as exporting the SPFILE. This is helpful in
the following ways:

1. As a means of text-based backup.

2. For verifying and analyzing the values currently being
used.

3. Modifying the export file and then recreating the SPFILE.


SQL> create pfile from spfile;

File created.

SQL> create pfile=’initdb1.ora.bkp’ from spfile;

File created.

SQL> create pfile=’initdb2.ora.bkp’ from spfile=’spfiledb2.ora’;

File created.

Checking at OS level:


[oracle@amzone dbs]$ file initdb1.ora
initdb1.ora: ASCII text
[oracle@amzone dbs]$ file spfiledb1.ora
spfiledb1.ora: data
[oracle@amzone dbs]$ file initdb1.ora.bkp
initdb1.ora.bkp: ASCII text

Providing the wrong file name gives an OS error:-


SQL> create pfile=’initdb2.ora.bkp’ from spfile=’spfiledbx’;
create pfile=’initdb2.ora.bkp’ from spfile=’spfiledbx’
*
ERROR at line 1:
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

Reverting to PFILE

Reverting to a PFILE can be done by simply creating a PFILE
from SPFILE. Remove the existing SPFILE from the default directory. The SPFILE
parameter should no longer be present in the PFILE. Oracle resorts to using the
PFILE.

Changing static parameters in the SPFILE

There are parameters that cannot be changed in the lifetime
of an instance. The database needs to be restarted for the modified parameters
to take effect. If SPFILE is enabled, then the only alternative for changing
static parameters is to post the changes to the file and not try changing the
current instance.

Changing static parameters requires the SPFILE option for the
SCOPE parameter with ALTER SYSTEM. These parameters cannot be changed for the
presently active instance, i.e., with scope set to MEMORY/BOTH. All parameter
that have the column ISSYS_MODIFIABLE column set to FALSE in the V$PARAMETER
view, fall in this category. The scope should be SPFILE so that the parameters
take effect on the next restart. The example below shows that the ALTER SYSTEM
command will not put the new value in the V$PARAMETER view but will show it in
the V$SPPARAMETER view as the SPFILE is updated.


SQL> select name, value, issys_modifiable from v$parameter where name = ‘utl_file_dir’;

NAME VALUE ISSYS_MODIFIABLE
—————————— ——————– —————————
utl_file_dir FALSE

SQL> alter system set utl_file_dir=’/tmp’ scope=both;
alter system set utl_file_dir=’/tmp’ scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set utl_file_dir=’/tmp’ scope=spfile;

System altered.

SQL> select name, value, issys_modifiable from v$parameter where name = ‘utl_file_dir’;

NAME VALUE ISSYS_MODIFIABLE
—————————— ——————– —————————
utl_file_dir FALSE

SQL> select name, value, isspecified from v$spparameter where name = ‘utl_file_dir’;

NAME VALUE ISSPECIFIED
—————————— ——————– ——————
utl_file_dir /tmp TRUE

Using ALTER SYSTEM command

1. Though SPFILE can be created from a PFILE with
SYSDBA/SYSOPER privilege (without actually starting the database), parameter
values can only be modified with ALTER SYSTEM that will work after the STARTUP
command (also in nomount stage) is issued. The example below uses
MAX_ENABLED_ROLES parameter; this one is deprecated in Oracle 10g and mentioned
here as a sample case only.


SQL> alter system set max_enabled_roles=20 scope=spfile;
alter system set max_enabled_roles=20 scope=spfile
*
ERROR at line 1:
ORA-01034: ORACLE not available

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 146800640 bytes
Fixed Size 777836 bytes
Variable Size 124789140 bytes
Database Buffers 20971520 bytes
Redo Buffers 262144 bytes
SQL> alter system set max_enabled_roles=20 scope=spfile;

System altered.

Identifying the SPFILE being used from V$PARAMETER.


SQL> select name, value from v$parameter where name = ‘spfile’;
–alternatively, show parameter spfile can also be used.

NAME VALUE
———— ————————————————————
spfile /home/oracle/product/10.1.0/db1/dbs/spfiledb1.ora

SQL> select count(1) from v$spparameter where value is not null;

COUNT(1)
———-
25

SQL> select count(1) from v$spparameter where isspecified = ‘TRUE’;

COUNT(1)
———-
26

Storing comments for SPFILE changes

Comments can be added along with the parameter value changes
in the ALTER SYSTEM command. Only the last provided comment is available. The previous
comment is overwritten with the new comment, when provided as part of the
command. The UPDATE_COMMENT column in V$SPPARAMETER stores the provided
comment.

In case you plan to maintain a log of the changes, one
option would be create a routine and change the parameter using it rather than
direct statements. This way, the routine can store additional information like
the old value, new value provided, comment provided, change date etc., into
another table for audit purpose.


SQL> alter system set user_dump_dest = ‘/home/oracle/product/10.1.0/db1/admin/db1/udump’
2 comment=’Changed for maintenance 070804′ scope=both;

System altered.

SQL> select name, value from v$parameter where name=’user_dump_dest’;

NAME VALUE
—————————— —————————————-
user_dump_dest /home/oracle/product/10.1.0/db1/admin/db
1/udump

SQL> select name, value, update_comment from v$spparameter where name = ‘user_dump_dest’;

NAME VALUE UPDATE_COMMENT
—————– —————————- ——————————
user_dump_dest /home/oracle/product/10.1.0/ Changed for maintenance 070804
db1/admin/db1/udump

Working with Control files

Oracle recommends the following method to change the control
file path or name.

From SQL*Plus:


SQL> create pfile from spfile;

File created.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.

OS command prompt:


[oracle@amzone db1]$ vi initdb1.ora
–manually modify the paths of the controlfile or rename them in init.ora.
[oracle@amzone db1]$ cp control01.ctl control01.org
–making a backup copy, just in case.
[oracle@amzone db1]$ mv control01.ctl db1_01.ctl
[oracle@amzone db1]$ mv control02.ctl db1_02.ctl
[oracle@amzone db1]$ mv control03.ctl db1_03.ctl
[oracle@amzone dbs]$ mv spfiledb1.ora spfilexxx.ora
–delete/move the spfile.

Go back to the SQL*Plus session and start the database:


SQL> startup pfile= ‘/home/oracle/product/10.1.0/db1/dbs/initdb1.ora’;
ORACLE instance started.

Total System Global Area 146800640 bytes
Fixed Size 777836 bytes
Variable Size 124789140 bytes
Database Buffers 20971520 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.

SQL> create spfile from pfile;

File created.

Shutdown and restart the database to check on the SPFILE
usage.

ORA-07446 error

While experimenting on one account I received ORA-07446 for
exporting to a PFILE. I could not identify the cause but it was resolved by
restarting the SQL*Plus session again.


SQL> startup nomount;
..
SQL> alter system set max_enabled_roles=20 scope=spfile;

System altered.

SQL> alter system set user_dump_dest=’/tmp’ scope=both;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-07446: sdnfy: bad value ‘o?=Qo?=o?=o?=o?=E’ for parameter user_dump_dest.
ORA-07446: sdnfy: bad value ” for parameter user_dump_dest.

Metalink note 166608.1 states that on a first conversion of
PFILE to SPFILE, shutting down and trying to start the instance in the same
SQL*Plus session gives the 0RA-03113 error. The solution is to simply reconnect
as SYSDBA again and start the database.

Bug 2372332 affects version 9.2.0.1 (fixed in 9.2.0.2 and
above) and expects the parameters in the SPFILE to be of lower case. Modifying a parameter that is present, in upper case,
in the SPFILE, will result in multiple entries in the SPFILE. The alternative is
to export the parameters to a PFILE, make changes and re-create an SPFILE from
it.

On Fedora Core 1 Platform, the PFILE created from SPFILE
looks like the example below. As you can see, the parameters are prefixed with
‘*.’, but these do not cause any issue in starting the database from the PFILE.
The SPFILE has a similar format but in binary mode.


*.background_dump_dest=’/home/oracle/product/10.1.0/db1/admin/db1/bdump’
*.compatible=’10.1.0.2.0′
*.control_files=’/home/oracle/product/10.1.0/oradata/db1/control01.ctl’,
‘/home/oracle/product/10.1.0/oradata/db1/control02.ctl’,
‘/home/oracle/product/10.1.0/oradata/db1/control03.ctl’
*.core_dump_dest=’/home/oracle/product/10.1.0/db1/admin/db1/cdump’
*.db_block_size=8192
*.db_cache_size=20971520
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’db1′

Conclusion

As mentioned earlier, the SPFILE option aims at advancing towards oracle’s self-tuning
capabilities. Just like other database files, SPFILE should also be backed up.
It is a good practice to export the SPFILE and store the text format, as a
safety measure (as long as it is available).

»


See All Articles by Columnist
Amar Kumar Padhi

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles