oracle 判断为最大值_Oracle系统参数/DB参数优化脚本

详细介绍

• 本脚本主要Oracle 11gR2 + centos 7.6 系统进行参数优化，包含系统参数和DB参数

  

• 主要功能：
  1、内存计算
  2、大页计算
  3、SGA预分配计算
  4、PGA预分配计算

优化脚本化

#!/bin/bash

# 系统参数优化

# 判断用户

CURUSER=$(whoami)

if [ $CURUSER != "root" ]; then

    echo "Error: Current osUser is $CURUSER , Please su - root "

    exit

fi

NOW=$(date "+%Y-%m-%d %H:%M:%S")

NOWFILE=$(date "+%Y-%m-%d_%H_%M_%S")

MEMKB=$(cat /proc/meminfo | grep MemTotal | awk '{print $2}')

MEMBYTE=$(expr $MEMKB \* 1024)

SHMALL=$(expr $MEMKB / 4)

SHMMAX=$(expr $MEMKB \* 1024 - 1)

# 计算SGA大小,单位M

SGAFLOAT=$(bc <

$MEMKB / 1024 * 0.4

EOF

)

SGA=${SGAFLOAT%%.*}

# 计算PGA大小,单位M

PGAFLOAT=$(bc <

$SGA * 0.1

EOF

)

PGA=${PGAFLOAT%%.*}

# 大页计算，sga大小，单位GB，必需要小于hugepages，sga*1024/2+1024

HUGESIZE=$(expr $SGA / 2 + 1024)

if (( $MEMKB >= 16777216 )); then

    PAGESIZE="vm.nr_hugepages = ${HUGESIZE}"

else

    PAGESIZE=""

fi

# 备份参sysctl.conf参数文件

cp -r /etc/sysctl.conf /etc/sysctl.conf.${NOWFILE}

echo "

# >>>>>>>>>>>>>>>>>>>>>>>> 内核参数配置 by enmotech ${NOW} >>>>>>>>>>>>>>>>>>>>>>>>

# vm参数优化

# 单个进程的脏页数量达到系统总内存的多大比例后，就会触发pdflush/flush/kdmflush等后台回写进程运行

vm.dirty_ratio=20

# 所有全局系统进程的脏页数量达到系统总内存的多大比例后，就会触发pdflush/flush/kdmflush等后台回写进程运行

vm.dirty_background_ratio=3

# 单位:1/100秒；表示每1秒pdflush会被唤起去刷新脏数据，如果刷新脏数据的时间超过了这个时间，完成后将会sleep1秒

vm.dirty_writeback_centisecs=100

# 单位:1/100秒；指定脏数据能存活的时间，超过该时间就会从内存中写入磁盘

vm.dirty_expire_centisecs=500

# 表示内存使用率超过90%时开始出现有交换分区的使用

vm.swappiness=10

# 最低保留多少内存，设置过高会触发kswapd0进程消耗大量 CPU一般512M即可

vm.min_free_kbytes=524288

# oracle需要的内核参数优化

# 此参数限制并发未完成的请求，应该设置避免I/O子系统故障

fs.aio-max-nr = 1048576

# 该参数决定了系统中所允许的文件句柄最大数目，文件句柄设置代表linux系统中可以打开的文件的数量,fs.file-max为512 乘以 processes

fs.file-max = 6815744

# kernel.shmall 参数是控制共享内存页数。该参数大小为物理内存除以pagesize, getconf PAGE_SIZE

kernel.shmall = $SHMALL

# 定义单个共享内存段的最大值，设置应该足够大，能在一个共享内存段下容纳下整个的SGA，设置过低可能会导致需要创建多个共享内存段)

# 建议值:取内存大小减1

kernel.shmmax = $SHMMAX

# shmmni 内核参数是共享内存段的最大数量,缺省值 4096 ，一般肯定是够用了

kernel.shmmni = 4096

# 信号集容纳最大信号数量 所有信号的最大数量 调用单个信号集中最大信号数量 信号集的最大值

kernel.sem = 10000  10240000 10000 1024

# 接收套接字缓冲区大小的默认值

net.core.rmem_default = 262144

# 接收套接字缓冲区大小的最大值

net.core.rmem_max = 4194304

# 发送套接字缓冲区大小的默认值

net.core.wmem_default = 262144

# 发送套接字缓冲区大小的最大值

net.core.wmem_max = 1048576

# 当前系统开放端口范围，默认是32768到61000，对于繁忙的网络服务器需要增加网络端口范围来增强它的处理能力

net.ipv4.ip_local_port_range = 9000 65500

# 大页配置，建议内存大于16G才配置

#vm.nr_hugepages = 

${PAGESIZE}

# <<<<<<<<<<<<<<<<<<<<<<<< 内核参数配置 by enmotech ${NOW} <<<<<<<<<<<<<<<<<<<<<<<<

" > /etc/sysctl.conf

sysctl -p

# 备份数据库参数文件

su - oracle << EOF

sqlplus / as sysdba <

create pfile='/tmp/pfile_${NOWFILE}.ora' from spfile;

EEE

EOF

# 数据库参数优化

