Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a reporting database (in SQL Server) that takes nightly snapshots of the production data (Oracle) for users to query against for the next day, but now the requirement is getting to be that they want near realtime data (about every 15-30 mins). What is the best way to do this in production without downtime or any partial data getting returned?

I've looked into realtime data mirroring using triggers, but because of licensing we can't alter anything in the Oracle database.

I've seen some information on partition switching, where I do a full refresh into a set of alternate tables then switch the partitions. Is it good practice to be doing that on 20 tables every 15 minutes? A lot of these tables have one-to-many relationships, but I figure if I replace the "many" tables before the "one" tables, reports won't return partial data if they run during this process.

I also want to avoid locking the whole database, because I want the system to be able to scale to many concurrent users, and it seems like if I do that, even if it takes just a second, it might take some time to acquire the lock.

Any strategies or insights are appreciated! Thanks!

share|improve this question

migrated from stackoverflow.com Nov 19 '13 at 8:56

This question came from our site for professional and enthusiast programmers.

2 Answers 2

I've encountered this issue before and in previous cases for us the solution was to develop a system of ETL processes which bring the information over into the warehouse or reporting database. What we did was design the process to run near continuously and at the head of each iteration we would grab the current maximum datetime stamp of each table. We would then process each record that was generated or updated between the last timestamp and the current one we just retrieved then repeat this process constantly. There is a chance with this for partial data, but with the process running continuously any partial data would be resolved within a few minutes.

share|improve this answer

As already suggested, you'll need to build an ETL (or series of ETLs) to extract data at regular intervals. Have a look at Oracle's Change Data Capture (CDC) so you can pick up only those records which have been modified since the last load.

http://docs.oracle.com/cd/B28359_01/server.111/b28313/cdc.htm

As for the destination end, the Kimball methodology suggests you'll should use two separate partitions, a real time partition and a data warehouse partition. Push all real time information into the real time partition and then nightly empty that table into the data warehouse.

http://www.kimballgroup.com/2001/11/design-tip-31-designing-a-real-time-partition9/

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.