Could maybe someone help for this problem (Version 10.0.1.4213): We are using DBREMOTE for replicating a cons. against several tens of remotes (bidrectional repl.), where everything seems to be ok, as DBREMOTE logs show no any error and the offsets in SYSREMOTEUSERS are matching, the problem is that 'from time to time' we receive incomplete records on cons., in other words our application is writing in table T1 on each remote, and then T1 is replicated to cons., and at the end we see some gaps in table T1 on cons. Below is a snapshot of log files:

Remote

I. 2018-02-26 11:00:06. Sending message to "DBSRV_user" (1-05474989504-05475193069-0)
I. 2018-02-26 11:00:06. Sending message to "DBSRV_user" (1-05475193069-05475299039-0)
I. 2018-02-26 11:00:07. Sending message to "DBSRV_user" (1-05475299039-05475305061-0)
.........
I. 2018-02-26 11:00:06. Sending message to "DBSRV_user" (1-05474989504-05475193069-0)
I. 2018-02-26 11:00:06. Sending message to "DBSRV_user" (1-05475193069-05475299039-0)
I. 2018-02-26 11:00:07. Sending message to "DBSRV_user" (1-05475299039-05475305061-0)
.........
I. 2018-02-26 12:32:04. Sending message to "DBSRV_user" (1-05475305061-05475926378-0)
.........
I. 2018-02-26 13:00:05. Sending message to "DBSRV_user" (1-05475926378-05476015975-0)
.........

Consolidate

I. 2018-02-26 11:15:35. Received message from "DBLA02_user" (1-05474989504-05475193069-0)
I. 2018-02-26 11:15:35. Applying message from "DBLA02_user" (1-05474989504-05475193069-0)
.......replicated transactions......
I. 2018-02-26 11:15:35. Received message from "DBLA02_user" (1-05475193069-05475299039-0)
I. 2018-02-26 11:15:35. Applying message from "DBLA02_user" (1-05475193069-05475299039-0)
.......replicated transactions......
I. 2018-02-26 11:15:36. Received message from "DBLA02_user" (1-05475299039-05475305061-0)
I. 2018-02-26 11:15:36. Applying message from "DBLA02_user" (1-05475299039-05475305061-0)
.......replicated transactions......
I. 2018-02-26 13:15:40. Received message from "DBLA02_user" (1-05475305061-05475926378-0)
I. 2018-02-26 13:15:40. Applying message from "DBLA02_user" (1-05475305061-05475926378-0)
I. 2018-02-26 13:15:40. COMMIT
I. 2018-02-26 13:15:40. Received message from "DBLA02_user" (1-05475926378-05476015975-0)
I. 2018-02-26 13:15:40. Applying message from "DBLA02_user" (1-05475926378-05476015975-0)
.......replicated transactions......

The missing records are actually contained in the message (05475305061-05475926378), which is a big message compared to the other messages, but nothing is replicated through this message (there is only a commit statement as a result of applying this message!)

From remote side, I can notice that (for some unknown reason) the DBREMOTE seems to be executed concurrently (for generating the messages (05474989504-05475193069)(05475193069-05475299039)(05475299039-05475305061)), but those messages are actually replicated correctly to cons.

Does DBREMOTE have any option/switch to prevent concurrent instances? Is there any hint for solving this problem?

Thanks in advance

asked 08 Mar '18, 18:21

Baron's gravatar image

Baron
2.1k134146174
accept rate: 46%

Does DBREMOTE have any option/switch to prevent concurrent instances? Is there any hint for solving this problem?

AFAIK, DBREMOTE uses a mutex to prevent another instance to run concurrently on the same remote. I remember to get an according message when starting a second DBREMOTE instance, possibly something like "Cannot register ... since another exclusive instance is running". However, I do not know whether that would prevent just a second instance running on the same box (i.e. a machine-level/OS-level mutex) connecting to the same database, or whether that would also prevent another DBREMOTE instance running on a different box connecting to the same database.

That being said, the duplicate entries in the sending remote are certainly surprising. I would try to use DBREMOTE -v on the cons to log the actual SQL statements that are applied.

