-
Title
Post stopped due to ORA-00060 -
Description
Post stopped with following errors:
"Notice 2014-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 2014-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]"
-
Cause
1. Missing index for foreign key constraint (FK).
2. Table has bitmap indexes
3. There is known performance issue with oracle apps interface table which can cause deadlock for post. Interface tables will have 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 key then it updates keys to something else. In such case post may have multiple sessions updating the same row with NULL key thus causing deadlock ORA-60.
4. A low value of INITRANS for the problem table or its associated index(es) on target can also result in deadlocks. What constitutes a low value is a subjective matter but for most cases an INITRANS value less than ten can be considered a low value.
-
Resolution
Workaround:
1. Add missing index for the FK and restart post.
2. Stop post, convert Bitmap index to B-tree or optionally drop Bitmap index, 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 same key of NULL.
Typically interface tables do not need to be replicated for oracle apps for reporting purposes and the work around would be to disable such tables.
If you must replicate this 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 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
Please refer to solution 67427 for more inforation about defining a key column.4. Reorg the table with a higher INITRANS and rebuild the index(es) with a higher INITRANS as applicable. The