Thursday, April 1, 2010

"PAUSED" state for streams capture process

My test replication somehow got broken and does not apply changes to the target database (Seem a new challange for me!) First thing i checked was streams capture process and i saw the weird message following.

select sid,
serial#,
capture_name,
state
from v$streams_capture;

/*
SID SERIAL# CAPTURE_NAME STATE
-- ------- ------------ -----
121 1524 CAPTURE_SCHEMA_U_EPEKER PAUSED FOR FLOW CONTROL
*/


Now if capture process is stopped, may be i need to check what is going on the apply process. But it seems nothing is received by the capture process. I think the problem should be on the capture side.

select sid,
serial#,
state,
apply#,
apply_name,
total_applied,
total_errors
from v$streams_apply_coordinator@ORCL11G;

/*
SID SERIAL# STATE APPLY# APPLY_NAME TOTAL_APPLIED TOTAL_ERRORS
--- ------ ---- ------ ----------- ------------- ------------
110 4208 IDLE 1 APPLY_SCH_U_EPEKER 0 0
*/


I still didnt understand anything from this message "PAUSED FOR FLOW CONTROL". I did a little search on Google and the i come to Oracle Documentation as always. It seems i need to check the streams_pool_size parameter.

select queue_name, sender_name, num_msgs, unbrowsed_msgs, memory_usage, publisher_state from V$BUFFERED_PUBLISHERS;

/*
QUEUE_NAME SENDER_NAME NUM_MSGS UNBROWSED_MSGS MEMORY_USAGE PUBLISHER_STATE
--------- ----------- -------- -------------- ----------- ---------------
QUEUE_CAPT_SCH_U_EPEKER CAPTURE_SCHEMA_U_EPEKER 15057 15057 87 IN FLOW CONTROL: TOO MANY UNBROWSED MESSAGES
*/


If the memory usage stands for MB which seems 87, then there could be a problem. As with the following invesitgation it seems ASSM gives 56Mb to streams_pool_size and it seems there could be a lack of memory. I decided to increase the streams_pool_size to 200Mb.

select name, value from v$parameter where name = 'streams_pool_size';

/*
NAME VALUE
---- -----
streams_pool_size 0
*/

select name, value/1024/1024 as mb from v$parameter where name = 'sga_target';

/*
NAME MB
---- --
sga_target 512
*/


select pool, round(sum(bytes)/1024/1024) as MB from v$sgastat group by pool;
/*
POOL MB
---- --
140
java pool 12
streams pool 56
shared pool 300
large pool 4
*/


alter system set sga_target=1G scope=spfile;
alter system set streams_pool_size=200M scope=spfile;
shutdown immediate;
startup;


As soon as the database opened capture process is up again. And the publishers state is now "PUBLISHING MESSAGES"

select sid,
serial#,
capture_name,
state
from v$streams_capture;
/*
SID SERIAL# CAPTURE_NAME STATE
--- ------ ------------ -----
151 1 CAPTURE_SCHEMA_U_EPEKER CAPTURING CHANGES
*/


select queue_name, sender_name, num_msgs, unbrowsed_msgs, memory_usage, publisher_state from V$BUFFERED_PUBLISHERS;
/*
QUEUE_NAME SENDER_NAME NUM_MSGS UNBROWSED_MSGS MEMORY_USAGE PUBLISHER_STATE
---------- ----------- -------- -------------- ------------ ---------------
QUEUE_CAPT_SCH_U_EPEKER CAPTURE_SCHEMA_U_EPEKER 44 0 5 PUBLISHING MESSAGES
*/


No comments:

Post a Comment