概述

在 Oracle 11g 安裝並建庫後，需要進行一些調整，使資料庫能夠穩定、高效地運行。花了一段時間整理了以下的參數設置規範，僅供參考。

一、建庫參數設置

1、進程及會話數(默認150)

--查看進程及會話數
show parameter process;
show parameter sessions;
--修改進程及會話數
alter system set processes=1200 scope=spfile; 
alter system set sessions=1325 scope=spfile; 

參考：sessions = 1.1 * processes + 5

2、游標數

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

3、字符集（建議建庫時就設置好）

--查詢資料庫服務端的字符集：NLS_LANG = language_territory.charset 
select * from nls_database_parameters;
--常見伺服器參數設置
export NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

4、sga pga大小

oltp系統參考（要設置好，防止內存抖動）： sga=內存*80%*80% pga=內存*80%*20%

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

說明：控制文件默認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 默認50M, 改成100M或更大，具體根據每天產生redo的量來設置

--查看相關設置
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每組默認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,具體看實際業務而定)

--設置undo_retention 參數，默認是900s ，推薦設置為設置為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表空間增加數據文件
alter tablespace UNDOTBS1 add datafile '/home/u01/app/oracle/oradata/ytzx/undotbs2.dbf' size 10240M autoextend on; 
--查看現在undo表空間大小 
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1'; 

8、臨時表空間(20~30G,具體看實際業務而定)

--查看臨時表空間大小、是否自動擴展
select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files; 
--增大臨時表空間文件的大小，把20M 調整成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、關於審計

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

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 默認會將 DEFAULT 的 PROFILE 設置登錄失敗嘗試次數（10 次）。這樣在無意或惡意的連續使用錯誤密碼連接時，導致資料庫用戶被鎖住，影響業務。因此需要將登錄失敗嘗試次數設為不限制。

12、關於維護任務

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

說明：關閉一些不需要的維護任務，這兩個屬於 ORACLE_OCM 的任務不關閉，可能會在 alert日誌中報錯。

13、其他

13.1、考慮是否要關閉自動統計信息收集（慎用，除非有其他手工收集統計信息的完整方案，否則不建議關閉）

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

說明：如果是需要採用手工收集統計信息策略，則關閉統計信息自動收集任務。

13.2、考慮是否要關閉自動收集直方圖

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

說明：為減少統計信息收集時間，同時為避免直方圖引起的 SQL 執行計劃不穩定，可以在資料庫全局級關閉自方圖的收集，對於部分需要收集直方圖的表列，可以使用

DBMS_STATS.SET_TABLE_PREFS 過程來設置。

13.3、關閉 auto space advisor

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

說明：關閉資料庫的空間 Advisor，避免消耗過多的 IO，還有避免出現這個任務引起的 library cache lock。

13.4、關閉 auto sql tuning

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

說明：關閉資料庫的 SQL 自動調整 Advisor，避免消耗過多的資源。

13.5、調整時間窗口：

--查詢窗口定義詳情--10g
select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
--查詢窗口定義詳情--11g和12c（周一到周五每晚10點開始收集統計信息，duration是4h；周六周日早上6點開始收集統計信息，duration是20h）
SQL> select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
--根據具體業務情況調整
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);

說明：一些業務系統即使在周末，也同樣處於正常的業務工作狀態，比如面向公眾的業務系統，在月底（雖然是周末）有批處理操作的系統，以及節假日調整的周末等，建議調整周六和周日窗口的起止時間和窗口時間長度，避免有時候周六或周日影響業務性能。

二、針對 RAC 資料庫的參數調整(隱含參數)

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

說明：這個參數是 11g 的新增參數，用於將並行的 slave 進程限制在發起並行 SQL 的會話所在的節點，即避免跨節點並行產生大量的節點間數據交換和引起性能問題。

這個參數用於取代 11g 之前 instance_groups 和 parallel_instance_group 參數設置。

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

說明：這兩個參數用於關閉 RAC 的 DRM（dynamic remastering）特性，避免頻繁的 DRM 使系統性能不穩定、嚴重的時候使資料庫掛起。同時也關閉 Read-mostly Locking 新特性，這個特性目前會觸發大量的 BUG，嚴重時使資料庫實例宕掉。

針對 11g RAC，需要注意的是如果節點的 CPU 數量不一樣，這可能導致推導出來的 lms 進程數量不一樣，根據多個案例的實踐來看，lms數量不一樣在高負載時會產生嚴重的性能問題，在此種情況下，需要手工設置 gcs_server_processes 參數，使 RAC 資料庫所有節點的 lms 進程數相同。

三、RAC 資料庫和非 RAC 資料庫都適用的參數調整（隱含參數）

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;

說明：這幾個參數都是用於關閉 11g 的 adaptive cursor sharing、cardinality feedback 特性，避免出現 SQL 性能不穩定、SQL 子游標過多的問題。

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

說明：這個參數用於關閉 11g 的段延遲創建特性，避免出現這個新特性引起的 BUG，比如數據導入導出 BUG、表空間刪除後對應的表對象還在數據字典裡面等。

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

說明：這個參數主要設置 2 個事件：

1） 10949 事件用於關閉 11g 的自動 serial direct path read 特性，避免出現過多的直接路徑讀，消耗過多的 IO 資源。

2） 28401 事件用於關閉 11g 資料庫中用戶持續輸入錯誤密碼時的延遲用戶驗證特性，避免用戶持續輸入錯誤密碼時產生大量的 row cache lock 或 library cache lock 等待，嚴重時使資料庫完全不能登錄。

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

說明：這兩個參數用於將資源管理計劃強制設置為「空」，避免 Oracle 自動打開維護窗口（每晚 22:00 到早上 6:00，周末全天）的資源計劃（resource manager plan），使系統在維護窗口期間資源不足或觸發相應的 BUG。

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

說明：關閉 UNDO 表空間的自動調整功能，避免出現 UNDO 表空間利用率過高或者是 UNDO段爭用的問題。

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

說明：關閉優化器的 null aware antijoin 特性，避免這個新特性帶來的 BUG。

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

說明：11g 資料庫中，並行會話默認使用的是 shared pool 用於並行執行時的消息緩衝區，並行過多時容易造成 shared pool 不足，使資料庫報 ORA-4031 錯誤。將這個參數設置為 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 裡面，新建分區會給一個比較大的初始 extent 大小（8M），如果一次性建的分區很多，比如按天建的分區，則初始占用的空間會很大。

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

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

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

說明：11.2.0.3 版本裡面，即使是手工管理內存方式下，如果某個 POOL 內存吃緊，Oracle仍然可能會自動調整內存，用這個參數來關閉這種行為。

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

說明：在 11g 裡面，打開這個參數可以將 ddl 語句記錄在 alert 日誌中。以便於某些故障的排查。建議在 OLTP 類系統中使用。

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

說明：這個參數默認值與 CPU 相關，OLTP 系統中將這個參數設置小一些，可以避免過多的並行對系統造成衝擊。

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

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

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

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

後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~