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. |
Kerry Osborne’s Oracle Blog » Blog Archive Oracle 10gR2 Autotuned DB_FILE_MULTIBLOCK_READ_COUNT:
[...] post on autotuned DBFMBRC [...]
January 13, 2010, 11:01 pm