Reset Oracle init.ora / spfile parameters

There is a fair amount of information available on this topic already, but I thought I’d add my two cents worth here.

So why might you need to do this in the first place? Well generally it’s because some parameter has been set that someone later decides was a mistake. Or, as in my case, because Oracle comes out with a new way to handle a parameter making it attractive to let Oracle automatically handle it. db_file_multiblock_read_count is just such a parameter, but I’ll have to save that for another post. Anyway, there are a couple of key points to keep in mind:
 

  1. Oracle does a lot of things automatically (if we don’t prevent it)
  2. Some of the automatic things are good
  3. Setting a parameter back to it’s default value IS NOT THE SAME as unsetting it

 
But first a few house keeping basics. Oracle has a view called V$PARAMETER which lists all the non-hidden parameters, their current values and a few fields which indicate whether the current value has been altered from the default value (ISMODIFIED and ISDEFAULT in particular). The view is based on X$KPPI and X$KSPPV. These X$ views contain the so called hidden parameters (those beginning with “_”) as well as the regular init.ora / spfile parameters, although the V$PARAMETER view doesn’t expose the hidden ones. There is a metalink note with all the fixed view definitions by the way (220021.1). But it’s easier just to pull the definition from V$FIXED_VIEW_DEFINITION (fixed_view_def.sql) and you’ll be sure you have the version that you’re actually running. Here is a link to the 11gR1 doc on the V$PARAMETER view.

One of the things that has always bugged me about the V$PARAMETER view is that the ISDEFAULT column is not updated when a parameter is dynamically changed. There’s another column, ISMODIFIED, that is changed when a parameter is changed with an alter system or alter session command. But it’s a pain to have to look at two columns. So I wrote this little script (parms.sql) to make it a little easier to deal with. It also displays hidden parameters if you ask for them. It’s based on the 10.2.0.4 definition of V$PARAMETER but it works in 9i and 11gR1 as well.

So back to resetting/unsetting a parameter. First let me prove that setting the value back to the default is not the same as unsetting.

 


> sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Nov 13 15:06:15 2008

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @parms
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:   parms.sql
SQL> --
SQL> -- Purpose:     Display parameters and values.
SQL> --
SQL> -- Author:      Kerry Osborne
SQL> --
SQL> -- Usage:       This scripts prompts for three values, all of which can be left blank.
SQL> --
SQL> --              name: the name (or piece of a name) of the parameter(s) you wish to see
SQL> --
SQL> --              isset: "TRUE" or "T" to see only nondefault parameters
SQL> --
SQL> --              show_hidden: "Y" to show hidden parameters as well
SQL> --
SQL> ---------------------------------------------------------------------------------------
SQL> set lines 155
SQL> col name for a50
SQL> col value for a70
SQL> col isdefault for a8
SQL> col ismodified for a10
SQL> col isset for a10
SQL> select name, value, isdefault, ismodified, isset
  2  from
  3  (
  4  select flag,name,value,isdefault,ismodified,
  5  case when isdefault||ismodified = 'TRUEFALSE' then 'FALSE' else 'TRUE' end isset
  6  from
  7     (
  8         select
  9              decode(substr(i.ksppinm,1,1),'_',2,1) flag
 10              , i.ksppinm name
 11              , sv.ksppstvl value
 12              , sv.ksppstdf  isdefault
 13  --            , decode(bitand(sv.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismodified
 14              , decode(bitand(sv.ksppstvf,7),1,'TRUE',4,'TRUE','FALSE') ismodified
 15           from x$ksppi  i
 16              , x$ksppsv sv
 17          where i.indx = sv.indx
 18     )
 19  )
 20  where name like nvl('%¶meter%',name)
 21  and upper(isset) like upper(nvl('%&isset%',isset))
 22  and flag not in (decode('&show_hidden','Y',3,2))
 23  order by flag,replace(name,'_','')
 24  /
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y

NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    TRUE     FALSE      FALSE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      8                                                                      TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE

SQL> alter system set db_file_multiblock_read_count=32;

System altered.

SQL> set echo off
SQL> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y

NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      32                                                                     TRUE     TRUE       TRUE
_db_file_exec_read_count                           32                                                                     TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      32                                                                     TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE

SQL> -- notice that _db_file_exec_read_count and _db_file_optimizer_read_count are now equal to each other
SQL>
SQL> -- let's try putting it back to 128
SQL>
SQL> alter system set db_file_multiblock_read_count=128;

System altered.

SQL> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y

NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    TRUE     TRUE       TRUE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      128                                                                    TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE

SQL> -- notice that db_file_multiblock_read_count still shows up as being Set
SQL> -- also notice that _db_file_exec_read_count and _db_file_optimizer_read_count are still equal to each other
SQL> -- let's try bouncing just to be sure
SQL>
SQL> startup force
ORACLE instance started.

Total System Global Area 3113586688 bytes
Fixed Size                  1316204 bytes
Variable Size            1409288852 bytes
Database Buffers         1694498816 bytes
Redo Buffers                8482816 bytes
Database mounted.
Database opened.
SQL> 
SQL> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y

NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    FALSE    FALSE      TRUE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      128                                                                    TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE

SQL> -- so Oracle still knows that db_file_multiblock_read_count has been set 
SQL> -- and while the db_file_multiblock_read_count is set to it's original default value, 
SQL> -- the hidden parameters that depend on it are not
SQL>
SQL> -- let's reset the parameter 
SQL> 
SQL> set echo on
SQL> @reset_parms
SQL> alter system reset ¶meter_name  scope=spfile sid='*';
Enter value for parameter_name: db_file_multiblock_read_count

System altered.

SQL> set echo off
SQL> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y

NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    FALSE    FALSE      TRUE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      128                                                                    TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE

SQL> -- have to bounce for the reset to take effect
SQL> 
SQL> startup force
ORACLE instance started.

Total System Global Area 3113586688 bytes
Fixed Size                  1316204 bytes
Variable Size            1409288852 bytes
Database Buffers         1694498816 bytes
Redo Buffers                8482816 bytes
Database mounted.
Database opened.
SQL> 
SQL> @parms
Enter value for parameter: read_count
Enter value for isset: 
Enter value for show_hidden: Y

NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
db_file_multiblock_read_count                      128                                                                    TRUE     FALSE      FALSE
_db_file_exec_read_count                           128                                                                    TRUE     FALSE      FALSE
_db_file_noncontig_mblock_read_count               11                                                                     TRUE     FALSE      FALSE
_db_file_optimizer_read_count                      8                                                                      TRUE     FALSE      FALSE
_sort_multiblock_read_count                        2                                                                      TRUE     FALSE      FALSE

SQL> -- note that Oracle no longer thinks the parameter has been set 
SQL> -- also note that the _db_file_optimizer_read_count parameter is back to it's default value of 8
SQL> 
SQL> exit

So the key point is that setting a parameter back to it’s original default value is not the same thing as unsetting it with the ALTER SYSTEM RESET command. One other thing worth mentioning is that if you try to unset a parameter that hasn’t been set to begin with you get the slightly unclear error message – “cannot find entry to delete in SPFILE”. This is due to the fact the spfile only contains entries for parameters that have been set and the ALTER SYSTEM RESET command actually deletes the entry for the specified parameter. So if the parameter has not been modified, an entry doesn’t exist in the spfile for it, and the message. Here’s an example:


> sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Nov 13 15:54:21 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> @parms
Enter value for parameter: optimizer_dynamic_sampling
Enter value for isset: 
Enter value for show_hidden: 

NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_dynamic_sampling                         1                                                                      TRUE     FALSE      FALSE

SQL> @reset_parms
Enter value for parameter_name: optimizer_dynamic_sampling
alter system reset optimizer_dynamic_sampling  scope=spfile sid='*'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

SQL> !oerr ora 32010
32010, 00000, "cannot find entry to delete in SPFILE"
// *Cause:  The SPFILE did not contain the sid.parameter entry.
// *Action: Change the sid and/or the parameter.

SQL> -- SP files only contain entries for parameters that have been changed, 
SQL> -- so if you try to change one that hasn't been set you get this error
SQL>
SQL> -- so let's change it to a non-default value and try again
SQL>
SQL> alter system set optimizer_dynamic_sampling=4;

System altered.

SQL> @parms
Enter value for parameter: optimizer_dynamic_sampling
Enter value for isset: 
Enter value for show_hidden: 

NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_dynamic_sampling                         4                                                                      TRUE     TRUE       TRUE

SQL> @reset_parms
Enter value for parameter_name: optimizer_dynamic_sampling

System altered.

One Comment

Leave a Reply