ICON / Infomart Database Restore

[Adam_W]

Hi all

Thankfully (having been caught out by over-eager DBAs before), this is a hypothetical scenario!

Because of the way the Datamart ETL works, if ODS was corrupted for any reason, I've found it necessary in the past to also restore Datamart to the same point in time.  Otherwise ETL writes duplicate records to Datamart, creating a mess that's very tricky to get out of!

Is it the same with ICON and the Infomart databases?  I'd assume that the chain of dependency works something like IDB -> Staging Area -> Infomart.  So if ICON's IDB needed to be restored, you'd need to also restore the Staging and Infomart databases, to avoid records being extracted which had already been transferred.

Following on from that, if the Staging database needed to be restored, you would also need to restore the Infomart database, but not the IDB.

And finally, if only the Infomart database needed to be restored, you would not need to restore the others, and when the Infomart ETL ran, at least some of the missing data from the period after the last backup would hopefully be transferred.

Does the above sound right?

[N.Ali]

Hi,

I think you don't need to restore infomart database incase something happens to your ICON db.

If the extract job has successfully transferred the data from ICON to Staging and then GIM, then GIM Extract job will always look for the data which isn't transferred in GIM. I don't really remember but there is a table in GIM named ETL history which contains all the information related to ETL process and it has the time-stamp when the data was transferred last time from ICON.

so you can fix the problem with the ICON db and run extract job again, it will fetch where it left.


Regards

[Adam_W]

Excellent, just checked the DB to confirm this.  That makes things much easier 

[Allan]

[i]So if ICON's IDB needed to be restored, you'd need to also restore the Staging and Infomart databases, to avoid records being extracted which had already been transferred.[/i]

It is advisable to have Highly Available Icon so that if you lose an Icon IDB, the data will be in the other half of the pair and it would not be necessary to restore anything.

Anyway, if the data had already been extracted and was in Infomart there would be no need to restore GIM and or Staging as the data would already be in Infomart for the business to utilize!

[i]Following on from that, if the Staging database needed to be restored, you would also need to restore the Infomart database, but not the IDB.[/i]

Correct, Staging has pointers to what has been extracted and it needs to be synchronized with GIM, therefore a restore of Staging should have a corresponding restore of GIM. Provided the data was still in Icon, you could re-extract the data and get back to the point where the database failed.

[i]And finally, if only the Infomart database needed to be restored, you would not need to restore the others, and when the Infomart ETL ran, at least some of the missing data from the period after the last backup would hopefully be transferred.[/i]

You will have problems if Staging and GIM are out of sync and therefore would always recommend a restore of GIM and Staging.

What is key is sufficient capacity in Icon, Highly Available Icon and a daily database backup of Staging and GIM with the ability to revert to a specific point in time.

regards

Allan

[Adam_W]

Thanks Allan, just what I was after!

[dtorres265]

Hello,

I've a problem that Infomart is not taking data from an specific Icon from some previous days so Iv'e been told by Genesys that this cannot be done manually.
do you know where the information of the time-stamp is being stored or why this is happening?

thanks




[quote author=N.Ali link=topic=6471.msg28159#msg28159 date=1307902050]
Hi,

I think you don't need to restore infomart database incase something happens to your ICON db.

If the extract job has successfully transferred the data from ICON to Staging and then GIM, then GIM Extract job will always look for the data which isn't transferred in GIM. I don't really remember but there is a table in GIM named ETL history which contains all the information related to ETL process and it has the time-stamp when the data was transferred last time from ICON.

so you can fix the problem with the ICON db and run extract job again, it will fetch where it left.


Regards
[/quote]