注册登录
提 问
English中文版
系统集成OracleOracle11gr2

oracle 11G 日志Thread 1 cannot allocate new log, sequence 13503 Checkpoint not complete？

tail -100f alert*.log
Archived Log entry 22067 added for thread 1 sequence 13501 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:46:14 2017
Thread 1 cannot allocate new log, sequence 13503
Checkpoint not complete
Current log# 2 seq# 13502 mem# 0: +DATA/erpdb/onlinelog/group_2.262.848792895
Thread 1 advanced to log sequence 13503 (LGWR switch)
Current log# 5 seq# 13503 mem# 0: +DATA/erpdb/onlinelog/group_5.263.848792895
Wed Dec 27 07:46:18 2017
Archived Log entry 22068 added for thread 1 sequence 13502 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:47:17 2017
Thread 1 cannot allocate new log, sequence 13504
Checkpoint not complete
Current log# 5 seq# 13503 mem# 0: +DATA/erpdb/onlinelog/group_5.263.848792895
Thread 1 advanced to log sequence 13504 (LGWR switch)
Current log# 1 seq# 13504 mem# 0: +DATA/erpdb/onlinelog/group_1.261.848792895
Wed Dec 27 07:47:21 2017
Archived Log entry 22070 added for thread 1 sequence 13503 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:48:20 2017
Thread 1 cannot allocate new log, sequence 13505
Checkpoint not complete
Current log# 1 seq# 13504 mem# 0: +DATA/erpdb/onlinelog/group_1.261.848792895
Thread 1 advanced to log sequence 13505 (LGWR switch)
Current log# 2 seq# 13505 mem# 0: +DATA/erpdb/onlinelog/group_2.262.848792895
Wed Dec 27 07:48:24 2017
Archived Log entry 22071 added for thread 1 sequence 13504 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:49:23 2017
Thread 1 cannot allocate new log, sequence 13506
Checkpoint not complete
Current log# 2 seq# 13505 mem# 0: +DATA/erpdb/onlinelog/group_2.262.848792895
Thread 1 advanced to log sequence 13506 (LGWR switch)
Current log# 5 seq# 13506 mem# 0: +DATA/erpdb/onlinelog/group_5.263.848792895
Wed Dec 27 07:49:27 2017
Archived Log entry 22072 added for thread 1 sequence 13505 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:50:26 2017
Thread 1 cannot allocate new log, sequence 13507
Checkpoint not complete
Current log# 5 seq# 13506 mem# 0: +DATA/erpdb/onlinelog/group_5.263.848792895
Thread 1 advanced to log sequence 13507 (LGWR switch)
Current log# 1 seq# 13507 mem# 0: +DATA/erpdb/onlinelog/group_1.261.848792895
Wed Dec 27 07:50:30 2017
Archived Log entry 22074 added for thread 1 sequence 13506 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:51:29 2017
Thread 1 cannot allocate new log, sequence 13508
Checkpoint not complete
Current log# 1 seq# 13507 mem# 0: +DATA/erpdb/onlinelog/group_1.261.848792895
Thread 1 advanced to log sequence 13508 (LGWR switch)
Current log# 2 seq# 13508 mem# 0: +DATA/erpdb/onlinelog/group_2.262.848792895
Wed Dec 27 07:51:33 2017
Archived Log entry 22075 added for thread 1 sequence 13507 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:52:29 2017
Thread 1 cannot allocate new log, sequence 13509
Checkpoint not complete
Current log# 2 seq# 13508 mem# 0: +DATA/erpdb/onlinelog/group_2.262.848792895
Thread 1 advanced to log sequence 13509 (LGWR switch)
Current log# 5 seq# 13509 mem# 0: +DATA/erpdb/onlinelog/group_5.263.848792895
Wed Dec 27 07:52:33 2017
Archived Log entry 22076 added for thread 1 sequence 13508 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:53:32 2017
Thread 1 cannot allocate new log, sequence 13510
Checkpoint not complete
Current log# 5 seq# 13509 mem# 0: +DATA/erpdb/onlinelog/group_5.263.848792895
Thread 1 advanced to log sequence 13510 (LGWR switch)
Current log# 1 seq# 13510 mem# 0: +DATA/erpdb/onlinelog/group_1.261.848792895
Wed Dec 27 07:53:36 2017
Archived Log entry 22078 added for thread 1 sequence 13509 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:54:35 2017
Thread 1 cannot allocate new log, sequence 13511
Checkpoint not complete
Current log# 1 seq# 13510 mem# 0: +DATA/erpdb/onlinelog/group_1.261.848792895
Thread 1 advanced to log sequence 13511 (LGWR switch)
Current log# 2 seq# 13511 mem# 0: +DATA/erpdb/onlinelog/group_2.262.848792895
Wed Dec 27 07:54:39 2017
Archived Log entry 22079 added for thread 1 sequence 13510 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:55:38 2017
Thread 1 cannot allocate new log, sequence 13512
Checkpoint not complete
Current log# 2 seq# 13511 mem# 0: +DATA/erpdb/onlinelog/group_2.262.848792895
Thread 1 advanced to log sequence 13512 (LGWR switch)
Current log# 5 seq# 13512 mem# 0: +DATA/erpdb/onlinelog/group_5.263.848792895
Wed Dec 27 07:55:42 2017
Archived Log entry 22080 added for thread 1 sequence 13511 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:56:38 2017
Thread 1 cannot allocate new log, sequence 13513
Checkpoint not complete
Current log# 5 seq# 13512 mem# 0: +DATA/erpdb/onlinelog/group_5.263.848792895
Thread 1 advanced to log sequence 13513 (LGWR switch)
Current log# 1 seq# 13513 mem# 0: +DATA/erpdb/onlinelog/group_1.261.848792895
Wed Dec 27 07:56:42 2017
Archived Log entry 22082 added for thread 1 sequence 13512 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 07:59:59 2017
Thread 1 cannot allocate new log, sequence 13514
Checkpoint not complete
Current log# 1 seq# 13513 mem# 0: +DATA/erpdb/onlinelog/group_1.261.848792895
Thread 1 advanced to log sequence 13514 (LGWR switch)
Current log# 2 seq# 13514 mem# 0: +DATA/erpdb/onlinelog/group_2.262.848792895
Wed Dec 27 08:00:03 2017
Archived Log entry 22083 added for thread 1 sequence 13513 ID 0xffffffffffe006b9 dest 1:
Wed Dec 27 08:33:03 2017
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20171227072452.ERPDB' SCOPE=MEMORY SID='erpdb1';
ALTER SYSTEM SET service_names='erpdb' SCOPE=MEMORY SID='erpdb1';

