Oracle 19c 最佳参数实践.txt

JiekeXu

All China Database Union

4925

3页

60次

2023-03-14

25墨值下载

Oracle 19c 最佳参数实践.txt

注意：参数最佳实践适用于 12c 及以上版本，个别参数在 11g 中可能没有；另外每个公司均有自己生产实

践过的参数，以下仅供参考

alter system set db_files=2048 scope=spfile sid='*';

alter system set event='10949 trace name context forever,level 1','28401 trace

name context forever,level 1','10503 trace name context forever, level 4000'

scope=spfile sid='*';

alter system set PARALLEL_DEGREE_POLICY=manual scope=spfile sid='*';

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

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

alter system set audit_trail = none scope=spfile sid='*';

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

alter system set ARCHIVE_LAG_TARGET=1800 scope=spfile sid='*';

alter system set DB_BLOCK_CHECKING=MEDIUM scope=spfile sid='*';

alter system set DB_BLOCK_CHECKSUM=FULL scope=spfile sid='*';

alter system set DB_LOST_WRITE_PROTECT=TYPICAL scope=spfile sid='*';

alter system set DB_WRITER_PROCESSES = 8 scope=spfile sid='*';

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

alter system set MAX_DUMP_FILE_SIZE=102400000 scope=spfile sid='*';

alter system set MAX_IDLE_BLOCKER_TIME=15 scope=spfile sid='*';

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

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

alter system set undo_retention=86400 scope=spfile sid='*';

alter system set processes=6000 scope=spfile sid='*';

alter system set control_file_record_keep_time=60 scope=spfile sid='*';

alter system set "_OPTIM_PEEK_USER_BINDS"=FALSE scope=spfile sid='*';

alter system set "_cursor_obsolete_threshold"=100 scope=spfile sid='*';

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

alter system set "_clusterwide_global_transactions" = false scope=spfile

sid='*';

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

alter system set "_cleanup_rollback_entries" = 10000 scope=spfile sid='*';

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

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

alter system set "_ash_size" = 254M scope=spfile sid='*';

alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=spfile

sid='*';

alter system set "_optimizer_extended_cursor_sharing"=none scope=spfile

sid='*';

alter system set "_optimizer_adaptive_cursor_sharing"=false scope=spfile

sid='*';

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

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

--close inmemory

alter system set inmemory_size=0 scope=spfile;

alter system set inmemory_query=disable scope=spfile;

----内存参数调整参考

alter system set sga_max_size=40G scope=spfile;

alter system set db_cache_size=25G scope=spfile;

alter system set shared_pool_size=8G scope=spfile;

alter system set java_pool_size=1G scope=spfile;

alter system set large_pool_size=1G scope=spfile;

alter system set pga_aggregate_target=10G scope=spfile;

alter system set PGA_AGGREGATE_LIMIT=20G scope=spfile sid='*';

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

ALTER PROFILE DEFAULT limit FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME

UNLIMITED;

exec

dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>40*24*

60);

设置归档路径

alter system set log_archive_dest_1='location=+ARCH' sid='*';

alter database archivelog;

如果为 ADG 或测试库,添加定时删除归档脚本

vi deleteArch.sh

#!/bin/bash

DATE=`date +%Y%m%d%H`

source /home/oracle/.bash_profile

rman log=/tmp/rman_${DATE}.log <<EOF

connect target /

run{

crosscheck archivelog all;

delete noprompt force expired archivelog all;

delete noprompt force archivelog all completed before 'sysdate-3';

}

exit;

EOF

exit

chmod a+x deleteArch.sh

使用计划任务每天 0 点调用脚本进行清理归档（oracle 用户部署）

crontab -e

0 0 * * * /home/oracle/deleteArch.sh

------------------------ASM 参数设置----------------------------------

su - grid

sqlplus / as sysasm

select value from v$parameter where name='max_dump_file_size';

alter system set MAX_DUMP_FILE_SIZE='200M' sid='*' scope=both;

alter system set memory_max_target=4096M scope=spfile;

alter system set memory_target=4096M scope=spfile;

alter system set large_pool_size=32M scope=spfile;

alter system set pga_aggregate_target=516M scope=spfile;

alter system set sga_max_size=1500M scope=spfile;

alter system set sga_target=1500M scope=spfile;

alter system set shared_pool_reserved_size=120M scope=spfile;

alter system set shared_pool_size=750M scope=spfile;

alter system set processes=2000 scope=spfile;

alter system set sessions=3000 scope=spfile;

alter system set "_asm_hbeatiowait"=120 scope=spfile;

------------------设置 pdb 随机启动及基础管理

sql---------------------------------------

--启动 pdb:

alter pluggable database all open;

--设置 pdb 随 CDB 启动:

alter pluggable database all save state instances=all;

--查看保留状态信息：

col CON_NAME for a30

col INSTANCE_NAME for a30

col STATE for a30

select con_name,instance_name,state,restricted from cdb_pdb_saved_states;

---查看 pdb:

show pdbs；

--切换到指定 pdb

alter session container=pdb;