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.
Configuring conflict handling
The response to a conflict can be configured before the conflict occurs. The configuration can be specified for each replicated table and for each operation type separately, and for the special case ("data" divergence) mentioned above.
The options are as follows:
| Operations | "Data" handler | "Error" handler |
|---|---|---|
| Update | discard | retry | overwrite | pause | abort | newer | older | sql | plsql | error | discard | retry | overwrite | pause | abort | plsql | error |
| Delete | discard | retry | overwrite | pause | abort | newer | older | sql | plsql | error | discard | retry | overwrite | pause | abort | plsql | error |
| Insert | n/a | discard | retry | overwrite | pause | abort | plsql | error |
| Transaction | n/a | discard | retry | overwrite | pause | abort | plsql | error |
The transaction handler is used for DDL, commits etc.
The "Data" handler is used only once for a conflict – if using this handler leads to a conflict again, "Error" handler is used for the next attempt. For the next change SQL, the Data conflict will be used again.
Additionally, logging may be specified:
- log: the conflict is logged, and a error plog is created with the offending statement
- nolog: the conflict is not logged
- fast_nolog: the conflict is not logged and it may be omitted from conflict counters altogether
- log_transaction: the conflict is logged, and a error plog is created with the offending transaction
- default: same as log
See the command chapter or use HELP SET_CONFLICT_HANDLERS for the exact syntax.
Use SHOW_CONFLICT_HANDLERS to query current settings of handlers.
Available handlers
- DISCARD: ignore the offending SQL and continue with the next one
- OVERWRITE: do not check old values, try again with just primary key in the where clause (thus this will fail if there is no row at all on apply with the PK value)
- NEWER,OLDER: look into target table (by primary key) and get values of specified columns (usually dates or number sequence). If the source row is newer/older, the operation becomes OVERWRITE, otherwise DISCARD.
- PLSQL: call user-specied PL/SQL function. The function must have prototype:
f(apply_old_data table%rowtype, has_found_apply_row boolean, primary_key_data table%rowtype, new_data table%rowtype) return number;
The return values are:
- 0: discard
- 1: overwrite
- 2: retry
- 3: the PL/SQL function resolved the conflict by itself and made all necessary changes.
The function must not issue a commit, as the transaction may be yet rolled back, if a rollback happened on source.
- RETRY: wait a few seconds (set by variable RETRY_TIME ) and try again
- PAUSE: wait for manual user resolution
- ABORT: kill apply process
- ERROR: rollback the transaction, continue applying other transactions
Handling current conflict (resolve conflict)
If the apply was paused due to a conflict, or it is retrying in a loop the same SQL again and again, you can instruct it about what to do next using the command:
RESOLVE CONFLICT id AS resolution
The conflict id is the number shown in the status bar, the resolution can be:
- IGNORE: skip the transaction
- RETRY: try again
- ABORT: abort the apply process
- RESTART: rollback and restart the transaction
- ROLLBACK:rollbacks the transaction
To see the SQL that is causing the conflict, logon to the apply (target database) as the Dbvisit Replicate schema owner (dbvrep) and query table:
DBRSAPPLY_CONFLICT_LOG
This lists all the conflicts encountered, including failing SQL.
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.