Point-in-time recovery

Backing out work to a point-in-time

The RECOVER utility can recover your data to a point in time by backing out committed work from the current state of the data. To recover data by backing out, specify BACKOUT YES on the RECOVER control statement.

In some circumstances, recovering to a point in time by backing out work can be faster than recovering to a point in time by restoring a copy of the data and applying the logs forward.

When the RECOVER utility performs a point-in-time recovery by backing out committed work, the recovery is a point-in-time recovery with consistency, because any work that was uncommitted at the point in time to which the data is being recovered is also backed out. When the recovery is complete, the data is left in a transaction consistent state.

Before running the RECOVERY utility with the BACKOUT YES option, run the REPORT utility with the RECOVER option on the object being recovered to identify events that might prevent you from recovering the object by backing out work to a given point in time.

Recovery considerations after rebalancing partitions with REORG

For partitioned table spaces, image copies that were taken before a REORG job that materialized limit key changes are not usable for recovering to a current RBA or LRSN. Avoid recovering a partitioned table space to a point-in-time that is after the REORG-pending or advisory REORG-pending status was set but before the REORG that redistributed data records. To determine an appropriate point in time:

1. Run REPORT RECOVERY.
2. Select an image copy for which the recovery point is a point after the REORG that redistributed data records.

Suppose that you run the REORG utility to turn off a REORG-pending status, and then recover to a point in time before that REORG job. In this case,Db2 sets restrictive statuses on all partitions that you specified in the REORG job, as follows:

• Sets REORG-pending (and possibly CHECK-pending) on for the data partitions
• Sets REBUILD-pending on for the associated index partitions
• Sets REBUILD-pending on for the associated logical partitions of nonpartitioned secondary indexes

Using offline copies to recover after rebalancing partitions

To recover data after a REORG job redistributes the data among partitions, use RECOVER LOGONLY. If you perform a point-in-time recovery, you must keep the offline copies synchronized with the SYSCOPY records. Therefore, do not use the MODIFY RECOVERY utility to delete any SYSCOPY records with an ICTYPE column value of 'A' because these records might be needed during the recovery. Delete these SYSCOPY records only when you are sure that you no longer need to use the offline copies that were taken before the REORG that performed the rebalancing.

Restrictions for point-in-time recoveries

Actions that can affect recovery status

When you perform the following actions before you recover a table space, the recovery status is affected as described:

• If you alter a table to rotate a partition (with an ALTER TABLE statement and a ROTATE PARTITION clause):
  • You can recover the partition to the current time.
  • You can recover the partition to a point in time after the alter. The utility can use a recovery base, (for example, a full image copy, a REORG LOG YES operation, or a LOAD REPLACE LOG YES operation) that occurred before the alter.
  • You cannot recover the partition to a point in time before the alter; the recover fails with MSGDSNU556I and RC8.
• If you change partition boundaries (with an ALTER TABLE statement and an ALTER PARTITION clause or with a REORG REBALANCE utility control statement):
  • You can recover the partition to the current time if a recovery base (for example, a full image copy, a REORG LOG YES operation, or a LOAD REPLACE LOG YES operation) exists.
  • You can recover the partition to a point in time after the change.
  • You can recover the partitions that are affected by the boundary change to a point in time before the materialization of those changes by the REORG TABLESPACE utility. However, after the RECOVER utility completes successfully, the affected partitions with the limit key changes are placed in REORG-pending (REORP) status. You then need to run REORG TABLESPACE to correctly redistribute the data according to the previous limit key values.
• If you alter a table to add a partition (with an ALTER TABLE statement and an ADD PARTITION clause):
  • You can recover the partition to the current time.
  • You can recover the partition to a point in time after the alter.
  • You can recover the partition to a point in time before the alter; RECOVER resets the partition to be empty.
• If you add a column (with an ALTER TABLE statement and an ADD COLUMN clause), you cannot recover a table space to a point in time between the time that you alter the table to add a column and the time that you take either of the following actions:
  • Drop the default value (with an ALTER TABLE statement and an ALTER COLUMN clause that specifies DROP DEFAULT)
  • Alter the default value (with an ALTER TABLE statement and an ALTER COLUMN clause that specifies SET DEFAULT)
  
