Undocumented parameter _fix_control: How to break your database

by Luke Davies on May 9, 2008 12:00:00 AM

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!

No Comments Yet

Let us know what you think

See all

Share this

Undocumented parameter _fix_control: How to break your database

Share this

No Comments Yet

Subscribe by email