0 积分	0 好友	8 主题

发消息

[SQL调优] 11g R2 数据库的告警日志--Thread 1 cannot allocate new log

1^#

发表于 2013-7-24 16:39:46 | 查看: 6141| 回复: 2

查看告警日志-------日志切换不够用，请解答一下

Sat Jun 15 00:01:45 2013
Starting background process CJQ0
Sat Jun 15 00:01:45 2013
CJQ0 started with pid=130, OS id=6978
Sat Jun 15 00:07:56 2013
Incremental checkpoint up to RBA [0xc17.20054.0], current log tail at RBA [0xc17.354be.0]
Sat Jun 15 00:16:14 2013
ALTER SYSTEM ARCHIVE LOG
Sat Jun 15 00:16:15 2013
Thread 1 cannot allocate new log, sequence 3096  ------  日志轮换不够用？
Private strand flush not complete
Current log# 5 seq# 3095 mem# 0: /u02/PROD/db/apps_st/data/log05a.dbf
Current log# 5 seq# 3095 mem# 1: /u02/PROD/db/apps_st/data/log05b.dbf
Beginning log switch checkpoint up to RBA [0xc18.2.10], SCN: 5965526807050
Thread 1 advanced to log sequence 3096 (LGWR switch)
Current log# 2 seq# 3096 mem# 0: /u02/PROD/db/apps_st/data/log02a.dbf
Current log# 2 seq# 3096 mem# 1: /u02/PROD/db/apps_st/data/log02b.dbf
Sat Jun 15 00:21:18 2013
Completed checkpoint up to RBA [0xc18.2.10], SCN: 5965526807050
Sat Jun 15 00:28:01 2013
Incremental checkpoint up to RBA [0xc18.247bf.0], current log tail at RBA [0xc18.3d153.0]
Sat Jun 15 00:32:40 2013
Stopping background process CJQ0
Sat Jun 15 00:34:16 2013
Beginning log switch checkpoint up to RBA [0xc19.2.10], SCN: 5965526865097
Thread 1 advanced to log sequence 3097 (LGWR switch)
Current log# 1 seq# 3097 mem# 0: /u02/PROD/db/apps_st/data/log01a.dbf
Current log# 1 seq# 3097 mem# 1: /u02/PROD/db/apps_st/data/log01b.dbf
Sat Jun 15 00:38:01 2013
Completed checkpoint up to RBA [0xc19.2.10], SCN: 5965526865097
Sat Jun 15 00:48:02 2013
Incremental checkpoint up to RBA [0xc19.32d2f.0], current log tail at RBA [0xc19.49e42.0]
Sat Jun 15 00:51:31 2013
Beginning log switch checkpoint up to RBA [0xc1a.2.10], SCN: 5965526941617
Thread 1 advanced to log sequence 3098 (LGWR switch)
Current log# 3 seq# 3098 mem# 0: /u02/PROD/db/apps_st/data/log03a.dbf
Current log# 3 seq# 3098 mem# 1: /u02/PROD/db/apps_st/data/log03b.dbf
Sat Jun 15 00:52:15 2013
Completed checkpoint up to RBA [0xc1a.2.10], SCN: 5965526941617
Sat Jun 15 00:56:40 2013
Beginning log switch checkpoint up to RBA [0xc1b.2.10], SCN: 5965526968234
Thread 1 advanced to log sequence 3099 (LGWR switch)
Current log# 4 seq# 3099 mem# 0: /u02/PROD/db/apps_st/data/log04a.dbf
Current log# 4 seq# 3099 mem# 1: /u02/PROD/db/apps_st/data/log04b.dbf
Sat Jun 15 01:00:43 2013
Completed checkpoint up to RBA [0xc1b.2.10], SCN: 5965526968234
Sat Jun 15 01:08:07 2013
Incremental checkpoint up to RBA [0xc1b.29628.0], current log tail at RBA [0xc1b.3e282.0]
Sat Jun 15 01:18:08 2013
Beginning log switch checkpoint up to RBA [0xc1c.2.10], SCN: 5965527034696
Thread 1 advanced to log sequence 3100 (LGWR switch)
Current log# 5 seq# 3100 mem# 0: /u02/PROD/db/apps_st/data/log05a.dbf
Current log# 5 seq# 3100 mem# 1: /u02/PROD/db/apps_st/data/log05b.dbf
Sat Jun 15 01:21:52 2013
Starting background process CJQ0
Sat Jun 15 01:21:52 2013
CJQ0 started with pid=130, OS id=8576
Sat Jun 15 01:23:13 2013
Completed checkpoint up to RBA [0xc1c.2.10], SCN: 5965527034696
Sat Jun 15 01:28:08 2013
Incremental checkpoint up to RBA [0xc1c.eea2.0], current log tail at RBA [0xc1c.21e9f.0]
Sat Jun 15 01:37:47 2013
Beginning log switch checkpoint up to RBA [0xc1d.2.10], SCN: 5965527101856
Thread 1 advanced to log sequence 3101 (LGWR switch)
  Current log# 2 seq# 3101 mem# 0: /u02/PROD/db/apps_st/data/log02a.dbf
  Current log# 2 seq# 3101 mem# 1: /u02/PROD/db/apps_st/data/log02b.dbf
