1 Reply Latest reply: Apr 25, 2013 1:01 AM by Rc-Oracle RSS

    identical tables - synchronous capture

    johnnie d
      i have an enterprise edition DB (11.2.0.3) that needs to be kept in sync with a standard edition DB (11.2.0.3). About twenty tables need replicating - they are not volatile, so the general daily traffic will not be high.

      I need the tables on both DBs to be IDENTICAL - rows will be updated and not just inserted/deleted.

      This raises a question about how the DESTINATION table is to be populated. Do replicated tables have to have primary keys ? And if they don't, how do updates work - what is the procedure that maps the ROWIDs from the source DB to the destination DB ?

      johnnie
        • 1. Re: identical tables - synchronous capture
          Rc-Oracle
          Hi Johnnie,

          To keep the replicated tables in sync, there should be primary key define on source and target DB.

          In the absence of the PKs , a substitute key needs to be defined using SET_KEY_COLUMNS procedure of DBMS_APPLY_ADM. A substitute key is a column or set of columns that Oracle can use to identify uniquely rows in the table. This will enable APPLY to identify the row and apply the changes When using SET_KEY_COLUMNS be sure to list ALL of the key columns, in order, in the column list. Do not specify each column in a separate call to SET_KEY_COLUMNS

          EXECUTE DBMS_APPLY_ADM.SET_KEY_COLUMNS('<owner>.<table_name>','<col1>,<col2>,....');
          EXEC DBMS_APPLY_ADM.START_APPLY('<applyname>');

          Thanks,
          Reena