This discussion is archived
4 Replies Latest reply: Oct 19, 2012 4:47 AM by user476243 RSS

Deadlocks on destination database (Apply process) during batch process

user476243 Newbie
Currently Being Moderated
11.2.0.3 OEL 5

Has anyone experienced deadlocking on the destination database during a batch process run on the source database?

I am seeing this and it seems to skip a record and then it gets hung. The apply process cannot continue until the missing record is applied.

Note this only happens during batch processing. Is it that it cannot keep up?

THanks.
  • 1. Re: Deadlocks on destination database (Apply process) during batch process
    damorgan Oracle ACE Director
    Currently Being Moderated
    Yes it has been observed before and from my experience always caused by application design.

    For further help you can either provide us with a lot of information or open an SR at MOS.

    In either case the first thing you need to do is track down the specific transactions that are deadlocking.
  • 2. Re: Deadlocks on destination database (Apply process) during batch process
    user476243 Newbie
    Currently Being Moderated
    Thanks for your reply Dan.

    I might just open up a SR.

    Apart from deadlocks, would you say the same thing if we are getting the ORA-26787 error that the row does not exist (BTW, I am aware of error/conflict handlers for these ORA errors as mentioned in the documentation...but they are not working for me right now)?
    It's as if a LCR got skipped/missed and then the following LCR could not apply the changes because it needed the changes in the previous LCR to successfully complete. So now we have a gap and thousands of errors (dependencies). And until that is resolved, the apply process cannot apply the LCRs. I have even tried to explicitly insert the missing record from the source 'insert into table_name select * from table_name@sourcedblink where...' and it says it has inserted and commited, but there is no row there. Strange.

    Any feedback in understanding this would be awesome!

    Thanks again.
  • 3. Re: Deadlocks on destination database (Apply process) during batch process
    damorgan Oracle ACE Director
    Currently Being Moderated
    Take a look here ... this may help.
    http://docs.oracle.com/cd/B28359_01/server.111/b28322/troub_rep.htm
  • 4. Re: Deadlocks on destination database (Apply process) during batch process
    user476243 Newbie
    Currently Being Moderated
    Thank you Mr. Morgan.

    It was helpful, even though I had read it before.

    I ended up using the 'EXEC DBMS_STREAM.SET_TAG(tag => HEXTORAW('17'));' solution and then ran EXECUTE_ALL_ERRORS for the apply process. This did not really make sense to me as the changes were already captured and propagated over to the dest db. Anyways it cleared up the errors.

    However, I did find something strange. In the documentation, it mentions to use the DBMS_STREAM.SET_TAG... to prevent changes from being captured. So I did a test after running that procedure. I create a test table in my schema and when I checked the dest db, IT WAS THERE! Why did that happen? It was executed in the same session too. Just curious.

    Thanks for all your help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points