Roll-forward recovery

Derby supports roll-forward recovery to restore a damaged database to the most recent state before a failure occurred.

Derby restores a database from full backup and replays all the transactions after the backup. All the log files after a backup are required to replay the transactions after the backup. By default, the database keeps only logs that are required for crash recovery. For roll-forward recovery to be successful, all log files must be archived after a backup. Log files can be archived using the backup function calls that enable log archiving.

In roll-forward recovery, the log archival mode ensures that all old log files are available. The log files are available only from the time that the log archival mode is enabled.

Derby uses the following information to restore the database:

The backup copy of the database
The set of archived logs
The current online active log

You cannot use roll-forward recovery to restore individual tables. Roll-forward recovery recovers the entire database.

To restore a database by using roll-forward recovery, you must already have a backup copy of the database, all the archived logs since the backup was created, and the active log files. All the log files should be in the database log directory.

There are two types of log files in Derby: active logs and online archived logs.

Active logs: Active logs are used during crash recovery to prevent a failure that might leave a database in an inconsistent state. Roll-forward recovery can also use the active logs to recover to the end of the log files. Active logs are located in the database log path directory.
Online archived logs: Log files that are stored for roll-forward recovery use when they are no longer needed for crash recovery. Online archived logs are also kept in the database log path directory.

Enabling log archival mode

Online archive logs are available only if the database is enabled for log archival mode. You can use the following system procedure to enable the database for log archival mode:

SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE
(IN BACKUPDIR VARCHAR(32672), IN SMALLINT DELETE_ARCHIVED_LOG_FILES)

The input parameters for this procedure specify the location where the backup should be stored and specify whether or not the database should keep online archived logs for the backup. Existing online archived log files that were created before this backup will be deleted if the input parameter value for the DELETE_ARCHIVED_LOG_FILES parameter is non-zero. The log files are deleted only after a successful backup.

Note: Make sure to store the backup database in a safe place when you choose the log file removal option.

The SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE procedure will issue an error if there are any unlogged operations in the same transaction as the backup procedure.

If any unlogged operations are in progress in other transactions in the system when the backup starts, this procedure will block until those transactions are complete before performing the backup. Derby automatically converts unlogged operations to logged mode if they are started while the backup is in progress (except operations that maintain application jar files in the database). Procedures to install, replace, and remove jar files in a database are blocked while the backup is in progress.

If you do not want backup to block until unlogged operations in other transactions are complete, use the SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT procedure. This procedure issues an error immediately at the start of the backup if there are any transactions in progress with unlogged operations, instead of waiting for those transactions to complete.

Disabling log archival mode

After you enable log archival mode, the database will always have the log archival mode enabled even if it is subsequently booted or backed up. The only way to disable the log archive mode is to run the following procedure:

SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE
(IN SMALLINT DELETE_ARCHIVED_LOG_FILES)

This system procedure disables the log archive mode and deletes any existing online archived log files if the input parameter DELETE_ARCHIVED_LOG_FILES is non-zero.

Performing roll-forward recovery

If you have a backup made by using SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE or SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE_NOWAIT, you can restore it to its most recent state by using the full backup copy, archived logs, and active logs. You perform a roll-forward recovery by specifying the connection URL attribute rollForwardRecoveryFrom=path at boot time. All the log files should be in the database log path directory.

The steps involved are as follows. They do not show the commands to start ij.

Back up the database with log archive mode enabled.

For example, you could back up a database named wombat to the /backup directory as follows. After many operations, the database crashes.

ij> connect 'jdbc:derby:wombat;create=true';
ij> create table t1(a int not null primary key);
0 rows inserted/updated/deleted
------------------DML/DDL Operations
ij> CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE
('/backup', 0);
0 rows inserted/updated/deleted
ij> insert into t1 values(19);
1 row inserted/updated/deleted
ij> create table t2(a int);
0 rows inserted/updated/deleted
-----------------DML/DDL Operations
-----------------Database Crashed (Media Corruption on data disks)

Prepare the database for restoration.
Before you restore the database, shut down the original database and rename the original database directory. For example, after shutdown, you could issue the following commands in a Linux shell:
```
mv /databases/wombat /databases/brokenwombat 
cd /databases
```
Restore the database using roll-forward recovery.
Since you moved the database, you need to specify the logDevice=logDirectoryPath attribute in addition to the rollForwardRecoveryFrom=path attribute when you restore the database using roll-forward recovery. Use commands like the following (the connection URL must be all on one line):
```
ij> connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=/backup/wombat;
logDevice=/databases/brokenwombat';
ij> select * from t1;
A          
-----------
19         

1 row selected
---------------DML/DDL Operations
```
After a database is restored from full backup, transactions from the online archived logs and active logs are replayed. This brings the database to its most recent state. All the log files should be in the directory specified by the logDevice=logDirectoryPath attribute.

For more information, see "rollForwardRecoveryFrom=path attribute" and "logDevice=logDirectoryPath attribute" in the Derby Reference Manual.