How to Add Table to Existing HVR Channel

Question

I have an existing channel with the capture and integrate jobs replicating tables. How can I add a table to that channel?

Environment

HVR 5

Answer

This scenario assumes that all the tables in the channel and the additional table are actively being updated by the application on the source at the time you want to add them into the channel. It also assumes the additional table does not yet exist on the target database.

NOTE: If the channel has the AdaptDDL action defined with an /AddTablePattern then any tables created on the source database that matches the pattern are captured and replicated and the procedure described below is likely inapplicable. This process is for adding an existing table without relying on AdaptDDL to replicate new table creation.

The following steps describe how to add a table (for example, new_order) into an existing HVR channel (for example, ora2rsh), which is actively replicating tables, and bring a replica of that additional table into synchronization.

1. Suspend the capture and integrate jobs as we will need to add the new table into the list of tables that the capture and integrate is processing.

2. Right-click the Tables node and select Table Explore.

   

3. Select the source database and click Connect.

   

4. Select the table you want to add amd click Add.

   

5. Click OK on the confirmation dialog that is then displayed, then click Close on the Table Explore dialog.

6. Right-click the channel name and select Initialize and then select the Advanced Options tab in the Initialize dialog.

   

7. To ensure that the capture and integrate positions are retained, select only the Table Enrollment, Supplemental Logging, and Scripts and Jobs options are enabled. This is important as selecting the wrong options can result in transaction data being missed once the Capture and Integrate jobs are resumed in a later step. The three options which are selected ensures that the channel is aware of the table that has been added, the supplemental logging attributes are enabled for the new table, and the capture and integrate jobs are rebuilt to a consistent state.

   

8. Start the capture job from within the Scheduler node in the UI.

   

9. The Capture job should now show as Running with a green icon.

10. Right-click the channel name and select Refresh.

    

11. In the Refresh dialog, do the following:

    • Unselect all the tables apart from the one we are adding

    • Select the Online Refresh option

    • Select the Create Absent Tables option

    This ensures that the new table is created on the target database and HVR coordinates the copying of the row data for the table with the ongoing change data that is already being captured.

    

12. Click Refresh to start the refresh of this new table and click Yes on the confirmation dialog. Once it completes you should see a dialog similar to this. Confirm that the status is Finished, and then click Close to dismiss the dialog and the Refresh dialog.

    

13. In the Scheduler node, start the Integrate job for this channel, which applies any changes that have occurred while the refresh was running and brings all the tables up to date.

    

14. The Integrate job should be in the RUNNING state with a green icon. When the latency reaches zero, you are back replicating change data for all the tables in the channel in real-time.