Short introduction to SPFILE

I decided to write this post after reading post "Why I still use pfiles" on Jeff Hunter's blog.

1. What is SPFILE
SPFILE is server parameter file and it was introduced in Oracle 9i. As it is binary file managed by server, it cannot be modified using text editor. When you execute STARTUP Oracle will look at first for SPFILE then for PFILE. SPFILE can be backed up by RMAN.

2. How to change values in SPFILE
As I said before SPFILE is binary file managed by Oracle so it cannot by
modified by text editor. To change values in spfile you need execute (example)
command:

alter system set parameter_name=true scope=spfile;

See the magic parameter SCOPE. This parameter has three possible options:

• SPFILE - changes settings in SPFILE only


• MEMORY - changes settings in already running instance, new values will be lost after restart


• BOTH - changes settings in SPFILE and apply settings on already running
  instance. This is the default option if SCOPE is not defined in the ALTER
  SYSTEM command.

Note: if you get "ORA-02095: specified initialization parameter cannot be
modified" message it means that specific parameter is static, so it needs instance restart.
To check whether parameter is static or dynamic look at documentation or query v$parameter table as in example:

select name, issys_modifiable from v$parameter where name = 'parameter_name';

If the result is FALSE then parameter is static.
Note if the result of the query is DEFFERED instead of IMMEDIATE then it is also dynamic parameter will afffect only new created sessions.

3. How to convert PFILE to SPFILE

CREATE spfile FROM pfile;

In this case SPFILE will create in $ORACLE_HOME/dbs directory.
or

CREATE spfile='/storage/config/spfileORCL.ora' FROM

pfile='/storage/config/initORCL.ora';

In this case spfile will be created in specified location.

4. How to convert SPFILE to PFILE

There are are two ways how to convert SPFILE to PFILE, quick and standard way.

At OS system level (quick):

strings spfileORCL.ora > initORCL.ora

At Oracle level (standard):

CREATE pfile FROM spfile;

or

CREATE pfile='/storage/config/initORCL.ora' FROM spfile;

5. SPFILE in RAC enviroment

SPFILE can hold configuration for all nodes in Real Application Cluster.
You can set parameter for all nodes in cluster and also you can set parameter
only for specific node cluster.

Example:

alter system set shared_pool_size=256M scope=spfile sid='*';

This command will set parameter to value same for the all of instances (nodes) in the
cluster. The entry in spfile will be looking as following:

*.shared_pool_size=268435456

alter system set shared_pool_size=256M scope=spfile sid='ORCL_NODE1';

This command will set the shared pool for specific instance. The entry in
spfile will be looking as following:

ORCL_NODE1.shared_pool_size=268435456

Note if you didn't specify the SID it is the same as you typed SID='*', so
value for this parameter will be the same for all instances in the cluster.

If you have your SPFILE placed on shared storage you can use at least two ways how to point your instance to the new location.

1. Create symlink:

ln -s /storage/config/spfileORCL.ora $ORACLE_HOME/dbs/spfileORCL_NODE1.ora

2. Create pfile which points to spfile:

echo "spfile='/storage/config/spfileORCL.ora' >

$ORACLE_HOME/dbs/initORCL_NODE1.ora"

6. Some hints

1. Comment your parameters

It is useful to use comments during change of parameters (you will help to
workmates and yourself in the future).
To do this use COMMENT parameter as following:

alter system set java_pool_size=150M 

  comment=' 25-11-2006 IKA - recommended by patchset' scope=spfile;

Entry will be looking as following:

*.java_pool_size=157286400# 25-11-2006 IKA - recommended by patchset

Note keep you comments short as possible, informative and easy to understand.

2. View SPFILE settings

From sqplus:

select * from v$spparameter where value is not null order by name;

From OS: You can use commands as cat, less, more or better you can use

strings spfileORCL.ora

which displays more readable output (only text).

Update:

3. Useful informations about SPFILE

For more information about SPFILE you can find on Metalink.
Note:293698.1 - All What you Wanted To Know About SPFILE and PFILE Files
Note:249664.1 - Pfile vs SPfile

Posted on Saturday, November 25, 2006 Comments [30]

Tips & Tricks: Linux startup script for Oracle application server

Download startup script for Oracle Application Sever (Midle tier).

Installation is very simple:

1. Create new file "oas-start" in /etc/init.d/ directory and copy above script to it.
2. Modify ORACLE_HOME and ORACLE_OWNER variables and set the appropriate values.
3. Execute "chmod 750 /etc/init.d/oas-start".
4. Execute "chkconfig --add oas-start --level 0356".

Note: this script does not start DB (if DB is part of infrastructure installation).

Posted on Friday, November 10, 2006 Comments [0]

Tips & tricks: Simple alert.log monitor in bash

I was looking for simple script for monitoring of specific events in alert log. After searching on the web (I was found some pretty scripts but most of them was very huge or complicated (IMHO)) I decided to write my own script in Bash shell.

Download alert log monitor script.

Script after execution checks some prerequisities, then execute tail command and post the output to trigger function which is parsing (using egrep command) the output for interesting events defined in $NEEDLE variable. If the conditions (in $NEEDLE) are met then action function is executed. In this case action emails the event message along with instance name to speciefied e-mail address (sending of emails should be configured on OS level). Of course you can write your own action.

Hope you'll find this script as useful.

Posted on Thursday, November 09, 2006 Comments [0]