Replicating only changes made by a specific database user

Question

I am performing parallel testing. We have upgraded the legacy batch jobs to a new framework which we need to test against the existing batch jobs.

In the existing setup, I have web application which creates or updates records based on user input and these batch jobs will process the records.

The plan is to have 2 databases, primary and secondary, with the existing batch job connected to the primary database server and the new batch job connected to the secondary database server.

The web application will populate data to both primary and secondary databases.

I would like to perform replication for all the tables in the database. However, I would only like to replicate any DML changes done by the specific database account used by the web application.

Is that possible?

Answer 1

I agree with Phil⁺¹ that this is not readily possible. Furthermore, it is very unclear how this could even work. Imagine the following simple table is being replicated.

 C1   C2
  1    'Apple'
  2    'Orange'
  3    'Pear'

Scenario 1

 batchjobaccount : INSERT INTO T1 VALUES (4,'Peach');
 appserveraccount: UPDATE T1 SET C2='Grape' WHERE C1=4;

What should happen in the replication? Should the record be created so that the appserveraccount action can take place or should the update be ignored?

Scenario 2

 batchjobaccount : UPDATE T1 SET C1=5 WHERE C1=2;
 appserveraccount: DELETE FROM T1 WHERE C1=5;

What should happen in the replication? Should the record be deleted or not?

Scenario 3

 batchjobaccount : DELETE FROM T1 WHERE C1=3;
 appserveraccount: UPDATE T1 SET C2='Strawberry' WHERE C1=3;

What should happen? Should the update fail on the local database but succeed on the replicated database or should it not run on the remote database because it failed on the local database?

If you don't want changes by batchjobaccount to replicate, then why give them permission to make the changes on the local database? If they need a local playground, then can you just make a local copy of the tables and only give batchjobaccount access to those?

Answer 2

Change Data Capture can do this.

I'm running Change Data Capture on a database. For each table that I've defined as being part of the change set, it populates the change table with rows showing how the table being tracked changes. When I defined the change table, I requested a USERNAME$ column be added (the USER_ID argument to the DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE() procedure). That column will contain the name of the user who made the change that was captured in the change table.

Your code that processes these change records and applies it to the other database (and you will have to write that code) can then seek out rows from the web application user and ignore rows from all other users, subject to Leigh's concerns; if the row won't exist in proper form in the destination database without the other users' work being replicated, then you won't be able to replicate the web user's changes.

Answer 3

I don't think this is doable using:

• Streams (get_logon_user isn't exposed for Row LCRs)

• Data Guard (DBMS_LOGSTDBY.SKIP only works for schemas, not logon names)

• Materialised Views (too high level)

Only thing I can think of is a trigger on each table that merges the :new values to another identical table if the user is "appserveraccount", which you then replicate using a Materialised View.

But having said that, it may not fit your requirement because it would apply the new values rather than executing the statement.

May I ask why do you need to do this? It seems very strange.

Answer 4

Just to share. I have found the solution to my problem.

I can check the user account which fires the dml statements.

http://www.oracle-base.com/articles/9i/complete-data-audit.php