概述

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

[[274359]]

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

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

--查看進(jìn)程及會話數(shù) 
show parameter process; 
show parameter sessions; 
--修改進(jìn)程及會話數(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)前打開的游標(biāo)數(shù)目 
select count(*) from v$open_cursor; 
--修改最大游標(biāo)數(shù) 
alter system set open_cursors=1000 scope=both 

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

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

4、sga pga大小

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

show parameter memory; 
show parameter sga; 
show parameter pga; 
--如果要防止高并發(fā)情況下的內(nèi)存抖動,考慮固定內(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、控制文件

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

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的量來設(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組每組一個成員，建議每組兩個成員以上  
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,具體看實際業(yè)務(wù)而定)

--設(shè)置undo_retention 參數(shù)，默認(rèn)是900s ，推薦設(shè)置為設(shè)置為10800，即3個小時  
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、臨時表空間(20~30G,具體看實際業(yè)務(wù)而定)

--查看臨時表空間大小、是否自動擴(kuò)展 
select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files;  
--增大臨時表空間文件的大小，把20M 調(diào)整成10240 M  
 alter database tempfile '/u01/app/oracle/oradata/ytzx/temp01.dbf' resize 10240M autoextend on next 100M maxsize 10G;  
--增加臨時文件 
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)于審計

--考慮關(guān)閉審計（oracle 11g 默認(rèn)打開審計） 
alter system set audit_trail=none sid='*' scope=spfile; 
--說明：11g 默認(rèn)打開數(shù)據(jù)庫審計，為了避免審計帶來的 SYSTEM 表空間的過多占用，可以關(guān)閉審計。 
--最近一年審計記錄 
select * from aud$ where substr(sysdate-NTIMESTAMP#,2,9)<360; 
--清理數(shù)據(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; 

說明：11g 默認(rèn)會將 DEFAULT 的 PROFILE 設(shè)置登錄失敗嘗試次數(shù)(10 次)。這樣在無意或惡意的連續(xù)使用錯誤密碼連接時，導(dǎo)致數(shù)據(jù)庫用戶被鎖住，影響業(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' ); 

說明：關(guān)閉一些不需要的維護(hù)任務(wù)，這兩個屬于 ORACLE_OCM 的任務(wù)不關(guān)閉，可能會在 alert日志中報錯。

13、其他

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

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

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

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

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' ); 

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

DBMS_STATS.SET_TABLE_PREFS 過程來設(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; 
/ 

說明：關(guān)閉數(shù)據(jù)庫的空間 Advisor，避免消耗過多的 IO，還有避免出現(xiàn)這個任務(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; 
/ 

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

 

 

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

--查詢窗口定義詳情--10g 
select window_name,repeat_interval,duration,enabled from dba_scheduler_windows; 
--查詢窗口定義詳情--11g和12c（周一到周五每晚10點開始收集統(tǒng)計信息，duration是4h；周六周日早上6點開始收集統(tǒng)計信息，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); 

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

 

 

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

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

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

這個參數(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; 

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

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

三、RAC 數(shù)據(jù)庫和非 RAC 數(shù)據(jù)庫都適用的參數(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; 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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