1

Suppose we have 2 identical Oracle 11g DB schemas: PRODUCTION and ARCHIVE.

The goal is to keep in PRODUCTION the minimal amount of data required for online operations, and use ARCHIVE to store all the data ever appeared in PRODUCTION to build reports.

To be clear:

  1. we define "obsolete data" using some custom rules
  2. "active data" is all the data except for "obsolete data"
  3. ARCHIVE should be populated by "active data" from PRODUCTION by schedule (for example every midnight or every week)
  4. any new "obsolete data" should be removed from PRODUCTION but remained in ARCHIVE

I believe all these points could be satisfied by independently running:

  • Uni-directional replication (for instance, Oracle Streams)
  • application for "Obsolete data" removal (suppose it is implemented)

But the problem is that any Obsolete data removing application implicitly produces REDO logs in PRODUCTION which are going to be propagated to ARCHIVE and hence this process leads to data loss in ARCHIVE.

Could you give an advice, how to organize the process of "partial replication", which could ignore some specific deletions in master schema.

Improve this question
4
  • Your archive is more a data warehouse than a replica/stand by. Look for information about ETL, you will find plenty of help online.
    – Gerard H. Pille
    Jun 4, 2018 at 12:42
  • you have two schemas, but are they in the same or in different databases?
    – miracle173
    Jun 4, 2018 at 12:45
  • Agreed with @GerardH.Pille look up ETL methodologies. You can choose a product like Informatica or DataStage, or you can design your own with something like MERGE (docs.oracle.com/cd/B28359_01/server.111/b28286/…)
    – SQLDevDBA
    Jun 4, 2018 at 13:59
  • @miracle173 in general, they could be in two different DBs
    – diziaq
    Jun 5, 2018 at 3:17

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.