Weird issue with data transfer to Oracle

  • Hello all...  had an odd issue crop up and was wondering if anyone had ideas on what might have caused it...

    We have a nightly job that copies data from one of our local databases (SQL Server 2016) to an Oracle database at our corporate headquarters.  The setup is pretty basic - we have a linked server using the OraOLEDB.Oracle provider.  Overall this has worked pretty flawlessly.  We're only transferring a few hundred thousand rows.

    The job is set to retry 10 times in case of network issues and we have logic to track the last successful table that was copied and start after that point.

    The issue is that last night, it doesn't appear to have copied all of the data on multiple tables.  It obviously copied some data because the first thing we do is clear the tables on the Oracle side - but the row counts did not match.  However, according to the job history no errors occurred; there were no retries and the job reports success.  I ran the exact same script today and it worked as expected.

    We have had failures in the past due to networking issues, but those have always been caught.  I am trying to figure out how this time the inserts failed - multiple times - but no error.

    Anyway, I realize this is all kinda vague but was just wondering if anyone has seen something like this before, or might have some insight as to how it might happen.

    thanks

     

  • so , some tables got partial data? are you using SSIS? i'm kinda going down the "batch size" route.. maybe incorrectly

    not sure why you aren't getting an error, but you could hand code in a check at then end of each table copy, and throw an error if the rowcounts don't match .. I suppose i'm suggesting check after each table, not at the end of the job

     

    mv

    MVDBA

  • cphite wrote:

    We have had failures in the past due to networking issues, but those have always been caught.  I am trying to figure out how this time the inserts failed - multiple times - but no error.

    Just a thought but maybe it's not the inserts failing - could be the selects didn't capture all the rows. One scenario I can think of is if any locking hints are used and there is some activity on the tables in the SQL Server end when the job runs.

    Sue

  • Sue_H wrote:

    cphite wrote:

    We have had failures in the past due to networking issues, but those have always been caught.  I am trying to figure out how this time the inserts failed - multiple times - but no error.

    Just a thought but maybe it's not the inserts failing - could be the selects didn't capture all the rows. One scenario I can think of is if any locking hints are used and there is some activity on the tables in the SQL Server end when the job runs.

    Sue

    good point - a further question  - at a guess, what percentage of data is missing?  if it's less than 5% i'd go with sue's theory. 50% then i'm going with my first guess

    MVDBA

  • We aren't using SSIS - we have a stored procedure that we call where we're doing basically this for each of about a dozen tables:

    truncate table xfer_fact_booking
    insert xfer_fact_booking
    select
    *
    from
    bizint.financials.dbo.fact_booking

    The fact_booking is a view; we put the results in table xfer_fact_booking specifically to avoid issues with locking and whatnot.  The database is used only for this purpose, so there really isn't any other activity that should occur.  After we fill that table we delete from the remote Oracle table and insert into it as follows:


    delete from [ORACLEBOX.OURCORP.COM:1533]..[STG_OWNER].[FACT_BOOKING_STG]
    insert [ORACLEBOX.OURCORP.COM:1533]..[STG_OWNER].[FACT_BOOKING_STG]
    select * from xfer_fact_booking

    In the past we have had network issues where it'll time out - but that always results in an error.  In this case, it reported no errors and proceeded as though everything was fine.  The only indication we had that anything was wrong was that we do a COUNT(*) against the tables on both sides as a last check, and that indicated different row counts.

    I appreciate the replies though...  I am going to take Mike's advice and add some checks at each table...

     

  • Found the problem....  *sigh*

    Our infrastructure team spun up a copy of our production environment, but forgot to update the firewall to prevent it from communicating outside itself.  So it updated the data after the "real" one did...

     

     

  • cphite wrote:

    Found the problem....  *sigh*

    Our infrastructure team spun up a copy of our production environment, but forgot to update the firewall to prevent it from communicating outside itself.  So it updated the data after the "real" one did...

    Heh... perhaps you invented a whole new meaning to "double entry accounting". 😀

    Anyway... thanks for updating us on what the true fault turned out to be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hehe...  the worst part is the same thing happened like a year ago, but it's been fine since we did the firewall changes...  they just forgot to apply them when they spun up the test environment.

    I've added a step to the job to check via IP address if it's running in production, and to abort if it's not.

  • cphite wrote:

    Hehe...  the worst part is the same thing happened like a year ago, but it's been fine since we did the firewall changes...  they just forgot to apply them when they spun up the test environment.

    I've added a step to the job to check via IP address if it's running in production, and to abort if it's not.

    be carefull about using IP address - use a DNS name - when you upgrade or move hardware it becomes a real pig

    MVDBA

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply