The issue was related to incorrect handling of unused columns during such loads and can cause incorrect data getting loaded into a database under certain conditions.
I tried to reproduce the issue using one database in my setup but wasn't able to do that and stuck with two databases.
I used Oracle Database 12.1.0.2 with DBBP 12.1.0.2.160419 applied and GoldenGate 12.2.0.1.160419 Patch Set.
I'm going to use two database in my test case, let it be SOURCE and TARGET. Firstly, let's create a test schema with a single table populating it with some data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | DBA@SOURCE> grant create session, create table, unlimited tablespace to tc identified by tc;Grant succeeded.DBA@SOURCE> conn tc/tc@sourceConnected.TC@SOURCE> SQL> create table direct_load( 2 column1 int, 3 unused1 int, 4 column2 int, 5 column3 int);Table created.TC@SOURCE> TC@SOURCE> insert into direct_load values (1, 0, 2, 3);1 row created.TC@SOURCE> commit;Commit complete.TC@SOURCE> select * 2 from direct_load; COLUMN1 UNUSED1 COLUMN2 COLUMN3---------- ---------- ---------- ---------- 1 0 2 3 |
1 2 3 | TC@SOURCE> alter table direct_load set unused column unused1;Table altered. |
Consider the scenario when we have decided to resynchronize data in TARGET database from SOURCE database.
I'm going to remove all rows from the table in TARGET database first:
1 2 3 | TC@TARGET> truncate table direct_load;Table truncated. |
Let's use IRTC - as a name of the initial load replicat and IETC - as a name of the initial load extract:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | -- setup initial load replicatGGSCI (ogg-test) 2> edit params irtc-- UserIdAlias may be customized accordingly to your environment or simply use a username/password pairReplicat irtcAssumeTargetDefsUserIdAlias target_ogg_replicatDiscardFile ./dirrpt/irtc.dsc, Purge-- I want to use Direct Bulk LoadBulkLoad-- I used PDB "orcl" for my experiments:Map orcl.tc.*, target orcl.tc.*;-- setup initial load extractGGSCI (ogg-test) 149> edit params ietcExtract ietcRmtHost ogg-test, MgrPort 7809RmtTask Replicat, Group irtcUserIdAlias source_ogg_extractTable orcl.tc.direct_load;-- let's create GoldenGate processesGGSCI (ogg-test) 116> add replicat irtc SpecialRunREPLICAT added.GGSCI (ogg-test) 117> add extract ietc SourceIsTableEXTRACT added. |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | GGSCI (ogg-test) 15> info i*tc, tasksEXTRACT IETC Initialized 2016-09-01 08:54 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Not Available First Record Record 0Task SOURCEISTABLEREPLICAT IRTC Initialized 2016-09-01 08:54 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:00:28 ago)Log Read Checkpoint Not AvailableTask SPECIALRUNGGSCI (ogg-test) 16> start extract ietcSending START request to MANAGER ...EXTRACT IETC starting |
1 2 3 4 5 6 7 8 9 10 11 12 | GGSCI (ogg-test) 17> info i*tc, tasksEXTRACT IETC Last Started 2016-09-01 08:55 Status STOPPEDCheckpoint Lag Not AvailableLog Read Checkpoint Table ORCL.TC.DIRECT_LOAD 2016-09-01 08:55:27 Record 1Task SOURCEISTABLEREPLICAT IRTC Initialized 2016-09-01 08:54 Status STOPPEDCheckpoint Lag 00:00:00 (updated 00:02:09 ago)Log Read Checkpoint Not AvailableTask SPECIALRUN |
Yes, I had a warning like this:
1 | 2016-09-01 08:55:12 WARNING OGG-00752 Oracle GoldenGate Capture for Oracle, ietc.prm: Failed to validate table ORCL.TC.DIRECT_LOAD. Likely due to existence of unused column. Please make sure you use sourcedefs in downstream Replicat, or the target table has exactly the same unused columns when using ASSUMETARGETDEFS or DDL replication. |
Let's see what has loaded into TARGET database:
1 2 3 4 5 | TC@TARGET> select * from direct_load; COLUMN1 COLUMN2 COLUMN3---------- ---------- ---------- 1 2 |
1 2 3 4 5 | TC@SOURCE> select * from direct_load; COLUMN1 COLUMN2 COLUMN3---------- ---------- ---------- 1 2 3 |
That's the worst scenario. Originally I faced the issue while I was moving a bunch of data from one database to another using GoldenGate.
The load got ABENDED with ORA-01840 as in my case COLUMN3 had a DATE datatype which may be easily reproduced by the following code:
1 2 3 4 5 | SQL> select to_date('2', 'yyyy-mm-dd hh24:mi:ss') from dual;select to_date('2', 'yyyy-mm-dd hh24:mi:ss') from dual *ERROR at line 1:ORA-01840: input value not long enough for date format |
A few words about workarounds.
In this simple scenario, which I have written just for demonstration purposes, it was enough to drop unused columns in TARGET database:
1 2 3 | TC@TARGET> alter table direct_load drop unused columns;Table altered. |
