alter 日志提示 Private strand flush not complete

diyindo 发表于 2014-2-20 11:23:48

linux +11.2.0.4 单机

看到alter日志提示：
Current log# 1 seq# 5272 mem# 0: /oradata/orcl/orcl/redo01.log
Thu Feb 20 11:00:46 2014
Thread 1 cannot allocate new log, sequence 5273
Private strand flush not complete
  Current log# 1 seq# 5272 mem# 0: /oradata/orcl/orcl/redo01.log
Beginning log switch checkpoint up to RBA , SCN: 126089045
Thread 1 advanced to log sequence 5273 (LGWR switch)
  Current log# 2 seq# 5273 mem# 0: /oradata/orcl/orcl/redo02.log
Thu Feb 20 11:03:02 2014
Completed checkpoint up to RBA , SCN: 125964089
Thu Feb 20 11:04:51 2014
Thread 1 cannot allocate new log, sequence 5274
Private strand flush not complete
  Current log# 2 seq# 5273 mem# 0: /oradata/orcl/orcl/redo02.log

增加了redo日志组每个redo size 300M

1 5272 ACTIVE NO
2 5273 ACTIVE NO
3 5271 ACTIVE NO
4 5274 CURRENT NO
5 5269 ACTIVE NO
6 5270 ACTIVE NO
7 5266 INACTIVE NO
8 5267 INACTIVE NO
9 5268 INACTIVE NO
发现很多都是active的

日志切换频率
select thread#, to_char(first_time,'dd hh24:mi:ss') first_time, round(24 * 60 * (lead(first_time,1) over(partition by thread# order by first_time) - first_time) ,2) minutes from v$log_history v where recid >= 117 order by thread#, recid
1 19 12:46:56 2.03
1 19 12:48:58 2.68
1 19 12:51:39 3.37
1 19 12:55:01 4.07
1 19 12:59:05 5.67
1 19 13:04:45 4.23
1 19 13:08:59 2.45
1 19 13:11:26 3.63
1 19 13:15:04 6.68
1 19 13:21:45 8.03
1 19 13:29:47 3.85
1 19 13:33:38 7.45
1 19 13:41:05 3.18
1 19 13:44:16 8.52
1 19 13:52:47 0.12
1 19 13:52:54 0.07
1 19 13:52:58 0.13
1 19 13:53:06 2.25
1 19 13:55:21 6.32
1 19 14:01:40 4.7
1 19 14:06:22 3.07
1 19 14:09:26 8.13
1 19 14:17:34 4.27
1 19 14:21:50 7.93
1 19 14:29:46 0.67
1 19 14:30:26 7.22
1 19 14:37:39 8.18
1 19 14:45:50 8.15
1 19 14:53:59 5.93
1 19 14:59:55 0.68
1 19 15:00:36 4.32
1 19 15:04:55 2.72
1 19 15:07:38 2.68
1 19 15:10:19 3.82
1 19 15:14:08 2.88
1 19 15:17:01 4.35
1 19 15:21:22 3.13
1 19 15:24:30 3.22
1 19 15:27:43 3
1 19 15:30:43 2.53
1 19 15:33:15 2.88
1 19 15:36:08 3.52
1 19 15:39:39 3.18
1 19 15:42:50 5.05
1 19 15:47:53 5.63
1 19 15:53:31 6.43
1 19 15:59:57 1.37
1 19 16:01:19 4.47
1 19 16:05:47 2.28
1 19 16:08:04 2.73
1 19 16:10:48 3.28
1 19 16:14:05 3.97
1 19 16:18:03 5.28
1 19 16:23:20 4.32
1 19 16:27:39 2.57
1 19 16:30:13 1.7
1 19 16:31:55 4.38
1 19 16:36:18 5.58
1 19 16:41:53 5.03
1 19 16:46:55 5.97
1 19 16:52:53 9.2
1 19 17:02:05 6.07
1 19 17:08:09 4.38
1 19 17:12:32 0.55
1 19 17:13:05 0.7
1 19 17:13:47 1.92
1 19 17:15:42 2.57
1 19 17:18:16 4.5
1 19 17:22:46 4.75
1 19 17:27:31 3.62
1 19 17:31:08 2.18
1 19 17:33:19 2.72
1 19 17:36:02 4.68
1 19 17:40:43 8.05
1 19 17:48:46 9.88
1 19 17:58:39 1.7
1 19 18:00:21 2.52
1 19 18:02:52 3.08
1 19 18:05:57 2.27
1 19 18:08:13 1.83
1 19 18:10:03 2.77
1 19 18:12:49 3.1
1 19 18:15:55 3.35
1 19 18:19:16 9.53
1 19 18:28:48 2.08
1 19 18:30:53 4.15
1 19 18:35:02 10.45
1 19 18:45:29 11.17
1 19 18:56:39 3.82
切换时间间隔很小，是不是数据操作很频繁，300M redo很快就满了

