POINT-IN-TIME RECOVERY
Topic extracted from RevealNet's Knowledge Base for DB2

Point-in-Time (PIT) recovery can be to an image copy or a point of consistency. The point of consistency can be established by the QUIESCE utility by recording the RBA or log record sequence number (LRSN) in a sysplex data sharing environment in the START_RBA column of SYSCOPY. The older term RBA is often used rather than LRSN and is used in this section. Keep in mind that it is actually the LRSN in a sysplex data sharing environment. The RECOVER utility parameter TOLOGPOINT should be used rather than TORBA. It has the same effect in a data sharing and non-data sharing environment. It is required in a data sharing environment and should be used in a non-data sharing environment if there is any possibility of using data sharing in the future. This avoids having to change TORBA to TOLOGPOINT if it is decided to use data sharing.

The goal of recovery to a RBA is to eliminate updates made after the PIT recovery. For example, assume that updates U20 through U30 are in the log for a tablespace as shown below.

Assume that an image copy is made after U21 and there is a requirement to recover to that copy using the RECOVERY utility parameter TOCOPY. Update U22 and all subsequent updates are no longer on the tablespace after the recovery. This is because the RECOVER utility writes the image copy to the tablespace and does not apply the log records.

Another requirement might be to recover to an established RBA for update U24. All updates made after and including the RBA are no longer on the tablespace after a TOLOGPOINT or TORBA recovery. This is because the RECOVER utility writes the image copy to the tablespace and applies the log records up to and including the RBA for U24.

An image copy is not required after a PIT recovery although it is advisable to avoid having to go too far back in the log for future recoveries. The records between the RBA for U24 and the start RBA in SYSCOPY (U31 in the example) are not included in subsequent recoveries.

The QUIESCE and REPORT utilities are useful for PIT recoveries. We will look first at the QUIESCE utility.

POINT-IN-TIME RECOVERY PROCEDURES

Recover to an RBA 
Logonly Recovery 
Related Dependent Tablespaces 
Prepare for Recovering Related Tablespaces 
Recovering a RI Structure to a RBA 
Recover TOCOPY 

Recover To an RBA

Lets assume that it is necessary to recover to the X'000008A50B9C' RBA based on information from executing the REPORT utility or from selecting from SYSCOPY. The RBA identified is specified in the TOLOGPOINT or TORBA parameter like:

RECOVER TABLESPACE DASPJDB.DASPJTSP
TOLOGPOINT X'000008A50B9C'

The RECOVER utility determines the most recent available image copy from SYSCOPY, writes it to the tablespace and applies the log records to the copy up to the specified RBA. All updates made after and including the RBA are lost as planned.

RECOVER TABLESPACE DASPJDB.DASPJTSP
TOLOGPOINT X'4A970C310154'

Indexes must be recovered with a PIT recovery. The existing indexes include updates effectively discarded from the tablespace with the PIT recovery. Here is an example of requesting the rebuild of all indexes on all tables in the DASPJDB.DASPJTSP.

REBUILD INDEX (ALL)
TABLESPACE DASPJDB.DASPJTSP

The most recent RBA written to SYSCOPY by the QUIESCE utility for a specified database and tablespace can be determined like:

SELECT HEX(START_RBA)
FROM SYSIBM.SYSCOPY
WHERE DBNAME = 'dbname'
AND TSNAME = 'tsname'
AND ICTYPE = 'Q'
AND TIMESTAMP =
(SELECT MAX(TIMESTAMP)
FROM SYSIBM.SYSCOPY
WHERE DBNAME = 'dbname'
AND TSNAME = 'tsname'
AND ICTYPE = 'Q');

Logonly Recovery

If an image copy is not available, a logonly recovery is attempted by scanning the log since the tablespace was first created. This can obviously be very resource intensive and it is unlikely that archive logs have been retained if the tablespace was created months or years ago. Also a logonly recover will not be successful if any non-recoverable event occurred, for example a load or reorganization with log of no. However, if LOAD fails during the RELOAD phase, it is possible to recover to the RBA written to SYSCOPY at the beginning of phase with log of no or at the end of the phase with log of yes. It is also possible to recover to a previous copy.

Related Dependent Tablespaces

Any related dependent tablespaces not in the named tablespaces for a PIT recovery are placed in the check pending status. An exception is if the named RBA was established on all related tablespaces with the QUIESCE utility. This is because there can be inconsistencies between the parent and dependent tables after a PIT recovery. The diagram below shows how inconsistencies can occur if a PIT recovery is not done to a quiesce point.

Lets assume that a TOLOGPOINT recovery is done such that the U23 and U24 work are removed from the parent S table. There is no recovery of the dependent table. Further assume that U23 is a delete of S4 from the S table with a cascade to the dependent table where it is also deleted. After the TOLOGPOINT recovery, S4 is in the S table again but is not in the dependent table. The effect of U23 is removed from the parent table but not the dependent table. This is an example of why check pending is set on.

Prepare for Recovering Related Tablespaces

It is advisable to prepare for recovering related tablespaces. This can be done by quiescing the related tablespaces before making image copies to ensures that all pending states are off and that all updates in the virtual pool are written to the tablespaces and indexes if WRITE YES is used. Make image copies of the related tablespaces. Do another quiesce of the related tablespaces after the image copies to provide for a TOLOGPOINT recovery. A recovery using TOCOPY will set check pending on because each of multiple copies probably completed at different times and can contain different update activity. 

There are performance advantages to recovering TOLOGPOINT compared to a recovery to currency. This is because the log need not be scanned beyond the specified RBA.

Recovering a RI Structure to a RBA 

After the QUIESCE utility has been used to establish a RBA for all tablespaces that are part of a DB2 defined RI structure or group of tablespaces that need to be kept synchronized, the tablespaces must be named in the RECOVER statement along with the established RBA value like:

The recovery of the multiple tablespaces uses a single scan of the log. The utility is restartable in case of failure. Indeed, do restart the RECOVER utility rather than terminating it and resubmitting the utility. Termination of the utility places all of the named tablespaces in a RECOVERY PENDING state.

All indexes on all tables in all tablespaces recovered using TOLOGPOINT, TORBA and TOCOPY must be rebuilt prior to V6 like:

All indexes on all tables in the named tablespaces can be named in the RECOVER statement in V6.

Recover TOCOPY

Recovery can be to a specific image copy. All updates made after the image copy are lost. Here is an example of recovering the DASPJDB.DASPJTSP table to a named image copy.

RECOVER TABLESPACE DASPJDB.DASPJTSP
TOCOPY C8145GD.DASPJTSP.TIC.G0007V00

The copy named in the TOCOPY parameter can be a full or incremental copy. If the copy is not available, it is necessary to use the previously made copy and go further back in log for the log apply.

The named copy should be an image copy made with SHRLEVEL REFERENCE (not concurrent copy). Otherwise, data pages could have been changed during the copy after the space map page was copied resulting in an inconsistent copy. If an attempt is made to recovery to other than an image copy made with SHRLEVEL REFERENCE a warning is issued that the DB2 object has been recovered to an indeterminate point.

If a TOCOPY or TOLOGPOINT recovery is done of some but not all data sets in a multi data set nonpartitioned tablespace, a warning is issued prior to the recovery that the data set to be recovered might have been compressed with a different dictionary.

It is necessary to recover or rebuild all indexes on all tables in all tablespaces that have been recovered with TOCOPY, TOLOGPOINT, and TORBA.