Required Configuration

Owned by Anonymous
Last updated: Apr 17, 2016 by Martin Knazovicky (Deactivated)

There are a few prerequisites for the Oracle to SQL Server replication to take place. 

On the target database ensure that snapshot isolation and read committed snapshot are ON:

ALTER DATABASE TEST
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE TEST
SET READ_COMMITTED_SNAPSHOT ON

 

Once the tables have been created on target database:

  • Ensure that you create index on target database that has disabled page locking  "ALLOW_PAGE_LOCKS = OFF"
  • On the target database ensure that table has an index created on all columns that will be used in where clauses in the update/delete sql statements on source database. (This will prevent certain situation when MS SQL server tends to create table lock and possible blocking locks).
  • Disable lock escalation on tables.
ALTER TABLE TEST.RATE SET (LOCK_ESCALATION=DISABLE);
ALTER INDEX RATE_UK ON TEST.RATE SET (ALLOW_PAGE_LOCKS=OFF);
ALTER TABLE schema.tablename SET (LOCK_ESCALATION=DISABLE)

In the replication console:

  • In the dbvisit replication console set the parameter APPLY_POSTPONE_LAST_LCR to YES (and restart)

dbvrep> SET *.APPLY_POSTPONE_LAST_LCR=YES
dbvrep>set *._APPLY_COUNT_LOOKAHEAD_READ = 10000
dbvrep>set *._APPLY_COUNT_LOOKAHEAD_READ_FASTFAIL = 50