关注3
评论
参与7

3同行回答
|
按赞同排序
按时间排序

AcdanteAcdante技术总监SHFY
1）这准确来说并不是报错，只是一个警示类信息。2）产生原因是因为在日志被写满时会切换 日志组，这个时候会触发一次checkpoint，DBWR进程会把内存中Data Buffer中的脏块往数据文件中写，只要没写结束就不会释放这个日志组。如果归档模式被开启的 话，还会伴随着ARCH写归档的过程。如...显示全部

1）这准确来说并不是报错，只是一个警示类信息。
2）产生原因是因为在日志被写满时会切换 日志组，这个时候会触发一次checkpoint，DBWR进程会把内存中Data Buffer中的脏块往数据文件中写，只要没写结束就不会释放这个日志组。如果归档模式被开启的 话，还会伴随着ARCH写归档的过程。如果redo log产生的过快，当CPK或归档还没完成，LGWR已经把其余的日志组写满，又要往当前的日志组里面写redo log的时候，这个时候就会发生冲突，oracle就会在alert日志中记录报错信息。
3）可通过如下语句查询最近8天的redo log切换频率，以小时为单位输出。oracle官方建议是每小时切换4-5次，即15分钟切换一次为最佳配置。可根据业务量和目前最大的归档量重新调整redo log大小，以满足业务要求。
4）可检查切换频繁时间段的操作，是否有大量导入导出，RMAN备份。或者是数据提交等操作。根据业务和实际切换频率去调整redo log才是最优解决方案。

）======================================(
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(first_tim e, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 ,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
COUNT(*) TOTAL
FROM v$log_history a
where first_time>=to_char(sysdate-8)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;

收起
互联网服务 · 2017-12-27
浏览4795
查看赞同的人
彬彬 邀答
评论
793135970a793135970a信息安全师qqq
1222222.png日志在线显示全部

1222222.png

1222222.png

日志在线

收起
系统集成 · 2017-12-27
浏览4864
评论
haizdlhaizdl技术经理大连
查看一下日志文件的状态。显示全部

查看一下日志文件的状态。

收起
银行 · 2017-12-27
浏览4845
彬彬 邀答
评论

提问者

793135970a
793135970a003
信息安全师qqq
擅长领域： 服务器，Unix，AIX
关注5
回答44
评论230

相关问题

  • 现在入行数据库，选择Oracle数据库为切入点，是对是错呢？
  • 国产分布式数据库真的能全面替换核心业务吗？Oracle为何没有向分布式发力？
  • PG系or MySQL系，国产数据库如何进行选型？
  • 金融行业里，从性能、容量等角度来看，PostgreSQL适合替换什么规模的Oracle数据库？
  • 国产数据库同Oracle数据库的能力差别在哪里？国产数据全面开始替代Oracle等数据库我们还需要再那些方面努力？

    • 相关资料

  • Redhat7.6 Oracle 19C RAC部署手册
  • Redhat7.0 Oracle 12C RAC部署手册
  • SQL必知必会（第4版）
  • Oracle数据库日常维护手册v1.8
  • 国产K1 Power10 K8880G3上AIX由7.2升级7.3+Oracle_19c升级实践

    • 相关文章

  • 数据库租户能力大调研
  • 某大型银行手机银行等重要交易系统不做大幅改造整体去IOE最佳实践-技术路线选择篇
  • Oracle 兼容性面面观
  • Oracle数据库日常巡检指令
  • 一次Oracle bug的故障排查过程思考

    • 问题状态

  • 发布时间：2017-12-27
  • 关注会员：3 人
  • 问题浏览：6049
  • 最近回答：2017-12-27

    • 关于TWT使用指南社区专家合作厂商入驻社区企业招聘投诉建议版权与免责声明联系我们
    © 2024talkwithtrend — talk with trend,talk with technologist京ICP备09031017号-30

    X社区推广