Post stopped with the following
"Notice 2024-11-04 19:59:59.393863 13486 1088403776 s:4 Poster: ORA-00060: deadlock detected while waiting for resource. (posting from source, queue qname, to target) [module osp]
Error 2024-11-04 19:59:59.396364 13486 1088403776 s:4 Poster: que/seq = x/xxxxx seqno/offset = xx/xxxxx Unexpected Oracle error: ORA-00060: deadlock detected while waiting for resource. (object name: "OWNER"."TABLE") (posting from source, queue qname, to target) [module opo]"
1. Missing index for foreign key constraint (FK).
2. The table has bitmap indexes.
3. There is a known performance issue with the Oracle apps interface table, which can cause deadlock for Post.
Interface tables will have the table name with %_INTERFACE_% or %_INT_% in them.
The reason is that interface tables usually have nullable keys, and there may be duplicate rows with NULL keys.
Typically, the application is inserting null values for the key, then it updates the key to something else.
In such cases, Post may have multiple sessions updating the same row with NULL key, thus causing deadlock ORA-00060.
4. A low value of INITRANS for the problematic table or its associated index(es) on Target can also result in deadlocks.
What constitutes a low value is a subjective matter, but typically, an INITRANS value less than 10 is considered a low value.
Workarounds:
1. Add the missing index for the FK and restart Post.
2. Stop Post, convert the Bitmap index to B-tree index, or optionally drop the Bitmap index, and restart Post.
3. If you are replicating tables with %_INTERFACE_% or %_INT_% in them, check for duplicate rows with NULL keys for those tables:
For example: select count(*) from AR.RA_INTERFACE_LINES_ALL where INERFACE_LINE_ID is null;
If the above query returns more than one row, then you have duplicate rows with the same key of NULL.
Typically, interface tables do not need to be replicated for Oracle apps for reporting purposes, and the workaround would be to disable such tables.
If you must replicate the table, then you must define a key in the config file that will use this column and another column(s),
and thus avoid the null key column.
In other words, identify a unique column(s) (or those with the most distinct) and combine with the column that has the nulls.
Here's the query to help you find distinct columns:
select num_distinct, column_name from dba _tab_columns where table_name='YOURTABLE" order by num_distinct;
Here's an example of a config file entry:
jane.sales !key (emp_no,emp_name) jane.sales sysB@o.oraB
4. Reorg the table with a higher INITRANS and rebuild the index(es) with a higher INITRANS as applicable.
