Tables are OUT OF SYNC in RAC environment. (16080)

In a RAC Environment a lot of tables are going out of sync constantly.

Under very unusual circumstances involving rapid updates and queries of the same data from different instances, the SCN might not be refreshed in a timely manner if MAX_COMMIT_PROPAGATION_DELAY is set to 700 on target RAC.

Check to see if LOAD BALANCE is set to ON in TNSNAMES and MAX_COMMIT_PROPAGATION_DELAY is set to 700 on the target RAC databases.

1. Shutdown shareplex
2. Set MAX_COMMIT_PROPAGATION_DELAY to 0 on all nodes of RAC on target and bounce instances on each node.
3. Restart shareplex
4. Resync target either using hot backup and reconcile or use compare/repair.

See below for the details about MAX_COMMIT_PROPAGATION_DELAY parameter. This parameter specifies the maximum amount of time allowed before the system change number (SCN) held in the SGA of an instance is refreshed by the log writer process (LGWR). It determines whether the local SCN should be refreshed from the lock value when getting the snapshot SCN for a query. Units are in hundredths of seconds.

Under very unusual circumstances involving rapid updates and queries of the same data from different instances, the SCN might not be refreshed in a timely manner. Setting the parameter to zero causes the SCN to be refreshed immediately after a commit.

Since Shareplex connects to both the nodes at the same time if two updates occur within the same second and the first statement executes on node A and due to the commit delay Node B does not see the change and the second update on Node B fails as the pre image does not match.

Set this parameter value to 0 on both the nodes if target is RAC.

NOTE: In 9i RAC for Oracle, the default value for MAX_COMMIT_PROPAGATION_DELAY is 700. In 10G RAC for Oracle, the default value for this parameter is 0.

RAC on target.