概述

在 Oracle 11g 安裝并建庫(kù)后，需要進(jìn)行一些調(diào)整，使數(shù)據(jù)庫(kù)能夠穩(wěn)定、高效地運(yùn)行。花了一段時(shí)間整理了以下的參數(shù)設(shè)置規(guī)范，僅供參考。

[[274359]]

一、建庫(kù)參數(shù)設(shè)置

1、進(jìn)程及會(huì)話(huà)數(shù)(默認(rèn)150)

--查看進(jìn)程及會(huì)話(huà)數(shù) 
show parameter process; 
show parameter sessions; 
--修改進(jìn)程及會(huì)話(huà)數(shù) 
alter system set processes=1200 scope=spfile;  
alter system set sessions=1325 scope=spfile;  

參考：sessions = 1.1 * processes + 5

2、游標(biāo)數(shù)

--查看游標(biāo)數(shù) 
show parameter open_cursors; 
--查看當(dāng)前打開(kāi)的游標(biāo)數(shù)目 
select count(*) from v$open_cursor; 
--修改最大游標(biāo)數(shù) 
alter system set open_cursors=1000 scope=both 

3、字符集(建議建庫(kù)時(shí)就設(shè)置好)

--查詢(xún)數(shù)據(jù)庫(kù)服務(wù)端的字符集：NLS_LANG = language_territory.charset  
select * from nls_database_parameters; 
--常見(jiàn)服務(wù)器參數(shù)設(shè)置 
export NLS_LANG=AMERICAN_AMERICA.UTF8 
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 

4、sga pga大小

oltp系統(tǒng)參考(要設(shè)置好，防止內(nèi)存抖動(dòng))： sga=內(nèi)存*80%*80% pga=內(nèi)存*80%*20%

show parameter memory; 
show parameter sga; 
show parameter pga; 
--如果要防止高并發(fā)情況下的內(nèi)存抖動(dòng),考慮固定內(nèi)存 
alter system set memory_target=4096m scope=spfile; 
alter system set memory_max_target=4096m scope=spfile;  
alter system set sga_target=3027m scope=spfile;  
alter system set sga_max_size=3027m scope=spfile; 
alter system set pga_aggregate_target=3027m scope=spfile;  
alter system set pga_aggregate_target=1024m scope=spfile; 

5、控制文件

說(shuō)明：控制文件默認(rèn)2個(gè)，建議增加一個(gè)在不同位置

show parameter control; 
alter system set control_files="/oracle/redolog/RFDB/controlfile/o1_mf_gjrb59v8_.ctl","/u01/app/oracle/product/11.2.0/db_1/dbs/control02.ctl" scope=spfile; 
shutdown immediate; 
!cp /oracle/redolog/RFDB/controlfile/o1_mf_gjrb59v8_.ctl /u01/app/oracle/product/11.2.0/db_1/dbs/control02.ctl  
startup;  
show parameter control; 

6、redo日志

redo 默認(rèn)50M, 改成100M或更大，具體根據(jù)每天產(chǎn)生redo的量來(lái)設(shè)置

--查看相關(guān)設(shè)置 
select member from v$logfile;  
select group#,members,bytes/1024/1024,status from v$log;  
--增加日志組 
alter database add logfile group 4 ('/home/u01/app/oracle/oradata/nwppdb/redo04.log') size 100M;  
alter database add logfile group 5 ('/home/u01/app/oracle/oradata/nwppdb/redo05.log') size 100M;  
alter database add logfile group 6 ('/home/u01/app/oracle/oradata/nwppdb/redo06.log') size 100M;  
--redo每組默認(rèn)3組每組一個(gè)成員，建議每組兩個(gè)成員以上  
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo04_2.log' to group 4;  
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo05_2.log' to group 5;  
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo06_2.log' to group 6;  
--切換 
alter system switch logfile;  
--刪除之前小的日志組  
alter database drop logfile group 1; 
alter database drop logfile group 2; 
alter database drop logfile group 3； 

7、undo(20~30G,具體看實(shí)際業(yè)務(wù)而定)

