How to add a table to an existing HVR channel?
You have an existing channel with the capture and integrate jobs replicating tables. You now want to add an additional table into that existing channel with the minimum of disruption. 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 will be 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.
Overview
The article describes how to add an additional table (new_order) into to an existing HVR channel (ora2rsh) which is actively replicating tables and bring a replica of that additional table into synchronization.
Steps
The following steps will add the new table into the channel:
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 on the Tables node and select Table Explore.
3. Select the source database and click Connect.
4.Select the table you want to add, then click on Add.
5. Click OK on the confirmation dialog that is then displayed, then click Close on the Table Explore dialog.
6. Right-click on the channel name and select HVR Initialize from the context menu.
7. Right-click on the channel name and select HVR Initialize from the context menu, then select the Advanced Options tab in the HVR Initialize dialog.
8. To ensure that the Capture and Integrate positions are retained make sure only the Table Enrollment, Supplemental Logging, and Scripts and Jobs checkboxes are checked, and everything else is unchecked. 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.
9. Start the capture job from within the Scheduler node in the UI. Do not start the integrate job yet.
10. The Capture job should now show as 'Running' with a green icon.
11. Next, we need to create a new table on the target database and instantiate the data from the source to the target. This is achieved by an Online Refresh. From the channel node in the UI, right-click the channel name and select HVR Refresh from the context menu.
12. In the HVR Refresh dialog make the following selections:
a. Unselect all the tables apart from the one we are adding
b. Select the Online Refresh option
c. Select the Create Absent Tables option
This will ensure that the new table will be created on the target database and HVR will coordinate the copying of the row data for the table with the ongoing change data that Capture is already capturing.
13. 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 the State says 'Finished' then click Close to dismiss the dialog and the Refresh dialog.
14. From within the Scheduler node in the UI, start the Integrate job for this channel, which will apply any changes that occurred while the refresh was running and bring all the tables into a synchronized state
15.The Integrate job should show as being in the RUNNING state, with a green icon and the latency will decrease until it reaches zero, at which point you are back replicating change data for all the tables in the channel in real-time.