applying recent transaction logs with smaller offset

[Baron]

I am trying to apply recent transaction log files on an old copy of a database (SQL Anywhere 10) (the database is located under c:\\mytest\\mydb.db).

Let say the transaction log file is called tobeapplied.log it starts from Y1 and ends at Y2 and it is located under c:\\mytest\\logs.

I run the following command against my database:

dblog.exe "c:\\mytest\\mydb.db" -z Y1 -x 0 -t mydb.log -is -r

and then I run the following line:

dbsrv10.exe "c:\\mytest\\mydb.db" -ad "c:\\mytest\\logs"

then I get a message stating: tobeapplied.log is an invalid transaction log

What could be the reason for this error?

P.S. the log file tobeapplied.log was generated from the same database mydb.log (so it is original but with unmatched offset/date)

P.S. the offset of mydb.db was higher than the offset of tobeapplied.log, (i.e. through dblog.exe I lowered the offset range of the database).

[Breck_Carter]

For more information see this Help topic.

Example of dbtran on log file...

REM -a      include uncommitted transactions
REM -d      display output as chronological comments
REM -ir ... include offset1,offset2 range 
REM -it ... include affected user1.table1,user2.table2,... 
REM -s      produce ANSI UPDATE transactions
REM -u ...  include transactions for user1,user2,...
REM -y      overwrite output file without confirmation
REM uno.log         input transaction log file
REM dbtran_uno.sql  output translated log sql file

"%SQLANY10%\\win32\\dbtran.exe"^
  -a^
  -s -u UNO -y uno.log dbtran_uno_03.sql

PAUSE

Example of dbtran against active log file...

"%SQLANY16%\\bin64\\dbtran.exe"^
  -y^
  -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql;"^
  -n dbtran_output.sql

PAUSE

[Baron]

Thanks, good to know that I can even translate ACTIVE logs too!!

Usually I was translating only offline logs, or had to stop the database.

With -C seems cool!!

[VolkerBarth]

I guess you are trying to do something unsupported: The restore mechanism via dbsrvX -a or -ad is meant to apply "newer" transaction log files to an older database file, and in continuous order, so there never should be a need to adapt log offsets - those should fit. In contrast, you are trying to restore from an older log, which is "suspicious", as its operations should already be incorporated in the newer database file. I guess this explains the error message.

When you want to apply operations from "some log" that is not part of the restore chain of the current database file, you can do so by translating the log (or parts) via DBTRAN to SQL statements, and then run those against the active database. So their effect is applied but with different offsets.

[Baron]

Thanks, I was trying to do it with dbsrv -a.

But I don't know why my approach is unsupported or suspicious, since I am adjusting the offsets.

[Breck_Carter]

Personally I am very happy that dbsrv -a does not allow any unusual use, since it is VERY easy to get log files mixed up and it's nice to know that dbsrv -a catches all our errors.

In this case the error message was "invalid transaction log" which seems to indicate a much more serious error than just "out of order"... did you edit the tobeapplied.log file itself?

[VolkerBarth]

Note, in my understanding (and according to the docs) using DBLOG -x -z is meant for one particular purpose, namely after reloading databases that are used in replication/synchronization:

• To adapt those database files after a reload to make them fit to the last active transaction log before the database file was reloaded, and that is only because the reload will modify the log offsets, which is undesired here.

I don't think it is meant to make a new database file think it should accept an older log file, and this situation is also none of the supported recovery situations.

[Baron]

Yes I translated the tobeapplied.log and then read it in the database and it has worked.

The transactions in tobeapplied.log are not older than Database (from the view of timestamp), but they are older in terms of offset range. This is because they were generated recently from an old restored DB.