We have a use case like of banks, where we generate large number of transactions, say in transaction table and few other tables with large number of inserts, every day. Transactions are most inserted and updated within a day or two. Our database is Oracle 12c(planned upgrade to 19). Coming to use case, we need to set up a big data cluster(Oracle or mongo/cassandra) ,say , X so we can support people fetching account statement of older time.

So, in our primary(and data guard replicated standby) Oracle database, we should keep 3 months of data and people querying for data older than 3 months must use cluster X.

What are the industry practices for building such replications?

Process must be:

  • Stable,resilient and well monitored
  • Cluster X must be scalable
  • Highly consistent and must not ever loose data

What are our initial thoughts:

Set up oracle goldengate pipeline from source DB to a y-nodes sharded oracle DB cluster. Although Oracle19c sharded db does supports adding a node with auto balancing, I'm reluctant to use a relational db. Not using relational DB and using replication to DBs like Mongo/cassandra also making me skeptical of instabilities in CDC pipelines, NoSQL inconsistency.

What does banks do for such cases?

  • "What does banks do for such cases?" - They don't typically split up the data by age between clusters (of the queryable data to the end users). 3 months of data will unlikely be enough to split up.
    – J.D.
    Commented Jan 7 at 20:21
  • @J.D., 3 months is just an example. I have seen banks offer different ways to generate account statements, for last 1 years vs more older data. Last 1 year's reports are generated instantly and older data reports are generated in few hours. I assume they keep older data in a separate cluster else the main DB's table size would grow to unmanageable size. Commented Jan 8 at 16:57
  • "I have seen banks offer different ways to generate account statements, for last 1 years vs more older data." - That's why I said "of the queryable data to the end users". The actual statements will only go back so far but all of that data is within the same table and cluster usually. After that cutoff of 1 year or a few years is when the end users won't have access to that data anymore. If data from it is available, it's usually offered as a summarized dataset, probably living in a separate summary table, and quite possibly on the same cluster still.
    – J.D.
    Commented Jan 8 at 19:43


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.