We have 2 databases in our application. 1 is un-normalized which is the primary database and used for OLTP operations and gets updated throughout the day. Lets call this DB1. Another database is a normalized one and is used mostly as a data warehouse. Lets call this DB2.
Really? It's usually the opposite ... normalized OLTP and judicious de-normalization in the DW.
On a nightly basis, incremental data is moved from DB1 to DB2. This is done using a combination of triggers on DB1 populating CreatedDateStamp and ModifiedDateStamp in EACH table, and identifying the DELTA based on last refreshed timestamp maintained on DB2 (using public db links). There are approximately 300+ tables on DB1 from where data is moved to 150+ tables of DB2 (using MERGE statements - OWB).
The problem is this process tables around 90-100 mins. And for these 90-100 mins my DB1 and DB2 has to be down which is unacceptable for a production system.
Is their any other better approach to go about this ? I have tried data pump, but it adds complexity as tables are not directly related on DB1 (un-normalized) and DB2 (normalized). Also no performance benefits with added complexity.
Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production
Database size of DB1: 1 TB
Database size of DB2: 0.5 TB
Daily incremental data volume: 2 GB.
I am not sure if OWB built this for you or if you built it yourself. When I see performance problems in migrations I look to see if the work is being done in SQL or PL/SQL. If two cursors are returning data from two tables and PL/SQL is being used to compare the two dates and then merge RBAR (row by agonizing row) this will be very slow. If this is the case and you can modify the code I would use merge and let the "USING" clause return the data that has changed or doesn't exist in the warehouse. As has been said before,
NEVER USE PL/SQL when SQL can do the job.
So you are write you own replication that is similar to ORACLE Materialized views (snapshot in the past).
You have to find what part of you process is the most time consuming part. That could be data replication from DB1 to DB2 or applying deltas on DB2.
If I have to solve this type of logic that I will use this:
1/ replicate data from DB1 to DB2 using Materialized views
2/ using trigger on Materialized view on DB2 to build deltas (store deltas in table) and then apply deltas (full scan delta table) to data or applying deltas directly (no delta table) if I found that it is faster