Dangerous behavior of ‘create spfile’ in 12c RAC

These days I was testing some commands of the system parameters and the PFILE/SPFILE, and unfortunately, I encountered a really bad bug in my test environment.

My RAC DB is 12.1.0.2.0, and I did below tests about the 'create pfile/spfile' command:

The configuration of the database resource of test db:

[oracle@rac12-node1 OPatch]$ srvctl config database -db rac12
Database unique name: rac12
Database name: rac12
Oracle home: /u01/app/oracle/product/12.1.0
Oracle user: oracle
Spfile: +DATA/rac12/spfilerac12.ora
Password file: +DATA/RAC12/PASSWORD/pwdrac12.276.902472499
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: racpool
Disk Groups: DATA
Mount point paths: 
Services: racdb
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: dba
Database instances: 
Configured nodes: 
Database is policy managed

Please note the value of the parameter Spfile: +DATA/rac12/spfilerac12.ora.

SQL> create pfile='/tmp/ffile.ora' from spfile='+DATA/rac12/spfilerac12.ora';

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: +DATA/rac12/spfilerac12.ora

SQL> create spfile='/tmp/ffile.spfile' from pfile='/tmp/ffile.ora';

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/ffile.spfile

SQL> create pfile='/tmp/fmem.ora' from memory;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/ffile.spfile

SQL> create spfile='/tmp/fmem.spfile' from memory;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/fmem.spfile

SQL> create pfile from spfile='+DATA/rac12/spfilerac12.ora';

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/fmem.spfile

SQL> create spfile from pfile='/tmp/ffile.ora';

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: +DATA/spfilerac12_1.ora

SQL> create pfile='/tmp/ffile2.ora' from spfile;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: +DATA/spfilerac12_1.ora

SQL> create spfile='/tmp/ffile2.spfile' from pfile;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/ffile2.spfile

SQL> create pfile from spfile;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/ffile2.spfile

SQL> create spfile from pfile;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: +DATA/spfilerac12_1.ora

Find what? Yes, every time I run the 'create spfile', then it would update the configuration of the database resource.

So it was easy that sometimes you found the DBs were using different SPFILE:

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';

INSTANCE_NAME NAME      VALUE
---------------- -------------------- ----------------------------
rac12_3  spfile       +DATA/rac12/spfilerac12.ora
rac12_1  spfile       /tmp/ffile.spfile
rac12_4  spfile       +DATA/rac12/spfilerac12.ora

I had to say it was a really stupid action, and I found bug:

and got:

This bug is only relevant when using Real Application Clusters (RAC)

As of 12c creating an spfile also updates the spfile location in the cluster.
This is different to 11.2 behaviour and can affect scripts that create a 
local SPFILE that is not accessible to other RAC nodes.
 
Rediscovery Notes
 After an spfile is created, the spfile location is updated in the cluster.
 Other nodes may then be unable to access the new spfile.
 
Workaround
 None other than be sure to create SPFILE on a shared disk accessible to
 all nodes.
 
Note:
 This fix extends the CREATE SPFILE syntax to add an "AS COPY" option.
 If 'AS COPY' is specified the cluster wide spfile location is not updated.

I patched my test DB soon, and run the tests again:

SQL> create spfile='/tmp/aferpatch_ffile.spfile' from pfile='/tmp/ffile.ora';

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile.spfile

SQL> create spfile='/tmp/aferpatch_fmem.spfile' from memory;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile.spfile

SQL> create spfile='/tmp/aferpatch_ffile2.spfile' from pfile;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile2.spfile

SQL> create spfile from pfile='/tmp/ffile.ora';

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile2.spfile

SQL> create spfile from memory;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile2.spfile

SQL> create spfile from pfile;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile2.spfile

So I still could not specify the the path of the generated SPFILE.

Continued to test with 'as copy' option:

SQL> create spfile='/tmp/aferpatch_ffile.spfile' from pfile='/tmp/ffile.ora' as copy;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile2.spfile

SQL> create spfile='/tmp/aferpatch_ffile.spfile' from pfile as copy;

File created.

SQL>  !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile2.spfile

SQL> create spfile='/tmp/aferpatch_ffile.spfile' from memory as copy;
create spfile='/tmp/aferpatch_ffile.spfile' from memory as copy
                                                        *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> create spfile from pfile='/tmp/ffile.ora' as copy;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile2.spfile

SQL> create spfile from pfile as copy;

File created.

SQL> !srvctl config database -db rac12|grep -i 'spfile'
Spfile: /tmp/aferpatch_ffile2.spfile

SQL> create spfile from memory as copy;
create spfile from memory as copy
                          *
ERROR at line 1:
ORA-00933: SQL command not properly ended

So, in RAC DB environments, after version 12.1.0.2.0, I suggest:

1. Install the corresponding patch or the latest PSU.
2. Try to use the 'as copy' option for all your 'create spfile from pfile' command.