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)
It is possible to use the following script to generate the list of statements for execution against the desired schema.
SELECT 'ater table [' + schema_name(t.schema_id) + '].[' + t.NAME + '] set (LOCK_ESCALATION=DISABLE);' AS stmt FROM sys.tables t WHERE lock_escalation <> 0 UNION ALL SELECT 'ALTER INDEX [' + i.NAME + '] on [' + schema_name(t.schema_id) + '].[' + t.NAME + '] set (ALLOW_PAGE_LOCKS=OFF);' AS stmt FROM sys.tables t INNER JOIN sys.indexes i ON (i.object_id = t.object_id) WHERE i.NAME IS NOT NULL AND allow_page_locks <> 0
In the replication console:
- In the dbvisit replication console set the parameter APPLY_POSTPONE_LAST_LCR to YES (and restart)
- Set _APPLY_COUNT_LOOKAHEAD_READ and _APPLY_COUNT_LOOKAHEAD_READ_FASTFAIL
dbvrep> SET *.APPLY_POSTPONE_LAST_LCR=YES dbvrep>set *._APPLY_COUNT_LOOKAHEAD_READ = 10000 dbvrep>set *._APPLY_COUNT_LOOKAHEAD_READ_FASTFAIL = 50