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
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
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
The fix was slightly different as the destination_version needed to be 19 (for Oracle 19c) instead of 12.
use master
exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '19'
exec sys.sp_MSrepl_ORA_MSSQL_datatypemappings @source_dbms = 'ORACLE', @source_version = '19', @destination_dbms = 'MSSQLServer'
go
Creating datatype mappings for MSSQLSERVER to ORACLE19