Oracle log file switch (checkpoint incomplete) Solution

Source: Internet
Author: User

Oracle log file switch (checkpoint incomplete) solution Description: oracle log file switch (checkpoint incomplete), Every 15 min, tpm will drop to almost zero. cpu load is not high, very low. 1 set the parameter: alter system set log_checkpoint_interval = 20000 SCOPE = BOTH; 2 generated @? /Rdbms/admin/awrrpt. SQL Report -- see SQL ordered by Elapsed Time -- SQL Statistics -- see the cpu select ash. SQL _id, sum (decode (ash. session_state, 'on CPU ',) "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 enwhere SQL _ID is not null and en. event # = ash. event # and ash. sample_time> sysdate-1/(24*60) group by ash. SQL _idorder by sum (decode (ash. session_state, 'on CPU ',) desc; get the executed SQL, baidu, google, and many people say that redo needs to be increased. -- 3 see redo logs select * from v $ log; select * from v $ logfile; 300 M. We recommend less than 1 GB, and there is room for adjustment. -- 4 add new redo logs add new redo log group alter database add logfile thread 1 group 6 ('/opt/app/product/11.2/oradata/novadb/redo16.log ') size 600 M; alter database add logfile thread 1 group 7 ('/opt/app/product/11.2/oradata/novadb/redo17.log') size 600 M; alter database add logfile thread 1 group 8 ('/opt/app/product/11.2/oradata/novadb/redo18.log') size 600 M; alter database add logfile thread 1 group 9 (' /Opt/app/product/11.2/oradata/novadb/redo19.log ') size 600 M; alter database add logfile thread 1 group 10 ('/opt/app/product/11.2/oradata/novadb/redo1_log') size 600 M; -- 5 see the new logs5.1 see the statusSQL> 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 1010 Rows selected.5.2 see the log file directorySQL> select member, GROUP # from v $ logfile; MEMBER consumer 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/redow.log 10/opt/app/p Roduct/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. OK, the application gets stuck every 15 minutes when the oracle concurrency is 800/second.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.