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 Responses to “Undocumented parameter _fix_control: How to break your database”
Leave a Reply