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:
Bug 18799993 CREATE SPFILE updates the DB resource by default as of 12.1
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 endedSo, in RAC DB environments, after version 12.1.0.2.0, I suggest:
- Install the corresponding patch or the latest PSU.
- Try to use the 'as copy' option for all your 'create spfile from pfile' command.
