11gr2简单安装后的一些参数调整

最新推荐文章于 2024-06-04 17:07:07 发布

转载最新推荐文章于 2024-06-04 17:07:07 发布 · 107 阅读

本文介绍了Oracle数据库的多项配置优化操作，包括密码有效期设置、控制文件记录保持时间、会话缓存游标数量调整、Redo Log组大小及成员配置、控制文件路径更改、Scheduler窗口时间调整及AWR采集频率等。

--修改密码有效期及屏蔽密码延迟验证
alter profile default limit password_life_time unlimited;
ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE;

--修改控制文件中保留归档记录和备份记录的天数
alter system set control_file_record_keep_time=15;

--修改单个会话打开和缓存游标的数量,之后再根据实际情况调整
alter system set session_cached_cursors=300 scope=spfile;
alter system set open_cursors=1000;

--修改redo log的成员数及大小,大小之后再根据实际情况调整
--select * from v$log;
--alter system switch logfile;
--alter system checkpoint;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 '/u02/oradata/orcl/redo01a.log' SIZE 512 M ;
ALTER DATABASE ADD LOGFILE MEMBER   '/u03/oradata/orcl/redo01b.log' TO GROUP 1 ;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 '/u02/oradata/orcl/redo02a.log' SIZE 512 M ;
ALTER DATABASE ADD LOGFILE MEMBER   '/u03/oradata/orcl/redo02b.log' TO GROUP 2 ;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 '/u02/oradata/orcl/redo03a.log' SIZE 512 M ;
ALTER DATABASE ADD LOGFILE MEMBER   '/u03/oradata/orcl/redo03b.log' TO GROUP 3 ;

--修改控制文件的路径
alter system set control_files='/u02/oradata/orcl/control01.ctl','/u03/oradata/orcl/control02.ctl' scope=spfile;

--修改系统默认的scheduler window时间,根据实际情况调整
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=THU;ByHour=04;ByMinute=0;BySecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=THU;ByHour=04;ByMinute=0;BySecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=THU;ByHour=04;ByMinute=0;BySecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=THU;ByHour=04;ByMinute=0;BySecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=THU;ByHour=04;ByMinute=0;BySecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=THU;ByHour=04;ByMinute=0;BySecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByDay=THU;ByHour=04;ByMinute=0;BySecond=0');
end;

--修改awr采集频率及保留时间
begin
dbms_workload_repository.modify_snapshot_settings(interval => 30,retention => 60*24*60);
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-1761859/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-1761859/