RSS

DB2: Offline and Online Full Backup, Incremental Backup, and Recovery Operations

DB2 Offline and Online Full Backup, Incremental Backup, and Recovery Operations
Last Update:

A quick reference the procedures for performing offline and online full backups, incremental backups, and database recovery in DB2.

Offline Full Backup

  1. First, ensure that no users are using DB2:
db2 list applications for db sample
  1. Stop the database and restart it to disconnect all connections:
db2stop force
db2start
  1. Execute the backup command (using TSM as the backup medium):
db2 backup db sample use tsm

A successful backup will return a timestamp.

  1. Check the backup success:
db2 list history backup all for sample

You should see the record for this backup. The db2adutl query command can also show the return value.

  1. Note: First execute the backup command on the primary node (the node where the catalog tablespace is located), then perform this operation on other nodes.

Online Backup

  1. First, enable the database configuration parameters that support online backup:
db2 update db cfg for sample using userexit on  -- Enable user exit
db2 update db cfg for sample using logretain on  -- Enable archive logging
db2 update db cfg for sample using trackmod on   -- Enable incremental backup feature

(These settings need to be applied on each node separately.)

After enabling these parameters, the database will be in a backup pending state, requiring an offline full backup. Perform an offline full backup using the commands mentioned above.

  1. The online backup command is as follows:
db2 backup db sample online use tsm

A successful backup will return a timestamp.

  1. You can also use db2adutl and db2 list history to check the backup records.

  2. Note: Perform this operation on each node as well.

Online Incremental Backup

  1. With the necessary three parameters enabled, perform the incremental backup:
db2 backup db sample online incremental use tsm

A successful backup will return a timestamp.

  1. You can also use db2adutl and db2 list history to check the backup records.

  2. There is also a type of delta backup:

db2 backup db sample online incremental delta use tsm

The difference between these two types of backups is similar to Oracle Exports’ Incremental and Cumulative methods; DB2’s incremental corresponds to Oracle’s cumulative method, while DB2’s delta corresponds to Oracle’s incremental method.

  1. Note: Perform this operation on each node as well.

Database Recovery

  1. Manually drop the database to simulate a disaster recovery situation, and execute the following operation:
db2 drop db sample
  1. Restore the backup history records (every backup, regardless of type, will back up the history record file). The timestamp here should be the most recent:
db2 restore db sample history file use tsm taken at 20030102223107 buffer 100
  1. Use DB2’s recovery assistance tool:
db2ckrst -d sample -t 20030101224424 -r database

The command will return suggested necessary recovery operation commands.

  1. According to the prompts from the assistance tool, first perform version recovery with the following command:
db2 restore db sample incremental use tsm taken at 20030101224424 buffer 100

Similarly, perform the recovery on the primary node first, then on other nodes.

  1. At this point, the database is in a rollforward-pending state, requiring a rollforward operation:
db2 rollforward db sample to 2003-01-12-13.27.25.000000 on all nodes and stop

Roll forward to the same timestamp. This operation should be performed on the primary node.

Additional Notes:

  1. Recovery operations can also be online or offline, similar to backup operations.

  2. Recovery at the tablespace level is similar to backup and restore; simply add the clause TABLESPACE (tablespace-name). Tablespace-level backup/recovery operations require the database to be in archive log mode and with incremental backup enabled.

  3. In the recovery example, only version recovery was performed. If there are newer full backups and incremental backups available, you can perform recovery in sequence (note the recommended recovery order and frequency using db2ckrst) before proceeding with roll forward.

OmniLock - Block / Hide App on iOS

Block distractive apps from appearing on the Home Screen and App Library, enhance your focus and reduce screen time.

DNS Firewall for iOS and Mac OS

Encrypted your DNS to protect your privacy and firewall to block phishing, malicious domains, block ads in all browsers and apps

Ad