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.