--設(shè)置undo_retention 參數(shù)，默認(rèn)是900s ，推薦設(shè)置為設(shè)置為10800，即3個(gè)小時(shí)  
show parameter undo_retention; 
alter system set undo_retention=10800 scope=spfile;  
--查看undo表空間大小  
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';  
--增大undo表空間大小 
alter database datafile '/home/u01/app/oracle/oradata/ytzx/undotbs01.dbf' resize 10240M; 
--給undo表空間增加數(shù)據(jù)文件 
alter tablespace UNDOTBS1 add datafile '/home/u01/app/oracle/oradata/ytzx/undotbs2.dbf' size 10240M autoextend on;  
--查看現(xiàn)在undo表空間大小  
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';  

8、臨時(shí)表空間(20~30G,具體看實(shí)際業(yè)務(wù)而定)

--查看臨時(shí)表空間大小、是否自動(dòng)擴(kuò)展 
select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files;  
--增大臨時(shí)表空間文件的大小，把20M 調(diào)整成10240 M  
 alter database tempfile '/u01/app/oracle/oradata/ytzx/temp01.dbf' resize 10240M autoextend on next 100M maxsize 10G;  
--增加臨時(shí)文件 
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ytzx/temp02.dbf' size 10240M aitpextemd pm mext 100M maxsize 10G;  

9、歸檔及閃回配置

--1、配置歸檔 
archive log list; 
select name,log_mode,open_mode from v$database; 
show parameter cluster; //RAC 
alter system set cluster_database=false scope=spfile; //RAC 
shutdown immediate; 
startup mount; 
alter database archivelog; 
alter system set cluster_database=true scope=spfile; //RAC 
shutdown immediate; 
startup; 
--2、配置閃回(11g不需要重啟) 
select flashback_on from V$database; 
show parameter recover; 
alter system set db_recovery_file_dest_size=1500m scope=both; 
alter system set db_recovery_file_dest='+dgrecovery' scope=both; 
alter system set cluster_database=false scope=spfile; //RAC 
shutdown immediate; 
startup mount; 
alter database flashback on; 
alter system set cluster_database=true scope=spfile; //RAC 
shutdown immediate; 
startup; 

10、關(guān)于審計(jì)

