Need some pointers - how to 'update' a "data mart"
I'm not looking for complicated, state of the art solutions here guys. No-one's willing to spend money or a lot of time on this project here, so here's the spec:
We have 5 databases (ranging from Ora 8 to 10). There is a requirement to move data/tables from each into a central database for reporting purposes. Creating Mat Views is out (or may not be!) so I was thinking of a way of using the Archived Redo Logs of each to deduce the SQL/DML and periodically using these to update the central 'Mart'.
Is that idea practical? Anyone done it? Is there a better solution? It may only be updated once or twice a day.
There are fears of impacting on the performance of the source databases, so I'm not keen to do the updates via SQL and DB links.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
No Streams for db 8, it was a 9i feature, I´m afraid... JMAC, your answer is simple : first of all, your "way of using the Archived Redo Logs" is called LOG MINER, and it is a 8i feature, so no soup for your 8.0.x databases... Even more, Log Miner only becomes practical in 9i, with the intro of Log Miner Viewer and the Extended Logging, sorry again... So, we can say with a reasonable degree of security, NO BUILT-IN non-programing SOLUTION will be of use in your env, with the (very!) old versions you have.... Third-party solutions (like Shareplex, or generic data-extraction and transform tools) exists, but you says "No-one's willing to spend money ", so commercial solutions are out, I think...
Your solution probably will be a mix of programming (yes, with triggers and so on), data dumps in some cases (exps/imps, some C program doing dump in text and loading with sql*loader, dblinks inserts, etc) AND some built-ins like snapshots (materialized views only in 8i), NO built-in complete solution for you, I´m afraid... And yes, it WILL cause some overhead much probably, and WILL cost some value in effort, time, men-hours, for sure...
Reply With Quote
Originally Posted by JMac
Bookmarks