-
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.
-
Originally Posted by JMac
Is that idea practical?
No. ETL is not transactional replication.
Originally Posted by JMac
Is there a better solution?
Yes. A properly designed ETL process.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
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.
-
Why not mviews with fast refresh? Maybe logical standby?
Jeff Hunter
-
Originally Posted by marist89
Maybe logical standby?
JMac has 5 databases (ranging from Ora 8 to 10). I think logical standby can't through Ora 8 to 10. thank you !
-
Originally Posted by Great Wall
JMac has 5 databases (ranging from Ora 8 to 10). I think logical standby can't through Ora 8 to 10. thank you !
And your solution is?
Jeff Hunter
-
Originally Posted by marist89
And your solution is?
if I am JMac, maybe I like ETL (pentaho: http://reporting.pentaho.org/)
-
May be, stream replication could help you.
-
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...
Regards,
Chiappa
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|