Confused about the Oracle CONTROL_FILES initialization parameter

oracle

This is the explanation of this parameter from Oracle. This is the description of the description in the explanation. From the documentation we know that this parameter is not modifiable (Modifiable: No). But in fact I can modify it:

sys@ORCL>alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl';
alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


sys@ORCL>alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' scope = spfile;

System altered.

sys@ORCL>

So, what does it mean by Modifiable: No in the documentation?

BTW, this is my Oracle version:

sys@ORCL>select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

sys@ORCL>

Thanks,

Best Answer

When you see Modifiable you should read MEMORY Modifiable. For ALTER SYSTEM then this would mean can the parameter be modified in memory for the system. The Database Reference document you site includes part of this information:

Modifiable Specifies whether the parameter can be changed for the current session (by an ALTER SESSION statement) or for all sessions in the current instance (by an ALTER SYSTEM statement):

ALTER SYSTEM can be used to change the value in the server parameter file (SPFILE) of any initialization parameter. Such a change takes effect only in subsequent instances. The parameter descriptions indicate only those parameters that can be modified for the current instance.

To understand what will happen in entirety you have to understand the SCOPE parameter and what happens when no SCOPE is specified. From the SQL Language Reference we can see that the ALTER SYSTEM statement can have three different SCOPE specifiers or none at all.

SCOPE The SCOPE clause lets you specify when the change takes effect. Scope depends on whether you started up the database using a traditional plain-text parameter file (pfile) or server parameter file (spfile).

MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.

SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference.

BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

So, since you were able to specify a scope of SPFILE, we can infer that you are using an SPFILE, so the default for the first ALTER SYSTEM when you did not specify a scope was BOTH which would fail because the parameter is not MEMORY modifiable. You can prove this by repeating your first ALTER SYSTEM and adding SCOPE=BOTH or SCOPE=MEMORY both of which will also fail.

Related Question