The replication is a row-based one; that is, the changes are mined row-by-row on the mine. Thus while any SQL issued on the source database can change arbitrary number of rows, Dbvisit does not care about the actual SQL issued, only about the changes made to individual rows.The major consequence is that the SQL issued at the apply database is not the same issued against mine – instead, each SQL updates/deletes/inserts exactly one row, applying just one change. Thus if the SQL actually affects zero or more than one rows, data divergence occurred.
Another type of conflict is any error reported by Oracle – this can range from usual primary key or foreign key violation (another type of data divergence) to purely technical reasons (cannot extend datafile). The last type of conflict is lock timeout – if the apply waits for a row lock more than WATCHDOG_TIMEOUT seconds, a conflict is also reported. Note that when applying a row-change, previous values of the row at apply are checked (by means of adding them to the where clause). This effectively detects if the data in that row are inconsistent between mine and apply, and this is reported as a conflict, "0 rows updated/deleted." This can happen for update or delete only (there is no previous row to check for insert) and due to its special role, it can be handled differently.
Handling errors on source database, partially executed statements
The mine (and then apply as well) process follow the changes as they are written to redo logs by Oracle. This also defines it's (sometime peculiar) behavior in case of errors, statement rollbacks, statement restarts etc. In general:
- Oracle handles these cases in transaction-consistent way, and so do mine and apply. Thus after a commit (and thus to any other session as well), the data is in sync with source.
- However, Oracle writes to redo in an optimistic way – and thus in case of an partial execution followed a statement or partial rollback of a statement, the data is actually written to the redo logs and thus replicated as well.
- This means, in consistence with usual behavior on source, triggers can execute on data that are later rolled back. See for example discussion on one way how can this happen at write "consistency" at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:11504247549852
- One interesting case of this behavior is (primary, unique) key checking – Oracle first writes the row data, then goes on to update the index, failing on key violation, then rolls back the row change. The consequence is that apply also updates the row and if the key is present on apply database as well, it will also fail, causing a conflict.