Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of dagroenewald@optusnet.com.au
 designates 211.29.132.185 as permitted sender)
Mime-Version: 1.0 (Apple Message framework v752.3)
In-Reply-To: <47AC4C0C.3000706@sun.com>
References: <49D46C7E-7AA1-4FC3-A318-74B90712C6CC@optusnet.com.au>
 <47AB1DF6.4090105@sun.com>
 <B852DAE8-1A94-418C-A2F1-945A3AE127BD@optusnet.com.au>
 <x7hcgjq3ey.fsf@ugle.localdomain>
 <F0FA19F9-A384-4A90-BB55-CC3620B2F707@optusnet.com.au>
 <47AC4C0C.3000706@sun.com>
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <646A6E43-B23F-4356-BDB1-DEF684FE2555@optusnet.com.au>
Content-Transfer-Encoding: quoted-printable
From: Duncan Groenewald <dagroenewald@optusnet.com.au>
Subject: Re: Derby Transaction Log Shipping
Date: Thu, 14 Feb 2008 10:59:10 +1100
To: "Derby Discussion" <derby-user@db.apache.org>

Thanks for that explanation - it pretty much confirms what I expected =20=

- which was that booting the secondary means that things get changed =20
and that logs can no longer be loaded.

Duncan

On 08/02/2008, at 11:33 PM, J=F8rgen L=F8land wrote:

> Hi Duncan,
>
> First of all, the scenario you describe seems (to me) to be solved =20
> by the new replication functionality. However, I think it can be =20
> done the hard way with a plan similar to what you describe. Here =20
> goes :)
>
> Log files can be found in <database_dir>/log. When you enable log =20
> archive mode, the log files will not be deleted. Hence, you do not =20
> need to perform backup on day 2 and 3 - you may simply copy the log =20=

> files from the <database_dir>/log directory.
>
> So, ideally, the steps would be like this:
>
> Day 1: make a backup, copy it to the secondary location. Boot the =20
> secondary db and check that it is all ok
> Day 2: copy the log files generated since the backup was made
> Day 3: copy the log files generated since the backup was made
> Day 4: boot secondary db, which now is in the same state as the =20
> primary was in when the log was copied on day 3.
>
> With a few modifications, this should work just fine:
>
> Problem, day 1: Assuming that users are allowed access to the =20
> primary database when you make the first backup (as indicated by =20
> your scenario), the data pages and log files will contain =20
> information from uncommitted transactions. When you boot the =20
> secondary to check that everything is ok, Derby will go through the =20=

> same steps as when doing crash recovery. That means going through a =20=

> redo phase (redoing operations in the log that are not reflected in =20=

> the data pages) and an undo phase (basically abort transactions =20
> that were active at the time the backup made). The undo phase is =20
> key here because Derby do operations on the data pages of the =20
> secondary that were not done on the primary. This is fine if you =20
> want to use the secondary, but not if you want to keep sending it =20
> log files.
>
> Solution: Don't allow any active transactions when you make the =20
> initial backup or (probably better in your scenario) don't boot the =20=

> secondary database to check if it is ok. Wait until the primary has =20=

> failed before booting it.
>
> Problem, day 2 and 3: The log file with highest number copied on =20
> day 1 (say logN.dat) may have been modified since you copied it.
>
> Solution: Overwrite the secondary log file logN.dat with logN.dat =20
> from the primary database.
>
> I think that should do it, but if you do not require this NOW, I =20
> would rather wait for replication in 10.4.
>
> Good luck,
> J=F8rgen
>
>
> Duncan Groenewald wrote:
>> I still don't know if I really understand the Derby model as it =20
>> seems the transaction logs are archived when a database backup is =20
>> run.  So here is a scenario:
>> Day 1:  Backup Primary Derby (enabling logging), copy backup =20
>> database to secondary server and boot secondary server to check it =20=

>> is all OK.
>> Day 2:  Backup Primary Derby DB and copy archived log files to =20
>> secondary server.
>> Day 3:  Backup Primary Derby DB and copy new archived log files to =20=

>> secondary server.
>> Day 4:  Boot secondary Derby DB to check its OK...  In theory then =20=

>> the boot process will replay all the log files and the database =20
>> should be in the same state as the Primary was on Day 3 ?
>> Somehow I don't think this would actually work - but I will give =20
>> it a try...
>> Here is the scenario I am try to cater for:
>> 24x7 realtime system needs to be relocated to another site (or =20
>> needs to have a warm standby system that can be enabled in 15 =20
>> minutes or less).
>> Basic approach is to have two databases running and logs from the =20
>> primary are loaded on the secondary within a couple of minutes of =20
>> them being written.
>> Transaction dumps on primary database are written to timestamped =20
>> files and file is renamed  TRXDUMP20080206091545212_DONE.DAT once =20
>> dump write process has completed.  A script checks for presence of =20=

>> *_DONE.DAT files every 30 seconds and copies file to remote =20
>> servers file system (or this gets done by the dump process as =20
>> well).  Script on the remote server checks for presence of =20
>> *_DONE.DAT files every 30 seconds and runs a Transaction Load =20
>> process on remote database to load the dump files.  At any given =20
>> point in time the remote site is always within a few minutes of =20
>> the primary site.
>> It seems unlikely one could do this with Derby because there are =20
>> no commands to periodically dump the transaction logs or to load =20
>> the transaction logs.
>> Cheers
>> On 08/02/2008, at 7:05 PM, Knut Anders Hatlen wrote:
>>> Duncan Groenewald <dagroenewald@optusnet.com.au> writes:
>>>
>>>> Thanks - the specification looks like its close to what I would =20
>>>> like.
>>>> The model I work from is one used by Sybase (and possibly  others)
>>>> where you can specify a database dump and a separate  =20
>>>> transaction log
>>>> dump at defined intervals using a script or some  other =20
>>>> programmatic
>>>> method.  =46rom what I can tell its not possible to  do this with =20=

>>>> Derby,
>>>> since you can only dump the database and not the  logs.  Its also
>>>> unclear how you would load a log file on its own.
>>>>
>>>> What I would like to see is two additional commands added to dump
>>>> transaction logs to specified directory or file name and another
>>>> command to load a transaction log file from a specified location/
>>>> file.  Ideally a transaction log file load should function much the
>>>> same way a normal user does to allow concurrent user access while
>>>> loading a transaction log file.
>>>
>>> Not exactly what you want (it won't allow concurrent user access =20
>>> while
>>> loading the transaction log), but you may achieve something =20
>>> similar with
>>> log archiving and roll-forward recovery, combined with some creative
>>> scripts. I haven't tried it myself, but you may get some ideas here:
>>> http://db.apache.org/derby/docs/dev/adminguide/=20
>>> cadminrollforward.html
>>>
>>> --=20
>>> Knut Anders
>> Duncan Groenewald
>> mobile: +61406291205
>> email: dagroenewald@optusnet.com.au
>
>
> --=20
> J=F8rgen L=F8land

Duncan Groenewald
mobile: +61406291205
email: dagroenewald@optusnet.com.au