su - oracle << EOF

sqlplus / as sysdba <

alter system set parallel_force_local=true         scope=spfile;

alter system set "_gc_policy_time"=0               scope=spfile;

alter system set "_gc_undo_affinity"=false         scope=spfile;

alter system set memory_target=0                   scope=spfile;

alter system set sga_max_size=${SGA}m              scope=spfile;

alter system set sga_target=${SGA}m                scope=spfile;

alter system set pga_aggregate_target=${PGA}m      scope=spfile;

alter system set streams_pool_size=256m            scope=spfile;

Alter system set audit_trail=none                  scope=spfile;

alter system set undo_retention=10800              scope=spfile;

alter system set session_cached_cursors=200        scope=spfile;

alter system set db_files=4000                     scope=spfile;

alter system set max_shared_servers=0              scope=spfile;

alter system set sec_max_failed_login_attempts=100 scope=spfile;

alter system set deferred_segment_creation=false   scope=spfile;

alter system set parallel_max_servers=32           scope=spfile;

alter system set sec_case_sensitive_logon=false    scope=spfile;

alter system set open_cursors=3000                 scope=spfile;

alter system set open_links = 40                   scope=spfile;

alter system set open_links_per_instance =40       scope=spfile;

alter system set db_cache_advice=off               scope=spfile;

alter system set gcs_server_processes=6            scope=spfile;

alter system set result_cache_max_size=0           scope=spfile;

alter system set enable_ddl_logging=true scope=spfile;

alter system set db_securefile=always scope=spfile;

alter system set "_ktb_debug_flags"=8 scope=both;

alter system set cell_offload_processing=false scope=spfile;

alter system set "_kttext_warning"=1 scope=spfile;

alter system set "_optimizer_ads_use_result_cache" = FALSE  scope=spfile;

alter system set "_b_tree_bitmap_plans"=false           scope=spfile;

alter system set "_gc_defer_time"=3                     scope=spfile; 

alter system set "_lm_tickets"=5000                     scope=spfile;

alter system set "_optimizer_use_feedback"=false        scope=spfile;

alter system set "_high_priority_processes"='LMS*|VKTM|LGWR' scope=spfile;

alter system set "_undo_autotune"=false                 scope=both ;

alter system set "_bloom_filter_enabled"=FALSE          scope=spfile;

alter system set "_cleanup_rollback_entries"=4000      scope=spfile;

alter system set "_px_use_large_pool"=true              scope=spfile;

alter system set "_optimizer_extended_cursor_sharing_rel"=NONE  scope=spfile;

alter system set "_optimizer_extended_cursor_sharing"=NONE      scope=spfile;

alter system set "_optim_peek_user_binds"=FALSE                 scope=spfile;

alter system set "_optimizer_adaptive_cursor_sharing"=false     scope=spfile;

alter system set "_optimizer_cartesian_enabled"=FALSE                 scope=spfile;

alter system set "_sort_elimination_cost_ratio"=1               scope=spfile;

alter system set "_index_partition_large_extents"=FALSE         scope=spfile;

alter system set "_memory_imm_mode_without_autosga"=FALSE       scope=spfile;

alter system set "_clusterwide_global_transactions"=FALSE       scope=spfile;

alter system set "_part_access_version_by_number"=FALSE         scope=spfile;

alter system set "_partition_large_extents"=FALSE                scope=spfile;

alter system set "_use_adaptive_log_file_sync"=FALSE            scope=spfile; 

alter system set "_lm_sync_timeout"=1200                        scope=spfile;

alter system set "_ksmg_granule_size"=134217728                 scope=spfile;

alter system set "_external_scn_logging_threshold_seconds"=3600 scope=spfile;

alter system set "_external_scn_rejection_threshold_hours"=24 scope=spfile;

alter system set "_datafile_write_errors_crash_instance"=false  scope=spfile;

alter system set event='28401 trace name context forever,level 1','60025 trace name context forever','10943 trace name context forever,level 2097152','10949 trace name context forever,level 1','10262 trace name context forever, level 90000' scope=spfile;

alter database force logging;

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;

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

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

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'auto space advisor',

operation => NULL,

window_name => NULL);

END;

/

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

/

EEE

EOF

echo "SGA Size: ${SGA}m"

echo "PAG Size: ${PGA}m"

# 是否重启

read -p "The DB parameters need to be restarted to take effect. Do you want to restart now？y|n :" ISRESTART

if [ "$ISRESTART" = "y" ]; then

su - oracle << EOF

sqlplus / as sysdba <

shutdown immediate

startup

EEE

EOF

fi

# 检查补丁是否更新成功

su - oracle << EOF

sqlplus / as sysdba <

set linesize 200

set pagesize 999

col action_time format a30

col action format a20

col id format a10

col version format a10

col comments format a20

select substr(action_time,1,30) action_time,substr(id,1,8) id,substr(action,1,10) action,substr(version,1,8) version,substr(comments,1,20) comments from dba_registry_history;

archive log list;

EEE

EOF

# 检查归档

echo "参数优化完成"

温馨提示

部份参数可能非最优化，如有错误，希望能给予指正，谢谢。