undo reorganizing transaction log files (DBREMOTE -X)

[Baron]

We are using SQL-Remote for replicating 2 databases.

DBREMOTE -X had for any reason reorganized transaction log files, which are not replicated to server.

In other words, sysremoteusers on remote has (log_send = 40k, log_sent=38k, confirm_sent=30K), and the main transaction log file starts from offset 40k.

Error message of DBremote:

I. 2017-06-20 09:53:38. Scanning logs starting at offset 0038180596 
E. 2017-06-20 09:53:38. No log operation at offset of 0038180596 in the current transaction log
E. 2017-06-20 09:53:38. Sending messages failed.

the looking for offset is no more in the main transaction log file, but it is existing in the reorganized transaction log file.

Question: what is the reason that the DBremote has reorganized offset which are not confirmed from consolidated?

Question: is it possible to undo this reorganization, (i.e. merge the two transaction log files together).

Thanks in advance

[VolkerBarth]

Where are the renamed transaction logs located?

See the DBREMOTE syntax:

DBREMOTE [options] [directory]

where directory is the name of the directory containing those renamed logs.

That does not answer your question 1, and for question 2, AFAIK, the answer is "no, you can't" but it should keep you going, i.e. if the renamed logs are there, DBREMOTE should find and use them for its scanning.

[Breck_Carter]

Dbremote -x does not "reorganize" anything, it renames the transaction log file and starts a new one. The word "reorganization" implies a dramatic rearrangement of data inside a file and that absolutely does not happen with dbremote -x.

Please do all the following steps.

\\1. Please confirm that you understand this Help topic: Maintaining transaction logs for remote databases

Please note that it is up to you to preserve the renamed log files until they are no long needed.

\\2. Please tell us the version and build of SQL Anywhere you are using; e.g., dbisql SELECT @@version

@@version
'16.0.0.2344'

\\3. Please take the time to give precise information, not abbreviated values like "40k" and "38k". You can copy and paste the result set from dbisql SELECT * FROM sysremoteusers

user_name,consolidate,type_name,address,frequency,send_time,next_send,log_send,time_sent,log_sent,confirm_sent,send_count,resend_count,time_received,log_received,confirm_received,receive_count,rereceive_count,time_sent_utc,time_received_utc
'cons','Y','FILE',cons,'A',,,690299,'2016-12-30 16:01:07.000',690299,690299,1,0,'2016-12-30 16:02:29.000',679429,679429,2,0,'2016-12-30 20:01:07.000+00:00','2016-12-30 20:02:29.000+00:00'

\\4. Have you set the remote database delete_old_logs option to any value other than the default 'off'?

\\5. Have you accidentally deleted a renamed log file that contains data that hasn't been confirmed yet?

\\6. Please show us the starting and ending log offsets of each renamed log file, and the current log file. You use the dbtran utility by specifying "nul" instead of the normal "whatever.sql" output file:

"%SQLANY16%\\bin64\\dbtran.exe" -o 161230AA.txt -y 161230AA.LOG nul

-- contents of 161230AA.txt:

SQL Anywhere Log Translation Utility Version 16.0.0.2344
Transaction log "161230AA.LOG" starts at offset 0000672554
Transaction log ends at offset 0000690299

[Baron]

the renamed transaction logs along with the main transaction log file + db file are located under same path. Dbremote is called with the following options: "%SQLBIN%\\DBRemote.EXE" -c "ENG=%DBEngine%;DBN=%DBVP%;links=TCPIP(HOST=Loopback);UID=???;PWD=???;charset=none" %DBDIR% -v -t -k -o %Repl-LOGFILE% -x -l 200000

where %DBDIR% is the physical path where db is located.

[VolkerBarth]

In my understanding, the path must be specified behind all parameters (in contrast to your command line).

Does the DBREMOTE output contain a line like

I. Processing transaction logs from directory "..."

with the specified directory?

If not, I would think the directory is ignored.

[Baron]

I can see this line (processing transaction logs from directory...) in the output. I controlled the productive DB and actually I see that the problem is somehow different: DBremote is actually considering the renamed transactions, but can't find in it the exact offset (i.e. the offset which is written in sysremoteusers/log_sent).

Once again the status and the error code: select * from sysremoteusers:

user_name,consolidate,type_name,address,frequency,send_time,next_send,log_send,time_sent,log_sent,confirm_sent,send_count,resend_count,time_received,log_received,confirm_received,receive_count,rereceive_count
'DBSRV_user','Y','FILE','C:\\\\ViPOS\\\\POS\\\\Replikation\\\\SRV','A',,,40800657,'2017-06-13 04:17:24.000',38180596,38180596,217,4,'2017-05-17 04:17:43.000',72045949,,208,19

DBRemote output:
 2017-06-20 09:53:38. Scanning logs starting at offset 0038180596
I. 2017-06-20 09:53:38. Processing transaction logs from directory "C:\\Users\\SKA\\Desktop\\Kassa\\Daten"
I. 2017-06-20 09:53:38. Transaction log "C:\\Users\\SKA\\Desktop\\Kassa\\Daten\\170612AA.LOG" starts at offset 0027404455
I. 2017-06-20 09:53:38. Processing transactions from transaction log "C:\\Users\\SKA\\Desktop\\Kassa\\Daten\\170612AA.LOG"
E. 2017-06-20 09:53:38. No log operation at offset of 0038180596 in the current transaction log
E. 2017-06-20 09:53:38. Sending messages failed
I. 2017-06-20 09:53:39. Execution completed

Whereas: first offset in 170612AA.LOG is 0027408576 and the last offset is 0040796489

[VolkerBarth]

See Breck's list of things to do, particularly no. 6.

And please tell us additionally the log offsets as seen from the database file via "DBLOG -o <output.txt> MyDbFile.db" to show what the database file itself tells about the logs and SQL Remote offset.

As the current SQL Remote offset (0038180596 AFAIK) is contained in an older transaction log... - has the TL been renamed outside of DBREMOTE -x?

[regdomaratzki]

1) Is it possible this remote database been restored from a backup and that dbremote ran and sent messages to the consolidated database from a transaction log that no longer exists?

2) As Breck asked (and Volker, and now me) can you please post the starting and ending log offset of ALL the transaction logs in C:\\Users\\SKA\\Desktop\\Kassa\\Daten?

3) Can you run dbtran against 170612AA.LOG (that should contain offset 38180596 based on your last post) with the switches below?

dbtran -a -z -y -hd C:\\Users\\SKA\\Desktop\\Kassa\\Daten\\170612AA.LOG  > dbtran_hd.txt

Please post the lines near the top of the "dbtran_hd.txt" file and the end of the file that show the starting and ending offsets :

Transaction log "asademo.log" starts at offset 0001036975
Transaction log ends at offset 0001043535

Please also post the 20 lines on either side of offset 38180596 (or where it should appear) from this file. For example (I've only posted five lines on either side :

0038180538 Begin_Transaction       3  12 742
0038180541 Update         15  12 742
0038180556 Update         23  12 743
0038180579 Commit          3  12 743
0038180582 Start_Checkpoint       21  12 743
0038180603 Finish_Checkpoint       5  12 743
0038180608 User            7  12 743
0038180615 Release         5  12 743
0038180620 Start_Checkpoint       21  12 743
0038180641 Finish_Checkpoint       5  12 743

[regdomaratzki]

@Volker: If no [directory] is specified on the the command line, dbremote (and dbmlsync) will always look for old logs in the same directory where the active transaction log resides if it fails to find the offset it needs in the active transaction log.

[VolkerBarth]

Thanks for the clarification, and the Golden Watcom Rule applies again, obviously:)

[VolkerBarth]

Ah, so there's a -hd switch to dbtran? Good to know:)

[regdomaratzki]

Don't tell anyone. 🙂

[Baron]

@Volker: "DBLOG -o <output.txt> MyDbFile.db" ->.

SQL Anywhere Transaction Log Utility Version 10.0.1.4213 "C:\\Users\\SKA\\Desktop\\Kassa\\Daten\\mydbfile.db" is using log file "mydbfile.log".

"C:\\Users\\SKA\\Desktop\\Kassa\\Daten\\mydbfile.db" is using no log mirror file.

Transaction log starting offset is 0040796494.

SQL Remote truncation offset is 0022930178.

Transaction log current relative offset is 0008503263

[Baron]

@reg: 0038168333 Commit 1 18 958

0038168334 Begin_Transaction 1 18 958

