Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

up vote 3 down vote accepted

I agree with Phil+1 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?

share|improve this answer
1  
I'm looking forward to the requirement being revealed ;) –  FreshPhilOfSO Sep 14 '12 at 18:26
    
We expect such events to happen since we are conducting parallel test. There might be bugs in the new version of batch job. We dont expect to handle discrepancy –  optimus Sep 15 '12 at 3:56
    
since the users who verify the parallel system test, will look at result / output from the web application. –  optimus Sep 15 '12 at 4:23
    
I don't understand why you would expect these problems, but not deal with the discrepancy. It seems as though you don't care whether the appserveraccount replication is accurate or complete, only that the batchjobaccount changes aren't replicated. –  Leigh Riffel Sep 17 '12 at 12:41
    
Have you considered just replicating the system before the batch runs and then running the batch run on each so you can do the comparison. You would have to reconcile the changes made by the appserveraccount while the batch jobs were running, but that seems like it would be easier than building a complex, fragile replication system that will end up with discrepancies anyway. –  Leigh Riffel Sep 17 '12 at 12:44
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
how can triggers know the DML (INSERT/UPDATE/DELETE) are issued by which accounts? –  optimus Sep 15 '12 at 0:51
    
I have updated the requirements =) –  optimus Sep 15 '12 at 1:10
1  
In plsql (and therefore triggers) you can use sys_context() to get info about the executing user. –  FreshPhilOfSO Sep 15 '12 at 2:59
    
I see you've modified the question. Can you please address Leigh's concerns? –  FreshPhilOfSO Sep 15 '12 at 3:01
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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