数据库学习案例20240207-19C RAC部署优化资源管理-

执笔画情ora

已于 2024-02-19 16:10:35 修改

阅读量196

点赞数

分类专栏： ORACLE数据库RAC管理文章标签：数据库学习 linux

于 2024-02-07 01:37:11 首次发布

原文链接：www.eimhe.com

版权

ORACLE数据库RAC管理专栏收录该内容

133 篇文章 5 订阅

订阅专栏

操作系统最佳实践操作系统最佳实践（（RHEL7.6 ）

3.1. 关闭透明大页关闭透明大页

关闭 Transparent HugePages 特性(RHEL7)：
需要先确认操作系统是否使用 EFI 启动方式，使用 df -h，如果
存在/boot/efi 挂载目录的话，即为使用 EFI 启动方式。
a. 将 “transparent_hugepage=never“ 内核参数追加到 /etc/default/grub
文件中
#vi /etc/default/grub
GRUB_CMDLINE_LINUX="rd.lvm.lv=rhel/root rd.lvm.lv=rhel/swap ...
transparent_hugepage=never"
b. 重新生成 grub 配置文件
如果为 BIOS 启动方式
grub2-mkconfig -o /boot/grub2/grub.cfg
如果为 EFI 启动方式
grub2-mkconfig -o /boot/efi/EFI/redhat/grub.cfg
c. 重启操作系统
#reboot
操作重启之后，查看效果：
# cat /proc/cmdline
-->>>有此行

# cat /sys/kernel/mm/transparent_hugepage/enabled
-->>>[never]表示成功禁用 THP。
# grep AnonHugePages /proc/meminfo
-->>>返回值若是零，代表成功禁用 THP。

3.2. 确认 IO 调度算法

dmesg |grep scheduler |grep default
Rdhat 7.2 以后版本为，已经默认为 dead line 算法，不需修改

3.3. 配置时钟同步配置时钟同步
3.4. 修改修改 grid 和 oracle 进程数量限制

需要修改文件：
/etc/security/limits.d/20- nproc.conf
echo "oracle soft nproc
16384" >>/etc/security/limits.d/20-nproc.conf
echo "oracle hard nproc
16384" >>/etc/security/limits.d/20-nproc.conf

3.5. 配置配置系统参数

more /etc/sysctl.conf
net.ipv4.ipfrag_high_thresh = 16777216
net.ipv4.ipfrag_low_thresh = 15728640

3.6. 禁用物理地址随机分配禁用物理地址随机分配
kernel.randomize_va_space=0
kernel.exec-shield=0
3.7. RemoveIPC RemoveIPC 处理
在 RHEL7.2 中，systemd-logind 服务引入了一个新特性，该新特性
是：当一个 user 完全退出 os 之后，remove 掉所有的 IPC objects。
解决方法：
1) Set RemoveIPC=no in /etc/systemd/logind.conf
2) Reboot the server or restart systemd-logind as follows:
systemctl daemon-reload
systemctl restart systemd-logind
3.8. NOZEROCONF
当系统无法连接 DHCP server 的时候，就会尝试通过 ZEROCONF 来获取 IP。
然后网卡将会被设置为 169.254.0.0 段的地址，可以禁止这项功能。
# echo "NOZEROCONF=yes" >> /etc/sysconfig/network
3.9. 3禁用不必要服务禁用不必要服务
systemctl disable firewalld
systemctl disable avahi-daemon
systemctl disable bluetooth
systemctl disable cpuspeed
systemctl disable cups
systemctl disable firstboot
systemctl disable ip6tables
systemctl disable iptables
systemctl disable pcmcia
3.10. 启用启用 rc- - local 服务
chmod +x /etc/rc.d/rc.local
systemctl enable rc-local
systemctl start rc-local
systemctl is-active rc-local

2 GI 最佳实践

4.1. 禁用 AVAHI daemon

