有哪些RAC环境中需要完整down库(Not rolling fashion)才能修改的参数，这个问题，在oracle的文档中也没有很好的说明。我们处理的时候，一般都是case by case。
之前队友们有过讨论，究竟哪些参数是需要完整down库的。
猜想一：ISINSTANCE_MODIFIABLE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
SQL> show parameter process NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ processes integer 150 SQL> alter system set processes=200 scope=spfile sid='ora11g1'; System altered. SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@rac1 ~]$ [oracle@rac1 ~]$ srvctl stop instance -d ora11g -n rac1 ; [oracle@rac1 ~]$ [oracle@rac1 ~]$ [oracle@rac1 ~]$ [oracle@rac1 ~]$ ps -ef |grep ora_ oracle 10751 9141 0 10:21 pts/1 00:00:00 grep ora_ [oracle@rac1 ~]$ [oracle@rac1 ~]$ [oracle@rac1 ~]$ srvctl start instance -d ora11g -n rac1 ; [oracle@rac1 ~]$ [oracle@rac1 ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Mon May 9 10:27:24 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter process NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ processes integer 200 processor_group_name string SQL> SQL> select inst_id,NAME,VALUE,ISINSTANCE_MODIFIABLE from gv$parameter where name='processes'; INST_ID NAME VALUE ISINS ---------- -------------------- ------------------------------ ----- 1 processes 200 FALSE 2 processes 150 FALSE SQL> |
结论，并不是所有ISINSTANCE_MODIFIABLE=FALSE的参数都需要完整down库才能修改。不能完全参考这个标准。
猜想二：在线文档中提到的参数。但是抽查几个参数，也并不是所有的这些参数需要完整down库才能修改。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
--(1)测试db_domain： SQL> alter system set db_domain='mydomain' scope=spfile sid='ora11g2'; [oracle@rac2 ~]$ srvctl stop instance -d ora11g -n rac2 [oracle@rac2 ~]$ srvctl start instance -d ora11g -n rac2 [oracle@rac2 ~]$ SQL> SQL> l 1* select inst_id,NAME,VALUE,ISINSTANCE_MODIFIABLE from gv$parameter where name='db_domain' order by 1 SQL> / INST_ID NAME VALUE ISINS ---------- -------------------- ------------------------------ ----- 1 db_domain FALSE 2 db_domain mydomain FALSE SQL> --(2)测试parallel_max_servers： SQL> alter system set parallel_max_servers=100 scope=spfile sid='ora11g2'; System altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@rac2 ~]$ [oracle@rac2 ~]$ [oracle@rac2 ~]$ srvctl stop instance -d ora11g -n rac2 [oracle@rac2 ~]$ srvctl start instance -d ora11g -n rac2 SQL> l 1* select inst_id,NAME,VALUE,ISINSTANCE_MODIFIABLE from gv$parameter where name='parallel_max_servers' order by 1 SQL> / INST_ID NAME VALUE ISINS ---------- -------------------- ------------------------------ ----- 1 parallel_max_servers 80 TRUE 2 parallel_max_servers 100 TRUE SQL> |
结论，也并不是所有Parameters that Must Have Identical Settings on All Instances的参数都需要完整down库才能修改。也不能完全参考这个标准。
我们在如下这个文档中看到有相关的说法（但也不是指出了所有需要完全down库的参数，只是列举了几个）：How to change the DB_FILES parameter in RAC (Doc ID 1636681.1)
|
1 2 3 4 5 6 |
Certain INIT parameters must have the same value across all active instances. For example, these include but are not restricted to, the following parameters: ACTIVE_INSTANCE_COUNT, CLUSTER_DATABASE_INSTANCES, COMPATIBLE, CONTROL_FILES, DB_FILES, DB_NAME, DB_UNIQUE_NAME, UNDO_MANAGEMENT None of these parameters can be changed in a RAC rolling fashion. |
另外，我们禁用DRM的时候，涉及到如下参数：
|
1 2 3 4 5 6 7 |
Disable DRM： (1) _gc_affinity_time=0（10g）/_gc_policy_time=0（11g） 注：_gc_affinity_time has been rename to _gc_policy_time in 11g (2) _gc_undo_affinity =FALSE |
当使用上述2个参数，禁用DRM的时候，也是需要完整down库的。
综上，case by case，我们总结目前RAC环境中已知的需要完全down的参数有（非完整列表，如果还有其他的，欢迎补充）:
|
1 2 3 4 5 6 7 8 9 10 |
ACTIVE_INSTANCE_COUNT CLUSTER_DATABASE_INSTANCES COMPATIBLE CONTROL_FILES DB_FILES DB_NAME DB_UNIQUE_NAME UNDO_MANAGEMENT _gc_affinity_time=0（10g）/_gc_policy_time=0（11g） _gc_undo_affinity |