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:
- Try to use shared SPFILE in any conditions
- The value of the parameter will be changed always, whenever shared SPFILE is used or not.
- 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.
- In fact, every instance has its own SPFILE. If the SPFILE is shared, it will be read more then one time.