show parameter FAST_
NAME       TYPE    VALUE
------------------------------------ ----------- ------------------------------
fast_start_io_target       integer    0
fast_start_mttr_target       integer    0
fast_start_parallel_rollback       string    LOW

SQL> show parameter FAST_START_MTTR_TARGET
NAME       TYPE    VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target       integer    0

SQL> show parameter LOG_CHECKPOINT_TIMEOUT
NAME       TYPE    VALUE
------------------------------------ ----------- ------------------------------
log_checkpoint_timeout       integer    1800

SQL> show parameter LOG_BUFFER
NAME       TYPE    VALUE
------------------------------------ ----------- ------------------------------
log_buffer       integer    46784512

SQL> show parameter sga

NAME       TYPE    VALUE
------------------------------------ ----------- ------------------------------
lock_sga                boolean    FALSE
pre_page_sga       boolean    FALSE
sga_max_size       big integer 32192M
sga_target       big integer 0

我开始以为是IO很慢，测试了下IO速度 180M/s

不知道还从哪里排查和分析，请朋友提示谢谢

江南的诗 发表于 2014-2-20 12:28:05

结果贴下：
select name,asynch_io from v$datafile f,v$iostat_file i where f.file#=i.file_no and (filetype_name='Data File' or filetype_name= 'Temp File' );

Maclean Liu(刘相兵 发表于 2014-2-20 12:31:17

问前先google;

http://t.askmaclean.com/thread-970-1-1.html

diyindo 发表于 2014-2-20 13:10:46

江南的诗发表于 2014-2-20 12:28 static/image/common/back.gif
结果贴下：
select name,asynch_io from v$datafile f,v$iostat_file i where f.file#=i.file_no and (file ...

结果如下：
/oradata/orcl/orcl/system01.dbf ASYNC_OFF
/oradata/orcl/orcl/system01.dbf ASYNC_OFF
/oradata/orcl/orcl/sysaux01.dbf ASYNC_OFF
/oradata/orcl/orcl/undotbs01.dbf ASYNC_OFF
/oradata/orcl/orcl/users01.dbf ASYNC_OFF

diyindo 发表于 2014-2-20 13:18:13

恩我每次手动checkpoint的时候，会等待
em显示

psufnxk2000 发表于 2014-2-20 13:41:36

回复的怎么都是仅作者可见？？

我也试试。
Alert Log Messages: Private Strand Flush Not Complete (Doc ID 372557.1)

diyindo 发表于 2014-2-20 14:05:08

psufnxk2000 发表于 2014-2-20 13:41 static/image/common/back.gif
回复的怎么都是仅作者可见？？

我也试试。

我看看论坛哪里可以设置

diyindo 发表于 2014-2-20 14:14:06

我手动checkpoint 的时间是20分钟不知道为什么这么久

页: [1]

Oracle数据库数据恢复、性能优化's Archiver

alter 日志 提示 Private strand flush not complete

alter 日志提示 Private strand flush not complete