This is just one way to resync the replicated database in Sybase
Stop Replication
- log into primary server and change database
- sp_stop_rep_agent <DBNAME>
- dbcc settrunc(ltm, ignore)
- log into repserver
- suspend connection to <secondary_dbserver>.<dbname>
Flush queue
- Check disk space on the RepServer
- log into RepServer
- admin disk_space
- log into RSSD database (typically on the standby Sybase ASE)
- run rs_helppartition “partition name” to determine which queues this partition contains
- log into RepServer
- If we are going to resync, we need to flush the queue (in the RepServer)
- sysadmin hibernate_on, “Reason for flushing queue”
- sysadmin sqm_purge_queue, <connection #>, 0
- sysadmin sqm_purge_queue, <connection #>, 1
- sysadmin sqm_purge_queue, 140, 0 (“0″ is the outbound queue. “1″ is the inbound queue) – do for both queues
- sysadmin hibernate_off, “Reason for flushing queue”
Sync databases
- log into RSSD database
- rs_zeroltm <primary_server>.<dbname>
- turn secondary truncation marker in primary server.dbname
- dbcc settrunc(ltm, valid)
- dump database on primary server
- sp_start_rep_agent <dbname>
- this will transfer the transactions from the primary database’s log segment into the queues.
- load database on secondary server (do not bring on line)
- dbcc dbrepair(<dbname>, ‘ltmignore’)
- online database <dbname>
Enable Replication
- log into rep server
- resume connection to <secondary_dbserver>.<dbname>
- if the replication definitions are for individual tables and not the entire database
- set autocorrection on for <table_repdef> with replicate at <secondary_dbserver>.<dbname>
- when the tables are in sync, turn off autocorrection (replace ‘on’ with ‘off’)
- if the replication definition is for the entire database
- be prepared to run “resume connection to <secondary_dbserver>.<dbname> skip transaction” until the databases are in sync – can be quite a few (this can be scripted if necessary).
« DBD::Sybase and signal handling Stacktrace can be produced when the log of a database is completely full »
Hello Jason,
The method you discribe we do perform when dropping the queues is not possible or takes a lot of time when drop connection is not possible or is stuck. Recreating the ltm and using the ltm with rs_zeroltm we find very dangerous. Therefore we do the following. Another method is using a dump marker initializing the standby database :
1. Drop the connection to the standby database.
2. Create the connection to the standby database using the ‘dsi_suspended’ and ‘use dump marker’ clause.
3. Dump the active database and load the database into the standby database.
4. Verify if every login/suid/userid are the same in active and the standby database.
5. Resume the connection to the standby database.
- Dropping the connection to the standby database. This will automatically flush the queue(s) for the specific database. There is no need for purging the queues manually. Replicating more databases through the same repserver making the mistake of flushing the wrong queues, in stressfull situations, creates more damage we want…..
- The above method has the advantage that the ‘second truncation point’ (ltm) NOT has to be deleted and recreated. The rs_zeroltm command in your example does (re)set the ltm to the beginning of the log. Using rs_zerotlm and bringing the database online in your example the ‘admin who, sqm’ will show a lot of duplicates before it reaches the ltm point in the logsegment, and beginning accually replicating the records which have to be replicated. In this case you need i guess a big stable queue and database log for excepting and analysing the logrecords before the replication actually takes place to the standby database.
- The time between setting the ltm with ‘dbcc settrunc(‘ltm’,'ignore’)’ and the and of the dump has to very small when a lot of transactions in the meantime in the active database, which can do produce a lot of log
?. The active log has probably to be extended ? This because many record processing from the active ltm are still present in the standby database resume connection … skip transaction has to to be done many times, how many times ? Accidently making a ‘…skip transaction’ is made easily.
- Using a dumpmarker the actually replication to the standby will start from the ‘dump marker’.The ‘dump marker’ is set just before the dump of the active database ends. Transactions between the ltm and the dump marker are yet available and processed in the standby database, so you will not see (m)any ‘duplicates’ with the ‘admin who, sqm’ command. Mostly only one (1) ‘resume connection to .’ command without the ‘skip transaction’ clause is nessisairy. The ‘after’ work [ resume connection..] is mainly ready in seconds.
- Another advantage is the replication server does not has to be queisce. When the repserver manages other active/standby databases next to the faulty one processing of the other active/standby connections will go on. The other databases than do have to wait for the faulty active/standby situation is ready. When queisce and having other database replicating with the same repserver do need big stable queues and/or do need big logsegment(s) considering the repair of the standby takes a long time. The other databases have to wait till repserver is not queiesce anymore and processing can take place, which in many situations is not.
Greetings,
Jan Kootstra
DBA Sybase
Thanks Jan!!
I’ve been testing a method very close to your method but haven’t tried it in production yet. Have you experienced any issues with the dump marker? I haven’t in my testing but just because it no issues show up in testing doesn’t mean an issue won’t pop up at 3am
jason
Jason,
I believe Jan can still use your method if one keeps the generation id handy before disabling the truncation point to set it back to current +1 generation id
admin get_generation, data_server, database
dbcc settrunc(‘ltm’, ‘gen_id’, db_generation)
Thanks
Anil
JL,
Good documentation. This approach is the easiest one in a complext replication setup that involves defs, func strings and subs
Thanks
KRV