--考慮關(guān)閉審計(jì)（oracle 11g 默認(rèn)打開(kāi)審計(jì)） 
alter system set audit_trail=none sid='*' scope=spfile; 
--說(shuō)明：11g 默認(rèn)打開(kāi)數(shù)據(jù)庫(kù)審計(jì)，為了避免審計(jì)帶來(lái)的 SYSTEM 表空間的過(guò)多占用，可以關(guān)閉審計(jì)。 
--最近一年審計(jì)記錄 
select * from aud$ where substr(sysdate-NTIMESTAMP#,2,9)<360; 
--清理數(shù)據(jù)庫(kù)審計(jì) 
truncate table sys.aud$ reuse storage; 
alter table sys.aud$ deallocate unused keep 25000m;  
alter table sys.aud$ deallocate unused keep 20000m; 
alter table sys.aud$ deallocate unused keep 15000m; 
alter table sys.aud$ deallocate unused keep 10000m; 
alter table sys.aud$ deallocate unused keep 5000m; 
alter table sys.aud$ deallocate unused keep 2000m; 
alter table sys.aud$ deallocate unused keep 500m; 
alter table sys.aud$ deallocate unused keep 250m; 
alter table sys.aud$ deallocate unused keep 10m;  

11、關(guān)于密碼重用

alter profile "DEFAULT" limit PASSWORD_GRACE_TIME UNLIMITED; 
alter profile "DEFAULT" limit PASSWORD_LIFE_TIME UNLIMITED; 
alter profile "DEFAULT" limit PASSWORD_LOCK_TIME UNLIMITED; 
alter profile "DEFAULT" limit FAILED_LOGIN_ATTEMPTS UNLIMITED; 

說(shuō)明：11g 默認(rèn)會(huì)將 DEFAULT 的 PROFILE 設(shè)置登錄失敗嘗試次數(shù)(10 次)。這樣在無(wú)意或惡意的連續(xù)使用錯(cuò)誤密碼連接時(shí)，導(dǎo)致數(shù)據(jù)庫(kù)用戶(hù)被鎖住，影響業(yè)務(wù)。因此需要將登錄失敗嘗試次數(shù)設(shè)為不限制。

12、關(guān)于維護(hù)任務(wù)

exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_CONFIG_JOB' ); 
exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_STATS_CONFIG_JOB' ); 

說(shuō)明：關(guān)閉一些不需要的維護(hù)任務(wù)，這兩個(gè)屬于 ORACLE_OCM 的任務(wù)不關(guān)閉，可能會(huì)在 alert日志中報(bào)錯(cuò)。

13、其他

13.1、考慮是否要關(guān)閉自動(dòng)統(tǒng)計(jì)信息收集(慎用，除非有其他手工收集統(tǒng)計(jì)信息的完整方案，否則不建議關(guān)閉)

BEGIN 
DBMS_AUTO_TASK_ADMIN.DISABLE( 
client_name => 'auto optimizer stats collection', 
operation => NULL, 
window_name => NULL); 
END; 
/ 

說(shuō)明：如果是需要采用手工收集統(tǒng)計(jì)信息策略，則關(guān)閉統(tǒng)計(jì)信息自動(dòng)收集任務(wù)。

13.2、考慮是否要關(guān)閉自動(dòng)收集直方圖

exec DBMS_STATS.SET_GLOBAL_PREFS( 'method_opt','FOR ALL COLUMNS SIZE 1' ); 
或者 
exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' ); 

說(shuō)明：為減少統(tǒng)計(jì)信息收集時(shí)間，同時(shí)為避免直方圖引起的 SQL 執(zhí)行計(jì)劃不穩(wěn)定，可以在數(shù)據(jù)庫(kù)全局級(jí)關(guān)閉自方圖的收集，對(duì)于部分需要收集直方圖的表列，可以使用

DBMS_STATS.SET_TABLE_PREFS 過(guò)程來(lái)設(shè)置。

13.3、關(guān)閉 auto space advisor

BEGIN 
DBMS_AUTO_TASK_ADMIN.DISABLE( 
client_name => 'auto space advisor', 
operation => NULL, 
window_name => NULL); 
END; 
/ 

說(shuō)明：關(guān)閉數(shù)據(jù)庫(kù)的空間 Advisor，避免消耗過(guò)多的 IO，還有避免出現(xiàn)這個(gè)任務(wù)引起的 library cache lock。

13.4、關(guān)閉 auto sql tuning

BEGIN 
DBMS_AUTO_TASK_ADMIN.DISABLE( 
client_name => 'sql tuning advisor', 
operation => NULL, 
window_name => NULL); 
END; 
/ 

說(shuō)明：關(guān)閉數(shù)據(jù)庫(kù)的 SQL 自動(dòng)調(diào)整 Advisor，避免消耗過(guò)多的資源。

13.5、調(diào)整時(shí)間窗口：

--查詢(xún)窗口定義詳情--10g 
select window_name,repeat_interval,duration,enabled from dba_scheduler_windows; 
--查詢(xún)窗口定義詳情--11g和12c（周一到周五每晚10點(diǎn)開(kāi)始收集統(tǒng)計(jì)信息，duration是4h；周六周日早上6點(diǎn)開(kāi)始收集統(tǒng)計(jì)信息，duration是20h） 
SQL> select window_name,repeat_interval,duration,enabled from dba_scheduler_windows; 
--根據(jù)具體業(yè)務(wù)情況調(diào)整 
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0'); 
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0'); 
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'duration', '+000 08:00:00'); 
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'duration', '+000 08:00:00'); 
exec dbms_scheduler.disable('WEEKNIGHT_WINDOW', TRUE); 
exec dbms_scheduler.disable('WEEKEND_WINDOW', TRUE); 

說(shuō)明：一些業(yè)務(wù)系統(tǒng)即使在周末，也同樣處于正常的業(yè)務(wù)工作狀態(tài)，比如面向公眾的業(yè)務(wù)系統(tǒng)，在月底(雖然是周末)有批處理操作的系統(tǒng)，以及節(jié)假日調(diào)整的周末等，建議調(diào)整周六和周日窗口的起止時(shí)間和窗口時(shí)間長(zhǎng)度，避免有時(shí)候周六或周日影響業(yè)務(wù)性能。

二、針對(duì) RAC 數(shù)據(jù)庫(kù)的參數(shù)調(diào)整(隱含參數(shù))

alter system set parallel_force_local=true sid='*' scope=spfile; 

說(shuō)明：這個(gè)參數(shù)是 11g 的新增參數(shù)，用于將并行的 slave 進(jìn)程限制在發(fā)起并行 SQL 的會(huì)話(huà)所在的節(jié)點(diǎn)，即避免跨節(jié)點(diǎn)并行產(chǎn)生大量的節(jié)點(diǎn)間數(shù)據(jù)交換和引起性能問(wèn)題。

