Database recovery provide -f to force the application of a log file
Jim Diaz
Recently
I attempted to recover a database in which the only backup is corrupt
and multiple log files need to be applied. Corruption was due to a OS file
error.
I had successfully unloaded and reloaded the corrupt database into a new
database file but when attempting to apply the log files I got the error
"cannot open transaction log xyz.log belongs to a different database"
I understand that under normal circumstances you would never want to do this
but sometimes..........
Thanks
Jim
Jeff Albion [Sybase iAnywhere]
Rebuilding (unloading and reloading) the database essentially "starts
the database from scratch".
The transaction logs (I assume incremental backups or similar) you have
can only be applied to the original database with the matching offsets.
(i.e. apply a transaction log starting at 1245678 to a database that
ends at 1245677). The message is essentially telling you that we're not
sure if the database we're trying to update is in a "good state" to
allow the operations that are being applied - the expectation is that
you won't have to deal with data differences since only valid,
successful recorded operations will be in the transaction log. This may
not be true in a "force application" situation.
The solution is to translate the .log files to .sql files using dbtran,
then apply the SQL to the new database to bring your rebuilt database
up-to-date.
Personally, I'd be hesitant to see a "force transaction log" application
switch in the field, since customers in general may not know whether the
transaction log really belongs to that database or not and could result
in inconsistent data if used improperly. This could also be an even
larger problem in a replication environment.
Regards,
Jim Diaz wrote:
> I attempted to recover a database in which the only backup is corrupt
> and multiple log files need to be applied. Corruption was due to a OS file
> error.
>
> I had successfully unloaded and reloaded the corrupt database into a new
> database file but when attempting to apply the log files I got the error
> "cannot open transaction log xyz.log belongs to a different database"
--
Jeff Albion, Sybase iAnywhere
iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/
Jim Diaz
Thanks for the rapid response. I understand and agree that normally you
would never want to do this. The test database I am referring to in this
message was a consolidated with approx 100 remotes which is why translate
and apply wouldn't work.
If this were a real situation I would have had to re-extract all remotes and
still had the problem of getting in all the data that was being entered at
the remotes before the re-extraction. To me the -f switch is the lesser of
two evils.
There are already many ways an inexperienced user can destroy a database.
Regards,
Jim
"Jeff Albion [Sybase iAnywhere]" <firstname...@ianywhere.com> wrote in
message news:4ae9e6ec@forums-1-dub...
Volker Barth
IMHO, when using a replication system, the situation "in which the only
backup is corrupt" is something that should not happen under *all
circumstances*. A well-tested backup and recovery plan is crucial here
unless one accepts the risk to have to re-extract all remotes.
The docs are really clear in this respect, methinks.
If I sound like a know-it-all: We have had our own problems with SQL
Remote and server crashes (but at least a valid backup) and took that as
a chance to optimize our backup-plan...
AFAIK, if your recovered database is out-of-sync w.r.t. log offsets, it
will be really hard to come out without re-extracting all remotes.
However, translating the logs and re-applying the SQL statements should
at least work to bring the consolidated's data back to its original state.
Just my thoughts
Volker
Jim Diaz
Thanks for your reply.
I agree this should never occur. My wording "the only backup was corrupt"
was incorrect it should have been "the only local backup was corrupt".
The situation I was referring to actually happened in a disaster recovery
scenario, which we perform annually. The local backup stored on a backup
server was validated then stored to tape. At some point the backup server
file became corrupt due to an OS or hardware issue (not sure which). If
this occurred on a live system we could have gone to tape but these are
stored off site and take quite some time to retrieve. This is a government
contract and I believe they store the tapes 300 miles under the surface of
the earth :-).
So the issue here was not the ability to recover but the time to recover.
We have a 72 hour recovery requirement which could have easily been met in
this dry run if we were able to force the database to apply the logs. The
database after the rebuild was in exactly the same state as the corrupt
backup database, so applying these logs would have resulted in a good
recovery with all remotes intact.
Philosophically I don't agree with the software preventing me from
performing an action which I with full knowledge of the possible
ramifications still want to perform. Perhaps the alternative would be to
provide a warning and let me be responsible for the outcome.
Thanks again,
Jim
"Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> wrote in message
news:4aea9dd6$1@forums-1-dub...
Volker Barth
I see your point clearer now - besides the rhetorical question "What's
the purpose of a backup if it's not really available in adequate time?".
Would one of the DBLOG switches (-x /-z) be able to make the log "fit"
the database, as the different log offset information in database and
log seem to be the problem? (I know they are generally aimed to help
unloading consolidated databases.)
Just another thought:)
Volker
Jim Diaz
somehow.
Thanks again
Jim
"Volker Barth" <No_VBarth@Spam_GLOBAL-FINANZ.de> wrote in message
news:4aee992b$1@forums-1-dub...