/etc/init.d/avahi-daemon stop
/sbin/chkconfig avahi-daemon off

4.2. 禁用禁用 ora.crf

crsctl modify res ora.crf -attr "AUTO_START=never" -init
crsctl modify res ora.crf -attr "ENABLED=0" -init
crsctl stop res ora.crf -init

4.3. 设置 ASM 实例内存参数

禁用 AMM
alter system set sga_target=2048M scope=spfile sid='*';
alter system set pga_aggregate_target=1024M scope=spfile sid='*';
alter system set memory_target=0 scope=spfile sid='*';
alter system set memory_max_target=0 scope=spfile sid='*';
alter system reset memory_max_target scope=spfile sid='*';
设置 large_pool 参数
alter system set large_pool_size=128M scope=spfile sid='*';
设置_asm_hbeatiowait
alter system set "_asm_hbeatiowait"=120 scope=spfile sid='*';

4.4. 修改 VIP 对对 network 的硬依赖关系

network分为2类，

1 network和asmnetwork

Root 用户下执行以下语句
for i in `crs_stat|grep -i vip|grep -i name|awk -F= '{print $2}'`
do
crsctl modify res $i -attr "STOP_DEPENDENCIES=hard(intermediate:ora.net1.network)"
done

4 DB 最佳实践

5.1. 关闭密码大小写敏感

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

关闭密码大小写敏感
alter system set sec_case_sensitive_logon=false scope=spfile sid='*';

5.2. PASSWORD_LIFE_TIME

alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_LOCK_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;

5.3. 关闭跨实例并行

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

5.4. 设置 dump 文件大小上限

alter system set max_dump_file_size='4096M' scope=spfile sid='*';

5.5. 调整默认分区大小

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

5.6. 设置并行子进程的数量上限

alter system set parallel_max_servers=128 scope=spfile sid='*';
alter system set parallel_adaptive_multi_user=false;
根据 CPU 核数计算，不建议设置过高

5.7. 关闭 DRM

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

5.8. 关闭初始化 extent 的延迟创建

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

5.9. 关闭 result_cache

alter system set result_cache_max_size=0 scope=spfile sid='*';

5.10. 增加实例延迟降级锁的时长

alter system set "_gc_defer_time"=3 scope=spfile sid='*';

5.11. 关闭自适应游标共享

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;

5.12. 关闭 Cardinality feedback

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

5.13. 设置 28401 和和 10949 和 60025 等等事件

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

5.14. 使并行会话改为使用 large pool

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

5.15. 恢复 LGWR 的的 post/wait 通知方式

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

5.16. 关闭 sql tuning advisor

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

5.17. 禁用 space advisor

exec dbms_auto_task_admin.disable(client_name => 'auto space advisor',operation =>
NULL,window_name => NULL);

5.18. 调整进程优先级

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

5.19. 关闭 direct path read

alter system set "_serial_direct_read" = false sid = '*' ;

5.20. 关闭 resource limit

alter system set resource_limit = false sid = '*' ;

5.21. 设置参数进行 bug 规避

alter system set "_gc_bypass_readers" = false sid = '*' scope=spfile;
alter system set "_gc_override_force_cr " = false sid = '*' scope=spfile;
alter system set "_gc_read_mostly_locking" = false sid = '*' scope=spfile;

5.22. 加速 smon 进程对事物的恢复速度

alter system set fast_start_parallel_rollback=low;
alter system set recovery_parallelism=10 scope=spfile;
alter system set "_cleanup_rollback_entries"=10000 scope=spfile;

5.23. 文件写入失败实例不 Crash

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

5.24. 设置隐含参数，避免 dataguard 切换后，索引块损坏

alter system set "_ktb_debug_flags"=8

5.25. 增加联机回滚段数量

alter system set "_rollback_segment_count"=400 scope=spfile sid='*';

5.26. 配置步磁带异步 IO

