Oracle Stream忽略大事务 C001: large txn detected

源数据库

操作系统：AIX 6.1

数据库版本：Oracle 10.2.0.4 RAC单向schema级别复制至非RAC

目标数据库

操作系统：Centos 5.4

数据库版本：Oracle 11.2.0.2

现象：apply长时间没有同步，apply error中无报错信息，查看captrue 端数据库alert发现有下面输入信息

1) 检查源库的alert log，发现大量large txn detected

C001: large txn detected (76042142 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 04:45:03 2011

C001: large txn detected (78290526 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 04:55:03 2011

C001: large txn detected (80502433 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 05:05:03 2011

C001: large txn detected (82753961 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 05:15:04 2011

C001: large txn detected (85000771 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 05:25:03 2011

C001: large txn detected (87087911 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 05:35:04 2011

C001: large txn detected (89236244 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 05:45:04 2011

C001: large txn detected (91461549 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 05:55:04 2011

C001: large txn detected (93237056 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 06:05:05 2011

C001: large txn detected (95410577 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 06:15:05 2011

C001: large txn detected (97406466 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 06:25:05 2011

C001: large txn detected (99577844 LCRs), xid: 0x0006.023.0000b016

Thu Oct 27 06:35:05 2011

C001: large txn detected (101705806 LCRs), xid: 0x0006.023.0000b016

2) 查询活动事务和事务号
select xid, xidusn, xidslt, xidsqn, mining_status,xidusn||'.'||xidslt||'.'||xidsqn transaction_id from v$logmnr_transaction where mining_status = 'ACTIVE';

发现有大量xid为0003000D0000945E的logmnr事务,注意这里的transaction_id下面需要用到

3) 查询事务的sql_id及其对应的语句

SQL> select distinct sql_id from gv$active_session_history where xid = '0003000D0000945E';

SQL_ID

-------------

0k91g13q8nhqc

SQL> select sql_text from dba_hist_sqltext where sql_id in ('0k91g13q8nhqc');

分析这个语句，为创建一个超过1亿条记录的临时表，和开发人员确认后这个表可以不进行同步，只是一个中间表(如果需要使用当然也可以使用其它方式进行同步，如expdp,impdp)

4) 停止捕捉进程、忽略活动事务、启动捕捉进程

-- #停止Capture进程

begin

dbms_capture_adm.stop_capture(

capture_name => 'CAPTURE_RACDB');

end;

/

-- #这里的事务ID为了2中的transaction_id

execute dbms_capture_adm.set_parameter('CAPTURE_RACDB','_ignore_transaction','3.13.37982');

-- #启动Capture进程

begin

dbms_capture_adm.start_capture(

capture_name => 'CAPTURE_RACDB');

end;

/

Apply端忽略大事务

当然从apply端也可以忽略大事务

1) 查看大事务和原库执行时间，执行记录数

SELECT t.xidusn||'.'||t.xidslt||'.'||t.xidsqn transaction_id ,message_count,t.first_message_create_time FROM DBA_APPLY_SPILL_TXN t;

2）忽略大事务

-- #停止Apply进程

begin

dbms_apply_adm.stop_apply(

apply_name => 'APPLY_RACDB_TO_ORA72');

end;

/

SQL> exec dbms_apply_adm.set_parameter('APPLY_RACDB_TO_ORA72','ignore_transaction','6.35.45078');

PL/SQL procedure successfully completed

-- #启动Apply进程

BEGIN

dbms_apply_adm.start_apply(

apply_name => 'APPLY_RACDB_TO_ORA72');

end;

/

再查看执行状态

SELECT sid,apply_name,server_id servid,state,commitscn,total_messages_applied total_mess,apply_time,applied_message_number app_mess_num,applied_message_create_time app_create_time FROM v$streams_apply_server;