使用alter system set修改參數不生效

最近在用虛擬機上的Oracle RAC做實驗，看到后臺日志一直報錯，原因是配置的log_archive_dest_2參數指到了節點2，而節點2又沒有開機，所以導致一直有報錯。于是想這一參數是之前做實驗的時候修改的，現在不需要了，直接把這個參數重置一下就可以了，于是執行了下面的語句：

alter system reset log_archive_dest_2;

再使用show parameter查看參數已經被清空了：

SQL> show parameter log_archive_dest_2 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string

看alert日志中報錯也沒有了，以為這樣事情就結束了。但事實上還沒有結束。等下一次數據庫重啟后看到之前的報錯又出現了，而且log_archive_dest_2參數的設置又恢復到了重置之前的值：

SQL> show parameter log_archive_dest_2 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string	 service=rac2

難道是上次重置沒有成功嗎？

從上次的檢查結果來看重置是成功的，而且alert日志中也提示修改成功。

那為什么這么靈異的事情居然發生了呢？

嘗試再次重置：

SQL> alter system reset log_archive_dest_2;
alter system reset log_archive_dest_2
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

看到出現上面的報錯，是說spfile中沒有相應的條目可以刪除。

如果沒有條目我的設置又是從哪里來的呢？

于是生成pfile來查看參數文件中關于log_archive_dest_2具體是怎么設置的：

orcl2.log_archive_dest_1='location=/home/oracle/rac2'
orcl1.log_archive_dest_1='location=/home/oracle/rac1'
*.log_archive_dest_1='location=+data'
orcl2.log_archive_dest_2='service=rac1'
orcl1.log_archive_dest_2='service=rac2'

從上面可以看到針對每一個實例的log_archive_dest_2都做了設置，難道是這個原因么？

再次嘗試重置，這次語句如下：

alter system reset log_archive_dest_2 sid='orcl1';

再次生成pfile文件，看到orcl1.log_archive_dest_2='service=rac2'這條記錄已經沒有了，說明已經把配置清除了，重啟實例再次查看：

SQL> show parameter log_archive_dest_2 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2		     string

alert日志中也不再報錯了。

那么問題又來了，我第一次清除的又是什么配置呢？

有經驗的同學應該已經想到這個問題的原因了。我們繼續看下面的分析。

我們再回到第一次生成的spfile文件中可以看到如下配置，我們以log_archive_dest_1參數為例

orcl2.log_archive_dest_1='location=/home/oracle/rac2'
orcl1.log_archive_dest_1='location=/home/oracle/rac1'
*.log_archive_dest_1='location=+data'

對于log_archive_dest_1參數設置有兩類，一類是'*.'開頭的，一個是以'<sid>.'開頭的。'*.'開頭的設置是對所有與數據庫相關的且沒有設置該參數的實例有效，而以'<sid>.'開頭的設置只對設置對應的實例有效。

我這前是對兩個實例orcl1和orcl2的log_archive_dest_1分別設置了不同的值，而分別設置之前所有的實例的log_archive_dest_1參數值都是'location=+data'。

我們再來看alter system set|reset parameter的語法：

set parameter

parameter_name =
   parameter_value [, parameter_value ]...
   [ COMMENT = string ]
   [ DEFERRED ]
   [ { SCOPE = { MEMORY | SPFILE | BOTH }
     | SID = { 'sid' | '*' }
     }...
   ]

reset parameter 

parameter_name
   [ { SCOPE = SPFILE 
     | SID = { 'sid' | '*' }
     }...
   ]

從上面的語法可以看到，設置和重置參數都有一個可選項：SID = { 'sid' | '*' }，而默認的都是SID='*'。

這里應該就可以解釋我第一次重置log_archive_dest_2時為什么可以成功，而第二次沒有成功的原因：我第一次刪除了*.log_archive_dest_2='xxxx'這一條目，第一次再次刪除時文件中沒有了，所以報錯。

這也能解釋為什么重置時加上sid='orcl1'項后重啟依然生效。

還有一個問題就是如果參數文件中，對于同一個參數同時有'*.'開頭的和以'<sid>.'開頭的參數設置時，Oracle會選哪個做為實例的參數設置的，我們來看一句官方文檔中對SID的描述，就可以回答這個問題了：

SID The SID clause lets you specify the SID of the instance where the value will take effect.

• Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances that do not already have an explicit setting for this parameter.

• Specify SID = 'sid' if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = '*'.

If you do not specify this clause, then:

• If the instance was started up with a pfile (traditional plain-text initialization parameter file), then Oracle Database assumes the SID of the current instance.

• If the instance was started up with an spfile (server parameter file), then Oracle Database assumes SID = '*'.

小提示：可以使用show spparameter <parameter_name>查看spfile中對于參數的設置

sys@ORA11G>show spparameter log_archive_dest_1

SID	 NAME			       TYPE	   VALUE
-------- ----------------------------- ----------- ----------------------------
ora11g	 log_archive_dest_1	       string	   location=/home/oracle/dump
*	 log_archive_dest_1	       string	   location=/u01/

參考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2014.htm#i2061284