磁带默认不支持 AIO，建议配置 backup_tape_io_slaves 参数，在 Large Pool 中分配缓冲
区，模拟异步 IO，加快备份速度。
alter system set tape_asynch_io=true scope=spfile;
alter system set backup_tape_io_slaves=true scope=spfile;

5.27. 控制子游标数量

避免子游标数量过多
Alter system set "_cursor_obsolete_threshold"=400 scope=spfile

5.28. 设置 sga 调整时间间隔

alter system set "_memory_broker_stat_interval"=999;

5.29. 禁用 sga durations

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

5.30. 修改 sqlnet

在每个节点的 grid 和 oracle 用户下修改：
cd $ORACLE_HOME/network/admin
vi sqlnet.ora
加入以下内容：
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

5.31. 禁用 OCM

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

5.32. 删除 SPD 执行计划

SELECT directive_id FROM dba_sql_plan_directives;
DECLARE
CURSOR del_dir IS
SELECT directive_id FROM dba_sql_plan_directives;
BEGIN
FOR dir IN del_dir LOOP
dbms_spd.drop_sql_plan_directive(directive_id => dir.directive_id);
END LOOP;
END;
/

5.33. 保存 pdb 打开状态

ALTER PLUGGABLE DATABASE all SAVE STATE instances=all;
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;

5.34. 调整统一审计设置

5.34.1. 5.34.创建表空间创建表空间
create tablespace AUDITTS datafile '+DATA01DG' size 100M autoextend
on;

统一审计记录默认存放在 sysaux 表空间，创建新的表空间，用于后
续存放审计记录。
5.34.2. 5.34.2. 修改存放审计记录默认表空间修改存放审计记录默认表空间
Exec
dbms_audit_mgmt.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_
MGMT.AUDIT_TRAIL_UNIFIED,audit_trail_location_value=>'AUDITTS');
5.34.3. 5.34.3. 修改审计记录按日分区修改审计记录按日分区
BEGIN
DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
interval_number => 1,
interval_frequency => 'DAY');
END;
/
在 19.1 之前版本，由于 Bug 27576342 ，即使修改表空间和按日分区，
lob 及 lob index 依旧存放在 sysaux 表空间。
5.34.4. 5.34.4. 部署统一审计清理保留策略部署统一审计清理保留策略
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME => SYSTIMESTAMP - 7 ,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/
这里保留最近 7 天。
5.34.5. 5.34.5. 部署统一审计自动清理任务部署统一审计自动清理任务
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
更多数据库资源请访问美河学习在线 www.eimhe.com
AUDIT_TRAIL_TYPE =>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_Clean',
USE_LAST_ARCH_TIMESTAMP => TRUE,
CONTAINER =>
DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/
每 12 小时进行一次清理。
5.35. 修改统一审计设置
5.35.1. 5.35.1. 创建新的审计表空间创建新的审计表空间
create tablespace AUDITTS datafile '<PATH>/auditts.dbf' size 100M
autoextend on;
5.35.2. 5.35.2. 修改存放审计记录默认表空间修改存放审计记录默认表空间
dbms_audit_mgmt.set_audit_trail_location(audit_trail_type=>DBMS_AU
DIT_MGMT.AUDIT_TRAIL_UNIFIED,audit_trail_location_value=>'AUDITTS');
5.35.3. 5.35.3. 修改审计记录按日分区修改审计记录按日分区
BEGIN
DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
interval_number => 1,
interval_frequency => 'DAY');
END;
/

5.35.4. 5.35.4. 部署统一审计清理保留策略部署统一审计清理保留策略
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME => SYSTIMESTAMP - 7 ,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/
5.35.5. 5.35.5. 部署统一审计自动清理任务部署统一审计自动清理任务
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE =>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => ‘Audit_Trail_Clean‘,
USE_LAST_ARCH_TIMESTAMP => TRUE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/

5.36. 禁用 Optimizer Statistics Advisor Task

