1 Reply Latest reply: Jan 20, 2012 6:19 AM by 891920 RSS

    DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS not applying changes?

    Giedrius S.
      Hello,

      I have set up a testing environment for streams using HR schema on two databases ( Oracle SE1, 11gR2 ) by following tutorial: ["Tutorial: Configuring Two-Database Replication with Synchronous Captures"| http://download.oracle.com/docs/cd/B28359_01/server.111/b28324/tdpii_repcont.htm#autoId24] . Everything runs fine, changed data goes from one database to another with no problem.

      Now I want to test some bad cases, when some changes can not be applied and I should solve them manually. So my steps:

      1. On one DB (lets say Source DB) I change tables DEPARTMENTS column DEPARTMENT_NAME to VARCHAR2(300) (was 30).
      2. I update data on that table. I change department name to something that is >30 chars.
      3. Commit
      4. I check that tables data on another DB (lets call Destination DB). Data has not been changed, as I was expecting because it could not fit into 30 lenght column.
      5. On STRADMIN schema i run: SELECT * FROM Dba_Apply_Error_Messages. I see one error, that says that column DEPARTMENT_NAME was not wide enough
      6. I go to Destination DB's HR schema and change DEPARTMENT_NAME column to VARCHAR2(300)
      7. Now i want to reexecute that failed transaction on destination DB. So on Destination DB, STRADMIN schema i execute begin DBMS_APPLY_ADM.execute_all_errors(); end;. It executes without any errors.
      8. Now i start apply process on DB schema (because it was aborted when encountered error): begin dbms_apply_adm.start_apply ('APPLY_EMP_DEP'); end;. It executes without any errors.
      9. Now i go and check DEPARTMENTS table on Destination DB. And values has not been changed!
      10. I run SELECT * FROM Dba_Apply_Error_Messages. once more - no rows returned. I run select * from apply_queue_table t - no rows returned.

      Why my changes are ignored when running DBMS_APPLY_ADM.execute_all_errors ? Maybe i am missing something?

      Thank you,
      Giedrius

      Changed link. Edited by: Giedrius S. on Oct 10, 2011 5:53 PM