0038168335 Text 84 18 958

0038168419 Commit 1 18 958

0038168420 Begin_Transaction 1 18 958

0038168421 Text 33 18 958

0038168454 Commit 1 18 958

0038168455 Begin_Transaction 1 18 958

0038168456 Text 15316 18 958

0038183868 Commit 1 18 958

0038183869 Begin_Transaction 1 18 958

0038183870 Text 17686 18 958

0038201676 Commit 1 18 958

0038201677 Begin_Transaction 1 18 958

0038201678 Text 38 18 958

0038201716 Commit 1 18 958

0038201717 Begin_Transaction 1 18 958

0038201718 Text 58 18 958

0038201776 Commit 1 18 958

0038201777 Begin_Transaction 1 18 958

0038201778 Text 3106 18 958

0038204884 Commit 1 18 958

0038204885 Begin_Transaction 1 18 958

0038204886 Text 4134 18 958

0038209044 Commit 1 18 958

[VolkerBarth]

IMHO, it's "always rename and restart":)

When using DELETE_OLD_LOGS = 'ON' (or the 'DELAY' or 'n days' variants), older logs should be automatically deleted once the SQL Remote truncation offset is higher than the log's ending offset (and when the according time has passed, when using the mentioned variants).

I'm surprised that the current remote offset is 0022930178 (i.e. begin of the first old log) although sysremoteuser has confirmed the sent offset 0038180596. IMHO then DBREMOTE -x should be able to delete the older logs up to "170611AA.LOG".

[VolkerBarth]

Hm, I can't comment on that contents except there is "No log operation at offset of 0038180596" - or has it been omitted here?

[Breck_Carter]

Argh! I accidentally deleted your comment! Sorry!

a. The default dbremote -x 0 means "always rename and restart, don't bother applying a threshold transaction log file size test".

To All: For another description of dbremote -x see SQL Remote Message Agent utility (dbremote)

b. I get confused about this stuff every time I look at dbremote, and I've been "looking at dbremote" ever since it was first released in 1895 🙂

Yes, you understand it correctly, but I will restate the obvious for my own benefit...

The dbremote -x [ size ] command-line option controls the "rename and restart" process, it does not delete anything.

The "delete_old_logs" database option controls the deletion of the old renamed transaction log files.

The Help statement "The following command deletes the transaction log when it is larger than 1 MB:" is just ... so ... wrong.

I used to be a Help fanboi, but [rant deleted].

Sadly, none of this is getting closer to answering your question 😞

[regdomaratzki]

I strongly suspect that the answer to question #1 that I asked is "yes". I've posted a new answer.

[Breck_Carter]

I promise: I will never click on the comment "X" ever again, it's too easy to click on the wrong "X"!

[regdomaratzki]

The is no operation at offset 38180596 in the log, and there is no other transaction log in this directory that contains this log offset. The only possible explanation is that at some point, dbremote scanned a transaction log for this remote database that contained a transaction log that did have an operation that began at offset 38180596. It will have been a commit, and would have been the last thing scanned by dbremote in that run. dbremote will have sent a message to the consolidated database with this operation. Before dbremote ran again, the remote database will have been incorrectly restored from a backup, and after the restore, the current log offset will have been less than 38180596. dbremote will have then run, and picked up a confirmation message from the consolidated confirming that offset 38180596 had been applied at the consolidated. However, different operations will now be in the transaction log, and this remote database is now hopelessly broken. Operations have been sent and applied at the consolidated database that no longer exist at the remote database. While it is theoretically possible to fix the offsets so that messages can be applied again, the data between these two database is no longer in synch, so a re-extraction of the remote database is likely your easiest option.

[Breck_Carter]

> a re-extraction of the remote database is likely your easiest option

To all: There are decades of experience behind that wise advice.

[Baron]

one small question: Is this expression correct? online transaction log file = the main transaction log file. offline transaction log file = the renamed transaction lof files.

[VolkerBarth]

Basically yes.

This is seen from a DBREMOTE point of view: As SQL Remote must have connected to the database in order to apply messages and scan the active log, the database must be running, and therefore the active transaction log is "online". "Offline" logs are renamed older logs, either renamed via DBREMOTE -x or via a backup or simply by renaming the log when the database is not running (the latter not necessarily recommended:)).