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;

　　拿到執(zhí)行的SQL,baidu之，google之，發(fā)現(xiàn)很多人都說redo需要加大。

　　-- 3 see redo logs

　　select * from v$log;

　　select * from v$logfile; 為300M，大家推薦的為1G以下，還有調(diào)整的空間。

　　-- 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 .