[Ian Godsell]

Hi All,

I am working on a datawarehouse, where all the source systems have been designated as untouchable. That is they are not allowing us to directly query any of the operational source systems ever. However they recognise that we obviously need to get data somehow, and it is up to us to come up with a solution.

All the source systems (5) are all Oracle based on either 8i or 9i versions. An additional problem with 2 of these systems is that they do not track history, but we need every change made to the data. So we need to produce our own net change logs.


There are two solutions that I currently have, which I am hoping for your opinion on.

1. Oracle Replication. With the Operational systems as the master system, with the changes only flowing one way to our secondary database which we can read. I am then thinking of placing hand written triggers on the tables which will have data changes made on them so that I can write out all records that are inserted / updated. I am concerned though as to what impact this may have on the operational systems.

2. Redo Logs. With the databases starting in sync, taking the redo logs from the operational systems and applying them to our secondary database. However, this will not give me all changes that are made to the tables, so as per the first solution I am then thinking of placing hand written triggers on the tables which will have data changes made on them so that I can write out all records that are inserted / updated. The question is, will these triggers fire when redo logs are being applied?

If you have some other ideas, please let me know.

Thanks in advance.