Backing up transactions logs

[aspect1958]

My issue is that I am trying to create a backup procedure that would limit my data loss exposure.
Currently a complete backup is performed each morning at 4:30am SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE('BackupDir’,1)

The logs reside in the database under logs.

On the hour I am using system commands to copy the logs with the archive flag set and the logctrl file,  to the BackupDir/logs directory.    On the theory that my exposure to a disk crash is limited to 1 hour.

But when ever I attempt to recover the database with roll forward with “rollForwardRecoveryFrom=’BackupDir'
I get a message like;
 Page Page(9,Container(0, 144)) is at version 2,613,545, the log file contains change version 2,621,080, either there are log records of this page missing, or this page did not get written out to disk properly.
ij version 10.2

Is this a difference between the actual logs and the logctrl?

Is my only other option to move the log file location to ‘BackupDir’
Thanks for any thoughts on this.

[Andrew Lawrenson]

What's worked for me (although anyone else feel free to correct me) is:

1) checkpoint the database by running SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()
2) freeze the database by running SYSCS_UTIL.SYSCS_FREEZE_DATABASE()
3) copy your log files (_all_ files in log dir)
4) unfreeze the database by running SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE()

#1 will flush cached data to disk
#2 will ensure that no write activity is taking place whilst you copy the files, so all should be consistent.

        Andy

-----Original Message-----
From: aspect1958 [mailto:gevans@...]
Sent: 30 June 2008 21:38
To: derby-user@...
Subject: Backing up transactions logs


My issue is that I am trying to create a backup procedure that would limit my data loss exposure.
Currently a complete backup is performed each morning at 4:30am
SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE('BackupDir',1)

The logs reside in the database under logs.

On the hour I am using system commands to copy the logs with the archive
flag set and the logctrl file,  to the BackupDir/logs directory.    On the
theory that my exposure to a disk crash is limited to 1 hour.

But when ever I attempt to recover the database with roll forward with "rollForwardRecoveryFrom='BackupDir'
I get a message like;
 Page Page(9,Container(0, 144)) is at version 2,613,545, the log file contains change version 2,621,080, either there are log records of this page missing, or this page did not get written out to disk properly.
ij version 10.2

Is this a difference between the actual logs and the logctrl?

Is my only other option to move the log file location to 'BackupDir'
Thanks for any thoughts on this.

--
View this message in context: http://www.nabble.com/Backing-up-transactions-logs-tp18203847p18203847.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

[Peter Ondruska-3]

Why not using SYSCS_UTIL.SYSCS_BACKUP_DATABASE? I have been using similar procedure in Cloudscape (derby's predecessor) for years and it creates consistent copy of the database.

On Tue, Jul 8, 2008 at 10:28 AM, Andrew Lawrenson <andrew.lawrenson@...> wrote:

What's worked for me (although anyone else feel free to correct me) is:

1) checkpoint the database by running SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()
2) freeze the database by running SYSCS_UTIL.SYSCS_FREEZE_DATABASE()
3) copy your log files (_all_ files in log dir)
4) unfreeze the database by running SYSCS_UTIL.SYSCS_UNFREEZE_DATABASE()

#1 will flush cached data to disk
#2 will ensure that no write activity is taking place whilst you copy the files, so all should be consistent.

       Andy

-----Original Message-----
From: aspect1958 [mailto:gevans@...]
Sent: 30 June 2008 21:38
To: derby-user@...
Subject: Backing up transactions logs


My issue is that I am trying to create a backup procedure that would limit my data loss exposure.
Currently a complete backup is performed each morning at 4:30am
SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE('BackupDir',1)

The logs reside in the database under logs.

On the hour I am using system commands to copy the logs with the archive
flag set and the logctrl file,  to the BackupDir/logs directory.    On the
theory that my exposure to a disk crash is limited to 1 hour.

But when ever I attempt to recover the database with roll forward with "rollForwardRecoveryFrom='BackupDir'
I get a message like;
 Page Page(9,Container(0, 144)) is at version 2,613,545, the log file contains change version 2,621,080, either there are log records of this page missing, or this page did not get written out to disk properly.
ij version 10.2

Is this a difference between the actual logs and the logctrl?

Is my only other option to move the log file location to 'BackupDir'
Thanks for any thoughts on this.

--
View this message in context: http://www.nabble.com/Backing-up-transactions-logs-tp18203847p18203847.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

[aspect1958]

Thanks for all the help, sorry it took so long.
I wound up moving the logs to another disk on the system so I'm fairly confident i can recover from most non-nuclear diasters.

I could put the freeze the db but I'm not sure what would happen with the transactions for the 5 or 10 minutes the backup would take.

In addition since i'm running an embedded version of derby I would have to stop the application to freeze the db,  I would think redunant.

Well thank you for your advice.

aspect1958
<