re-synchronize Replicate database in MSA

[Former Member]

What are the steps to re-synchronize Replicate database in MSA environment without stopping transactions at primary

[Former Member]

Thanks Mark,

But this is not applicable to REP 12.6.

regards

Manoj

[former_member182259]

For 12.6, the only real option would be to drop the database subscription and then use the basic define subscription ala:

define subscription pubs2_sub

for database replication definition pubs2_rep

with primary at NEWYORK_DS.pubs2

with replicate at TOKYO_DS.pubs2

subscribe to truncate table

use dump marker

Followed by the usual activate/validate.....

[former_member350489]

In all these scenarios, you will hit a problem if you switch from primary to replicate, since your gen_id will be bumped up from 0 to 1 on you primary side. And when you switch to replicate, the replication will not start to flow on its own if you do not alter gen_id manually from 0 to 1 also on the replicate !

If you however remove/drop "everything" related to that database/thread you will have a gen_id reset to 0 on both primary and replicate. That means also the rep_agent, repdef, connection etc has to be dropped

Just a small hint, since we've had this problem ourselves in the beginning

/Mike

[former_member776122]

Hi Manoj

Last time I have used these steps to sync my MSA database in PROD. ==================================================================================================================== 1. Put the autosys jobs on hold for primary and secondary databases.               

for primary -- put on hold the transaction log dumps jobs               

for secondary -- put all jobs on hold because load will run here during the resync duration

2. Drop subscription for the target database where refresh is to be done.

3. Stop Rep Agent for source(primary) database.

4. Define Subscription using dump marker.               

define subscription sub_2               

for database replication definition repdef_1               

with primary at PDS.primary_db               

with replicate at RDS.rdb               

use dump marker

5. Check Subscription should be in defined state.

6. Dump source database source.

7. Suspend the dsi for the target database which is to be refreshed.

8. Load the target database using the dump of step7

9. Online the target database.

10. Remove 2TP from the target database.

11. Drop the user targetdb_maint from the target database and Add  targetdb_maint user as dbo in targetdb database.

12. Start Rep Agent for source database

13. DSI for target database goes down once it hits the dump marker . . . resume the dsi thread.

14. Check the watchdog table records. (Please note that watchdog script shud not be stopped, it can keep running on source database)

15. Make sure Subscription status switched to VALID state.

16. check all connections on rep server and they should be up.

17. Once again check the heartbeat table records on primary and secondary. ==================================================================================================================