SPFILE in RAC environments

  • A+
Categories:Oracle

Shared SPFILE is recommended strongly in RAC environment, and I want to tell you the reason in this post.

The test environment is Oracle 12c 12.1.0.2, and the installed PSU is 21948354.

SQL> select PATCH_ID,PATCH_UID,BUNDLE_SERIES,BUNDLE_ID,STATUS from dba_registry_sqlpatch;

  PATCH_ID  PATCH_UID BUNDLE_SERIES                   BUNDLE_ID STATUS
---------- ---------- ------------------------------ ---------- ---------------
  21948354   19553095 PSU                                160119 SUCCESS

To show the difference, I used a non-shared SPFILE in the node rac12-node1 intentionally:

SQL> select INSTANCE_NAME,NAME,VALUE from gv$parameter gp, gv$instance gi
  2  where gp.INST_ID=gi.INST_ID and gp.name='spfile' order by instance_name;

INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- -----------------------------------
rac12_1          spfile               /tmp/aferpatch_ffile2.spfile
rac12_2          spfile               +DATA/rac12/spfilerac12.ora
rac12_3          spfile               +DATA/rac12/spfilerac12.ora

The parameter open_cursors is used during this test, and let me check its value in the DB and SPFILE before the test:

SQL> select INSTANCE_NAME,NAME,VALUE from gv$parameter gp, gv$instance gi
  2  where gp.INST_ID=gi.INST_ID and gp.name='open_cursors' order by instance_name;

INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- -----------------------------------
rac12_1          open_cursors         300
rac12_2          open_cursors         300
rac12_3          open_cursors         300

SQL> select INSTANCE_NAME,gsp.SID, NAME,VALUE from gv$spparameter gsp, gv$instance gi
  2  where gsp.INST_ID=gi.INST_ID and gsp.name='open_cursors' order by instance_name;

INSTANCE_NAME    SID             NAME                 VALUE
---------------- --------------- -------------------- ------------------------------
rac12_1          *               open_cursors         300
rac12_2          *               open_cursors         300
rac12_3          *               open_cursors         300

Usually we change parameter using sid='*', and what will happen in such situation?

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- -----------------------------------
rac12_1          rac12-node1.lunixdb.com

SQL> alter system set open_cursors=800;

System altered.

SQL> select INSTANCE_NAME,NAME,VALUE from gv$parameter gp, gv$instance gi
  2  where gp.INST_ID=gi.INST_ID and gp.name='open_cursors' order by instance_name;

INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- ---------------------------------
rac12_1          open_cursors         800
rac12_2          open_cursors         800
rac12_3          open_cursors         800

SQL> select INSTANCE_NAME,gsp.SID, NAME,VALUE from gv$spparameter gsp, gv$instance gi
  2  where gsp.INST_ID=gi.INST_ID and gsp.name='open_cursors' order by instance_name;

INSTANCE_NAME    SID        NAME                 VALUE
---------------- ---------- -------------------- ---------------------------------
rac12_1          *          open_cursors         800
rac12_2          *          open_cursors         300
rac12_3          *          open_cursors         300

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- -----------------------------------
rac12_2          rac12-node2.lunixdb.com

SQL> alter system set open_cursors=900;

System altered.

SQL> select INSTANCE_NAME,NAME,VALUE from gv$parameter gp, gv$instance gi
  2  where gp.INST_ID=gi.INST_ID and gp.name='open_cursors' order by instance_name;

INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- ---------------------------------
rac12_1          open_cursors         900
rac12_2          open_cursors         900
rac12_3          open_cursors         900

SQL> select INSTANCE_NAME,gsp.SID, NAME,VALUE from gv$spparameter gsp, gv$instance gi
  2  where gsp.INST_ID=gi.INST_ID and gsp.name='open_cursors' order by instance_name;

INSTANCE_NAME    SID        NAME                 VALUE
---------------- ---------- -------------------- ---------------------------------
rac12_1          *          open_cursors         800
rac12_2          *          open_cursors         900
rac12_3          *          open_cursors         900

If we change one specific sid, then what we will get?

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- -----------------------------------
rac12_1          rac12-node1.lunixdb.com

SQL> alter system set open_cursors=700 sid='rac12_3';

System altered.

SQL> select INSTANCE_NAME,NAME,VALUE from gv$parameter gp, gv$instance gi
  2  where gp.INST_ID=gi.INST_ID and gp.name='open_cursors' order by instance_name;

INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- ---------------------------------
rac12_1          open_cursors         900
rac12_2          open_cursors         900
rac12_3          open_cursors         700

SQL> select INSTANCE_NAME,gsp.SID, NAME,VALUE from gv$spparameter gsp, gv$instance gi
  2  where gsp.INST_ID=gi.INST_ID and gsp.name='open_cursors' order by instance_name;

INSTANCE_NAME    SID        NAME                 VALUE
---------------- ---------- -------------------- ---------------------------------
rac12_1          rac12_3    open_cursors         700
rac12_1          *          open_cursors         800
rac12_2          *          open_cursors         900
rac12_3          *          open_cursors         900

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- -----------------------------------
rac12_2          rac12-node2.lunixdb.com

SQL> alter system set open_cursors=600 sid='rac12_3';

System altered.

SQL> select INSTANCE_NAME,NAME,VALUE from gv$parameter gp, gv$instance gi
  2  where gp.INST_ID=gi.INST_ID and gp.name='open_cursors' order by instance_name;

INSTANCE_NAME    NAME                 VALUE
---------------- -------------------- ---------------------------------
rac12_1          open_cursors         900
rac12_2          open_cursors         900
rac12_3          open_cursors         600

SQL> select INSTANCE_NAME,gsp.SID, NAME,VALUE from gv$spparameter gsp, gv$instance gi
  2  where gsp.INST_ID=gi.INST_ID and gsp.name='open_cursors' order by instance_name;

INSTANCE_NAME    SID        NAME                 VALUE
---------------- ---------- -------------------- ---------------------------------
rac12_1          rac12_3    open_cursors         700
rac12_1          *          open_cursors         800
rac12_2          *          open_cursors         900
rac12_2          rac12_3    open_cursors         600
rac12_3          *          open_cursors         900
rac12_3          rac12_3    open_cursors         600

6 rows selected.

Do you find the difference?

Yes, seems the result is confusing, and let me summary the result as below:

  1. Try to use shared SPFILE in any conditions
  2. The value of the parameter will be changed always, whenever shared SPFILE is used or not.
  3. The change of the SPFILE will ONLY happen in the local SPFILE. So if the SPFILE is not shared, then other instances cannot find the changed value.
  4. In fact, every instance has its own SPFILE. If the SPFILE is shared, it will be read more then one time.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: