Dangerous behavior of ‘create spfile’ in 12c RAC

NeilZhang
NeilZhang
管理员
140
文章
106.8千
浏览
Oracle评论1,311字数 1032阅读3分26秒阅读模式

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:

  1. [oracle@rac12-node1 OPatch]$ srvctl config database -db rac12
  2. Database unique name: rac12
  3. Database name: rac12
  4. Oracle home: /u01/app/oracle/product/12.1.0
  5. Oracle user: oracle
  6. Spfile: +DATA/rac12/spfilerac12.ora
  7. Password file: +DATA/RAC12/PASSWORD/pwdrac12.276.902472499
  8. Domain: 
  9. Start options: open
  10. Stop options: immediate
  11. Database role: PRIMARY
  12. Management policy: AUTOMATIC
  13. Server pools: racpool
  14. Disk Groups: DATA
  15. Mount point paths: 
  16. Services: racdb
  17. Type: RAC
  18. Start concurrency: 
  19. Stop concurrency: 
  20. OSDBA group: dba
  21. OSOPER group: dba
  22. Database instances: 
  23. Configured nodes: 
  24. Database is policy managed

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

  1. SQL> create pfile='/tmp/ffile.ora' from spfile='+DATA/rac12/spfilerac12.ora';
  2.  
  3. File created.
  4.  
  5. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  6. Spfile: +DATA/rac12/spfilerac12.ora
  7.  
  8. SQL> create spfile='/tmp/ffile.spfile' from pfile='/tmp/ffile.ora';
  9.  
  10. File created.
  11.  
  12. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  13. Spfile: /tmp/ffile.spfile
  14.  
  15. SQL> create pfile='/tmp/fmem.ora' from memory;
  16.  
  17. File created.
  18.  
  19. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  20. Spfile: /tmp/ffile.spfile
  21.  
  22. SQL> create spfile='/tmp/fmem.spfile' from memory;
  23.  
  24. File created.
  25.  
  26. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  27. Spfile: /tmp/fmem.spfile
  28.  
  29. SQL> create pfile from spfile='+DATA/rac12/spfilerac12.ora';
  30.  
  31. File created.
  32.  
  33. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  34. Spfile: /tmp/fmem.spfile
  35.  
  36. SQL> create spfile from pfile='/tmp/ffile.ora';
  37.  
  38. File created.
  39.  
  40. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  41. Spfile: +DATA/spfilerac12_1.ora
  42.  
  43. SQL> create pfile='/tmp/ffile2.ora' from spfile;
  44.  
  45. File created.
  46.  
  47. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  48. Spfile: +DATA/spfilerac12_1.ora
  49.  
  50. SQL> create spfile='/tmp/ffile2.spfile' from pfile;
  51.  
  52. File created.
  53.  
  54. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  55. Spfile: /tmp/ffile2.spfile
  56.  
  57. SQL> create pfile from spfile;
  58.  
  59. File created.
  60.  
  61. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  62. Spfile: /tmp/ffile2.spfile
  63.  
  64. SQL> create spfile from pfile;
  65.  
  66. File created.
  67.  
  68. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  69. 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:

  1. SQL> select INSTANCE_NAME,NAME,VALUE from gv$parameter gp, gv$instance gi
  2.   2  where gp.INST_ID=gi.INST_ID and gp.name='spfile';
  3.  
  4. INSTANCE_NAME NAME      VALUE
  5. ---------------- -------------------- ----------------------------
  6. rac12_3  spfile       +DATA/rac12/spfilerac12.ora
  7. rac12_1  spfile       /tmp/ffile.spfile
  8. 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:

  1. This bug is only relevant when using Real Application Clusters (RAC)
  2.  
  3. As of 12c creating an spfile also updates the spfile location in the cluster.
  4. This is different to 11.2 behaviour and can affect scripts that create a 
  5. local SPFILE that is not accessible to other RAC nodes.
  6.  
  7. Rediscovery Notes
  8.  After an spfile is created, the spfile location is updated in the cluster.
  9.  Other nodes may then be unable to access the new spfile.
  10.  
  11. Workaround
  12.  None other than be sure to create SPFILE on a shared disk accessible to
  13.  all nodes.
  14.  
  15. Note:
  16.  This fix extends the CREATE SPFILE syntax to add an "AS COPY" option.
  17.  If 'AS COPY' is specified the cluster wide spfile location is not updated.

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

  1. SQL> create spfile='/tmp/aferpatch_ffile.spfile' from pfile='/tmp/ffile.ora';
  2.  
  3. File created.
  4.  
  5. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  6. Spfile: /tmp/aferpatch_ffile.spfile
  7.  
  8. SQL> create spfile='/tmp/aferpatch_fmem.spfile' from memory;
  9.  
  10. File created.
  11.  
  12. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  13. Spfile: /tmp/aferpatch_ffile.spfile
  14.  
  15. SQL> create spfile='/tmp/aferpatch_ffile2.spfile' from pfile;
  16.  
  17. File created.
  18.  
  19. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  20. Spfile: /tmp/aferpatch_ffile2.spfile
  21.  
  22. SQL> create spfile from pfile='/tmp/ffile.ora';
  23.  
  24. File created.
  25.  
  26. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  27. Spfile: /tmp/aferpatch_ffile2.spfile
  28.  
  29. SQL> create spfile from memory;
  30.  
  31. File created.
  32.  
  33. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  34. Spfile: /tmp/aferpatch_ffile2.spfile
  35.  
  36. SQL> create spfile from pfile;
  37.  
  38. File created.
  39.  
  40. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  41. 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:

  1. SQL> create spfile='/tmp/aferpatch_ffile.spfile' from pfile='/tmp/ffile.ora' as copy;
  2.  
  3. File created.
  4.  
  5. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  6. Spfile: /tmp/aferpatch_ffile2.spfile
  7.  
  8. SQL> create spfile='/tmp/aferpatch_ffile.spfile' from pfile as copy;
  9.  
  10. File created.
  11.  
  12. SQL>  !srvctl config database -db rac12|grep -i 'spfile'
  13. Spfile: /tmp/aferpatch_ffile2.spfile
  14.  
  15. SQL> create spfile='/tmp/aferpatch_ffile.spfile' from memory as copy;
  16. create spfile='/tmp/aferpatch_ffile.spfile' from memory as copy
  17.                                                         *
  18. ERROR at line 1:
  19. ORA-00933: SQL command not properly ended
  20.  
  21.  
  22. SQL> create spfile from pfile='/tmp/ffile.ora' as copy;
  23.  
  24. File created.
  25.  
  26. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  27. Spfile: /tmp/aferpatch_ffile2.spfile
  28.  
  29. SQL> create spfile from pfile as copy;
  30.  
  31. File created.
  32.  
  33. SQL> !srvctl config database -db rac12|grep -i 'spfile'
  34. Spfile: /tmp/aferpatch_ffile2.spfile
  35.  
  36. SQL> create spfile from memory as copy;
  37. create spfile from memory as copy
  38.                           *
  39. ERROR at line 1:
  40. 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.

 

 
  • 本文由 NeilZhang 发表于11/04/2016 23:52:12
  • Repost please keep this link: https://www.dbcloudsvc.com/blogs/oracle/dangerous-behavior-of-create-spfile-in-12c-rac/
Could I ignore the SID clause in command 'alter system'?

加载中...

匿名

发表评论

匿名网友
:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:
确定