• If you convert a table to support multiple XML versions (with a REORG TABLESPACE utility control statement):
  • You cannot recover the associated table space to a point in time before the table was converted.
  • You cannot recover any indexes for that table to a point in time before the table was converted.
• If you alter the organization of your table space to hash organization (with an ALTER TABLE statement and an ALTER ORGANIZATION clause):
  • You can recover the table space to the current time.
  • You can recover the table space to a point in time before or after the alter.
  • You can recover the table space to a point in time before or after the REORG that materialized the hash organization. RECOVER places the table space in AREOR status if the table space was recovered to a point before the REORG.
• If you alter the size of the hash space in your table space (with an ALTER TABLE statement and an ALTER ORGANIZATION clause):
  • You can recover the table space to the current time.
  • You can recover the table space to a point in time before or after the alter.
  • You can recover the table space to a point in time before or after the REORG that materialized the change in hash space size.
• If you drop the hash organization (with an ALTER TABLE statement and a DROP ORGANIZATION clause):
  • You can recover the table space to the current time.
  • You can recover the table space to a point in time after the alter.
  • You cannot recover the table space to a point in time before the alter.
• If you execute pending definition changes, you cannot perform a point-in-time recovery until you have either materialized the pending definition changes (with a REORG TABLESPACE or REORG INDEX utility control statement) or dropped those changes (with an ALTER TABLESPACE statement and a DROP PENDING CHANGES clause).
  Examples: Changing the following characteristics results in pending definition changes:

  • The segment size (with an ALTER TABLESPACE statement and a SEGSIZE clause)
  • The data set size (with an ALTER TABLESPACE statement and a DSSIZE clause)
  • The buffer pool page size (with an ALTER TABLESPACE statement and a BUFFERPOOL clause)
  • The MEMBER CLUSTER attribute (with an ALTER TABLESPACE statement and a MEMBER CLUSTER clause)
  • The table space type (with an ALTER TABLESPACE statement)
  • The limit key values (with an ALTER TABLE statement and an ALTER PARTITION clause)
• If you perform any of the following SQL operations on a table in a segmented (non-UTS) table space or universal table space, you cannot back out the changes (with a RECOVER utility control statement and a BACKOUT YES clause):
  • DELETE without a WHERE clause (mass DELETE)
  • TRUNCATE TABLE
  • DROP TABLE
  • ALTER TABLE with a ROTATE PARTITION clause

  If you perform any of the previously indicated actions on tables in a base table space that has indexes or auxiliary objects (LOB tables spaces or XML table spaces), this restriction also applies to those indexes or auxiliary objects.

• FL 508 If you move a table to another table space (by using the ALTER TABLESPACE statement with the MOVE TABLE clause):
  • You can recover the target table space and the source table space to the current time.
  • You can recover the target table space and the source table space to a point in time after the alter was materialized.
  • You can recover the target table space to a point in time before the alter was materialized, but the result is an empty table space. You cannot recover the source table space to a point in time before the alter was materialized.
  
• If you ran a redirected recovery, the target object cannot be recovered to a point in time prior to the redirected recovery. This restriction also applies to the unrecovered partitions of a target partitioned index when a redirected recovery was run on a subset of index partitions.

Planning for point-in-time recovery

Recovering to a point in time that is a point of consistency (QUIESCE or SHRLEVEL REFERENCE set) is desirable because there will be no uncommitted work to back out.

When making copies of a single object, use SHRLEVEL REFERENCE to establish consistent points for TOCOPY, TOLASTCOPY, or TOLASTFULLCOPY recovery. Copies that are made with SHRLEVEL CHANGE do not copy data at a single instant because changes can occur as the copy is made. A subsequent RECOVER TOCOPY operation can produce inconsistent data. Instead use RECOVER with the TOLOGPOINT option to identify a point after the SHRLEVEL CHANGE copy and any uncommitted units of work will be backed out.

When copying a list of objects, use SHRLEVEL REFERENCE. If a subsequent recovery to a point in time is necessary, you can use a single RECOVER utility statement to list all of the objects, along with TOLOGPOINT to identify the common RBA or LRSN value. If you use SHRLEVEL CHANGE to copy a list of objects, you should follow it with a QUIESCE of the objects.

