At times it may be required to change the primary key of a table in replication. This solution delves on the steps required to accomplish this. The solution handles scenarios where only primary key is changed on source database as well as the one where primary key is changed on both source and target database.
General information.
Changing primary key of a table in replication involves following steps:
A. If only the primary key of the source table is being changed:
1. Restrict user activity on the source table
2. Make a copy of the currently active config and comment out the table in question. Then activate the copy so as to take the table out of replication.
3. Change the primary key of the table.
4. Activate the original config file so that the table is brought back into replication.
5. Resume user activity on the source table.
B. If the primary keys of both the source and the target table are being changed:
1. Restrict user activity on the source table
2. Issue flush on the source sp_ctrl:
sp_ctrl> flush o.SID (where SID denotes the Oracle SID of the source database)
3. Make a copy of the currently active config and comment out the table in question. Then activate the copy so as to take the table out of replication.
4. Change the primary key of the source table.
5. Activate the original config file so that the table is brought back into replication.
6. Resume user activity on the source table.
7. Once Post stops due to flush on the target Shareplex, implement the corresponding DDL for primary key change on the target table and then restart Post.
