Skip to Content

Unable to apply logs on Sybase ASE

Dear Experts,

I am in a quite funny situation. I need to restore a Sybase ASE DB to another host. The restore itself goes perfect. Then I am able to apply only the first log after the backup, but it refuses to take the next one:

Specified file 'dump device' is out of sequence. Current time stamp is May 8
2018 7:08:02:096PM while dump was from May 8 2018 7:08:03:096PM.

This sounds like a bad joke, that only Sybase is capable of !!! There is merely a 1 second difference !!! And I can't understand how this can happen...

Of course, other logs can't be be applied either, as the time difference is even greater:

Specified file 'dump device' is out of sequence. Current time stamp is May 8
2018 7:08:02:096PM while dump was from May 8 2018 7:10:02:096PM.

or

Specified file 'dump device' is out of sequence. Current time stamp is May 8
2018 7:08:02:096PM while dump was from May 8 2018 7:06:03:096PM.

PLEASE kindly help me to fix this somehow !! If there can be a fix at all...

I am certain that there is NO other log that I am missing...

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • May 11 at 12:25 PM

    Hi Bret,

    many thanks for your fast reply.

    Here is our version:

    Adaptive Server Enterprise/16.0 SP02 PL04/EBF 26122 SMP/P/x86_64/Enterprise Lin
    ux/ase160sp02pl04x/2586/64-bit/FBO/Fri Jun 10 11:33:16 2016

    Yes, we are relying on a naming convention including date and time!
    I've been trying those logs back and forth and there is a 1 second slide which prevents me from applying the log.
    Honestly, this is SOOOO twisted, it can ONLY happen with Sybase :-D

    PLEASE kindly let me know if there is a chance do fix this.

    I also thought that starting with a new dump will help. This is my second attempt.
    The previous one finished, when after applying numerous logs, Sybase suddenly
    said that 2 different log files with different filenames and different sizes are having
    the same timestamp. So I was in pretty much the same situation. I have no speech...

    Add comment
    10|10000 characters needed characters exceeded

  • May 09 at 04:33 PM

    Hi Symon,

    Most likely you will have to start over with a new full database dump, but we can work on figuring out what happened

    What is your ASE version? (select @@version).

    What makes you certain that there is no other log you are missing?
    Are you relying on a naming convention of including date and time information in the tran dump file name to make the name unique?

    -bret

    Add comment
    10|10000 characters needed characters exceeded

  • May 14 at 05:33 PM

    Hi Symon,

    What makes you certain that there is no other log you are missing?

    I think the most likely reason for this happening is that you actually are missing a log dump.

    What I suspect is happening is that two DUMP TRAN procedures are occurring at about the same time and the same file name being generated for both dump files. ASE does use a locking mechanism to ensure that the transaction dumps themselves are serialized, but that is not necessarily true of the user code that generates the file names, and it can certainly get executed twice during the same second. If the names are the same, the second dump will occur just after the first (explaining the 1 second difference in sequence values), but will overwrite the first dump's archive file.

    You can check for additional evidence of this happening by examining the dump history file (if you have that feature enabled), or the backupserver errorlog. In the backupserver log, grep for the name of transaction log that is failing with a sequence timestamp 1 second off. It should appear twice, once is a "Creating new disk file" message, and once in a "Dumpfile name ... section number 1 mounted on disk file ..."
    message. If the "Dumpfile name" message appears twice, it means that two dumps were made to that file.

    Example:

    1> create database zotz on default log on default with override
    2> go
    CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'master'
    (1536 logical pages requested).
    CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk 'master'
    (1536 logical pages requested).
    Caution: You have set up this database to include space on disk 0 for both data
    and the transaction log. This can make recovery impossible if that disk fails.
    Database 'zotz' is now online.
    1> dump database zotz to "zotz.dmp"
    2> go
    Backup Server: 4.172.1.4: The value of 'allocated pages threshold' has been set
    to 40%.
    Backup Server session id is: 47. Use this value when executing the
    'sp_volchanged' system stored procedure after fulfilling any volume change
    request from the Backup Server.
    Backup Server: 4.41.1.1: Creating new disk file
    /bret/rel157.134/ASE-15_0/install/zotz.dmp.
    Backup Server: 6.28.1.1: Dumpfile name 'zotz1813408EE8 ' section number 1
    mounted on disk file '/bret/rel157.134/ASE-15_0/install/zotz.dmp'
    Backup Server: 4.188.1.1: Database zotz: 866 kilobytes (100%) DUMPED.
    Backup Server: 3.43.1.1: Dump phase number 1 completed.
    Backup Server: 3.43.1.1: Dump phase number 2 completed.
    Backup Server: 3.43.1.1: Dump phase number 3 completed.
    Backup Server: 4.188.1.1: Database zotz: 874 kilobytes (100%) DUMPED.
    Backup Server: 3.42.1.1: DUMP is complete (database zotz).
    1> dump tran zotz to "zotz.trn"
    2> go
    Backup Server session id is: 52. Use this value when executing the
    'sp_volchanged' system stored procedure after fulfilling any volume change
    request from the Backup Server.
    Backup Server: 4.41.1.1: Creating new disk file
    /bret/rel157.134/ASE-15_0/install/zotz.trn.
    Backup Server: 6.28.1.1: Dumpfile name 'zotz1813408EF9 ' section number 1
    mounted on disk file '/bret/rel157.134/ASE-15_0/install/zotz.trn'
    Backup Server: 4.58.1.1: Database zotz: 6 kilobytes DUMPED.
    Backup Server: 3.43.1.1: Dump phase number 3 completed.
    Backup Server: 4.58.1.1: Database zotz: 10 kilobytes DUMPED.
    Backup Server: 3.42.1.1: DUMP is complete (database zotz).
    1> dump tran zotz to "zotz.trn"
    2> go
    Backup Server session id is: 54. Use this value when executing the
    'sp_volchanged' system stored procedure after fulfilling any volume change
    request from the Backup Server.
    Backup Server: 6.28.1.1: Dumpfile name 'zotz1813408F08 ' section number 1
    mounted on disk file '/bret/rel157.134/ASE-15_0/install/zotz.trn'
    Backup Server: 4.58.1.1: Database zotz: 6 kilobytes DUMPED.
    Backup Server: 3.43.1.1: Dump phase number 3 completed.
    Backup Server: 4.58.1.1: Database zotz: 10 kilobytes DUMPED.
    Backup Server: 3.42.1.1: DUMP is complete (database zotz).



    [install]$ grep zotz.trn rel157_bret_bs.log
    May 14 10:10:01 2018: Backup Server: 4.41.1.1: Creating new disk file /bret/rel157.134/ASE-15_0/install/zotz.trn.
    May 14 10:10:01 2018: Backup Server: 6.28.1.1: Dumpfile name 'zotz1813408EF9 ' section number 1 mounted on disk file '/bret/rel157.134/ASE-15_0/install/zotz.trn'
    May 14 10:10:16 2018: Backup Server: 6.28.1.1: Dumpfile name 'zotz1813408F08 ' section number 1 mounted on disk file '/bret/rel157.134/ASE-15_0/install/zotz.trn' <--------- This repeated entry shows the file was overwritten.

    If this is what is happening, the problem can be avoided in a couple of ways.
    The best will probably be to add a serialized value to the file name. For instance, you could have a simple table with an identity column. When creating the filename, insert a new row and append the @@identity value to the time value in the name.

    Another option would be to use the "with retaindays" option on the dump which would prevent the file from being immediately overwritten. However, this approach causes the second dump attempt to hang waiting for operator intervention, and the logs could fill while waiting for that intervention.

    If this theory doesn't pan out, let us know and we can keep looking into the issue.


    Cheers,
    -bret


    Add comment
    10|10000 characters needed characters exceeded