Oracle安装配置：oraclelogfileswitch

oracle log file switch (checkpoint incomplete)解决

　　问题描述：oracle log file switch (checkpoint incomplete)，Every 15 min, tpm will drop to almost zero. cpu负载不高，很低。

　　1 set the parameter :

　　ALTER SYSTEM SET log_checkpoint_interval=20000 SCOPE=BOTH; 没有解决掉

　　2 生成@?/rdbms/admin/awrrpt.sql 报告

　　-- see SQL ordered by Elapsed Time

　　-- SQL Statistics

　　-- see the cpu

　　select ash.sql_id,

　　sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",

　　sum(decode(ash.session_state,'WAITING',1,0)) -

　　sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",

　　sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))

　　"IO",

　　sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"

　　from v$active_session_history ash,v$event_name en

　　where SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)

　　group by ash.sql_id order by sum(decode(ash.session_state,'ON CPU',1,1)) desc;

　　拿到执行的SQL,baidu之，google之，发现很多人都说redo需要加大。

　　-- 3 see redo logs

　　select * from v$log;

　　select * from v$logfile; 为300M，大家推荐的为1G以下，还有调整的空间。

　　-- 4 add new redo logs 添加新的redo log组

　　alter database add logfile thread 1 group 6 ('/opt/app/product/11.2/oradata/novadb/redo16.log') size 600M;

　　alter database add logfile thread 1 group 7 ('/opt/app/product/11.2/oradata/novadb/redo17.log') size 600M;

　　alter database add logfile thread 1 group 8 ('/opt/app/product/11.2/oradata/novadb/redo18.log') size 600M;

　　alter database add logfile thread 1 group 9 ('/opt/app/product/11.2/oradata/novadb/redo19.log') size 600M;

　　alter database add logfile thread 1 group 10 ('/opt/app/product/11.2/oradata/novadb/redo20.log') size 600M;

　　-- 5 see the new logs

　　5.1 see the status

　　SQL> select status,GROUP# from v$log;

　　STATUS GROUP#

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

　　ACTIVE 1

　　ACTIVE 2

　　ACTIVE 3

　　CURRENT 4

　　ACTIVE 5

　　ACTIVE 6

　　ACTIVE 7

　　ACTIVE 8 ACTIVE 9

　　ACTIVE 10

　　10 rows selected.

　　5.2 see the log file directory

　　SQL> select member,GROUP# from v$logfile;

　　MEMBER

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

　　GROUP#

　　----------

　　/opt/app/product/11.2/oradata/novadb/redo06.log

　　1

　　/opt/app/product/11.2/oradata/novadb/redo01.log

　　2

　　/opt/app/product/11.2/oradata/novadb/redo03.log

　　3

　　MEMBER

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

　　GROUP#

　　----------

　　/opt/app/product/11.2/oradata/novadb/redo16.log

　　6

　　/opt/app/product/11.2/oradata/novadb/redo17.log

　　7

　　/opt/app/product/11.2/oradata/novadb/redo18.log

　　8

　　MEMBER

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

　　GROUP#

　　---------- 　/opt/app/product/11.2/oradata/novadb/redo19.log

　　9

　　/opt/app/product/11.2/oradata/novadb/redo20.log

　　10

　　/opt/app/product/11.2/oradata/novadb/redo04.log

　　4

　　MEMBER

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

　　GROUP#

　　----------

　　/opt/app/product/11.2/oradata/novadb/redo05.log

　　5

　　10 rows selected.

　　6 oracle can continue to use the old redo logs .