這個(gè)參數(shù)用于取代 11g 之前 instance_groups 和 parallel_instance_group 參數(shù)設(shè)置。

alter system set "_gc_policy_time"=0 sid='*' scope=spfile; 
alter system set "_gc_undo_affinity"=false scope=spfile; 

說(shuō)明：這兩個(gè)參數(shù)用于關(guān)閉 RAC 的 DRM(dynamic remastering)特性，避免頻繁的 DRM 使系統(tǒng)性能不穩(wěn)定、嚴(yán)重的時(shí)候使數(shù)據(jù)庫(kù)掛起。同時(shí)也關(guān)閉 Read-mostly Locking 新特性，這個(gè)特性目前會(huì)觸發(fā)大量的 BUG，嚴(yán)重時(shí)使數(shù)據(jù)庫(kù)實(shí)例宕掉。

針對(duì) 11g RAC，需要注意的是如果節(jié)點(diǎn)的 CPU 數(shù)量不一樣，這可能導(dǎo)致推導(dǎo)出來(lái)的 lms 進(jìn)程數(shù)量不一樣，根據(jù)多個(gè)案例的實(shí)踐來(lái)看，lms數(shù)量不一樣在高負(fù)載時(shí)會(huì)產(chǎn)生嚴(yán)重的性能問(wèn)題，在此種情況下，需要手工設(shè)置 gcs_server_processes 參數(shù)，使 RAC 數(shù)據(jù)庫(kù)所有節(jié)點(diǎn)的 lms 進(jìn)程數(shù)相同。

三、RAC 數(shù)據(jù)庫(kù)和非 RAC 數(shù)據(jù)庫(kù)都適用的參數(shù)調(diào)整(隱含參數(shù))

alter system set "_optimizer_adaptive_cursor_sharing"=false sid='*' scope=spfile; 
alter system set "_optimizer_extended_cursor_sharing"=none sid='*' scope=spfile; 
alter system set "_optimizer_extended_cursor_sharing_rel"=none sid='*' scope=spfile; 
alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile; 

說(shuō)明：這幾個(gè)參數(shù)都是用于關(guān)閉 11g 的 adaptive cursor sharing、cardinality feedback 特性，避免出現(xiàn) SQL 性能不穩(wěn)定、SQL 子游標(biāo)過(guò)多的問(wèn)題。

alter system set deferred_segment_creation=false sid='*' scope=spfile; 

說(shuō)明：這個(gè)參數(shù)用于關(guān)閉 11g 的段延遲創(chuàng)建特性，避免出現(xiàn)這個(gè)新特性引起的 BUG，比如數(shù)據(jù)導(dǎo)入導(dǎo)出 BUG、表空間刪除后對(duì)應(yīng)的表對(duì)象還在數(shù)據(jù)字典里面等。

alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1' sid='*' scope=spfile; 

說(shuō)明：這個(gè)參數(shù)主要設(shè)置 2 個(gè)事件：

1) 10949 事件用于關(guān)閉 11g 的自動(dòng) serial direct path read 特性，避免出現(xiàn)過(guò)多的直接路徑讀，消耗過(guò)多的 IO 資源。

2) 28401 事件用于關(guān)閉 11g 數(shù)據(jù)庫(kù)中用戶(hù)持續(xù)輸入錯(cuò)誤密碼時(shí)的延遲用戶(hù)驗(yàn)證特性，避免用戶(hù)持續(xù)輸入錯(cuò)誤密碼時(shí)產(chǎn)生大量的 row cache lock 或 library cache lock 等待，嚴(yán)重時(shí)使數(shù)據(jù)庫(kù)完全不能登錄。

alter system set resource_limit=true sid='*' scope=spfile; 
alter system set resource_manager_plan='force:' sid='*' scope=spfile; 

說(shuō)明：這兩個(gè)參數(shù)用于將資源管理計(jì)劃強(qiáng)制設(shè)置為“空”，避免 Oracle 自動(dòng)打開(kāi)維護(hù)窗口(每晚 22:00 到早上 6:00，周末全天)的資源計(jì)劃(resource manager plan)，使系統(tǒng)在維護(hù)窗口期間資源不足或觸發(fā)相應(yīng)的 BUG。

