Page Comparison

There are manily two major prerequisites a few prerequisites for the Oracle to MSSQL replication to SQL Server replication to take place. The first requirement is to have correct snapshot isolation of target database in order to avoid locking when replication takes place. Dbvisit Replicate also requires Data Source Name(DSN) configured with the same name on both source and target systems.   

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