DB2 database backup and recovery

The so-called data backup is to retain a backup system, when the operating system fails, the original data can be restored with the minimum time. Database Backup can be a copy of the entire database, or a copy of one or more tablespaces of some of the data ). Data backup generally has two levels:
Hardware-level backup: Redundant hardware is used to ensure the continuous operation of the system, such as dual-host fault tolerance and hard disk image. If the primary hardware is damaged, the backup hardware can take over immediately.
Software-level backup: Save System data to other removable media, such as tapes, floppy disks, and optical disks. When the system fails, the system can be restored to the backup state. This article focuses on software-level backup of databases.

1. DB2 database backup

The DB2 database can back up the entire database or one or more tablespaces. It can be used to back up data when the application is connected to the database and when the transaction is being processed. This is called online backup ), you can also back up data when no application is connected to the database. This is called offline backup ). (See Table 1)
The DB2 database uses the BACKUP command to back up data. The BACKUP Command requires the permissions of SYSADM, SYSCTRL, or SYSMAINT. Its syntax is: backup database alias TO media name
Or
Backup database alias TABLESPACE name [ONLINE} TO media name

The media name is generally specified as the absolute path of the media, the name of the drive device that is not rotated in AIX is/dev/rmt0, and the name of the drive device that is rotated is/dev/rmt0.n. You can also back up the data on the hard disk. The media name is the absolute path. The BACKUP command generates one or more BACKUP files on the specified media. The following shows the file name structure generated on AIX 4.3 for RS/6000:
The following are examples of the BACKUP command:
(1) In this example, a complete backup is created for the mydb database under the specified server directory/backup/mydb:
Backup database mydb TO/backup/mydb
(2) In this example, the tablespace myspace1 specified in the mydb database is backed up online. During the backup, the application can continue to update the database:
Backup database mydb TABLESPACE myspace1 online to/backup/mydb

Table 1: Impact of database Forward Recovery

If forward recovery is not allowed	If forward recovery is allowed
After a failure occurs, you can restore the database to the State consistent with the last transaction before the failure.	Likewise, it supports restoring to the same state as the last transaction.
You can restore the database to any valid backup status, but you cannot re-execute the transaction that occurred after the backup.	You can restore the database to any valid backup status, and re-execute the transactions committed since the occurrence of the failure.
Backup can be performed only when no application is connected to the database. This is called offline backup ).	Backup can be performed when the application is connected to the database and when the transaction is being processed. This is called online backup ). Offline backup is also supported.
Each backup must contain the current status of the entire database.	A backup may contain the current status of the entire database, or one or more tablespaces. The tablespace of the database can be backed up and restored separately.

2. DB2 database recovery

DB2 database provides three types of recovery:
1. crash recovery): crash recovery is used to restore the database to a transaction consistent State immediately after a failure, all changes in this status take effect only after the transaction is committed. Run the RESTART command for crash recovery.
2. Backup recovery restore recovery): Backup recovery allows you to restore the database content from the previous backup. Run the RESTORE command for Backup recovery.
3. forward recovery): After a database is recovered from the backup, you can re-execute operations that have changed the database after the backup. In this case, the database can be recovered to any time break between the backup and the current time. Run the ROLLFORWORD command for forward recovery.
Crash recovery and Backup recovery are applicable to any database, and forward recovery is only available when the database is specified. Table 1 shows the impact of this function.

Table 1: Impact of database Forward Recovery

If forward recovery is not allowed	If forward recovery is allowed
After a failure occurs, you can restore the database to the State consistent with the last transaction before the failure.	Likewise, it supports restoring to the same state as the last transaction.
You can restore the database to any valid backup status, but you cannot re-execute the transaction that occurred after the backup.	You can restore the database to any valid backup status, and re-execute the transactions committed since the occurrence of the failure.
Backup can be performed only when no application is connected to the database. This is called offline backup ).	Backup can be performed when the application is connected to the database and when the transaction is being processed. This is called online backup ). Offline backup is also supported.
Each backup must contain the current status of the entire database.	A backup may contain the current status of the entire database, or one or more tablespaces. The tablespace of the database can be backed up and restored separately.

