一、当前服务器配置

1，查看透明大页配置

[root@DB1 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled 
[always] madvise never

[root@DB2 ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

2，查看大页配置

[root@DB1 ~]# grep Huge /proc/meminfo 
AnonHugePages:  93589504 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

[root@DB2 ~]# grep Huge /proc/meminfo
AnonHugePages:  136179712 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

3，查看系统内核参数

[root@DB1 ~]# grep -v '#' /etc/sysctl.conf 
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall =137438953472 
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.file-max = 6815744  
fs.aio-max-nr = 1048576
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
kernel.core_uses_pid = 1


[root@DB2 ~]# grep -v '#' /etc/sysctl.conf
net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 137438953472
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.file-max = 6815744  
fs.aio-max-nr = 1048576
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
kernel.core_uses_pid = 1

4，查看系统资源限制

[root@DB1 ~]# cat /etc/security/limits.conf 
grid soft nproc 65535
grid hard nproc 65535
grid soft nofile 65535
grid hard nofile 65536
oracle soft nproc  65535
oracle hard nproc  65535
oracle soft nofile 65535
oracle hard nofile 65536

[root@DB2 ~]# cat /etc/security/limits.conf
grid soft nproc 65535
grid hard nproc 65535
grid soft nofile 65535
grid hard nofile 65536
oracle soft nproc 65535
oracle hard nproc 65535
oracle soft nofile 65535
oracle hard nofile 65536

5，查看SGA配置

[oracle@DB1 ~]$ sqlplus / as sysdba
SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 44G
sga_target                           big integer 44G



[oracle@DB2 ~]$ sqlplus / as sysdba
SQL> show parameter sga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 44G
sga_target                           big integer 44G

6，查看数据库参数配置

rundb1.__db_cache_size=30198988800
rundb2.__db_cache_size=34628173824
rundb1.__java_pool_size=536870912
rundb2.__java_pool_size=671088640
rundb1.__large_pool_size=1073741824
rundb2.__large_pool_size=1073741824
rundb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rundb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
rundb1.__pga_aggregate_target=15837691904
rundb2.__pga_aggregate_target=15837691904
rundb1.__sga_target=47244640256
rundb2.__sga_target=47244640256
rundb1.__shared_io_pool_size=0
rundb2.__shared_io_pool_size=0
rundb1.__shared_pool_size=14898167808
rundb2.__shared_pool_size=10603200512
rundb2.__streams_pool_size=0
rundb1.__streams_pool_size=268435456
*._bloom_filter_enabled=FALSE
*._bloom_pruning_enabled=FALSE
*._cleanup_rollback_entries=400
*._clusterwide_global_transactions=FALSE
*._gc_defer_time=3
*._gc_policy_time=0
*._gc_read_mostly_locking=FALSE
*._gc_undo_affinity=FALSE
*._optimizer_adaptive_cursor_sharing=FALSE
*._optimizer_extended_cursor_sharing='NONE'
*._optimizer_extended_cursor_sharing_rel='NONE'
*._optimizer_use_feedback=FALSE
*._partition_large_extents='FALSE'
*._PX_use_large_pool=TRUE
*._resource_manager_always_off=TRUE
*._resource_manager_always_on=FALSE
*._serial_direct_read='NEVER'
*._smu_debug_mode=134217728
*._undo_autotune=FALSE
*.audit_file_dest='/u01/app/oracle/admin/rundb/adump'
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=14
*.control_files='+DATA/rundb/controlfile/current.265.978890499','+FRA/rundb/controlfile/current.16650.1039558769'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='/u01/oradata','+DATA/rundb/datafile','/u01/oradata','+DATA/rundb/tempfile'
*.db_name='rundb'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rundbXDB)'
*.event='28401 trace name context forever,level 1'
*.fal_client='rundb'
*.fal_server='stdrundb'
rundb1.instance_number=1
rundb2.instance_number=2
*.job_queue_processes=200
*.log_archive_config='DG_CONFIG=(rundb,stdrundb)'
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=rundb'
*.log_archive_dest_2='service=stdrundb LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=stdrundb'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='/u01/oradata','+DATA/rundb/onlinelog'
*.open_cursors=3000
*.pga_aggregate_target=15728640000
*.processes=3000
*.remote_listener='scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1500
*.sga_target=47185920000
*.standby_file_management='AUTO'
rundb2.thread=2
rundb1.thread=1
rundb1.undo_retention=18000
rundb2.undo_retention=18000
rundb1.undo_tablespace='UNDOTBS1'
rundb2.undo_tablespace='UNDOTBS2'

三、调整配置步骤(2台都操作)

1，禁用透明大页并重启服务器

[root@DB1 ~]# echo never >/sys/kernel/mm/transparent hugepage/enabled
[root@DB1 ~]# echo never > / sys/kernel/mm/transparent hugepage/ defrag
[root@DB1 ~]# reboot

[root@DB2 ~]# echo never >/sys/kernel/mm/transparent hugepage/enabled
[root@DB2 ~]# echo never > / sys/kernel/mm/transparent hugepage/ defrag
[root@DB2 ~]# reboot

2，配置memlock限制

设置 Oracle 用户的内存锁定限制：
[root@DB1 ~]# vi /etc/security/limits.conf
# 添加以下内容
* soft memlock unlimited   
* hard memlock unlimited  
验证设置生效：
su - oracle
ulimit -l  # 应输出 unlimited

[root@DB2 ~]# vi /etc/security/limits.conf
# 添加以下内容
* soft memlock unlimited   
* hard memlock unlimited  
验证设置生效：
su - oracle
ulimit -l  # 应输出 unlimited

3，配置内核参数并添加Hugepages

[root@DB1 ~]# ./hugepages_settings.sh     ---使用 Oracle 提供的脚本计算
Recommended setting: vm.nr_hugepages = 45067
[root@DB1 ~]# vi /etc/sysctl.conf  ---增加如下配置
vm.nr_hugepages = 45067
[root@DB1 ~]# vi /etc/sysctl.conf  ---修改如下配置
kernel.shmmax = 
kernel.shmall = 
计算kernel.shmmax=（内存总数）128G*1024*1024*1024*90% = 123695058126
计算kernel.shmall =  kernel.shmmax / 4096 = 123695058125/4096 = 30198988


[root@DB1 ~]# sysctl -p   ---配置生效

[root@DB2 ~]# ./hugepages_settings.sh     ---使用 Oracle 提供的脚本计算
Recommended setting: vm.nr_hugepages = 45067
[root@DB2 ~]# vi /etc/sysctl.conf  ---增加如下配置
vm.nr_hugepages = 45067
[root@DB2 ~]# vi /etc/sysctl.conf  ---修改如下配置
kernel.shmmax = 
kernel.shmall = 
计算kernel.shmmax=（内存总数）128G*1024*1024*1024*90% = 123695058126
计算kernel.shmall =  kernel.shmmax / 4096 = 123695058125/4096 = 30198988
[root@DB2 ~]# sysctl -p   ---配置生效

📌因为服务器上面还部署了应用，所以在这里没按照512G内存来计算，就用了128G内存计算📌

4，修改SGA配置

关闭实例2:
[grid@DB2]$ srvctl stop instance  -d rundb -i rundb2
实例2上启动数据库到mount状态：
[grid@DB2]$ srvctl start instance -d rundb -i rundb2 -o mount
修改实例2的sga_max_size到100G:
[oracle@DB2]$ sqlplus / as sysdba;
SQL> ALTER SYSTEM SET sga_max_size = 100g SCOPE=SPFILE SID='rundb2'
修改db2的sga_target到100G:
SQL> ALTER SYSTEM SET sga_target = 100g SCOPE=SPFILE SID='rundb2'
关闭实例2：
[grid@DB2]$ srvctl stop instance -d rundb -i rundb2
启动实例2：
[grid@DB2]$ srvctl start instance  -d rundb -i rundb2 open
查看实例2的SGA信息：
[oracle@DB2]$ sqlplus / as sysdba;
SQL> show parameter sga;

关闭实例1:
[grid@DB1]$ srvctl stop instance  -d rundb -i rundb1
实例1上启动数据库到mount状态：
[grid@DB1]$ srvctl start instance -d rundb -i rundb1 -o mount
修改实例1的sga_max_size到100G:
[oracle@DB1]$ sqlplus / as sysdba;
SQL> ALTER SYSTEM SET sga_max_size = 100g SCOPE=SPFILE SID='rundb1'
修改db1的sga_target到100G:
SQL> ALTER SYSTEM SET sga_target = 100g SCOPE=SPFILE SID='rundb1'
关闭实例1：
[grid@DB1]$ srvctl stop instance -d rundb -i rundb1
启动实例1：
[grid@DB1]$ srvctl start instance  -d rundb -i rundb1 open
查看实例1的SGA信息：
[oracle@DB1]$ sqlplus / as sysdba;
SQL> show parameter sga;