SQL Server Replication issue to oracle

Hi Friends,

Today I faced a challenging scenario with respect to SQL Server Replication.

We have SQL Server 2008 R2 acting as publisher/Distributor and Oracle 11 G acting as subscriber. As part of Migration both the technologies were upgraded.

SQL Server was upgraded to 2014 and Oracle to 12CR1.  After this up-gradation the replication started failing with the error message:

ORA-01867: the interval is invalid (Source: MSSQL_REPL_ORACLE, Error number: 1867)

As part of troubleshooting I initially suspected datatype mismatch. Luckily the database of oracle which was running on older version was not dropped.

This happened when we were replicating an indexed view so when I tried to change the connectivity to older version of Oracle(11 G) from the newer SQL version(2014) it was working fine.

So there is something dubious to check for and after googling I came across one solution. There lies some changes with respect to oracle datatype mappings from SQL Server to Oracle older and newer version's. However this really took a lot of time to figure out where the issue was and in the meanwhile I provided temporary solution by exporting the data through BCP.

The issue is  MSSQL date time data type is being translated to TIMESTAMP(6) on Oracle 12c
Where as on Oracle 11G this is being translated to TIMESTAMP(3).

SQLServer holds the Replications data mappings for database in view sysdatatypemappings

We tried running the below statement:

use msdb
exec sp_MSrepl_dropdatatypemappings
exec sp_MSrepl_createdatatypemappings
go

But this was creating until Oracle version 11.

In order to create the mappings for Oracle version 12 below queries needs to be run. (this needs to be run with DAC, do as described below. Update the username and password before running)
1. Run CMD
2. sqlcmd -S SERVERNAME  -U <<sysdbaUser>> -P <<sysdbapass>> -d master -A
3. exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'
4. go
5. exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'
6. go

After running the above statements inside the DAC connection the replication started working as expected.

Comments

Thank you so much for sharing this information!!
vijay said…

Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
Oracle training in Chennai
| Best Oracle training in Chennai | Top oracle training in Chennai |Oracle training Institute in Chennai | Oracle training in KK nagar

Finn said…
Thank you for the solution. Never faced this issue. However, this could be extremely useful for those, who googles similar bugs.