Undocumented parameter _fix_control Or How to break your database
May 9th, 2008 - by Luke DaviesBeware 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!
Tags: invalid ALTER • ORA-00904 • ORA-904 • undocumented parameter • v$session_fix_control • v$system_fix_control • _fix_control
May 12th, 2008 at 10:28 am
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.