Section 18.2. The backup, restore, rollforward, and recover Commands

18.2. The backup, restore, rollforward, and recover Commands

In order to be able to recover your DB2 database after a serious failure, you are going to need to have a solid backup and recovery plan. This plan is built around the backup and restore commands, which then enable you to do different types of recoveries.

18.2.1. The backup Command

The DB2 backup command (as its name suggests) backs up a database (or tablespace) to one or more devices or directories on the DB2 server machine.

To initiate the backup command, you must have the sysadm, sysctrl, or sysmaint database user authority.

If you enabled archive logging for the database you are trying to back up, you can perform an online backup (that is, you can back up the database when applications are connected to it). With archive logging, you also don't have to back up the whole database. Rather, the backup process can be performed at the tablespace level. If you want to keep your enterprise database highly available, and you cannot afford large scheduled maintenance windows that render a database unreachable by consuming applications, you should consider using online backups.

When performing the backup command, you can specify the following:

• The database alias of the database you want to back up (required).

• The name of the devices or directories on which the backup files will be created. If no name is explicitly specified, the backup operation writes the backup image to the current working directory of the client computer.

• The name of the tablespaces you want to be backed up (available only if you have enabled archive logging).

• The username and password to use to perform the backup operation.

• Whether to perform the backup online or offline. Offline is the default mode of backup. Online backup is allowed only if you have enabled archive logging. It is important to note that at least one full offline database must have been performed after the logretain option was enabled for a database before an online database backup can take place.

Some useful commands in your DBA toolbox include the list applications and force applications all commands. Respectively, these commands let you know what applications are connected to your database and allow you to kick off all the connected applications.

• Whether you want the backup to be either incremental or delta (see the section "Backup levels" later in this chapter).

• A parallelism value that allows you to specify the number of tablespaces the DB2 backup utility should read in parallel when performing the backup. If no value is specified, DB2 automatically provides one for you.

• Whether you want DB2 to compress data during the backup, saving space on the backup device.

Here's an example of a backup command:

C:> db2 backup db sample userid db2admin using password to c:\backup

This command creates a full database backup of the database named sample to a directory named C:\backup. The database user db2admin with a password of password performs the backup command. As stated earlier, it is assumed that db2admin has the sysadm, sysctrl, or sysmaint database user authority. This backup command is performed offline (the default mode of operation). You can change it to an online backup by adding the word online just after the password phrase. (You must have archive logging enabled to do this.)

Here's another example:

C:> db2 backup db sample user db2admin using password tablespace(userspace1)  online to c:\backup

This command backs up only the userspace1 tablespace of the sample database, showcasing the ability to back up at the tablespace level. This is highly useful when a database has a subset of tablespaces that change more frequently than the rest. It is important to note that you cannot back up temporary tablespaces using this command.

It is a good practice to back up related tablespaces together. Examples of such related tablespaces are tablespaces containing tables that have referential constraints between the tables. Another example of related tablespaces involves a typical topology in which the indexes of a table and LOBs are housed in different tablespaces from the main table data.

Going back to our example, the tablespace backup is performed online, so it is assumed that archive logging has been enabled for the sample database.

After issuing such a backup command, you should receive a message similar to the following, telling you that the backup operation was successful:

Backup successful. The timestamp for this backup image is: 20061119181253.

18.2.1.1. Backup levels

When running the backup command, you can opt for a full, incremental, or delta backup. A full backup, as its name implies, contains all of the data of the database or tablespace being backed up. An incremental backup (also called a cumulative backup) makes a copy of all database data that has changed since the most recent successful full backup. On the other hand, a delta backup copies all database data that has changed since the most recent successful backup (full or other). Incremental and delta backups are therefore reliant on previous backup images and cannot be used by themselves to restore a database. This reliance on other, previously created, backup images makes it very important to save all the backup images needed to perform a database recovery.

18.2.1.2. Backup path and filenaming convention

DB2 automatically determines the pathname and filename for backup files. The generation of this name is far from random; it follows a strict naming convention. Dissecting the name of a database backup path and file can let you know a lot about the DB2 backup that created the backup file.

The following information is contained in the path and/or name of a backup file:

