SQL Anywhere database replication - Is there a trick I can use to pull this off?

Author

Is there a trick I can use to pull this off?

Carol Stone

2005-11-03, 8:25 pm

In version 7. We're using Sybase Adaptive Server Anywhere Database
Engine Version 7.0.4.3362.

Bad news: we have a remote database which is missing a log.

Good news: The only thing in that log was a receive.

I'd like to know if there's a way to get the remote database to send
everything it hasn't sent to the consolidated and just skip this file,
since I'm going to stop the publication after I recover all my user's work.

I'm guessing that I need to get the remote database to send to the end
of the last log in sequence and then somehow convince it that the new
offset it needs is the beginning of the next log we have and tell it to go.

I wouldn't be trying to do it this way if I weren't extremely sure about
the contents of that lost log file.

Is there a way (in ASA 7.04) to trick/convince the database to do what
I'm asking? Or am I just going to have to translate the logs and script
a load of the data (first in a test environment and then live ... I've
learned *that* lesson the hard way, too.)

All my thanks,
-carol stone

Reg Domaratzki $iAnywhere Solutions$

2005-11-03, 8:25 pm

I will not tell you the trick directly, because it is too dangerous IMHO to
be posted on a public forum. I will tell you how to figure it out on your
own. If you can figure it out on your own, then I'm a little bit more
comfortable that you know in which situations it would be safe to use the
trick.

Find any replicating database and run dbunload -n to generate a reload.sql
file with all the schema for your database. Look in the section entitled
"SQL Remote Definitions" and locate a very interesting stored procedure
call. This stored procedure (if called with the proper parameters) will
allow you to skip over a section of the transaction log, as long as you
execute it properly at both the remote and the consolidated. DO NOT DO THIS
WITHOUT TESTING FIRST. DO NOT RUN THESE COMMANDS FOR THE FIRST TIME ON YOUR
PRODUCTION SYSTEM. YES, I'M YELLING!

That's the best you'll get from me, but it should be enough to help.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Carol Stone" <""carol.stone \"@ ctcommunitycare . org"> wrote in message
news:436a551d@forums
-2-dub...
> In version 7. We're using Sybase Adaptive Server Anywhere Database
> Engine Version 7.0.4.3362.
>
> Bad news: we have a remote database which is missing a log.
>
> Good news: The only thing in that log was a receive.
>
> I'd like to know if there's a way to get the remote database to send
> everything it hasn't sent to the consolidated and just skip this file,
> since I'm going to stop the publication after I recover all my user's
work.
>
> I'm guessing that I need to get the remote database to send to the end
> of the last log in sequence and then somehow convince it that the new
> offset it needs is the beginning of the next log we have and tell it to
go.
>
> I wouldn't be trying to do it this way if I weren't extremely sure about
> the contents of that lost log file.
>
> Is there a way (in ASA 7.04) to trick/convince the database to do what
> I'm asking? Or am I just going to have to translate the logs and script
> a load of the data (first in a test environment and then live ... I've
> learned *that* lesson the hard way, too.)
>
> All my thanks,
> -carol stone

Carol Stone

2005-11-03, 8:25 pm

Hmmm,

This stored procedure doesn't appear to be documented in the help files.
I'm going to have to search the web :)

Now, I think I probably am *not* going to use this for this purpose -
simply because I am feeling much too cautious. But would I be wrong in
surmmising that this same stored procedure may be the necessary
mysterious step to make unloading and reloading the consolidated in a
replication environment actually work? Or is that something else? (The
help file tells you to find out what the offsets are, but doesn't seem
to tell you what to do with that information later.)

-carol

Reg Domaratzki $iAnywhere Solutions$

2005-11-04, 9:23 am

> This stored procedure doesn't appear to be documented in the help files.
> I'm going to have to search the web :)

I'm sure it's on the web somewhere, but everything you should need to figure
it out is on the machine where SQL Anywhere is installed.

> Now, I think I probably am *not* going to use this for this purpose -
> simply because I am feeling much too cautious.

Thank you. It is much better to err on the side of caution with your
production system.

> But would I be wrong in
> surmising that this same stored procedure may be the necessary
> mysterious step to make unloading and reloading the consolidated in a
> replication environment actually work?

You are correct. This same stored procedure is used to make unloading and
reloading the consolidated ( or a remote ) in a replication environment
actually work.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Carol Stone" <""carol.stone \"@ ctcommunitycare . org"> wrote in message
news:436a820f$1@foru
ms-1-dub...
> Hmmm,
>
> This stored procedure doesn't appear to be documented in the help files.
> I'm going to have to search the web :)
>
> Now, I think I probably am *not* going to use this for this purpose -
> simply because I am feeling much too cautious. But would I be wrong in
> surmmising that this same stored procedure may be the necessary
> mysterious step to make unloading and reloading the consolidated in a
> replication environment actually work? Or is that something else? (The
> help file tells you to find out what the offsets are, but doesn't seem
> to tell you what to do with that information later.)
>
> -carol

Sponsored Links