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?

  • Whether or not you use Golden Gate, you are describing a logical standby database. It's possible that Golden Gate will give you that functionality, but I'm not sure. You might need to create database level triggers to silently fail any delete statement. But I'm not sure that you can use a trigger to stop truncate statements. You would also need to see what happens if you drop a partition assuming that you use partitioning. – Gandolf989 Dec 8 '15 at 15:35
  • 1
    What is the business need for this? – Philᵀᴹ Dec 8 '15 at 16:40
  • @Phil I am not sure what exact business need is for the OP but one Business need for this is to build and maintain an Archive of historical data while being able to purge data from the Primary database. – Annjawn Mar 8 '16 at 1:10
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)

  • Thanks. I was going to ask the same question as OP but luckily I found this. I think we need to change the title of the question so that the topic is easily searchable. – Annjawn Mar 7 '16 at 23:02

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.