Post process apparentelly is not posting messages or is too slow, and in the event_log you can see serious messages regarding sessions blocks and/or operation is too slow:
Notice 2015-04-09 10:37:30.477723 5982 7 Poster: Operation ODR_UPDATE1 on table "OWNER"."TABLE" is taking longer than 120 seconds. SID/serial: 9999/9998 Subqueue: 37. (posting from SOURCEDB, queue POST_QUEUE1, to TARGETDB) [module opo]
Warning 2015-04-09 10:37:48.592152 6263 1 Poster: Main thread is waiting 479 seconds for session 819 to commit before processing next operation. (posting from SOURCEDB, queue POST_QUEUE1, to TARGETDB) [module opo]
Warning 2015-04-09 10:40:51.615779 6263 1 Poster: Main thread is waiting 659 seconds for session 819 to commit before processing next operation. (osting from SOURCEDB, queue POST_QUEUE1, to TARGETDB) [module opo]
This situation can be caused by the lack of index or/and index in UNUSABLE state on target's table. A weak index or with a high Blevel also can cause slowerness.
This is a database issue, in order to identify and solve it, follow this procedure:
1 - Check what are the tables that event_log is Noticing that are taking too long to execute the transaction, in our example is the table OWNER.TABLE:
Notice 2015-04-09 10:37:30.477723 5982 7 Poster: Operation ODR_UPDATE1 on table "OWNER"."TABLE" is taking longer than 120 seconds. SID/serial: 9999/9998 Subqueue: 37. (posting from SOURCEDB, queue POST_QUEUE1, to TARGETDB) [module opo]
2 - Execute this query on the Target's Database, in order to verify how is the state for the index for this table.
SQL> select owner, index_name from dba_indexes where status='UNUSABLE' and owner='OWNER';
If there is any index listed as a result, rebuild the index in order to get it usable by Oracle Database.
If there is no index listed as a result, check if there is any index on the table with this query:
SQL> select owner, index_name from dba_indexes where owner='OWNER' and table_name='TABLE';
If there is no index on this table, replicate the same indexes that you have on Source for this table, in the Target.
If there is no unusable indexes and the table have proper indexes, verify the BLEVEL of those index in order to check if it's more than 3, and rebuild the index that have more than 3 as a BLEVEL value.
SQL> select owner, index_name,blevel,leaf_blocks from dba_indexes where owner='OWNER' and table_name='TABLE';
In order to rebuild the index, you can use the following statement:
SQL> ALTER INDEX OWNER.TABLE REBUILD ONLINE;
After that, stop and start post process and keep an eye on event_log in order to check if the messages are gone.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center