(09 Mar '18, 15:47) Volker Barth

We are using -v option on both cons and remotes. Should I upload the whole log file here? As mentioned above, the suspected message (about 600k long) is writing nothing on cons., the below 3 lines come directly after each other. I. 2018-02-26 13:15:40. Received message from "DBLA02_user" (1-05475305061-05475926378-0) I. 2018-02-26 13:15:40. Applying message from "DBLA02_user" (1-05475305061-05475926378-0) I. 2018-02-26 13:15:40. COMMIT

(09 Mar '18, 18:18) Baron

I am sorry for the inconvinience, the main cause for the problem was that from time to time an automatic job was connecting to DB and stopping the subscription. For me it was however confusing that if I stop a subscription and then start it again, then all transactions BEFORE AND after STOP SUBSCRIPTION were ignored, and the message agent starts considering only the transactions after START SUBSCRIPTION. The other approach with (ALTER PUBLICATION DELTE/ADD TABLE) works differently, as in this case the message agent ignores only the transactions between ALTER PUBLICATION DELETE TABLE & ALTER PUBLICATION ADD TABLE

One more question, the DBTRAN -x SR can't exclude the replicated transactions (having that I start the replication always with dbremote -c "dbf=my_db.db;uid=dba;pwd=sql"), so that I must use DBTRAN -x DBA in order to exclude all transactions of the user DBA.

(13 Mar '18, 06:18) Baron

There is a mechanism in SQL Remote to prevent two instances of dbremote from connecting to the same DATABASE, at least as far back as v12. I'm pretty sure that the same code exists in v10, but I can't confirm that right now, since I don't have access to our older source control system when I'm working remotely.

I. 2018-03-11 15:11:00. SQL Remote Message Agent Version 12.0.1.4391
I. 2018-03-11 15:11:00.
I. 2018-03-11 15:11:00. Copyright ⌐ 2001-2015, iAnywhere Solutions, Inc.
I. 2018-03-11 15:11:00. Portions copyright ⌐ 1988-2015, Sybase, Inc. All rights reserved.
I. 2018-03-11 15:11:00. Use of this software is governed by the Sybase License Agreement.
I. 2018-03-11 15:11:00. Refer to http://www.sybase.com/softwarelicenses.
I. 2018-03-11 15:11:00.
I. 2018-03-11 15:11:00. 1: -c
I. 2018-03-11 15:11:00. 2: **********************************
I. 2018-03-11 15:11:00. 3: -v
I. 2018-03-11 15:11:00. 4: -o
I. 2018-03-11 15:11:00. 5: cons_2.txt
I. 2018-03-11 15:11:00. 6: -qc
E. 2018-03-11 15:11:00. SQL statement failed: (-782) Cannot register 'sybase.asa.dbremote.both' since another exclusive instance is running
E. 2018-03-11 15:11:00. Cannot register 'sybase.asa.dbremote.both' since another exclusive instance is running (-782)
E. 2018-03-11 15:11:00. Error connecting to database

You can test this yourself quite easily with your v10 software by executing the following two commands in a DOS prompt against a running database with replication enabled to see the same message I posted above in one of the two output files :

start dbremote -c "..." -v -o rem_1.txt 
start dbremote -c "..." -v -o rem_2.txt 

Have you run dbtran (with -sr) against the source database and confirmed that there are actually committed transactions that occurred between log offsets 05475305061 and 05475926378?

Reg

permanent link

answered 11 Mar '18, 15:40

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

I'm quite sure I have even noticed that error message with v8...

(11 Mar '18, 16:02) Volker Barth

Yes, I tried to call concurrent 2 instances of DBREMOTE and I got the error message (-782). On the productive system we have the same version, and we call DBREMOTE once each 30 minute, nevertheless I can see two log files generated simultaneously (generating the messages (1-05474989504-05475193069-0);(1-05475193069-05475299039-0);(1-05475299039-05475305061-0) on 2018-02-26 11:00:06). Since we are using the switch -X with DBREMOTE, the Transaction-log files are reorganized and the suspecting range of offset is no more existing. I have deactiveted -X and will wait for next error occurence and try dbtran with -sr.

(12 Mar '18, 05:23) Baron
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×78

question asked: 08 Mar '18, 18:21

question was seen: 1,676 times

last updated: 13 Mar '18, 06:18