DB2 Version 9.7 for Linux, UNIX, and Windows

RECOVER DATABASE command

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.

Authorization

To recover an existing database, one of the following:

sysadm
sysctrl
sysmaint

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

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


>>-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-'   

>--+---------+-------------------------------------------------><
   '-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.

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.

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. This is different from the local time option from the Control Center, which is local to the client.
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 online archive log files listed in the database configuration parameter logpath are to be applied.

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.

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.

COMPRLIB lib-name

Indicates the name of the library to be used to perform the decompression. The name must be a fully qualified path referring to a file on the server. If this parameter is not specified, DB2® will attempt to use the library stored in the image. If the backup was not compressed, the value of this parameter will be ignored. If the specified library cannot be loaded, the restore operation will fail.

COMPROPTS options-string

Describes a block of binary data that is passed to the initialization routine in the decompression library. The DB2 database system passes this string directly from the client to the server, so any issues of byte reversal or code page conversion are handled by the decompression 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 residing on the server. The DB2 database system will then replace the contents of string with the contents of this file and pass the new value to the initialization routine instead. The maximum length for the string is 1 024 bytes.

RESTART

The RESTART keyword can be used if a prior RECOVER operation was interrupted or otherwise did not complete. Starting in V9.1, a subsequent RECOVER command will attempt to continue the previous RECOVER, if possible. Using the RESTART keyword forces RECOVER to start with a fresh restore and then rollforward to the PIT specified.

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.

Examples

In a single-partition database environment, where the database being recovered currently exists, and the most recent version of the history file is available in the dftdbpath:

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 PIT, issue the following. The most recent image that can be used will be restored, and logs applied until the PIT 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 PIT 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 PIT 
      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 on dftdbpath on all database partitions:

To recover the database to a PIT on all nodes. DB2 will verify that the PIT is reachable on all nodes before starting any restore operations.
```
   RECOVER DB SAMPLE TO  2001-12-31-04.00.00 
```
To recover the database to this PIT on all nodes. DB2 will verify that the PIT is reachable on all nodes before starting any restore operations. The RECOVER operation on each node is identical to a single-partition RECOVER.
```
   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 nodes instead of all nodes, however a PIT RECOVER can not be done in this case, the recover must be done to EOL.
```
   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 PIT 
      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.