To improve the performance of the recovery, take a full image copy of the table space or set of table spaces, and then quiesce them by using the QUIESCE utility. This action enables RECOVER TORBA or TOLOGPOINT to recover the table spaces to the quiesce point with minimal use of the log.

Ensuring consistency

You can use RECOVER TORBA, RECOVER TOLOGPOINT, and RECOVER TOCOPY to recover one of the following single objects:

• Partition of a partitioned table space
• Partition of a partitioning index space
• Data set of a simple table space

For any of the previously listed objects, restore all data sets to the same level; otherwise, the data becomes inconsistent.

If possible, specify a table space and all of its indexes (or a set of table spaces and all related indexes) in the same RECOVER utility statement, and specify TOLOGPOINT or TORBA to identify a QUIESCE point. This action avoids placing indexes in the CHECK-pending or REBUILD-pending status. If the TOLOGPOINT is not a common QUIESCE point for all objects, use the following procedure:

1. RECOVER table spaces to the value for TOLOGPOINT (either an RBA or LRSN).
2. Use concurrent REBUILD INDEX jobs to recover the indexes over each table space.

This procedure ensures that the table spaces and indexes are synchronized, and it eliminates the need to run the CHECK INDEX utility.

If you cannot specify TOLOGPOINT or TORBA to identify a QUIESCE point, you can specify any point in time, and Db2 will leave the data in a consistent state. The RECOVER utility automatically handles any uncommitted units of work and leaves the data in a consistent state when TORBA or TOLOGPOINT is specified.

When using RECOVER with the TORBA or TOLOGPOINT option, ensure that all of the objects that are changed by the active units of recovery at the recovery point are recovered to the same point-in-time so that they are synchronized:

• Db2 rolls back changes made to units of recovery that are inflight, inabort, postponed abort, or indoubt during the recovery point-in-time.
• Db2 does not roll back changes made to units of recovery that are INCOMMIT during the recovery point-in-time.
• Db2 rolls back only changes to objects in the RECOVER statement.

Avoiding CHECK-pending status

Db2 sets CHECK-pending status in the following point-in-time-recovery situations:

• You recover at least one member of a table space set to a prior point in time, but you do not recover all members of the table space set to the same quiesce point. In this case, all dependent table spaces that are recovered are placed in CHECK-pending status with the scope of the whole table space. All dependent table spaces of the recovered table spaces are placed in CHECK-pending status with the scope of the specific dependent tables.
• The RECOVER statement contains the TORBA option or TOLOGPOINT option and recovers all members of a table space set to the same point in time. However, referential constraints were defined in one of those table spaces after that point in time. In this case, the CHECK-pending status is set for the table space that contains the table with the referential constraint.
• The RECOVER statement contains the TORBA option or TOLOGPOINT option and recovers one or more indexes to a previous point in time. However, the same RECOVER statement does not recover the related table space. In this case, Db2 sets the CHECK-pending status for the indexes.

RECOVER does not place dependent table spaces that are related by informational referential constraints into CHECK-pending status.

To avoid setting CHECK-pending status, take the following actions:

• When you recover tables that are involved in a referential constraint, recover all of the table spaces that are involved in the constraint.
• Recover all dependent objects to the same point in time.
• Do not add table check constraints or referential constraints after the point in time to which you want to recover.
• Recover indexes and the related table space to the same point in time (preferably a quiesce point) or COPY SHRLEVEL REFERENCE point. RECOVER processing resets the CHECK-pending status for all indexes in the same RECOVER statement.

Compressed data

FL 509 After a point-in-time recovery completes successfully, RECOVER updates the COMPRESS_USED column of the SYSTABLEPART catalog table with the type of the compression dictionary that is currently in effect for each target page set..

Use caution when recovering a portion of a table space or partition (for example, one data set) to a prior point in time. If the data set that is being recovered has been compressed with a different dictionary, you can no longer read the data.

Recovery to a point in time before materialization of pending definition changes

You can recover a partition-by-growth table space, a partition-by-range table space, a LOB table space, or an XML table space to a point in time before a REORG job was run to materialize pending definition changes.

After you run RECOVER to a point in time that is before materialization of pending definition changes, the target table space is put in the REORG-pending state. You must run REORG on the entire table space to remove the REORG-pending state and complete the recovery process.