Oracle DB version: 10.2.0.5
I setup the uni-directional streams replication of a schema. The streams is running fine and the start SCN and required checkpoint SCN were moving forward. I set capture retention to 3 days. But, the required checkpoint SCN, start SCN and fist SCN are not moving forward from past four days. The streams capture and apply running perfectly and it's applying all the latest records.
But, if the streams is restarted for any case, the capture may look for the old archivelogs since the required checkpoint SCN is not moving forward. What is possible reason for it and how to resolve it?. Could someone explain please?.
We have found 2 causes for this so far:
1- Check the presence of pending transaction - look in DBA_2PC_PENDING and resolve these
2- Check for old open transactions. In our case, some developpers were doing explain plan in Toad that left open transaction for days...
select round((sysdate - t.start_date)*24*60) minutes, s.inst_id, s.sid, s.serial#, s.username, s.program, s.module, s.action, t.xidusn, t.used_urec, t.used_ublk, t.recursive, t.start_date
from GV$SESSION s, GV$TRANSACTION t
where s.saddr = t.ses_addr
and s.inst_id = t.inst_id
order by 1 desc;
Get rid of the old transaction then do:
exec DBMS_CAPTURE_ADM.set_parameter('capture_name', '_CHECKPOINT_FORCE', 'Y');
Hope this helps,
The First SCN, Required Checkpoint SCN, start SCN are hanging again. I don't see any 2PC pending transactions. The streams apply is working fine. But, these SCNs are not moving forward on capture side. Any idea why?.
I have already set the CHECKPOINTFORCE to Y.