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 |-'
>--+--------------------+--+---------------------------+-------->
'-COMPRLIB--lib-name-' '-COMPROPTS--options-string-'
>--+-------------------+--+--------------------------+---------->
'-ENCRLIB--lib-name-' '-ENCROPTS--options-string-'
>--+---------+-------------------------------------------------><
'-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-+--|
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.
- COMPRLIB | ENCRLIB lib-name
- Indicates the name of the library that is used to decompress or
decrypt a backup image. For example, db2compr.dll for Windows; and libdb2compr.so for Linux or UNIX 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
restore 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.
- 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/)
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).