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: neutral (athena.apache.org: local policy)
From: "Bergquist, Brett" <BBergquist@canoga.com>
To: Derby Discussion <derby-user@db.apache.org>
Date: Thu, 29 Mar 2012 08:25:01 -0700
Subject: RE: Can someone explain the use of logged archive backup?
Thread-Topic: Can someone explain the use of logged archive backup?
Thread-Index: Ac0NuNuGAyCcbIhoSWO0372c+WK2LgABL3rQ
Message-ID: <97EB699F861AD841B5908C7CA9C9565602065E174AD9@VSERVER1.canoga.com>
References: <97EB699F861AD841B5908C7CA9C9565602065E174AB1@VSERVER1.canoga.com>
 <4F74720A.7000104@gmail.com>
In-Reply-To: <4F74720A.7000104@gmail.com>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Thanks Bryan, but I am a little dense on this one and just want to understa=
nd the proper procedure to use "SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE=
_LOG_ARCHIVE_MODE" so that I can either consider it or not. =20

On one system that uses ZFS, we will probably use the SYS_UTIL.SYSCS_FREEZE=
_DATABASE to freeze, perform an ZFS snapshot, and SYS_UTIL.SYSCS_FREEZE_DAT=
ABASE to unfreeze the database.   I am concerned of a failure in the code b=
etween freezing the database and unfreezing the database as I believe if th=
e connection is lost, the database will remain frozen with no way to unfree=
ze.  I need to test that and maybe patch Derby to perform an unfreeze if th=
e connection is lost. I have had this happen when I used a script to call I=
J to do the freeze, the shell to do the ZFS snapshot, and IJ again to unfre=
eze.  The last IJ to unfreeze could not get a connection and could not unfr=
eeze.  And the database was locked up and could not even be shutdown cleanl=
y.

Another system is an older system that is running UFS so this will not be p=
ossible.

Back to "SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE".  So=
 the procedure would be:

	- use this procedure to create a backup of the database and enable log arc=
hiving.  Presumably the database would be created on some other media.
	- if the log directory has not been moved with the from "<database>/log", =
what does one do with this?  Periodically copy the file in there to the oth=
er media?  How does one determine the "archive" logs from the "active" logs=
 used for crash recovery or is even necessary.
	- time goes on and another full backup is done.  Does one use SYSCS_UTIL.S=
YSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE again?  I assume that the =
new archive logs need to be kept with this backup now, right?
	- I guess I confused by the archive logs and where they are located, what =
they look like, and what to do with them

Now there is a disk crash that contains the database, a new disk is put in =
place, formatted, etc. and is available to the OS.  So the procedure to rec=
over this is to issue a connect like:

	- connect 'jdbc:derby:<database>;rollForwardRecoveryFrom=3D<path to last f=
ull backup with logs>';

Is that correct?  Is that any different than physically copying the databas=
e and all of the logs to the new disk simply connecting like:

	- connect 'jdbc:derby:<database>'


Any light you can shed on this will be helpful.  I think the Administration=
 guide should go through this.  I t starts to, but the part about handling =
the "archive logs" is not really presented.


-----Original Message-----
From: Bryan Pendleton [mailto:bpendleton.derby@gmail.com]=20
Sent: Thursday, March 29, 2012 10:31 AM
To: Derby Discussion
Subject: Re: Can someone explain the use of logged archive backup?

> confused here on the archived logs and the active logs.

In general, there can be multiple logs covering the time between one backup=
 and the next backup, and those logs must be applied, serially, in the corr=
ect order, to recover the database fully.

Once you take that "next" backup, you no longer need the previous backup/lo=
gs, though it's probably wise to establish a reasonable holding period depe=
nding on your resources (e.g., backup weekly, keep
3 months of backups and logs, destroy the oldest set when you complete the =
most recent backup, etc.)

> I need to protect against a media crash and it is not so important to go =
back to specific periods of time for the database.

Perhaps you can entirely use lower-level mechanisms, then, such as RAID or =
other redundant storage hardware, or a modern filesystem which automaticall=
y replicates the underlying data against the failure of the storage, such a=
s ZFS (http://en.wikipedia.org/wiki/ZFS)

I think it's still wise to have an application-level backup strategy, becau=
se sometimes logical recovery is necessary (e.g., to recover from an applic=
ation bug or an administrative mistake), so I think that the exercise you'r=
e going through about documenting your backup and recovery strategies is an=
 excellent one.

And don't forget to test those backup/restore practices, since an untested =
restore is no better than no restore at all.

I've found that one useful technique is to provision a secondary machine, w=
hich can be MUCH smaller in terms of CPU, memory, networking, etc., and jus=
t has to have enough disk space, and automate things so that every time I t=
ake a backup, my scripts automatically copy the backup to this spare machin=
e, restore the backup and apply all the logs, and then run a few queries to=
 satisfy myself that the database is correctly recovered.

thanks,

bryan