manipulating sysremoteusers.confirm_received

[Baron]

I have an installation of 2 DBs replicating using DBRemote.

I lost the productive Cons. DB, but I have a backup of before 2 days (so now Remote DB. is dated 10.12.2019 and Cons. DB is dated 08.12.2019).

Now I want to use this 2 days old Cons. DB, but it misses all the transactions of Remote DB since 2 days (between 08.12.2019 - 10.12.2019).

What can I do in order to enforce the remote DB to resend all the transactions up 08.12.2012 (knowing that the Remote DB. was replicating against the lost Cons. till 10.12.2019, and knowing that the transaction logs of Remote DB still has the transactions of more than 2 days ago)?

I tried to manipulate the SYSREMOTEUSERS: I changed log_send, log_sent, confirm_sent values on Remote DB and set it to the values of log_receieved, confirm_received on the backup Cons. DB (i.e. -2 days), and, additionally I changed the log_received, confirm_received values on Remote DB and set it to the values of log_send, log_sent, confirm_sent on the backup Cons.

When I read the table SYSREMOTEUSERS then I see all the values are correct as I want, and when I start DBREMOTE then I see that the remote DB is generating the wanted packets (between 8.12.2019 - 10.12.2019) (The amount of generated packets tell me that they contain 2 days transactions), but the problem is that DBREMOTE additionaly (and unintendently) changes the value of confirm_received (on Remote DB) to the value of 10.12.2019!

In other words, after starting DBREMOTE the value of confirm_received is set back to the last value!! and I dont know from where it could remember this offset (having that I have overritten SYSREMOTEUSERS)

As a result, the Cons. DB does not accept those generated packets, because the confirmation offset is bigger than its current offset (the error message says, packets dont belong me!!).

I know that manipulating SYSREMOTEUSERS is not documented, but how is considered to solve such situations? (Replicated against backed up cons.).

[VolkerBarth]

The need to restore the consolidated from an old backup is always a severe problem with SQL Remote and usually will require that the remotes that have replicated after the cons's backup was taken need to be re-extracted. At least that's the way to go unless you want to mess around with SYSREMOTEUSER settings... - That's why the docs tell the consolidated logs are crucial in a SQL Remote setup...

---

Before you re-extract those remotes, you could usually try to get the active translog from the remotes and DBTRANslate it to get changes that have been made there after the last replication with the cons (as of its backup) has taken place, and check the according statements and apply them at the cons, if published data is affected. That way, one would hope that remotes could be re-extracted based on their current data.

[Baron]

Translating the transaction logs of Remotes and apply them on Cons. is not that easy, because some transactions from Remotes are not applied 1:1 on the Cons. (Conflict triggers, and another triggers written for our special cases, that distinguishes whether the transcation is being applied over replication or as local).

My question is, when I change the value of confirm_received & log_received, and later I can see it changed in SYSREMOTEUSERS, how can DBREMOTE ignore my changes on confirm_received and bring it again to its last value? How can i prevent this happening?

[VolkerBarth]

That's probably a question for @Reg... - I can't tell that, except to ask whether you "cleaned" the message stores so no unwanted messages from before the cons's crash are sitting somewhere?

[VolkerBarth]

Translating the transaction logs of Remotes and apply them on Cons. is not that easy.

Nobody said it would be... Therefore log backup is so crucial for consolidated databases. Are there no incremental log backups for the last two days? (I'm aware that it would still be a problem if the remote has replicated after the last log backup and has no backup itself, but at least the amount of data to be salvaged from the remote's log should be much smaller.)

[Baron]

Do you mean whether during starting DBREMOTE some messages are read inside Remote (from Cons.).

Here I can say no, the inbox folder for the Remote (select publisher_address from sysremotetype where type_name = 'file') was empty!

[Baron]

Moreover, after starting DBREMOTE, only the value of confirm_received was changed, and the value of log_received was still the same as my manipulation.

So, the value of confirm_received is restored from somewhere in DB (ISYSREMOTEUSER?)

[VolkerBarth]

Well, I meant inboxes for both databases (and "outboxes", if these are different folders, aka if the inbox of the remote is not also the outbox of the cons).

[regdomaratzki]

In other words, after starting DBREMOTE the value of confirm_received is set back to the last value!! and I dont know from where it could remember this offset (having that I have overritten SYSREMOTEUSERS)

Were there messages in the message system applied by the remote database when you ran dbremote?

Reg

[Baron]

No, moreover, I started DBREmote with -S (only send messages)! to make sure that no messages will be applied, but only send messages.

[Baron]

I do the following:

1. execute remote reset cons_user (on Remote).
2. Then I have sysremoteusers.log_received=0 && sysremoteusers.confirm_received=0 && syssubscriptions.created = syssubscriptions.started = current offset of DB.
3. I change the values of syssubscriptions.created && syssubscriptions.started to its same values before executing step 1.
4. I change the values of sysremoteusers.log_sent = sysremoteusers.confirm_sent=X, where X is my desired values (the last offset replicated to the backed up Cons.)
5. Now Sysremoteusers has the values which I want (log_sent =confirm_sent=X; log_send = log_received = confirm_received = send_count = resend_count = receive_count = rereceive_count = 0; time_received = time_sent = null).
6. Now I start DBREMOTE with -S, and then it generates the messages starting from X (exactly as I want), but the only problem is that now sysremoteusers.confirm_received is changed to the value before executing step 1, and of course this value is written als header in the messages, so that the remote will reject those messages!

Could you please help anyhow? @reg

[Baron]

Actually I did more, so I unloaded the database after STEP 5, and I started looking for the value of sysremoteusers.confirm_received, to see where is this value saved in DB, but I didnt find it neither in the unload.sql nor in the .dat files?

So for me is a something mysterious and very magic!!

[VolkerBarth]

Hm, if you do not understand those values and their calculation and meaning, then I doubt it is a good idea to use undocumented functions to manipulate them... (Note, I do not claim to understand them fully, either...)

[Baron]

I work with SQL Remote since more than 2 years, and I think I understand the meaning of the mentioned fields but I was always watching the values to check the health of the Replication, but this is the first time I manipulate its values (I must do it to restore my system)

[VolkerBarth]

That certainly should be contained in the reload.sql file in the context of the remote user creation...

[VolkerBarth]

I can't tell whether Reg has more support to offer, but in my understanding the settings of the remote's SYSREMOTEUSER table should mirror those of the consolidated's backup (with mirrored roles, apparently), particularly if you want to have the statements in the remote that have been made after the last replication before the consolidated's backup time to be replicated to the cons, again.

There should neither be a need to call REMOTE RESET nor to reload the remote.

[Baron]

I swear that it is not contained in reload.sql in the Create SQL Remote definitions section.

Should I copy the whole section here?

Note, I am unloading the DB before Step 6 and after Step 5.

If you mean unloading the DB after Step 6, then yes, I should drop my swear.

[VolkerBarth]

There should be a call to the undocumented function you are dealing with, at least that's my experience when using DBUNLOAD with according options... - But as stated, you should not need to reload the remote AFAICT.

[VolkerBarth]

In addition, the database property "RemoteTrunc" might interfere with your manipulations.

[Baron]

Yes, there is a call to the undocumented function, but in the value of '0' was passed to the confirm_receieved parameter. The parameters are exactly so:

( 108, 2217130948 , 2217130948,0,0,0,0,0,0);

Where, 108 is the User of Cons, 2217130948 is my X, and all other parameters are 0.

Once again, the problem is that when I run DBREMOTE then sysremoteusers.confirm_received gets the value of 19142614683, and the current offset on Cons (restored copy) is something like 19120000000 (i.e. < 19142614683), so that the Cons. will reject any messages coming from this (manipulated) Remote.

[VolkerBarth]

Yes, when you do the unload after you have already manually set these values, they do get unloaded as such. So what does database property "RemoteTrunc" say? (See my other comment.)

[Baron]

Which "other comment" you mean? What about this property "RemoteTrunc"? How can I find it?

[VolkerBarth]

Which "other comment" you mean?

That from currently two hours ago...

select db_property('RemoteTrunc')

returns the minimal confirmed log offset for the SQL Remote Message Agent, and I could imagine (but don't know) it does contain the value you have stumbled upon.

[Baron]

This property is related to the offset of the current Database (confirm_sent), but my problem is with the confirm_received.

[Baron]

Please correct me if I am mistaken!

The property RemoteTrunc is related to the offset of the current Database (in my opinion it should be equal to confirm_sent, despite in practice it is always smaller than it).

More specific, this property means Truncation of SQL Remote (despite its name sounds different at the first moment, and can be understood as Offset Truncation of the Remote Database), so, in other words, it tells till which offset the transaction logs are not any more necessary, so that it could be truncated.

[VolkerBarth]

Yes, that's pretty much my understanding, too, and it is part of the "DELETE OLD LOGS" handling, AFAIK.

Sorry for introducing some confusion between confirm_sent and confirm_received - I just wanted to mention that this database property might play a roll here when the SYSREMOTEUSER settings are manipulated. I wasn't sure whether that value is adapted then, as well, or remains unchanged and could make SQL Remote think there is something wrong here, leading in unexpected behaviour.

That being said, I'm out of my wits for further suggestions (other then re-extracting the remote), so I will shut up now...

[JimDiaz]

You should duplicate this on a test database but I believe this can be fixed using SetRemoteUser. If you still have the original databases before any attempts made above, post the result of SELECT * FROM Sys.SysRemoteUser from all three databases and I'll give you a hand. Basically you need to set log_sent, confirm_sent, log_received, confirm_received, resend and rereceive on all three databases to make this work.

Since the consolidated is the old backup you will need to use it's xxx_sent numbers because these are tied to the database log files. You will need to use the consolidated log_received as well to force the remotes to resend from the last good receive. As for the confirmed_xxx this is why we need to look at the SysRemoteUser results.

Of course if any transactions were applied to the cons after the last backup these will be lost.

There are other issues you should be aware of, for example if the remotes xxx_received are > the cons xxx_sent this means you will eventually resend offsets that the remotes have already received. Depending on what these transactions were there may be failures while applying at the remotes (transactions already exist).

This is not a guaranteed process, that's why backups are so important but I expect you know that.

Merry Christmas

Jim

[Baron]

Thanks for your help. Here I am posting the Sysremoteusers of one of the Remotes (as XML):

<sysremoteusers><log_send>2218767807</log_send><log_sent>2218767807</log_sent><confirm_sent>2218767807</confirm_sent><send_count>35351</send_count><resend_count>211</resend_count><log_received>19142614683</log_received><confirm_received>19142614683</confirm_received><receive_count>84094</receive_count><rereceive_count>1</rereceive_count></sysremoteusers>

What I want to do is to manipulate the offsets on this Remote (return it one day back).

I've manipulated SYSREMOTEUSERS on Remote so it looks like this:

<sysremoteusers><log_send>2218767807</log_send><log_sent>2215558422</log_sent><confirm_sent>2215558422</confirm_sent><send_count>34290</send_count><resend_count>211</resend_count><log_received>19122857111</log_received><confirm_received>19122857111</confirm_received><receive_count>84038</receive_count><rereceive_count>1</rereceive_count></sysremoteusers>

NOTE: Here there is no way to manipulate the log_send

Here I start DBREMOTE against this Remote and it works partially fine, because it sends the messages exactly in the range as I want (between between 02215558422 - 2218819601), but it additionally and unintentionly changes the value of confirm_received into 19142614683, so the reading looks like this:

<sysremoteusers><log_send>2218819601</log_send><log_sent>2218819601</log_sent><confirm_sent>2215558422</confirm_sent><send_count>34342</send_count><resend_count>211</resend_count><log_received>19122857111</log_received><confirm_received>19142614683</confirm_received><receive_count>84038</receive_count><rereceive_count>1</rereceive_count></sysremoteusers>.

Now of course those generated messages will be rejected by the (backed up) cons. (error looks like this):

Log offset sent to user 'Remote1' is '19122857111', but the message header indicates user 'DB111_user' has received log offset '019142614683'.

Knowing that the reading of Sysremoteusers on Cons. (backup, 1 day before) is like this:

<sysremoteusers><log_sent>19122857111</log_sent><confirm_sent>19122857111</confirm_sent><send_count>84038</send_count><resend_count>1</resend_count><log_received>2215558422</log_received><confirm_received>2215558422</confirm_received><receive_count>34290</receive_count><rereceive_count>211</rereceive_count></sysremoteusers>

[Baron]

So my main problem is that the DBREMOTE changes the value of confirm_received from 19122857111 to 19142614683!!

How can I prevent this happening? And from where does remember this value?

[JimDiaz]

Don't worry about log_send take a look at the SYS.sa_setremoteuser procedure this is how you should be manipulated SYSREMOTEUSERS it doesn't care about send. Remember the remotes sent is the cons received. Since the cons is old you need to use it's values on the remote for all procedure parameters SYS.sa_setremoteuser( p_user_id unsigned integer, Cons User Id p_log_sent numeric(20), 2215558422 p_confirm_sent numeric(20), 2215558422 p_send_count integer,34290 p_resend_count integer, 211 p_log_received numeric(20), 19122857111 p_confirm_received numeric(20),19122857111 p_receive_count integer,84038 p_rereceive_count integer 1 )

[Baron]

Yes, log_send does not bother me, and my main problem is that I can't set the value of confirm_received on the remote.

So in my case I can not set the confirm_received on remote to the old value of 19122857111!

Indeed the procedure SYS.sa_setremoteuser can set this wanted value, but as soon as I start DBREMOTE against this remote, then the Remote distinguishes somehow that the value 19122857111 is a manipulated value and sets it back to 19142614683, and this is the myterious part for me.

Actually I could overcome this situation with a some 'ugly' workaround, so that I did not change the confirm_received on the remote, but I have manipulated the log_sent & confirm_sent on the Cons. to a bigger value (19142614683).

Here of course the problem was that the current offset of the Cons. was smaller than 19142614683, so that I had to manipulate it too using (DBLOG -x -z).

This solution was ugly, because I created a gap in the transaction log files of the cons. (there are no transactions between 19122857111 and 19142614683), but at least the DBRMOTE does not complain anymore.