Link to home
Start Free TrialLog in
Avatar of Nosfedra

asked on

Recover Database from Lost System


I have a peculiar issue with restoring ASE databases from one environment to another.
The original system running SUN-OS which produced the DB dump is gone and all I have are the database backups and a wind2008 server with a Sybase License.

Everything is fine up until I reach the transaction log.
As I was fearing, the database was live at the time of the backup and there are transaction logs attached to the backup which fail to be loaded into the windows environment:
Adaptive Server cannot load this database because the database that was dumped
was not quiescent when the dump was performed. Run sp_flushstats before DUMP
DATABASE and ensure that the database is not updated during the dump.

Open in new window

Needless to say, I don't have the luxury of running sp_flushstats...

This database is active mostly during daytime and the backups were running at night.
I am extremely happy to give up the few commits affecting 0,00000000000001% of the data in order to salvage the other 99,99999999999% of the data on that database.

I need to find a way to bypass the transaction log conversion.

I would be startled to find out that there is no provision for such situation.  

Your help is greatly appreciated.

Avatar of Joe Woodhouse
Joe Woodhouse

Sybase take their transaction logs pretty seriously!

It isn't a matter of giving up "a few commits" - that would affect all transactions that came after it. It's not like there's a one-to-one relationship between "one log" and "one row of data" necessarily either.

Short answer: I'm not aware of any way to skip recovery during a cross-platform conversion.

I suggest instead you try restoring it in a Sun environment. Do you really have no Suns left at all? You may need to look into virtualisation or a short term hire. Or you can pick up old Suns pretty cheap on Ebay!

Another approach might be to not do a load at all. Can you bring the dump file(s) online as an archive database? I honestly don't know if that works cross-platform but it's worth having a go.
Avatar of Nosfedra


Thanks for your comments.
No question about the seriousness of the transaction logs.

I would have expected that once the backup starts, the entire database is being "frozen" and actual transactions on the database run into a temporary layer of the DB.

But if only portions of the database are being "frozen" at the time of the backup - namely, those sections that are currently being backed up - then indeed, ignoring the transaction log will lead to inconsistencies in the result and I am not surprised Sybase refuses to give those up.

The documentation I can find thus far indicates that you can't bring dumps as archive databases unless the dumps were created on the same platform.

And no... I don't have a SUN available at the moment. Buying one is an option but induces a lot of delays in my recovery project.

Very very briefly a standard Sybase ASE dump has three phases.

Phase 1: Write out all database pages. Transactions continue as normal during this phase, including minimally-logged transactions. Keep track of all these. This is where most of the time is taken.
Phase 2: Pause all minimally logged transactions. Write out all database pages changed by minimally logged transactions.
Phase 3: Do a transaction dump. At the end of this, write a dump marker - this is where the database will be restored to.

So the same database page can in fact be written out many times during a dump. Transactions are continuing and (ordinary logged ones) are frozen only at the end of the entire process. The log is pretty important for the integrity of the dump.