设置失效时间，这里设置为 15 天
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
task_name => 'AUTO_STATS_ADVISOR_TASK'
, parameter => 'EXECUTION_DAYS_TO_EXPIRE'
, value => 15
);
END;
更多数据库资源请访问美河学习在线 www.eimhe.com
/
或者禁用优化器统计信息顾问
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;
DECLARE
filter1 CLOB;
BEGIN
filter1 :=
DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK',NUL
L,NULL,'DISABLE');
END;
/
如果出现 ORA-20001: Statistics Advisor: Invalid task name for the current
user 多执行几次 EXEC DBMS_STATS.INIT_PACKAGE(); 然后在禁用任务

5.37. 其他参数修修改

alter system set optimizer_adaptive_plans=FALSE scope=spfile;
alter system set inmemory_query=DISABLE scope=spfile;
alter system set inmemory_size=0 scope=spfile;
alter system set gcs_server_processes=8 scope=spfile;
alter system set db_writer_processes=8 scope=spfile;
alter system set "_abort_on_mrp_crash"=FALSE scope=spfile;
alter system set "_adg_parselock_timeout"=500 scope=spfile;
alter system set "_ash_size"=254M scope=spfile;
alter system set "_dlm_stats_collect"=0 scope=spfile;
alter system set "_enable_NUMA_support"=FALSE scope=spfile;
alter system set "_enable_automatic_sqltune"=false scope=spfile;
alter system set "_enable_shared_pool_durations"=false scope=spfile;
alter system set "_fast_index_maintenance"=false scope=spfile;
alter system set "_gc_persistent_read_mostly"= FALSE scope=spfile;
alter system set "_ksmg_granule_size"=134217728 scope=spfile;
alter system set "_lm_sync_timeout"=400 scope=spfile;
alter system set "_lm_tickets"=5000 scope=spfile;
alter system set "_log_segment_dump_parameter"=false scope=spfile;
alter system set "_log_segment_dump_patch"=false scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=TRUE scope=spfile;
alter system set "_optimzer_cost_based_transformation"=on scope=spfile
alter system set "_rollback_segment_count"=1000 scope=spfile;
alter system set "_resource_manager_always_off"=TRUE scope=spfile;
alter system set "_report_capture_cycle_time"=0 scope=spfile;
alter system set "_rowsets_enabled"=TRUE scope=spfile;
alter system set "_use_single_log_writer"=TRUE scope=spfile;
alter system set gcs_server_processes=8 scope=spfile;
alter system set db_writer_processes=8 scope=spfile;
alter system set "_abort_on_mrp_crash"=FALSE scope=spfile;
alter system set "_adg_parselock_timeout"=500 scope=spfile;
alter system set "_ash_size"=254M scope=spfile;
alter system set "_dlm_stats_collect"=0 scope=spfile;
alter system set "_enable_NUMA_support"=FALSE scope=spfile;
alter system set "_enable_automatic_sqltune"=false scope=spfile;
alter system set "_enable_shared_pool_durations"=false scope=spfile;
alter system set "_fast_index_maintenance"=false scope=spfile;
alter system set "_gc_persistent_read_mostly"= FALSE scope=spfile;
alter system set "_ksmg_granule_size"=134217728 scope=spfile;
alter system set "_lm_sync_timeout"=400 scope=spfile;
alter system set "_lm_tickets"=5000 scope=spfile;
alter system set "_log_segment_dump_parameter"=false scope=spfile;
alter system set "_log_segment_dump_patch"=false scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=TRUE scope=spfile;
alter system set "_optimzer_cost_based_transformation"=on scope=spfile
alter system set "_resource_manager_always_off"=TRUE scope=spfile;
alter system set "_report_capture_cycle_time"=0 scope=spfile;
alter system set "_rowsets_enabled"=TRUE scope=spfile;
alter system set "_use_single_log_writer"=TRUE scope=spfile