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.

11-21-2008, 09:58 AM

PAVB

Quote:

Originally Posted by JMac

Is that idea practical?

No. ETL is not transactional replication.

Quote:

Originally Posted by JMac

Is there a better solution?

Yes. A properly designed ETL process.

11-24-2008, 10:47 PM

marist89

Why not mviews with fast refresh? Maybe logical standby?

11-25-2008, 10:08 AM

Great Wall

Quote:

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 !

11-26-2008, 12:03 AM

marist89

Quote:

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 !

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...