expdp seemed to create inconsistent export even with flashback set (or did it.....)

 The steps we are following are these:

1) source system on SLES10 - we are doing a full schema level export using
FLASHBACK_TIME="to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"

This should guarantee us a consistent export of the schema at the closest SCN to that timestamp
We get no errors during the export

2) we copy that file to a destination box which is on SLES10

3) we import that file - all the table data loads fine and the row counts are the same as those in the export log.

4) when datapump reached the FK constraints 6 of them woouldn't enable with 'parent keys not found'

The 6 FK's in question are all on delete cascade references back to the same parent table, there are a further 3 child tables which also reference back to the same parent table (all on the same parent PK single column) which do enable OK. The parent rows are indeed not there. In live those rows no longer exist. It appears as if the extract has been done half way between the parent being deleted and the children then being deleted - but i didn't think this would be possible with a 'consistent' datapump export?

So my question is how is this possible?

All i can think is:

a) the tables are being extracted a slightly different scn's due to some datapump bug?
b) on delete cascade actions are somehow in a different scn to the initial delete from the parent table (seems unlikely)


c) a process connected to the destination database and this is what happened

1) import starts off
2) tables completed loading after 1 hour
3) indexes build for about 4 hours
4) FK's are built
5) import finishes

During step 3 (before the fk's existed) one of the application services decided to connect up and delete some of the parent rows - no fk's were present so the children were left alone.

Then when the FK's are built you get parent key not found......
Moral of this is don;t always assume its an oracle issue somewhere - always blame the developers/testers first..... :-)


Post a Comment