alter system set "_undo_autotune"=false sid='*' scope=spfile; 

說(shuō)明：關(guān)閉 UNDO 表空間的自動(dòng)調(diào)整功能，避免出現(xiàn) UNDO 表空間利用率過(guò)高或者是 UNDO段爭(zhēng)用的問(wèn)題。

alter system set "_optimizer_null_aware_antijoin"=false sid ='*' scope=spfile; 

說(shuō)明：關(guān)閉優(yōu)化器的 null aware antijoin 特性，避免這個(gè)新特性帶來(lái)的 BUG。

alter system set "_px_use_large_pool"=true sid ='*' scope=spfile; 

說(shuō)明：11g 數(shù)據(jù)庫(kù)中，并行會(huì)話(huà)默認(rèn)使用的是 shared pool 用于并行執(zhí)行時(shí)的消息緩沖區(qū)，并行過(guò)多時(shí)容易造成 shared pool 不足，使數(shù)據(jù)庫(kù)報(bào) ORA-4031 錯(cuò)誤。將這個(gè)參數(shù)設(shè)置為 true，使并行會(huì)話(huà)改為使用 large pool。

alter system set "_partition_large_extents"=false sid='*' scope=spfile; 
alter system set "_index_partition_large_extents"=false sid='*' scope=spfile; 

說(shuō)明：在 11g 里面，新建分區(qū)會(huì)給一個(gè)比較大的初始 extent 大小(8M)，如果一次性建的分區(qū)很多，比如按天建的分區(qū)，則初始占用的空間會(huì)很大。

alter system set "_use_adaptive_log_file_sync"=false sid='*' scope=spfile; 

說(shuō)明：11.2.0.3 版本里面，這個(gè)參數(shù)默認(rèn)為 true，LGWR 會(huì)自動(dòng)選擇兩種方法來(lái)通知其他進(jìn)程 commit 已經(jīng)寫(xiě)入：post/wait、polling。前者 LGWR 負(fù)擔(dān)較重，后者等待時(shí)間會(huì)過(guò)長(zhǎng)，特別是高負(fù)載的 OLTP 系統(tǒng)中。在 10g 及之前的版本中是 post/wait 方式，將這個(gè)參數(shù)設(shè)置為 false恢復(fù)到以前版本方式。

alter system set "_memory_imm_mode_without_autosga"=false sid='*' scope=spfile; 

說(shuō)明：11.2.0.3 版本里面，即使是手工管理內(nèi)存方式下，如果某個(gè) POOL 內(nèi)存吃緊，Oracle仍然可能會(huì)自動(dòng)調(diào)整內(nèi)存，用這個(gè)參數(shù)來(lái)關(guān)閉這種行為。

alter system set enable_ddl_logging=true sid='*' scope=spfile; 

說(shuō)明：在 11g 里面，打開(kāi)這個(gè)參數(shù)可以將 ddl 語(yǔ)句記錄在 alert 日志中。以便于某些故障的排查。建議在 OLTP 類(lèi)系統(tǒng)中使用。

alter system set parallel_max_servers=64 sid='*' scope=spfile; 

說(shuō)明：這個(gè)參數(shù)默認(rèn)值與 CPU 相關(guān)，OLTP 系統(tǒng)中將這個(gè)參數(shù)設(shè)置小一些，可以避免過(guò)多的并行對(duì)系統(tǒng)造成沖擊。

alter system set sec_case_sensitive_logon=false sid='*' scope=spfile; 

說(shuō)明：從 11g 開(kāi)始，用戶(hù)密碼區(qū)分大小寫(xiě)，而此前的版本則是不區(qū)分大小寫(xiě)，在升級(jí)時(shí)，如果這個(gè)參數(shù)保持默認(rèn)值 TRUE，可能會(huì)使一些應(yīng)用由于密碼不正確而連接不上。

alter system set "_b_tree_bitmap_plans"=false sid='*' scope=spfile; 

說(shuō)明：對(duì)于 OLTP 系統(tǒng)，Oracle 可能會(huì)將兩個(gè)索引上的 ACCESS PATH 得到的 rowid 進(jìn)行 bitmap操作再回表，這種操作有時(shí)邏輯讀很高，對(duì)于此類(lèi) SQL 使用復(fù)合索引才能從根本上解決問(wèn)題。