I *think* (don't know for sure) a quiet dump is needed for cross-platform dump & load because it's already complicated with possible datatype conversions, Endian conversions, character set conversions, etc etc.

You will probably find it faster and cheaper to buy the appropriate VM software and virtualise a Sun environment than to provision Sun hardware. Although it seems VMWare only support the x86 versions of Solaris as a guest O/S - I assume your original was Sun SPARC?

If you and your organisation are willing to let the data out of your hands under appropriate non-disclosure, you may find someone here controls (rather than just has access to) a Sun environment and will handle the intermediate restore for you. Sadly I'm not in a position to offer this myself at the moment even if it were OK at your end, but out of interest, how large a database are we talking?
Thanks for the insight, I do appreciate it and it does clear some of the questions I had.

The source system is a SPARC environment indeed :-|

Mm, back in the days when I worked for Sybase, a sad client had a database dump that had been truncated by the old 2Gb file size limit. They were sure only 5% of the dump was missing, and wanted us to "do your internal-only surgery on it to make it work". They seemed sure this was something we could wave a magic wand at and it would be fixed overnight. I finally got it through to them that this would be like removing a random 5% of a jet engine and taking it to a mechanic "to make the remaining 95% work". I think I ended up quoting 3 months' with no guarantee of any success. In the end they hired a bunch of data entry temps and rekeyed 10 weeks' transactions!

VMware would've been a great answer but sadly Sun SPARC to Sun x86 requires an Endian conversion and the dumps aren't compatible without using XPDL... which as we've seen will fail.

I will continue to think more on this over the next day or so but right now all I've got is "buy or rent a Sun SPARC box". (I checked - Amazon's EC2 virtual machines don't do Solaris SPARC.)

FWIW I just confirmed through looking through the Sybase site for support cases that there is no traceflag to force XPDL to load if the source wasn't quiet. :(
Great analogy with the Jet Engine :D... And I bet you werent' referring to the homonym database engine :D!

I am a programmer myself so your explanation is pretty clear to me.
Thank you for still considering this. I am actually slowly losing hope.

What I still fail to understand is why is it that it is that difficult to work on the transaction logs when all the rest of the data was successfully ported. I presume sybase would use an internal representation that's independent of the endianess and the transaction logs are thus dependent?
Avatar of Joe Woodhouse
Joe Woodhouse

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Have you tried "dbcc dbrepair( dbid, ltmignore)"? I think it is not documented, but if I don't remember details wrong, it fixes transaction problem for XPDL as well. I have used it many times.
Alpmoon - can you please elaborate? Sounds intriguing, to say the least. II could not find dbcc utility on the distribution I have but I could find a "installdbccdb" script which I briefly went through.
In any case, doesn't that tool require the DB to be already loaded?

Thank you.
Hi alpmoon. I too am very very curious to know more because if this works it can save all of us a lot of trouble - this problem can occur even when all the rules are followed for producing a dump of a quiet database for XPDL! :)

I would've thought this would only touch a secondary truncation point rather than the main one - for instance you can still run a dump tran after running this, so it isn't doing anything to the actual main log marker. My memory tells me it also can only be run on a database that's online, which means it has to have completed recovery post-load...?

Still, this would be utter magic if it works. Tell us more!!!! :)
To be honest, I have used it more than one and half years ago. I don't remember exactly in which situations it works. It doesn't fix the problem in every case. However, you can definitely use it for an offline database. I don't think that a secondary truncation point prevents to make database online. So it should be fixing the actual transaction log issue.

You don't need to install anything. Just run the command:

dbcc dbrepair( <your_db_id>, "ltmignore")

You can use database name as well.
Thank you for getting back to this.
However, I do not see how this would work for a database dump as I can't even load that as an archive database, given the fact that is was created on a different system...

Or actually CAN you load, cross-platform a database as an archive DB? I thought that's impossible!
If it is a really active database, I don't think that it would fix the problem. But, as I said earlier, in some cases it fixes very similar problems you have. You can just try to see what happens. It doesn't take more than a few seconds and if you can't still make database online, it means it doesn't work in your situation.

You don't loose anything by trying it.
I was hoping to bring the database into an archive environment but to no avail. Unless I am doing something wrong, I don't think there is a way to mount the dumps (which are, by the way, compressed) into an archive database.
And I don't have too many options, at least from what I can tell, in loading the DB dumps other than creating the archive DB and doing a striped load on it.
I get blessed with 'Invalid dump header...' or 'unable to determine the size of the device..." etc... error messages.

I am pretty close to throw in the towel...
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for your assistance...
I greatly appreciate your diligence. Funny enough, the solution appears to be really your last entry. I am going to keep trying and if I ever get through I will share my experience, here, if possible.

Your educated guesswork is also a great insight into how things work with sybase so I am splitting the points between the two answers :)

Happy New Year!