这篇博客详细记录了Oracle数据库遇到日志切换问题，即Thread1无法分配新日志，Oracle建议增加日志组和增大日志文件大小。作者通过查询v$log和v$logfile视图，观察并调整了日志文件，增加了新的日志组，并将日志文件大小从50M增加到300M，最终解决了日志切换延迟的问题。
本帖最后由 wzz123snow 于 2014-1-24 10:48 编辑
alert 日志中有这个错误
Thread 1 cannot allocate new log, sequence 319708
Checkpoint not complete
查看下，当前数据库的每组日志大小为50M，每天切换300次左右。
对此，ORACLE给出的提示：
This message indicates that Oracle wants to reuse a redo log file, but the current checkpoint position is still in that log.
In this case, Oracle must wait until the checkpoint position passes that log.
Because the incremental checkpoint target never lags the current log tail by more than 90% of the smallest log file size,
this situation may be encountered if DBWR writes too slowly, or if a log switch happens before the log is completely full,
or if log file sizes are too small. When the database waits on checkpoints,redo generation is stopped until the log switch is done.
ORACCLE给出的建议：
Add additional log group
Increase size of redo logs
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as sys@SG8 AS SYSDBA
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 319964 52428800 1 YES INACTIVE 3864251677 1/24/2014 7
2 1 319965 52428800 1 NO CURRENT 3864256297 1/24/2014 8
3 1 319963 52428800 1 YES INACTIVE 3864240823 1/24/2014 7
SQL> select bytes/1024/1024 from v$log;
BYTES/1024/1024
---------------
50
50
50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
3 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO03.LOG NO
2 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO02.LOG NO
1 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO01.LOG NO
SQL> alter database add logfile group 4 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO04.LOG' size 100m;--这组日志加小了，一会儿删掉。
Database altered
SQL> alter database add logfile group 5 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO05.LOG' size 300m;
Database altered
SQL> alter database add logfile group 6 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO06.LOG' size 300m;
Database altered
SQL> alter database add logfile group 7 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO07.LOG' size 300m;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 319964 52428800 1 YES INACTIVE 3864251677 1/24/2014 7
2 1 319965 52428800 1 NO CURRENT 3864256297 1/24/2014 8
3 1 319963 52428800 1 YES INACTIVE 3864240823 1/24/2014 7
4 1 0 104857600 1 YES UNUSED 0
5 1 0 314572800 1 YES UNUSED 0
6 1 0 314572800 1 YES UNUSED 0
7 1 0 314572800 1 YES UNUSED 0
7 rows selected
SQL> alter system switch logfile;
System altered
SQL> alter system switch logfile;
System altered
SQL> alter system switch logfile;
System altered
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
1 1 319964 52428800 1 YES INACTIVE 3864251677 1/24/2014 7
2 1 319965 52428800 1 YES ACTIVE 3864256297 1/24/2014 8
3 1 319963 52428800 1 YES INACTIVE 3864240823 1/24/2014 7
4 1 319966 104857600 1 YES ACTIVE 3864260234 1/24/2014 8
5 1 319967 314572800 1 YES ACTIVE 3864260236 1/24/2014 8
6 1 319968 314572800 1 YES ACTIVE 3864260239 1/24/2014 8
7 1 319969 314572800 1 NO CURRENT 3864260241 1/24/2014 8
7 rows selected
SQL> alter database drop logfile group 1;
Database altered
SQL> alter database drop logfile group 3;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319965 52428800 1 YES ACTIVE 3864256297 1/24/2014 8
4 1 319966 104857600 1 YES ACTIVE 3864260234 1/24/2014 8
5 1 319967 314572800 1 YES ACTIVE 3864260236 1/24/2014 8
6 1 319968 314572800 1 YES ACTIVE 3864260239 1/24/2014 8
7 1 319969 314572800 1 NO CURRENT 3864260241 1/24/2014 8
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319970 52428800 1 NO CURRENT 3864260514 1/24/2014 8
4 1 319966 104857600 1 YES INACTIVE 3864260234 1/24/2014 8
5 1 319967 314572800 1 YES INACTIVE 3864260236 1/24/2014 8
6 1 319968 314572800 1 YES INACTIVE 3864260239 1/24/2014 8
7 1 319969 314572800 1 YES ACTIVE 3864260241 1/24/2014 8
SQL> alter database drop logfile group 4;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319970 52428800 1 NO CURRENT 3864260514 1/24/2014 8
5 1 319967 314572800 1 YES INACTIVE 3864260236 1/24/2014 8
6 1 319968 314572800 1 YES INACTIVE 3864260239 1/24/2014 8
7 1 319969 314572800 1 YES ACTIVE 3864260241 1/24/2014 8
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319970 52428800 1 YES ACTIVE 3864260514 1/24/2014 8
5 1 319971 314572800 1 NO CURRENT 3864261051 1/24/2014 8
6 1 319968 314572800 1 YES INACTIVE 3864260239 1/24/2014 8
7 1 319969 314572800 1 YES ACTIVE 3864260241 1/24/2014 8
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319970 52428800 1 YES ACTIVE 3864260514 1/24/2014 8
5 1 319971 314572800 1 YES ACTIVE 3864261051 1/24/2014 8
6 1 319972 314572800 1 NO CURRENT 3864261053 1/24/2014 8
7 1 319969 314572800 1 YES ACTIVE 3864260241 1/24/2014 8
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319970 52428800 1 YES ACTIVE 3864260514 1/24/2014 8
5 1 319971 314572800 1 YES ACTIVE 3864261051 1/24/2014 8
6 1 319972 314572800 1 YES ACTIVE 3864261053 1/24/2014 8
7 1 319973 314572800 1 NO CURRENT 3864261132 1/24/2014 8
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319974 52428800 1 NO CURRENT 3864261156 1/24/2014 8
5 1 319971 314572800 1 YES ACTIVE 3864261051 1/24/2014 8
6 1 319972 314572800 1 YES ACTIVE 3864261053 1/24/2014 8
7 1 319973 314572800 1 YES ACTIVE 3864261132 1/24/2014 8
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319974 52428800 1 YES ACTIVE 3864261156 1/24/2014 8
5 1 319975 314572800 1 NO CURRENT 3864261164 1/24/2014 8
6 1 319972 314572800 1 YES ACTIVE 3864261053 1/24/2014 8
7 1 319973 314572800 1 YES ACTIVE 3864261132 1/24/2014 8
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319974 52428800 1 YES ACTIVE 3864261156 1/24/2014 8
5 1 319975 314572800 1 YES ACTIVE 3864261164 1/24/2014 8
6 1 319976 314572800 1 NO CURRENT 3864261181 1/24/2014 8
7 1 319973 314572800 1 YES ACTIVE 3864261132 1/24/2014 8
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
2 1 319974 52428800 1 YES INACTIVE 3864261156 1/24/2014 8
5 1 319975 314572800 1 YES ACTIVE 3864261164 1/24/2014 8
6 1 319976 314572800 1 YES ACTIVE 3864261181 1/24/2014 8
7 1 319977 314572800 1 NO CURRENT 3864261244 1/24/2014 8
SQL> alter database drop logfile group 2;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
5 1 319975 314572800 1 YES ACTIVE 3864261164 1/24/2014 8
6 1 319976 314572800 1 YES ACTIVE 3864261181 1/24/2014 8
7 1 319977 314572800 1 NO CURRENT 3864261244 1/24/2014 8
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
5 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO05.LOG NO
6 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO06.LOG NO
7 ONLINE D:\ORACLE\PRODUCT\10.2.0\ORADATA\AGENCY\REDO07.LOG NO
SQL> select bytes/1024/1024 from v$log;
BYTES/1024/1024
---------------
300
300
300
更多精彩内容，请关注我的博客。
5219
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