Sat Jun 15 01:42:23 2013
Completed checkpoint up to RBA [0xc1d.2.10], SCN: 5965527101856
Sat Jun 15 01:48:10 2013
Incremental checkpoint up to RBA [0xc1d.26ce2.0], current log tail at RBA [0xc1d.3f6a8.0]
Sat Jun 15 01:52:38 2013
Stopping background process CJQ0
Sat Jun 15 01:53:45 2013
Beginning log switch checkpoint up to RBA [0xc1e.2.10], SCN: 5965527181187
Thread 1 advanced to log sequence 3102 (LGWR switch)
  Current log# 1 seq# 3102 mem# 0: /u02/PROD/db/apps_st/data/log01a.dbf
  Current log# 1 seq# 3102 mem# 1: /u02/PROD/db/apps_st/data/log01b.dbf

数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0    Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

日志文件情况

SQL>  select * from v$log;

GROUP# THREAD#  SEQUENCE#    BYTES    MEMBERS ARC  STATUS    FIRST_CHANGE# FIRST_TIME
----------    ----------    ----------          ----------    ----------    --- ------------- -------------          ------------------
      1       1          3572             209715200       2       NO    CURRENT    5.9656E+12    24-JUL-13
      2       1          3571             209715200       2       YES    INACTIVE    5.9656E+12    24-JUL-13
      3       1          3568             209715200       2       YES    INACTIVE    5.9656E+12    24-JUL-13
      4       1          3569             209715200       2       YES    INACTIVE    5.9656E+12    24-JUL-13
      5       1          3570             209715200       2       YES    INACTIVE    5.9656E+12    24-JUL-13

SQL>  select 209715200/1024/1024||'M' from dual;

209715200/1024/1024
-------------------
            200M

  SQL> select group#,member,status from v$logfile order by 1;

  GROUP# MEMBER                                                                                  STATUS
----------    -------------------------------------------------------------------------------- -------
      1       /u02/PROD/db/apps_st/data/log01a.dbf
      1       /u02/PROD/db/apps_st/data/log01b.dbf
      2    /u02/PROD/db/apps_st/data/log02a.dbf
      2    /u02/PROD/db/apps_st/data/log02b.dbf
      3    /u02/PROD/db/apps_st/data/log03a.dbf
      3    /u02/PROD/db/apps_st/data/log03b.dbf
      4    /u02/PROD/db/apps_st/data/log04a.dbf
      4    /u02/PROD/db/apps_st/data/log04b.dbf
      5    /u02/PROD/db/apps_st/data/log05a.dbf
      5    /u02/PROD/db/apps_st/data/log05b.dbf

分享0