ReplTalk – Oracle Publishing Error ORA-01455

by Chris Skorlinski, Microsoft SQL Server Escalation Services

Oracle Publishing, now deprecated in SQL Server 2014, provides ability to use Oracle server as a Transactional Replication Publisher to a Microsoft SQL Server subscriber.

Oracle Publishing Overview
http://technet.microsoft.com/en-us/library/ms151229.aspx

This feature was as designed as an Oracle to Microsoft SQL Server migration tool.  Other customers used Oracle Publishing to move occasionally changing tables.  The feature was never designed to handle large volume server-to-server traffic. As result, the Oracle Publishing will break after 2 billion data changes are replicated.  Once you hit 2147483647 (max of int datatype) data changes you’ll see the LogReader agent fail with messages like those shown below

Status: 0, code: 1455, text: ‘ORA-01455: converting column overflows integer datatype
Status: 0, code: 22037, text: ‘Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state ‘FETCH’.
The process could not format the specified message.
ORA-01455: converting column overflows integer datatype

You have 2 solutions

1) Take down and rebuild the Replication environment which resets the counter

2) Move to tools more suited for high-volume traffic such as Attunity CDC for Oracle.  http://www.attunity.com/products/attunity-cdc-ssis/oracle-cdc-for-ssis