ODS out of sync with Datamart

[Adam_W]

We had a major network and system outage yesterday and, to cut a long and painful story short, some tables in ODS were corrupted (unfortunately it was not possible to see which ones), which had the knock-on effect of stopping Datasourcer from working.

My main concern was getting Datasourcer working again, so I restored the previous evening's backup of ODS.  I knew some issues would arise from this but better to at least have some data being captured!

Now ODS and Datamart are obviously out of sync and I have the situation where chunks are being marked as transferred in DMA with a date earlier than they were recieved.  I also see the following error in the ETL logs:

[i]ETL_Trans_Only*:* 02:33:46.614 Thread.DstChunkWritersManager java.sql.BatchUpdateException: Violation of PRIMARY KEY constraint 'PK_CHUNK_LOG'. Cannot insert duplicate key in object 'dbo.CHUNK_LOG'.[/i]

I guess when Datasourcer started collecting data again, it assigned "new" chunk IDs to each chunk, but when ETL tries to transfer them to Datamart, the chunk IDs correspond to earlier data which had already been transferred.  And DMA obviously picks up the transfer time from the END_WRITE column in Datamart's CHUNK_LOG table.

What I need to do is try and get any data that hasn't been deleted from ODS since yesterday over into Datamart.  I know the chunk IDs of this data but what I don't know is whether I can simply:

1. Change the transfer time to NULL for those records in ODS which it thinks have been transferred but which I know contain new data;
2. Remove the records with corresponding chunk IDs from Datamart's CHUNK_LOG table
3. Run ETL and let it do its stuff

I know from reading previous posts that a few people here have had to try similar things so the benefit of any wisdom before I make things worse would be great!!

[Adam_W]

Well, I took a backup of ODS and Datamart and went ahead and tried this, and it worked fine.

In case it's of any use to anyone else, here's what I did altogether.  Although obviously be sure that you understand the problem fully before messing around with the DBs like this!  Also make sure ETL is not already running.

1.  Ran the following query against ODS to find all the chunks since the restore:

SELECT log_id
FROM ol_chunk_log
WHERE delete_time is NULL
AND receive_time > [i]<time of database restore>[/i]

2.  Some of these chunk IDs already existed in Datamart but related to older data so the transferred time in ODS had been set to an earlier date than was possible for the actual data in the new chunks.  I marked them as un-transferred:

UPDATE ol_chunk_log
SET transferred_time = NULL
where log_id >= [i]<The first chunk ID found with the query in step 1>[/i]

3.  Deleted the corresponding records in Datamart's chunk_log table to force it to accept the chunks of new data.  The values in column src_chunk_id are the same as the log_ids in ODS:

DELETE FROM chunk_log
WHERE src_chunk_id >= [i]<The first chunk ID found with the query in step 1>[/i]

4.  Start ETL and monitor to make sure the new chunks are pulled over and aggregated successfully.