|
Home > Archive > SQL Anywhere database replication > April 2005 > DBREMOTE problem - No log operation at offset xxx in the current transaction log
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
DBREMOTE problem - No log operation at offset xxx in the current transaction log
|
|
| djohel 2005-04-20, 11:23 am |
| We're using version 9.0.2.2572
dbremote - FTP
been running for years now
I have over 150 remote databases waiting for data.
Yesterday the offset that the consolidated database starts
looking from got screwed up somehow. Now it's looking back
to an earlier point that it can't find in any of the logs.
I. 04/20 09:42:43. Scanning logs starting at offset
0396082593
E. 04/20 09:42:50. No log operation at offset of 0396082593
in the current transaction log
Before that here are earlier times it ran successfully:
I. 04/18 16:50:02. Transaction log ends at offset 0399640718
I. 04/18 18:01:05. Scanning logs starting at offset
0399742143
I. 04/19 09:38:55. Scanning logs starting at offset
0400760949
I. 04/19 12:01:35. Scanning logs starting at offset
0400881331
I. 04/19 15:00:06. Scanning logs starting at offset
0396082593
What would cause it to jump back?
How can I fix where it should be starting from? I would
hope it would just use the current transaction log.
--CONNECT-1012-0400889814-consolid_hquser-2005-04-19 12:00
....
--COMMIT-1052-0400895798
COMMIT WORK
The offset is current pointing between to saved log files:
050416AD.LOG" starts at offset 0396078997
Thanks,
Dave
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2005-04-20, 11:23 am |
| Can you please post your dbremote command line, and also post the log
offsets of the current transaction log and all logs in your offline
directory. Assuming all the files are in the same directory, and all of the
form "*.log", the following command in NT/Win2K/XP will do it all in one
step :
for %i in (*.log) do dblog %i
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL
<djohel> wrote in message news:42667c24.3c4e.1681692777@sybase.com...
> We're using version 9.0.2.2572
> dbremote - FTP
> been running for years now
> I have over 150 remote databases waiting for data.
>
> Yesterday the offset that the consolidated database starts
> looking from got screwed up somehow. Now it's looking back
> to an earlier point that it can't find in any of the logs.
>
> I. 04/20 09:42:43. Scanning logs starting at offset
> 0396082593
> E. 04/20 09:42:50. No log operation at offset of 0396082593
> in the current transaction log
>
> Before that here are earlier times it ran successfully:
> I. 04/18 16:50:02. Transaction log ends at offset 0399640718
> I. 04/18 18:01:05. Scanning logs starting at offset
> 0399742143
> I. 04/19 09:38:55. Scanning logs starting at offset
> 0400760949
> I. 04/19 12:01:35. Scanning logs starting at offset
> 0400881331
> I. 04/19 15:00:06. Scanning logs starting at offset
> 0396082593
>
> What would cause it to jump back?
>
> How can I fix where it should be starting from? I would
> hope it would just use the current transaction log.
>
> --CONNECT-1012-0400889814-consolid_hquser-2005-04-19 12:00
> ...
> --COMMIT-1052-0400895798
> COMMIT WORK
>
> The offset is current pointing between to saved log files:
> 050416AD.LOG" starts at offset 0396078997
>
> Thanks,
>
> Dave
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2005-04-20, 1:23 pm |
| For some reason, a remote database has asked for a resend (because a message
was likely lost) from a log offset that doesn't point to the start of an
operation. I think you should be able to get by this issue for now by
running dbremote ONCE with the following added to the command line :
-hir 396082593,4294967296
You'll want to watch and make sure that the remote that caused the problem
fixes itself. You can identify which user asked for the resend with the
following SQL :
select user_name from sysremoteusers where log_sent = 396082593
Next time you received messages from this user, you should make sure the
problem hasn't occurred again.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL
<djohel> wrote in message news:426681ec.3c9f.1681692777@sybase.com...
> dblogs output is attached.
>
> d:\xxxx\xxxx\dbremot
e.exe -b -x -k -l 100000 -v -c
> " eng=xxxx;dbn=xxxx;co
n=xxxx;uid=xxxx;pwd=
xxxx" -o
> \\xxxx\xxx\dbremote_
messages.txt \\xxxx\xxxx
>
> I have to stop the DB to get the log info from the current
> log file?
>
> Dave
>
>
| |
| djohel 2005-04-20, 1:23 pm |
| Thanks for your help Reg, but it still is not working.
E. 04/20 13:25:23. Missing message from "rmt_rkxxxx"
(0-0366628583-0367332537-0)
I. 04/20 13:25:23. Scanning logs starting at offset
0396081149
I. 04/20 13:25:23. Processing transaction logs from
directory " \\xxxxx\sybase\enter
prise"
I. 04/20 13:25:34. Transaction log
" \\xxxxx\sybase\enter
prise\050416AD.LOG" starts at offset
0396078997
I. 04/20 13:25:34. Processing transactions from transaction
log " \\xxxxx\sybase\enter
prise\050416AD.LOG"
E. 04/20 13:25:34. No log operation at offset of 0396081149
in the current transaction log
E. 04/20 13:25:34. Sending messages failed
Dave
> For some reason, a remote database has asked for a resend
> (because a message was likely lost) from a log offset that
> doesn't point to the start of an operation. I think you
> should be able to get by this issue for now by running
> dbremote ONCE with the following added to the command line
> :
>
> -hir 396082593,4294967296
>
> You'll want to watch and make sure that the remote that
> caused the problem fixes itself. You can identify which
> user asked for the resend with the following SQL :
>
> select user_name from sysremoteusers where log_sent =
> 396082593
>
> Next time you received messages from this user, you should
> make sure the problem hasn't occurred again.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer
> Version 8 Please reply only to the newsgroup
>
> iAnywhere Developer Community :
> http://www.ianywhere.com/developer iAnywhere Documentation
> : http://www.ianywhere.com/developer/product_manuals ASA
> Patches and EBFs :
> http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> <djohel> wrote in message
> output is attached. >
>
>
| |
| djohel 2005-04-20, 1:23 pm |
| Hi Reg,
I have 5 remote users where the log sent is not current.
They share 3 different log_sent values.
Is there a way to override this value?
Is that what the -hir option was trying to do? I just need
to do it for 3 different log_sent values or 5 users.
Thanks,
Dave
> For some reason, a remote database has asked for a resend
> (because a message was likely lost) from a log offset that
> doesn't point to the start of an operation. I think you
> should be able to get by this issue for now by running
> dbremote ONCE with the following added to the command line
> :
>
> -hir 396082593,4294967296
>
> You'll want to watch and make sure that the remote that
> caused the problem fixes itself. You can identify which
> user asked for the resend with the following SQL :
>
> select user_name from sysremoteusers where log_sent =
> 396082593
>
> Next time you received messages from this user, you should
> make sure the problem hasn't occurred again.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer
> Version 8 Please reply only to the newsgroup
>
> iAnywhere Developer Community :
> http://www.ianywhere.com/developer iAnywhere Documentation
> : http://www.ianywhere.com/developer/product_manuals ASA
> Patches and EBFs :
> http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> <djohel> wrote in message
> output is attached. >
>
>
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2005-04-20, 1:23 pm |
| Having a log_sent value that is not current is OK. This means that while
this remote user was processing incoming messages, it determined that there
was a missing message, and asked for a resend from the last point it
successfully applied messages from the consolidated. This resend request is
sent in a message back to the consolidated. When the consolidated database
received and applies this message, it sets the log_sent value back to the
value request from the remote. This is all very normal.
What isn't normal is that the log offset requested from the remote (i.e. the
value in the log_sent column) should point to the beginning of an operation
in the old transaction logs stored on the consolidated, which is not
happening in your case. If you translate log 050416AD.LOG using dbtran, do
you see operations starting at offset 396082593 or 396081149? Did anything
strange happen on April 16th? Is it possible that dbremote ran and sent
messages from a transaction log that was later lost while you were forced to
recovery your consolidated database?
The -hir switch is forcing dbremote to only scan a certain range of log
offsets. When you are using this switch, dbremote will be more forgiving if
the offset you specify dos not match the beginning of an operation, and will
search forward in the log for the first operation is can find and start
there instead of failing with the error you are seeing.
I'd like you to capture a whole bunch of information for me
1) Capture the contents of the SYSREMOTEUSERS view by connect with DBISQL
and executing the following commands :
select * from SYSREMOTEUSERS;
output to c:\sysrem1.txt;
2) Run dbremote with the "-s" switch and "-hir 396081149,4294967296
" and
include "-o send2.txt" on the command line as well .
3) Capture the contents of the SYSREMOTEUSERS view by connect with DBISQL
and executing the following commands :
select * from SYSREMOTEUSERS;
output to c:\sysrem3.txt;
4) Run dbremote is receive mode only, by adding "-r" to the dbremote command
line, and save the output using "-o rec4.txt".
5) Capture the contents of the SYSREMOTEUSERS view by connect with DBISQL
and executing the following commands :
select * from SYSREMOTEUSERS;
output to c:\sysrem5.txt;
6) Run in send mode again ("-s"), with no -hir switch, and this time save
the output using "-o send6.txt"
Zip up and post the files to the forum.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL
<djohel> wrote in message news:426699ce.76b4.1681692777@sybase.com...[color=darkred]
> Hi Reg,
> I have 5 remote users where the log sent is not current.
> They share 3 different log_sent values.
>
> Is there a way to override this value?
>
> Is that what the -hir option was trying to do? I just need
> to do it for 3 different log_sent values or 5 users.
>
> Thanks,
>
> Dave
>
| |
| Dave Westphal 2005-04-20, 8:23 pm |
| Sorry for butting in, but thanks for the steps. We have been seeing this
oftener lately. First I questioned if a backup was restored on one of the
computers. But I am beginning to think that some message is lost and the
sync process is not recovering from the missed message.
I have 3 sites ( 3 different publisher computers) that are reporting the "No
log operation at offset" error.
thanks again,
Dave
"Reg Domaratzki (iAnywhere Solutions)" < Spam_bad_rdomarat@ia
nywhere.com>
wrote in message news:4266a0ec@forums
-1-dub...
> Having a log_sent value that is not current is OK. This means that while
> this remote user was processing incoming messages, it determined that
there
> was a missing message, and asked for a resend from the last point it
> successfully applied messages from the consolidated. This resend request
is
> sent in a message back to the consolidated. When the consolidated
database
> received and applies this message, it sets the log_sent value back to the
> value request from the remote. This is all very normal.
>
> What isn't normal is that the log offset requested from the remote (i.e.
the
> value in the log_sent column) should point to the beginning of an
operation
> in the old transaction logs stored on the consolidated, which is not
> happening in your case. If you translate log 050416AD.LOG using dbtran,
do
> you see operations starting at offset 396082593 or 396081149? Did
anything
> strange happen on April 16th? Is it possible that dbremote ran and sent
> messages from a transaction log that was later lost while you were forced
to
> recovery your consolidated database?
>
> The -hir switch is forcing dbremote to only scan a certain range of log
> offsets. When you are using this switch, dbremote will be more forgiving
if
> the offset you specify dos not match the beginning of an operation, and
will
> search forward in the log for the first operation is can find and start
> there instead of failing with the error you are seeing.
>
> I'd like you to capture a whole bunch of information for me
>
> 1) Capture the contents of the SYSREMOTEUSERS view by connect with DBISQL
> and executing the following commands :
> select * from SYSREMOTEUSERS;
> output to c:\sysrem1.txt;
>
> 2) Run dbremote with the "-s" switch and "-hir 396081149,4294967296
" and
> include "-o send2.txt" on the command line as well .
>
> 3) Capture the contents of the SYSREMOTEUSERS view by connect with DBISQL
> and executing the following commands :
> select * from SYSREMOTEUSERS;
> output to c:\sysrem3.txt;
>
> 4) Run dbremote is receive mode only, by adding "-r" to the dbremote
command
> line, and save the output using "-o rec4.txt".
>
> 5) Capture the contents of the SYSREMOTEUSERS view by connect with DBISQL
> and executing the following commands :
> select * from SYSREMOTEUSERS;
> output to c:\sysrem5.txt;
>
> 6) Run in send mode again ("-s"), with no -hir switch, and this time save
> the output using "-o send6.txt"
>
> Zip up and post the files to the forum.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> <djohel> wrote in message news:426699ce.76b4.1681692777@sybase.com...
>
>
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2005-04-21, 11:23 am |
| This is quite odd. I can't see why the log_sent values are being set
incorrectly, but they always seem to point back to the 050416AD.LOG
transaction log. Any chance you could zip up and attach all the
transactions logs from April 16th (i.e. 050416*.log), or possible even ALL
your old logs? At a minimum, I'd like to see 050416AC.LOG through
050416AE.LOG. I'm curious to see the operations in the around the two
offsets we've seen that are causing problems (396082593 and 396081149), and
also see whether these offsets were ever values in the log_sent on the
consolidated around this time as well.
If you're not comfortable posting the transaction logs in a public forum,
feel free to e-mail them to me directly (My e-mail address should be obvious
when you strip the Spam stuff on the address I use in my posts). Please zip
them up first no matter what you do though. I can send you instructions on
putting stuff on our FTP server if you're sending lots of information BTW.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL
<djohel> wrote in message news:4267b761.4dba.1681692777@sybase.com...
> Yesterday when I saw there were 5 users with older log_sent
> values I remote reset them and re-extracted their databases
> to get data going to the other users. It worked a few times
> and then 4 different users came up in sysremoteusers with
> older log_sent values and at least one of them not a valid
> value.
> After running what you asked, there is now one with a 'bad'
> log_sent.
>
> April 16th was a Saturday. I checked the event logs and
> found that the database was up the entire weekend.
>
> I am aware of the error that occurred on a remote database
> in one of the log files. If I find a duplicate in a before
> trigger during dbremote, can I delete the row from the 'new'
> table?
>
> Thanks,
>
> Dave
>
>
| |
| djohel 2005-04-21, 1:23 pm |
| Reg,
I'm blind because I can't find a email address for you.
Thanks,
Dave
NOSPAM__djohel@feldi
nc.com__NOSPAM
> This is quite odd. I can't see why the log_sent values
> are being set incorrectly, but they always seem to point
> back to the 050416AD.LOG transaction log. Any chance you
> could zip up and attach all the transactions logs from
> April 16th (i.e. 050416*.log), or possible even ALL your
> old logs? At a minimum, I'd like to see 050416AC.LOG
> through 050416AE.LOG. I'm curious to see the operations
> in the around the two offsets we've seen that are causing
> problems (396082593 and 396081149), and also see whether
> these offsets were ever values in the log_sent on the
> consolidated around this time as well.
>
> If you're not comfortable posting the transaction logs in
> a public forum, feel free to e-mail them to me directly
> (My e-mail address should be obvious when you strip the
> Spam stuff on the address I use in my posts). Please zip
> them up first no matter what you do though. I can send
> you instructions on putting stuff on our FTP server if
> you're sending lots of information BTW.
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer
> Version 8 Please reply only to the newsgroup
>
> iAnywhere Developer Community :
> http://www.ianywhere.com/developer iAnywhere Documentation
> : http://www.ianywhere.com/developer/product_manuals ASA
> Patches and EBFs :
> http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> <djohel> wrote in message
> times and then 4 different users came up in sysremoteusers
> http://www.ianywhere.com/developer/product_manuals ASA
>
>
| |
| Jeremy Swift 2005-04-21, 8:23 pm |
| < Spam_bad_rdomarat@ia
nywhere.com> (if your newsreader hides it you
should still be able to see the raw text and get it from that)
On 21 Apr 2005 10:22:51 -0700, djohel wrote:
[color=darkred]
>Reg,
>
>I'm blind because I can't find a email address for you.
>
>Thanks,
>
>Dave
>
> NOSPAM__djohel@feldi
nc.com__NOSPAM
>
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2005-04-22, 9:23 am |
| Dave e-mailed me the log I'd ask for and added this tidbit of information in
his e-mail, which explains what is happening for him :
> [someone] had a training session on Tuesday. He took a backup of the
> database and put it on a few users laptops for training. At the end of
> the session he put their old databases back.
>
> At least one of those users ran dbremote with the copy of production
> rather than with their own.
I'm assuming when you say he took a backup of "the database", you are
referring to the consolidated database, and I've removed the text that
identifies the person, to protect those involved. :)
Running a backup copy of the consolidated and sending messages from that
consolidated will DEFINITELY result in the problem that you are seeing. I
don't even need to look at the logs you sent given this information, since I
already know the answer to the question I was trying to answer. I'll call
the two consolidated database in this scenario REAL_CONS and BAD_CONS
Picture this :
1) A backup of real_cons is taken at time X, and user "rem1" has a log_sent
value of Y.
2) Somebody inserts data into a replicating table on bad_cons and runs
dbremote against bad_cons. The ending log offset of the message sent to
rem1 was Y+100, so that's the value of the log_sent column for rem1 in the
SYSREMOTEUSER table on bad_cons now
3) This message from bad_cons is picked up and successfully applied by rem1.
The confirm_received value in the remote is now set to Y+100, since it
successfully applied the message from bad_cons.
4) Real_cons runs dbremote and generates some messages for rem1. The ending
log offset of the message sent to rem1 from real_cons was Y+5000, so that's
the value of the log_sent column for rem1 in the SYSREMOTEUSER table on
real_cons now.
5) Dbremote runs on rem1. The message contains offsets Y through Y+5000,
but the remote has already applied everything up to Y+100, so it reports
"not applying messages that have already been applied" and asks for a
resend. It asks the consolidated (real_cons) to send everything from offset
Y+100 again, since that's the last offset it successfully applied.
6) When the consolidated gets this message, it sets the log_sent value to
Y+100, and during the next send phase, attempts to send messages again,
starting at offset Y+100. The odds that an operation exists at this offset
are low, since this is an offset that relates to bad_cons, not real_cons.
The "no log operation" error is returned by dbremote.
Anybody remote who applied messages from bad_cons will eventually run into
this problem.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL
<djohel> wrote in message news:4267e16b.4d3.1681692777@sybase.com...[color=darkred]
> Reg,
>
> I'm blind because I can't find a email address for you.
>
> Thanks,
>
> Dave
>
> NOSPAM__djohel@feldi
nc.com__NOSPAM
>
|
|
|
|
|