Need some pointers - how to 'update' a "data mart"
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Need some pointers - how to 'update' a "data mart"

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    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.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Why not mviews with fast refresh? Maybe logical standby?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Nov 2008
    Posts
    11
    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 !

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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 !
    And your solution is?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2008
    Posts
    11
    Quote Originally Posted by marist89
    And your solution is?
    if I am JMac, maybe I like ETL (pentaho: http://reporting.pentaho.org/)

  7. #7
    Join Date
    Nov 2008
    Posts
    9
    May be, stream replication could help you.

  8. #8
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    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
  •  


Click Here to Expand Forum to Full Width