Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Category Archives: Solutions

instance_mode parameter in 12.2

I noticed some interesting behavior with instance_mode parameter in 12.2. It is new in 12.2 and described in documentation. It is related to new concept of Read-Write and Read-Only instances. Read-Only instances is the part of the Reader Nodes concept of the Flex Cluster in 12.2. Leaf Nodes can be defined as Reader Nodes and now it is also possible to create Local  Temporary Tablespace for these Reader Nodes. All these designed mostly to optimize parallel query operations.

But, the reason why I created this small article is just to describe strange behavior related to instance_mode parameter.

In the new two Instance RAC database we found that instance_mode is set to read-only in spfile, but it was actually read-write on instance level:

show spparameter instance_mode

SID	 NAME			       TYPE	   VALUE
-------- ----------------------------- ----------- ----------------------------
*	 instance_mode		       string      read-only

show parameter instance_mode

---------------------------------------- ----------- ---------------------------
instance_mode				 string      READ-WRITE

Select from v$spparameter:

select family, sid, value, isspecified, update_comment from v$spparameter where name='instance_mode';

----------  ---------  ------------  ------  --------------
dw_helper	 *         read-only     TRUE

ISSPECIFIED shows that this parameter was specified in spfile, but we didn’t done it. There is also new FAMILY filed which is reserved for internal use, it shows value “dw_helper”, only Oracle knows what it means :).

I created pfile from spfile just to be sure that it is really set in it, and I found next line in pfile:


It is here, set by some process. But on instance startup it is not in effect, it is not even listed in alert.log of the database.

Any how I wanted to clear it from spfile, I tried to use alter system reset command but without success, it was still there. Then I tried this construction and it worked:

alter system reset instance_mode family='dw_helper' scope=spfile;




Aychin Gasimov, 10/2017, Munich, Germany