RECOVER DATABASE command

The RECOVER DATABASE command restores and rolls forward a database to a particular point in time or to the end of the logs.

Important: The Triple Data Encryption Standard (3DES) native encryption option is deprecated and might be removed in a future release. As a replacement, use the Advanced Encryption Standard (AES) native encryption option.

Scope

In a partitioned database environment, this command can only be invoked from the catalog partition. A database recover operation to a specified point in time affects all database partitions that are listed in the db2nodes.cfg file. A database recover operation to the end of logs affects the database partitions that are specified. If no partitions are specified, it affects all database partitions that are listed in the db2nodes.cfg file.

In a Db2® pureScale® environment, the RECOVER DATABASE command can be issued from any member.

Authorization

To recover an existing database, one of the following authorities:

SYSADM
SYSCTRL
SYSMAINT

To recover to a new database, one of the following authorities:

SYSADM
SYSCTRL

Required connection

To recover an existing database, a database connection is required. This command automatically establishes a connection to the specified database and will release the connection when the recover operation finishes. To recover to a new database, an instance attachment and a database connection are required. The instance attachment is required to create the database.

Command syntax

On Database Partition clause

Database Partition List clause

Log Overflow clause

History File clause

Encryption Options

Master Key Options

Command parameters

DATABASE database-alias

The alias of the database that is to be recovered.

TO

isotime

The point in time to which all committed transactions are to be recovered (including the transaction committed precisely at that time, as well as all transactions committed previously). This value is specified as a time stamp, a 7-part character string that identifies a combined date and time. The format is yyyy-mm-dd-hh.mm.ss.nnnnnn (year-month-day-hour-minutes-seconds-microseconds).

A RECOVER operation to a point in time returns a success message only if there is a transaction with a larger timestamp value in the log files. If a larger timestamp is not found, the RECOVER operation fails during the rollforward phase, with error code SQL4970N. The database remains in a rollforward pending state. How you deal with the SQL4970N failure depends on the circumstances:

If a recover operation is not able to reach a specified point in time because Db2 is not able to access some log files, the message is generated. If you are able to locate these log files and make them available to Db2, issue the ROLLFORWARD DATABASE command to the same point in time to complete the database recovery. If the log files are damaged or lost, reissue the RECOVER DATABASE command by using an earlier point-in-time value. The new recover operation runs the restore again, possibly using a different backup image.
If the point-in-time timestamp is correct, and there are no missing log files, then the specified point in time might be beyond any work done against the database. In this case, issue the ROLLFORWARD DATABASE command with the STOP or COMPLETE option to complete the rollforward recovery at the current position in the log files.

This value is specified as a time stamp, a 7-part character string that identifies a combined date and time. The format is yyyy-mm-dd-hh.mm.ss.nnnnnn (year, month, day, hour, minutes, seconds, microseconds). The time stamp in a backup image is based on the local time at which the backup operation started. The CURRENT TIMEZONE special register specifies the difference between UTC and local time at the application server. The difference is represented by a time duration (a decimal number in which the first two digits represent the number of hours, the next two digits represent the number of minutes, and the last two digits represent the number of seconds). Subtracting CURRENT TIMEZONE from a local time converts that local time to UTC.

USING LOCAL TIME

Specifies the point in time to which to recover. This option allows the user to recover to a point in time that is the server's local time rather than UTC time. This is the default option.

Note:

If the user specifies a local time for recovery, all messages returned to the user will also be in local time. All times are converted on the server, and in partitioned database environments, on the catalog database partition.
The timestamp string is converted to UTC on the server, so the time is local to the server's time zone, not the client's. If the client is in one time zone and the server in another, the server's local time should be used.
If the timestamp string is close to the time change of the clock due to daylight saving time, it is important to know if the stop time is before or after the clock change, and specify it correctly.
It is important to specify a valid timestamp when recovering a database. A valid timestamp would be the time that the last backup in the partitioned database system was completed.
When issuing multiple RECOVER DATABASE commands, the timestamp you specify for each subsequent command must be greater than the timestamp you specified in the previous command.

USING UTC TIME

Specifies the point in time to which to recover.

END OF LOGS

Specifies that all committed transactions from all logs in the path specified by logpath, plus logs on the highest-numbered log chain that can be retrieved from the locations specified by the logarchmeth1 and logarchmeth2 database configuration parameters, are to be rolled forward.

ON ALL DBPARTITIONNUMS

Specifies that transactions are to be rolled forward on all database partitions specified in the db2nodes.cfg file. This is the default if a database partition clause is not specified.

EXCEPT

Specifies that transactions are to be rolled forward on all database partitions specified in the db2nodes.cfg file, except those specified in the database partition list.

ON DBPARTITIONNUM | ON DBPARTITIONNUMS

Roll the database forward on a set of database partitions.

db-partition-number1: Specifies a database partition number in the database partition list.
TO db-partition-number2: Specifies the second database partition number, so that all database partitions from db-partition-number1 up to and including db-partition-number2 are included in the database partition list.

USER username

The user name under which the database is to be recovered.

USING password

The password used to authenticate the user name. If the password is omitted, the user is prompted to enter it.

USING HISTORY FILE history-file-path

Path to the history file for the database partition. The path must end with a path separator, such as a slash ("/").

history-file-path ON DBPARTITIONNUM

In a partitioned database environment, specifies a different history file.

OVERFLOW LOG PATH log-directory

Specifies an alternate log path to be searched for archived logs during recovery. Use this parameter if log files were moved to a location other than that specified by the logpath database configuration parameter. In a partitioned database environment, this is the (fully qualified) default overflow log path for all database partitions. A relative overflow log path can be specified for single-partition databases.

The OVERFLOW LOG PATH command parameter will overwrite the value (if any) of the database configuration parameter overflowlogpath.

log-directory ON DBPARTITIONNUM

In a partitioned database environment, allows a different log path to override the default overflow log path for a specific database partition.

PARALLELISM n

Specifies the number of buffer manipulators that are to be created during the restore operation. The Db2 database system will automatically choose an optimal value for this parameter unless you explicitly enter a value.

COMPRLIB | ENCRLIB lib-name

Indicates the name of the library that is used to decompress or decrypt a backup image. The path to the following libraries is $HOME/sqllib/lib. Encryption libraries: libdb2encr.so (for Linux® or UNIX based operating systems); libdb2encr.so.a (for AIX®); and libdb2encr.dll (for Windows operating systems).

Encryption and compression libraries: libdb2compr_encr.so or libdb2zcompr_encr.so (for Linux or UNIX based operating systems); libdb2nx842_encr.a (for AIX); libdb2compr_encr.a or libdb2zcompr_encr.a (for AIX); libdb2compr_encr.dll or libdb2zcompr_encr.dll (for Windows operating systems).

Note: The compression libraries libdb2zcompr_encr.so, libdb2zcompr_encr.a, and libdb2zcompr_encr.dll are available in Db2 11.5.7 and later versions.

The name must be a fully qualified path that refers to a file on the server. If this parameter is not specified, the Db2 database system attempts to use the library that is stored in the image. If the backup image is not compressed or encrypted, the value of this parameter is ignored. If the specified library cannot be loaded, the recovery operation fails.

COMPROPTS | ENCROPTS options-string

Describes a block of binary data that is passed to the initialization routine in the decompression or decryption library. The Db2 database system passes this string directly from the client to the server. Any byte reversal or code page conversion issues are handled by the library. If the first character of the data block is @, the remainder of the data is interpreted by the Db2 database system as the name of a file that is found on the server. The Db2 database system then replaces the contents of options-string with the contents of this file and passes the new value to the initialization routine instead. The maximum length for the string is 1024 bytes.

For the default Db2 libraries libdb2compr_encr.so (compression and encryption), libdbzcompr_encr.so (ZLIB-based compression and encryption), libdb2nx842_encr.a (NX842 compression and encryption) or libdb2encr.so (encryption only), the format of the ENCROPTS string is as follows:

Master Key Label=label-name

Note: The libdb2zcompr_encr.so library is available in Db2 11.5.7 and later versions.

The master key label is optional. If no master key label is specified, the database manager looks in the keystore for a master key label that was used to create the backup image. If you are using other libraries, the format of the ENCROPTS string depends on those libraries.

NO ENCRYPT

Specifies that an encrypted database is to be recovered into a non-encrypted new or existing database.

ENCRYPT

