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:
- Oracle does a lot of things automatically (if we don’t prevent it)
- Some of the automatic things are good
- 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.
[…] post on autotuned DBFMBRC […]