oracle 19c 最佳参数实践.txt

jiekexu

all china database union

3695

3页

53次

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 <

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;