Specifies that the recovered database is to be encrypted. Encryption includes all system, user, and temporary table spaces, indexes, and all transaction log data. All data types within those table spaces are encrypted, including long field data, LOBs, and XML data.

CIPHER

Specifies the encryption algorithm that is to be used for encrypting the database. You can choose one of the following FIPS 140-2 approved options:

AES: Advanced Encryption Standard (AES) algorithm. This is the default.
3DES: Triple Data Encryption Standard (3DES) algorithm

MODE CBC: Specifies the encryption algorithm mode that is to be used for encrypting the database. CBC (Cipher Block Chaining) is the default mode.

KEY LENGTH key-length

Specifies the length of the key that is to be used for encrypting the database. The length can be one of the following values, specified in bits:

128: Available with AES only
168: Available with 3DES only
192: Available with AES only
256: Available with AES only

MASTER KEY LABEL

Specifies a label for the master key that is used to protect the key that is used to encrypt the database. The encryption algorithm that is used for encrypting with the master key is always AES. If the master key is automatically generated by the Db2 data server, it is always a 256-bit key.

label-name: Uniquely identifies the master key within the keystore that is identified by the value of the keystore_type database manager configuration parameter. The maximum length of label-name is 255 bytes.

RESTART

Use the RESTART keyword when a recover operation is either interrupted or does not complete. When the RESTART keyword is omitted, a subsequent RECOVER DATABASE command attempts to continue the previous recover operation, if possible. Using the RESTART keyword forces the recover operation to start with a fresh restore and roll forward to the point in time specified.

Examples

The following examples apply to a single-partition database environment or a Db2 pureScale environment, where the database being recovered currently exists, and the most recent version of the history file is available on the default database path specified in the database manager configuration file (dftdbpath parameter):

To use the latest backup image and rollforward to the end of logs using all default values:
```
   RECOVER DB SAMPLE
```
To recover the database to a point in time, issue the following. The most recent image that can be used will be restored, and logs applied until the point in time is reached.
```
   RECOVER DB SAMPLE TO  2001-12-31-04.00.00
```
To recover the database using a saved version of the history file, issue the following. For example, if the user needs to recover to an extremely old point in time which is no longer contained in the current history file, the user will have to provide a version of the history file from this time period. If the user has saved a history file from this time period, this version can be used to drive the recover.
```
   RECOVER DB SAMPLE TO  1999-12-31-04.00.00 
      USING HISTORY FILE (/home/user/old1999files/)
```

In a single-partition database environment, where the database being recovered does not exist, you must use the USING HISTORY FILE clause to point to a history file.

If you have not made any backups of the history file, so that the only version available is the copy in the backup image, the recommendation is to issue a RESTORE followed by a ROLLFORWARD. However, to use RECOVER, you would first have to extract the history file from the image to some location, for example /home/user/fromimage/, and then issue this command. (This version of the history file does not contain any information about log files that are required for rollforward, so this history file is not useful for RECOVER.)
```
   RECOVER DB SAMPLE TO END OF LOGS
      USING HISTORY FILE (/home/user/fromimage/)
```
If you have been making periodic or frequent backup copies of the history, the USING HISTORY FILE clause should be used to point to this version of the history file. If the file is /home/user/myfiles/, issue the command:
```
   RECOVER DB SAMPLE TO 2001-12-31-04.00.00  
      USING HISTORY FILE (/home/user/myfiles/)
```
(In this case, you can use any copy of the history file, not necessarily the latest, as long as it contains a backup taken before the point-in-time (PIT) requested.)

In a partitioned database environment, where the database exists on all database partitions, and the latest history file is available in the dftdbpath on all database partitions:

To recover the database to a point in time on all database partitions. Db2 database systems will verify that the PIT is reachable on all database partitions before starting any restore operations.
```
   RECOVER DB SAMPLE TO  2001-12-31-04.00.00 
```
To recover the database to this point in time on all database partitions. Db2 database systems will verify that the specified point in time is reachable on all database partitions before starting any restore operations. The recover operation on each database partition is identical to a single-partition recovery
```
   RECOVER DB SAMPLE TO END OF LOGS 
```
Even though the most recent version of the history file is in the dftdbpath, you might want to use several specific history files. Unless otherwise specified, each database partition will use the history file found locally at /home/user/oldfiles/. The exceptions are nodes 2 and 4. Node 2 will use: /home/user/node2files/, and node 4 will use: /home/user/node4files/.
```
   RECOVER DB SAMPLE TO  1999-12-31-04.00.00 
      USING HISTORY FILE (/home/user/oldfiles/,
         /home/user/node2files/ ON DBPARTITIONNUM 2,
         /home/user/node4files/ ON DBPARTITIONNUM 4)
```
It is possible to recover a subset of database partitions instead of all database partitions, however a point-in-time recover operation cannot be done in this case; the recover must be done to the end of logs.
```
   RECOVER DB SAMPLE TO END OF LOGS ON DBPARTITIONNUMS(2 TO 4, 7, 9)
```

In a partitioned database environment, where the database does not exist:

If you have not made any backups of the history file, so that the only version available is the copy in the backup image, the recommendation is to issue a RESTORE followed by a ROLLFORWARD. However, to use RECOVER, you would first have to extract the history file from the image to some location, for example, /home/user/fromimage/, and then issue this command. (This version of the history file does not contain any information about log files that are required for rollforward, so this history file is not useful for the recover.)
```
   RECOVER DB SAMPLE TO 2001-12-31-04.00.00  
      USING HISTORY FILE (/home/user/fromimage/)
```
If you have been making periodic or frequent backup copies of the history, the USING HISTORY FILE clause should be used to point to this version of the history file. If the file is /home/user/myfiles/, you can issue the following command:
```
   RECOVER DB SAMPLE TO END OF LOGS
      USING HISTORY FILE (/home/user/myfiles/)
```

The following examples show how to specify encryption options.

Recover into a new encrypted database named CCARDS by using the default encryption options:
```
RECOVER DATABASE ccards ENCRYPT;
```

Recover into the same database by using explicitly provided encryption options to decrypt the backup image:

RECOVER DATABASE ccards
  ENCRLIB 'libdb2encr.dll'
  ENCROPTS 'Master key Label=mylabel.mydb.myinstance.myserver';

Usage notes

By default, when a recover operation fails, a subsequent RECOVER DATABASE command attempts to continue the failed recover operation:
- The command skips the restore portion of the operation if the previous recover attempt failed during the rollforward portion.
- The command redoes the restore portion of the operation if the previous recover attempt failed during the restore portion.
- The command redoes the restore portion of the operation if the RESTART keyword is specified.
Recovering a database might require a load recovery using tape devices. If prompted for another tape, the user can respond with one of the following:

c

Continue. Continue using the device that generated the warning message (for example, when a new tape has been mounted).

d

Device terminate. Stop using the device that generated the warning message (for example, when there are no more tapes).

t

Terminate. Terminate all devices.
If there is a failure during the restore portion of the recover operation, you can reissue the RECOVER DATABASE command. If the restore operation was successful, but there was an error during the rollforward operation, you can issue a ROLLFORWARD DATABASE command, since it is not necessary (and it is time-consuming) to redo the entire recover operation.
In a partitioned database environment, if there is an error during the restore portion of the recover operation, it is possible that it is only an error on a single database partition. Instead of reissuing the RECOVER DATABASE command, which restores the database on all database partitions, it is more efficient to issue a RESTORE DATABASE command for the database partition that failed, followed by a ROLLFORWARD DATABASE command.
In a Db2 pureScale environment, you must specify database partition 0 if you use the RECOVER DATABASE command with the ON DBPARTITIONNUMS clause or with the ON DBPARTITIONNUM clause (either as part of the database partition list clause or the log overflow clause).

Db2 native encryption

When you recover to an existing database, the encryption settings of the existing database are always preserved. If you specify the ENCRYPT option, an error is returned because the settings on theRECOVER command will not be used.

When you recover into a new database in a partitioned database environment, recover the catalog partition first, specifying the encryption options. You can then recover the other partitions without specifying the encryption options, because the database already exists. When you use the db2_all command, target the catalog partitions first.

A backup image that was encrypted with Db2 native encryption must be recovered into a database server that has Db2 native encryption available. If you want to recover into a server that is using a Db2 version that does not include Db2 native encryption, you must use an unencrypted backup image.