Database alias

The alias for the database.

Type of backup

0 for full database backup, 3 for tablespace(s) backup, 4 for a copy of rows loaded by the load utility.

UDB instance

The name of the UDB instance.

Database node number

For a single-partitioned database, NODE0000.

Catalog node number

For a single-partitioned database, CATN0000.

Timestamp of the backup

yyyy represents the year, mm represents the month (01 to 12), dd represents the day of the month (01 to 31), hh represents the hour (00 to 23), mm represents the minutes (00 to 59), and ss represents the seconds (00 to 59).

Sequence number

A three-digit number used as a file extension.

In Windows, this naming convention is represented by a pathname and a filename. In Unix, the naming convention is represented only by the filename. On Windows, a four-level subdirectory tree houses the backup file:

DB_alias.Type\Inst_name\NODEnnnn\CATNnnnn\yyyymmdd\hhmmss.Seq_num 

A backup filename in Windows might be:

SAMPLE.0\DB2INST\NODE0000\CATN0000\20060227\145655.001

For Linux and Unix, rather than dealing with a four-level subdirectory tree, all the information is built into the filename itself:

DB_alias.Type.Inst_name.NODEnnnn.CATNnnnn.timestamp.Seq_num 

Using the same parameters as the Windows example, our Linux or Unix filename example would be:

SAMPLE.0.DB2INST.NODE0000.CATN0000.20060227.145655.001

Don't Ignore Partial Backups

The backup software we were using at a U.K. insurance company reported a number of partial backups every night. (A partial backup is one in which one or more of the files were not backed up.) Since there were so many occurring on a nightly basis, they were not being investigated by the administrators. A business-critical DB2 database was performing full dumps to disk, and then incremental backups were being performed against those full dumps. Unfortunately, it was the nightly incremental backups of DB2 that were failing, which would have prevented us from recovering a business unit in a disaster situation. Checks were put in place to reduce the number of partial backups and investigate any that did occur. We dodged a bullet.

Hywel Matthews

18.2.1.3. Discovering the history of your backup operations

Backup, restore, and rollforward operations performed on a database are logged in a recovery history file named db2rhist.asc. Each database has its own recovery history file, located in the same directory as the database.

The list history command shows the backup, restore, and rollforward operations performed on a database. For example, to learn which backup and restore operations have been performed on the sample database, you can use this command:

C:> db2 list history backup all for db sample

The report generated by issuing this command contains a symbol indicating the operation performed (B for backup, R for restore). Here is a sample of the output from the db2 list history command:

                    List History File for sample Number of matching file entries = 1  Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID  -- --- ------------------ ---- --- ------------ ------------ --------------   B  D  20060409190953001   F    D  S0000000.LOG S0000000.LOG  ----------------------------------------------------------------------------

If the operation was a backup, a symbol indicates the type of backup:

F  Offline Backup N  Online Backup I - Incremental Offline Backup O - Incremental Online Backup D - Delta Offline Backup E - Delta Online Backup

The backup report also tells you which tablespaces were backed up as well as the location of the backup image. This information can come in quite handy when trying to recover your database.

To learn which rollforward operations have been performed on the sample database, use this command:

C:> db2 list history rollforward all for db sample

The report generated by issuing this command contains an F, indicating that a rollforward operation was performed. The type column of the report indicates whether the rollforward was performed to the end of the logs (indicated by an E) or to a point in time (indicated by a P).

18.2.1.4. Automatic maintenance

DB2 has components with autonomic features and automated backup utilities. These components include database configuration parameters, the health monitor and its indicators, and a number of GUI interfaces (including the Health Center, the Web Health Center, the Task Center, and the Configure Automatic Maintenance wizard). If you are a power user, you can skip the GUIs and configure automatic backup using the appropriate command-line processor (CLP) commands. See the IBM DB2 Universal Database System Monitor Guide and Reference for detailed instructions. This section provides a high-level overview.

The DB2 health monitor runs on the database server. The health monitor provides the functionality required for automatic database backup and maintenance. The health monitor gathers information about the health of the system using health indicators. Using these indicators does not impose a performance penalty. They are not the same thing as snapshot monitor switches.

