Restores and rolls forward a database to a particular point
in time or to the end of the logs.
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:
To recover to a new database, one of the following authorities:
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
>>-RECOVER--+-DATABASE-+--source-database-alias----------------->
'-DB-------'
>--+-------------------------------------------------------------------+-->
| .-USING LOCAL TIME-. |
'-TO--+-isotime--+------------------+--+------------------------+-+-'
| '-USING UTC TIME---' '-ON ALL DBPARTITIONNUMS-' |
'-END OF LOGS--+----------------------------------+---------'
'-| On Database Partition clause |-'
>--+-------------------------------------+---------------------->
'-USER--username--+-----------------+-'
'-USING--password-'
>--+-----------------------------------------------------------------------------+-->
'-USING HISTORY FILE--(--history-file-path--+----------------------------+--)-'
'-,--| History File clause |-'
>--+------------------------------------------------------------------------+-->
'-OVERFLOW LOG PATH--(--log-directory--+----------------------------+--)-'
'-,--| Log Overflow clause |-'
>--+----------------+--+----------------+----------------------->
'-PARALLELISM--n-' +-COMPRLIB--name-+
'-ENCRLIB--name--'
>--+-------------------+---------------------------------------->
+-COMPROPTS--string-+
'-ENCROPTS--string--'
>--+-----------------------------------------------------------------+-->
+-NO ENCRYPT------------------------------------------------------+
'-ENCRYPT--+------------------------+--+------------------------+-'
'-| Encryption Options |-' '-| Master Key Options |-'
>--+---------+-------------------------------------------------><
'-RESTART-'
On Database Partition clause
|--ON--+-| Database Partition List clause |----------------------------------+--|
'-ALL DBPARTITIONNUMS--+--------------------------------------------+-'
'-EXCEPT--| Database Partition List clause |-'
Database Partition List clause
.-,--------------------------------------------------.
V |
|--+-DBPARTITIONNUM--+--(----db-partition-number1--+--------------------------+-+--)--|
'-DBPARTITIONNUMS-' '-TO--db-partition-number2-'
Log Overflow clause
.-,------------------------------------------------------.
V |
|----log-directory--ON DBPARTITIONNUM--db-partition-number1-+---|
History File clause
.-,----------------------------------------------------------.
V |
|----history-file-path--ON DBPARTITIONNUM--db-partition-number1-+--|
Encryption Options
.-MODE--CBC-.
|--CIPHER--+-AES--+--+-----------+--KEY LENGTH--key-length------|
'-3DES-'
Master Key Options
|--MASTER KEY LABEL--label-name---------------------------------|
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). 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. Even if there is no transaction with a larger timestamp, you
can still issue a rollforward operation with the COMPLETE option.
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 (for Linux or UNIX based operating systems); libdb2compr_encr.a (for AIX); and libdb2compr_encr.dll (for Windows operating systems). 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) or libdb2encr.so (encryption
only), the format of the ENCROPTS string is
as follows:Master Key Label=label-name
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
- The RESTART keyword can be used if a prior
recover operation was interrupted or otherwise did not complete.
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 then
rollforward 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
- 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.