Beware this parameter can prevent your database from starting. Indeed it can prevent your instance from starting!
There are two dynamic views v$system_fix_control and v$session_fix_control which were introduced in 10.2 and control whether fixes for bugs in the optimizer can be turned on or off. This can also be controlled using the _fix_control initialization parameter.
If the parameter _fix_control is set incorrectly i.e. with invalid bug ids then, when starting the database, you may get the following error
$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 7 10:55:43 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup nomount ORA-00940: invalid ALTER command
There are no error messages in the alert log and the instance has refused to start.
And look at that helpful error message. No doubt behind the scenes it doing some sort of ALTER command but still …
If you get this error then check the pfile or spfile and see if there is some spurious _fix_control setting. This is especially valid for the upgrading of Oracle. I came across this error upgrading 10.2.0.2.0 to 10.2.0.3.0.
Also beware if setting this parameter in a running database. You can prevent further logins if you get it wrong or indeed try to unset it. Do not do
ALTER SYSTEM SET "_fix_control" = '';
This will require a bounce to fix. Once, of course, the parameter has been removed from the initialization files.
The moral of the story is be very careful with undocumented parameters when upgrading!
3 Comments. Leave new
I was curious as to weather unsetting the parameter via ‘alter system unset “_fix_control” would be possible or get around the having to bounce the instance?
You have got to love these parameters that have no type checking or validation. I suppose that is why oracle always puts such large caveats around the undocumented parameters.
Live and learn – I never heard about this parameter and those tables. Good to know. Thanks Luke!
Thank you for sharing.
The article indicates 2 problem.
If a bug fix number is invalid the database will not startup signaling an ORA-940
This was reported on bug 6756883.
Internally there is a registry service for the fixes.The issue is that if the bug was not registered and attempted to disable it then gives the error.
It is possible for an Oracle developer to forget to register the fix or not do registered on purpose so disabling it would not be possible and give ora-940.
If it is not registered by mistake it should be reported to oracle to fix it and once it is registered it should work for future versions too.
Some examples of bugs left out on purpose are wrong results bugs.
The second issue was that if no number was set the instance will become unusable.
This was reported on bug 6628797 and a fix is available.
Next time, I suggest to contact Oracle Support if something does not appear to function correctly so we can take care of it and then postings like this will provide a solution besides “don’t use it”.