1

How can I use Data Guard or GoldenGate to create a slave database where the master does not propagate delete queries to the slave database?

I want to have a replication/slave database to have all the records of the master but when a delete query is executed on the master database, it must not propagate to the slave database.

I'm using Oracle 12c

How can I achieve this requirement?

Ali Razeghi - AWS
  • 7,566
  • 1
  • 26
  • 38
Siya Stoan Sosibo
  • 113
  • 2
  • 8

1 Answers1

2

You can not do this with a physical standby, you will need some kind of logical replication.

With a logical standby, you can skip DMLs, but that would skip all DMLs.

With Oracle Goldengate, you can easily skip deletes, as you can specify the IGNOREDELETES option: IGNOREDELETES

Ok, now let's assume you have configured your system to ignore deletes. Imagine the following situation:

insert into mytable (unique_column, other_column) values (1, 1);
commit;

delete from mytable where unique_column = 1;
commit;

insert into mytable (unique_column, other_column) values (1, 2);
commit;

The above will break your replication because of the unique constraint violation. If you ignore the error, you will not have fresh data in the destination database.

It sounds to me you want to build a history table, but there is more to that than just ignoring delete operations, have a look at the below support note:

Oracle GoldenGate Best Practices: Creating History Tables (Doc ID 1314698.1)

Balazs Papp
  • 41,488
  • 2
  • 28
  • 47