Skip to Content
avatar image
Former Member

re-synchronize Replicate database in MSA

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Feb 25, 2015 at 02:38 PM

    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


    Add comment
    10|10000 characters needed characters exceeded

  • Jan 09, 2015 at 03:54 PM
    Add comment
    10|10000 characters needed characters exceeded

    • 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.....

  • avatar image
    Former Member
    Jan 14, 2015 at 04:11 AM

    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. ==================================================================================================================

    Add comment
    10|10000 characters needed characters exceeded