Health indicators exist at the instance, database, tablespace, and tablespace container levels. The DBA configures health indicators using the Health Center, the Web Health Center, the CLP, or APIs. DB2 monitors these indicators and can take actions identified by the DBA. These actions can include:

• Alerting the DBA of potential system problems via email or pager

• Executing a preconfigured action, such as increasing tablespace size or adding containers

• Logging alerts in the administration notification log

Health indicators fall into four categories:

Upperbounded threshold-based

This indicator type represents a statistic or percentage. For example, if you want to proactively monitor tablespace utilization, you can set the warning value to 70 and alarm value to 90 for the ts.ts_util indicator. This type of indicator has three valid states: normal, warning, and alarm.

Lowerbounded threshold-based

This indicator type also represents a statistic or percentage. For example, if you want to measure how much memory allocated for sorting is really being used, you can configure DB2 to notify you when the db.max_sort_shrmem_util indicator drops below 20 percent.

State-based

This indicator type represents a finite set of two or more distinct states of an object. One of the states is normal. All other states are considered abnormal, requiring attention. db.db_backup_req, for example, is the state-based indicator used to automate database backups.

Collection state-based

Theseindicators are database-level measurements that represent an aggregate state for one or more objects within the database. Collection state-based health indicators also have two valid states: normal and attention. The db.tb_reorg_req (reorganization required) and db.tb_runstats_req (statistics collection required) indicators are examples of this type of indicator.

The db.db_backup_req health indicator is a state-based, database-level indicator. This indicator determines when a database backup is required based on either the time elapsed or the amount of data changed since the last backup. The auto_db_backup database configuration parameter must be set to on for automatic backups to occur. Automatic database backups can be either offline (cold) or online (hot).

The db.tb_reorg_req and db.tb_runstats_req indicators are set after a database is restored. Here is the recommended procedure for restoring a DB2 database:

1. Restore the database.

2. Reorg the database tables and indexes.

3. Collect statistics (runstats) concerning the tables and indexes.

Following this procedure helps enhance the performance of your applications. Reorg and runstats can be run at any time in offline (cold) or online (hot) mode. They do not require a database restore operation. However, you should include these steps in your database restore or recovery operating procedures.

A backup policy specifies automated maintenance behavior to ensure that the database is backed up regularly. The backup policy for a database is created automatically when the DB2 Health Monitor first runs. You can still perform manual backup operations when automatic maintenance is configured. DB2 performs automatic backup operations only if they are required.

You define the time periods, or windows, for online and offline maintenance with the DB2 Health Center. DB2 then determines the need to perform a backup operation based on one or more of the following criteria:

• A full database backup has not been performed.

• The time elapsed since the last full backup is more than a specified number of hours.

• The transaction log space consumed since the last backup is more than a specified number of 4 KB pages (in archive logging mode only).

You configure the requested time or number of log pages between backups, the backup media, and the backup type (online or offline) using the Configure Automatic Maintenance wizard in the Control Center or Health Center. Automatic database backup can be enabled for either online (hot) or offline (cold) backup if the database is enabled for rollforward recovery (archive logging); otherwise, only offline backup is available. Automatic database backup supports disk, tape, Tivoli® Storage Manager (TSM), and vendor DLL media types.

If you select Backup to Disk, the automatic backup feature regularly deletes backup images from the directory specified in the Configure Automatic Maintenance wizard. Only the most recent backup image is guaranteed to be available at any given time. This directory should be kept exclusively for the automatic backup feature and not be used to store other backup images.

Offline backup, restore, reorg, and runstats restrict access to the database, as does online reorg and, to a certain extent, online restore. Offline database backups and table and index reorganization are run in the offline maintenance time period that you define. These features run to completion even if they go beyond the time period specified. DB2's internal scheduling mechanism learns over time and estimates job completion times. If the offline time period you define is too small for a particular database backup or reorganization activity, the scheduler does not start the job the next time around. Instead, the health monitor notifies you if you need to increase the offline-maintenance time period.

You can set up automated backup, reorg, and runstats using the DB2 Health Center:

1. Start the Health Center GUI:

2. 1. On Linux and Unix systems, log in as the DB2 instance, open a terminal session, and enter the command db2hc.

   2. On Windows systems, click StartProgramsIBM DB2Monitoring ToolsHealth Center.

3. Click Configure Automatic Maintenance. The Configure Automatic Maintenance wizard appears. Six steps are listed in the left frame. Click Next twice to reach the Timing screen.

4. Set the time and duration for online and offline maintenance windows. Each maintenance window type has its own Change button. Click one of the buttons to set the values for the start time, duration, and days of the week for this maintenance window type. Click Next when you are finished.

5. Set the notification email address. This page also provides troubleshooting assistance for notification problems.

6. Click Next to reach the Activities page, where you configure the settings for backup, reorg, and runstats. Highlight one of the maintenance activities, click the Automate checkbox, then click the Configure Settings button. There you will find the Backup Database (BACKUP) window that has three tabs: Backup criteria, Backup location, and Backup mode. The default mode is offline. Click Next to reach the final screen of the wizard, Summary. Click Finish to create and activate the automatic maintenance policy for the database.

The Health Center now shows two health indicators: Database backup required and Update statistics required. You should verify that automatic maintenance parameter settings for the database are enabled, substituting db_name with the name of the database for which you have created the automatic maintenance policy:

• On Unix or Linux, switch to the terminal session and enter this command:

• % db2 get db cfg for                                                            db_name                                                         | grep i auto_                         

  
  

• On Windows, open the DB2 command window on Windows by selecting StartProgramsIBM DB2Command Line ToolsCommand Window. Enter the following command:

• C:> db2 get db cfg for                                                            db_name                                                         | find /I "auto_"                         

  
  

If the database parameters are set to off, you can enable them with the following commands, which work on either platform (shown here on Windows):

C:> db2 connect to  db_name  C:> db2 update db cfg using auto_maint on  C:> db2 update db cfg using auto_db_backup on  C:> db2 update db cfg using auto_tbl_maint on  C:> db2 update db cfg using auto_runstats on  C:> db2 update db cfg using auto_reorg on  C:> db2 connect reset 

To reset the Database Backup Required Indicator, use this command:

C:> db2 backup db db_name

You have now automated the backup and maintenance of your database. Refer to the IBM DB2 Universal Database System Monitor Guide and Reference for information on setting other health indicators.

18.2.1.5. Using db2look

In addition to performing a backup, it's a good idea to run the db2look command against your database and save that output. db2look will display the statements necessary to reproduce the database objects of a database. The command below would create such an output for the sample database, and send it to path/file.

db2look -d sample -a -m -l -x -xd -f -o path/file

The output stored in path/file can come in very handy when recovering a DB2 database. It's a good idea to run this as soon as you create a database, or any time you make any structural changes to the database. Then place the output of this command someplace where you can easily retrieve it during a recovery operation.

18.2.2. Recovery Types

Before explaining the commands designed to recover a DB2 database, it's important to understand the different types of recoveries. DB2 UDB identifies the following three types of recoveries:

• Crash recovery

• Version recovery

• Rollforward recovery

18.2.2.1. Crash recovery

Crash recovery ensures that the database is brought to a consistent state after a software failure or power outage. Fortunately, crash recovery in DB2 is quite simple, and nothing special has to be done to a database to prepare it for crash recovery.

If you would rather have the database wait for you after a crash, you can turn off crash recovery by setting the autorestart database configuration parameter to off. If you change this setting, you must run the restart command to start the database after a crash:

% db2 restart db sample

The restart command effectively establishes a database connection. The database logs are then used to restore the database to a transaction-consistent state. Consequently, database changes made by committed transactions before the failure are made effective. On the other hand, rolled back transactions are rolled back in the database as are transactions that were in flight during the failure. At the end of the crash recovery, the DB2 database is restored to a transaction-consistent state.

18.2.2.2. Version recovery

If a database is damaged beyond the point where crash recovery can repair it (for example, the loss of a container), it must be restored from backup. Since this restores the database to a previous version, this is referred to as version recovery.

18.2.2.3. Rollforward recovery

A version recovery restores the database to the state it was in at the time the database backup was performed. Changes made to the database after a backup was taken are lost unless rollforward recovery is enabled prior to the failure and a rollforward command is issued after the restore (see the section "Performing a Rollforward Recovery" later in this chapter). Accordingly, in practice it's common to see a restore command used in tandem with a rollforward command.

DB2 8.2 introduced the recover command, which automatically performs a version recovery followed by a rollforward recovery.

18.2.3. The restore Command

A restore can be performed for a full database or a tablespace. It is important to note that a database restore must be performed offline whereas a tablespace restore (of any tablespace other than the one containing the system catalog tables) can be performed either online or offline. As mentioned earlier, an online tablespace restore is more desirable for enterprises wanting to minimize (if not eliminate) a database's downtime during the recovery of a database.

To execute the restore command, you must have sysadm, sysctrl, or sysmaint database authority if you intend to restore to an existing database. If you plan on doing a redirected restore (covered later in this chapter), you need either sysadm or sysctrl authority.

The following information can be specified with the restore command:

• The name of the database backup that is being used as the source for the restore (required).

• The devices or directories where the database backup(s) are stored.

• If multiple backups are located on the database backup device or in the directory where the database backup(s) are stored, a timestamp can narrow down which backup to use for the restore operation. If you recall, the timestamp is part of the naming convention of the database backup.

• Whether the restore should be directed to a different database than the one from which it was backed up. This is a concept known as redirected restore, which is covered later in this chapter.

By default, if archive logging was enabled for a database, when you issue the restore command, the database is left in a rollforward pending state. The database or tablespace in a rollforward pending state cannot be used until it is brought out of this state by applying the rollforward command, as described later in this chapter. The rollforward command can account for the transactions that were committed after the backup occurred.

A restore command can be issued with the clause without rolling forward. Doing so makes a database usable immediately after the restore command executes. However, remember that all the transactions committed after the database backup occurred are unaccounted for.

18.2.4. The rollforward Command

Once a database has been restored with the restore command, it can be rolled forward with the db2 rollforward command. The command allows you to roll forward to the last transaction log found or to a particular point in time prior to the last transaction.

To execute the rollforward command, you must have sysadm, sysctrl, or sysmaint database authority if you intend to restore to an existing database. If you plan on doing a redirected restore (covered later in this chapter), you need either sysadm or sysctrl authority.

18.2.5. The recover Command

DB2 version 8.2 introduced the recover command. This command combines the restore and rollforward commands into a single command; thus it has some options from both. It has a few caveats, though:

• It can be used only to both restore and roll forward of an entire database.

• It cannot perform just the restore or just the rollforward. The without rolling forward option is not available. If you want to restore without rolling forward, you should use the restore command.

• It does not support tablespace-level recoveries.

• It does not support incremental recoveries.

• The buffer, dlreport, without datalink, parallelism, and without prompting options to the restore command are not available in the recover command.

• You do not tell it which backups to use. You tell it what time you want to recover the database to, and it automatically determines which backup is prior to that time. (Remember, it must restore from a backup that is earlier than the point in time you've chosen, and then rollforward to that point in time.)

To execute the recover command, you must have sysadm, sysctrl, or sysmaint database authority if you intend to restore to an existing database. If you plan on doing a redirected restore (covered later in this chapter), you need either sysadm or sysctrl authority.

To recover a database using the recover command, use this command:

C:> db2 recover db DATABASE_NAME_OR_ALIAS

This automatically uses the best available backup image as determined from the recovery history file (described in the next section) and recovers the database to the last transaction recorded in the transaction log.

To recover to a particular point in time rather than to the end of logs, you can use this syntax:

recover DB DATABASE_NAME_OR_ALIAS TO POINT_IN_TIME

To recover the database sample to the point in time 2006-04-10-00.16.52, use the following command:

C:> db2 recover db sample to 2006-04-10-00.16.52

The point in time is specified in local time, not UTC time. With the recover commands shown so far, the point in time must be contained in the current history file. If you need to restore to a point in time not contained in the current history file, explicitly point to an external history file. For example, to point to a history file archived in the directory /home/user/archives/, use this command:

C:> db2 recover db sample to 2006-04-10-00.16.52 using history  file (/home/user/archives/db2rhist.asc)