oracle streams 11g数据复制 pdf,oracle 11g streams 数据不同步 | My Oracle Life

Streams – C001: large txn detected

Posted by getmehere on May 17, 2009 · Leave a Comment

We had a performance issue, due to data scrub session which was executed without a commit.

The transaction session was abruptly closed by a developer, forcing itself to get hung and was later killed by us

after confirming this session was the culprit.

Unluckily we killed this session after 12hrs, this impacted the our streams latency

the update which ran for more than 12hrs only to be killed by us on source, this obviously

will run long time in the target until the session gets rolled back after running more than

12hrs. This means latency difference will be more and this being the christmas holiday,

Luckily we didn’t get anybody complaining of it. Below is the solution followed, although we had our

doubts due to lack of confidence because we are ameture in streams process.

below is the info from alert log

——————————————–

Sun Dec 27 20:45:16 2009

C001: large txn detected (17372231 LCRs), xid: 0×0045.01e.0012f30b

convert 0045.01e.0012f30b (hexadecimal to decimal) in 3 sets, in this case it is

0045 = 69

01e = 30

0012f30b = 1241867

When Capture mines a long transaction, it logs transaction details into v$streams_transaction view. And, the XID info is also logged into alertSID.log by Capture process.

now exec the below sql

set lines 300 pages 3000

select * from V$STREAMS_TRANSACTION;

From the above, look for the values for the column [XIDUSN, XIDSLT, XIDSQN]

Some times, Streams may take time to mine a transaction and may not log any info about long transaction in alert.log OR in v$streams_transaction view. The v$logmnr_transaction view is really helpful and will also provide the transaction details being mined by capture’s logminer process.

now exec the below sql

select xid, xidusn, xidslt, xidsqn, mining_status from v$logmnr_transaction where mining_status = ‘ACTIVE’;

The xid column need not have the same value from the alert log, for eg in my case alert log had the value

(0045.01e.0012f30b) and the xid column had the value (45001E000BF31200), hence it will be confusing to select the correct xid, hence take the xid where these 3 columns (xidusn, xidslt, xidsqn) has the above calculated decimal value.

eg :- 45001E000BF31200, 69, 30, 1241867, PINNED

69.30.1241867 (is the transaction id – we will use this later)

Find the sql_id of the above long running transaction using the below sql, by substituting the xid value

select distinct sql_id from v$active_session_history where xid = ’45001E000BF31200′;

Find the exact sql using the sql_id we got from our last step

set long 10000

select sql_text from v$sql where sql_id = ’76n6dpqstc5ax’;

Note : The view v$active_session_history contains recent sessions activity. If the above transaction is most recent then it would be captured (if that sql happen to take couple of secs to run). If the above returns no rows then, we can use the below sql

select distinct session_id, session_serial#, sql_id from DBA_HIST_ACTIVE_SESS_HISTORY where xid = ’45001E000BF31200′;

We can also cross verify for the correct transaction if you happened to remember the session ID and the serial# which you killed in the source. We accidentally happened to remember , thereby assuring ourself.

Find the exact sql using the sql_id we got from our last step

select sql_text from dba_hist_sqltext where sql_id = ’76n6dpqstc5ax’;

select sql_text from dba_hist_sqltext where sql_id = ’76n6dpqstc5ax’;

SQL_TEXT

——————————————————————————–

update usr.tbl set col1 = ‘val’,col2=sysdate, col3=’value’,col4=sysdate+1

Now is that we have got the sql, sql_id, xid, transaction_id. We for sure know this transaction didn’t complete, since we killed it earlier in source, so why should we allow it to run in the target, only to be rolled back after the stipulated time it took in the source, we will be wasting our time by allowing it to run and may run into other issues.

Hence we simply ignored the transaction to be captured.

In the capture side, we did the following

prompt

prompt ++ Current Long Running Transactions ++

prompt Current transactions open for more than 20 minutes

prompt

col runlength HEAD ‘Txn Open|Minutes’ format 9999.99

col sid HEAD ‘Session’ format a13

col xid HEAD ‘Transaction|ID’ format a18

col terminal HEAD ‘Terminal’ format a10

col program HEAD ‘Program’ format a27 wrap

select t.inst_id, sid||’,’||serial# sid,xidusn||’.’||xidslot||’.’||xidsqn xid, (sysdate – start_date )* 1440

runlength ,terminal,program from gv$transaction t, gv$session s where t.addr=s.taddr

and (sysdate – start_date) * 1440 > 20;

The above will return rows only if the transaction is recent, in my case it is old, but since we already know the transaction id we directly executed the next step. Rememberthe value we got from the column (xidusn, xidslt, xidsqn).

For ignoring transaction at capture , use the following steps on source site:

execute dbms_capture_adm.stop_capture(‘CAPTURE_NAME’);

execute dbms_capture_adm.set_parameter(‘CAPTURE_NAME’,’_ignore_transaction’,’69.30.1241867′);

execute dbms_capture_adm.start_capture(‘CAPTURE_NAME’);

References –

746247.1 (Metalink Doc ID)