You can set the Database Configuration Parameter LOGRETAIN or USEREXIT to YES for the forward recovery to take effect. Logs are constantly added because new files are generated, and they are unrestricted. In this case, you must take some measures to save the old log file to the document to prevent the file system from being filled with logs.
1. RESTART command
This command is the first command to be issued after a power failure or software crash occurs when some operations are still being processed. It establishes a database connection and uses logs to restore the database to the transaction consistency state. All Database changes caused by the commit operation take effect before the failure occurs. All the operations that were undone before the failure and the changes that were being processed to the database during the failure were revoked.
The following is an example of the RESTART command:
Restart database mydb
2. RESTORE command
This command uses the content stored in the backup to restore the database content. The RESTORE command can create a new database. The RESTORE Command requires the SYSADM, SYSCTRL, or SYSMAINT privilege. The backup to be used by the RESTORE command may contain the entire database or a group of tablespaces. The restoration of the entire database must be performed offline. The recovery of tablespaces can be performed online, that is, when an application is connected to the database. The RESTORE command syntax is:
Restore db target database alias [TABLESPACE name] [FROM media name] [taken at time]
The following is an example of a RESTORE command:
1) recover the mydb database from a backup in a specific directory/backup/mydb. If mydb cannot be recovered, the database will be restored to the backup state and can be used immediately. If forward recovery can be performed, the database will be placed in the pending state of forward suspension until it is executed before the Forward Recovery command can be used.
Restore database mydb FROM/backup/mydb
2) the following command restores the database mydb and does not forward the database if it is specified externally. The database can be used immediately.
Restore database mydb FROM/backup/mydb WITHOUT ROLLING FORWARD
3) The following example restores a specified tablespace of the mydb database from a specified directory/backup/mydb. The Restoration uses the specified date and time:
Restore database mydb tablespace online from/backup/mydb taken at 20000926201226
3. ROLLFORWARD command
The ROLLFORWARD command is executed after a RESTORE command is executed to RESTORE the database or its tablespace forward. The ROLLFORWARD Command requires the SYSADM, SYSCTRL, or SYSMAINT privilege, and the database must be able to recover from the forward.
The ROLLFORWARD command can be used for the entire database or one or more tablespaces, whose statuses are pending forward. If you restore the entire database forward, this process must be offline and the database can be recovered to any time period between the backup time and the current time. If you only restore the independent tablespace forward, this process can be both online or offline, but you must restore the tablespace forward to the current time, re-execute all commit operations that cause changes to ensure that the restored tablespace is consistent with other parts of the database. The ROLLFORWARD command syntax is:
Rollforward database name [TO {time, end of logs}] [TABLESPACE {ONLINE, TABLESPACE name}]
The following is an example of some ROLLFORWARD commands:
1) In this example, the mydb database is restored to the current time. The statement and stop prevents the database from being set to rollforward pending and can be accessed:
Rollforward database mydb TO END OF LOGS AND STOP
2) The following example restores the mydb database and re-executes all transactions committed before the specified date and time:
Rollforward database mydb TO 2000-09-26-10.3059 AND STOP
3) The following example only restores a tablespace in the database mydb to the forward direction. This command is issued after a tablespace-level recovery is executed. It is only applicable to the restored tablespace. This command indicates that forward recovery is performed online and ends at the end of the current time log ):
Rollforward database mydb TO END OF LOGS AND STOP TABLESPACE ONLINE
NOTE: If some columns in the database are of the LOB data type and they are created with the not logged option, the update operations on these columns are NOT recorded in the log, forward recovery does not operate on these columns. If a column without logs is encountered during forward recovery, their values are set to hexadecimal 0.