Coordinating Table Attributes between Source and Target

Follow this procedure to change an attribute of a source table when Oracle GoldenGate DDL feature is not supported for your database. The attribute changes includes adding or changing columns or partitions. It directs you how to make the same change to the target table without incurring replication latency.

Note:

For Oracle and MySQL databases, you can enable the DDL support feature of Oracle GoldenGate to propagate the DDL changes to the target, instead of using this procedure.

1. On the source and target systems, create a table, to be known as the marker table, that can be used for the purpose of generating a marker that denotes a stopping point in the transaction log. Just create two simple columns: one as a primary key and the other as a regular column. For example:

   CREATE TABLE marker
   (
   id int NOT NULL,
   column varchar(25) NOT NULL,
   PRIMARY KEY (id)
   );

2. Insert a row into the marker table on both the source and target systems.

   INSERT INTO marker VALUES (1, 1);
   COMMIT;

3. On the source system, run Admin Client.
4. Open the Extract parameter file for editing.

   Caution:

   Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside the command line interface if this is the case; otherwise, the contents may become corrupted..

5. Add the marker table to the Extract parameter file in a TABLE statement.

   TABLE marker;

6. Save and close the parameter file.
7. Add the marker table to the TABLE statement of the data pump, if one is being used.
8. Stop the Extract and data pump processes, and then restart them immediately to prevent capture lag.

   STOP EXTRACT group
   START EXTRACT group

9. On the target system, run Admin Client.
10. Open the Replicat parameter file for editing.

    Caution:

    Do not use the VIEW PARAMS or EDIT PARAMS command to view or edit an existing parameter file that is in a character set other than that of the local operating system (such as one where the CHARSET option was used to specify a different character set). View the parameter file from outside Admin Client if this is the case; otherwise, the contents may become corrupted.

11. Add the marker table to the Replicat parameter file in a MAP statement, and use the EVENTACTIONS parameter as shown to stop Replicat and ignore operations on the marker table.

    MAP marker, TARGET marker, EVENTACTIONS (STOP, IGNORE);

12. Save and close the parameter file.
13. Stop, and then immediately restart, the Replicat process.

    STOP REPLICAT group
    START REPLICAT group

14. When you are ready to change the table attributes for both source and target tables, stop all user activity on them.
15. On the source system, perform an UPDATE operation to the marker table as the only operation in the transaction.

    UPDATE marker
    SET column=2,
    WHERE id=1;
    COMMIT;

16. On the target system, issue the following command until it shows that Replicat is stopped as a result of the EVENTACTIONS rule.

    STATUS REPLICAT group

17. Perform the DDL on the source and target tables, but do not yet allow user activity.
18. Start Replicat.

    START REPLICAT group

19. Allow user activity on the source and target tables.