Daily Archives: 12.01.2011

GoldenGate is a powerful tool bought by Oracle to replace the “Oracle Streams”. It allows heterogeneous replication of data and works just fine almost out-of-the-box.

My client uses it to make a cache copy of data a kind of active standby while running on Oracle 10.2.

Today I had to fight for a problem that comes from the application, which seldom writes to the source database dates in a invalid format, with an year 0 (Oracle should not accept, but the year got in, we are still investigating how).

This makes the GoldenGate replication process to abend, as the write on the target database fails with an “ORA-01841: (full) year must be between -4713 and +9999, and not be 0”.

For the workaround, using GoldenGate MAP function, I managed to transform the dates like the following:
Source: 0000-12-09:08:11:20
Target: 0001-12-09:08:11:20

MAP NESSOFT.NEXT_ORD, TARGET NESSOFT_NL.NEXT_ORD , &
COLMAP ( USEDEFAULTS,
EXPIRE = @IF ( @STRCMP(@STREXT(EXPIRE,1,4),"0000") = 0, @STRCAT("0001-",@STREXT(EXPIRE,6,18)),EXPIRE)
);

This allowed to me also to discover the discard file (.dsc) on the dirrtp folder of GoldenGate, where you can see the output of the MAP function, useful for the debug.