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!


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.


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.


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy