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:
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 'alter 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:
dbvrep>set *.APPLY_POSTPONE_LAST_LCR=YES dbvrep>set *._APPLY_COUNT_LOOKAHEAD_READ = 10000 dbvrep>set *._APPLY_COUNT_LOOKAHEAD_READ_FASTFAIL = 50 |