Learning Visually with Examples [Electronic resources] نسخه متنی

13.6. Database and Table Space Roll Forward

If you have to restore your database or a table space in one of your databases, you will lose any changes made since the backup was taken unless you have log retain enabled and use the

ROLLFORWARD command to replay the logs for your database.

13.6.1. Database Roll Forward

If a backup operation is performed online, then there are still users connecting to the database and they may be in the middle of a transaction. Therefore, an online backup contains the backup image of a database that is in an inconsistent state. After restoring the backup image into a database, the database is immediately placed in a roll forward pending state. You must run the

ROLLFORWARD DATABASE command to bring the database back to a normal state.

If you performed an offline backup but your database is configured to use archival logging, then the database is also placed in a roll forward pending state following a restore. In this case, you do not need to use the

ROLLFORWARD command because an offline backup implies that the database is already in a consistent state. To avoid this, use the

WITHOUT ROLLING FORWARD option in the

RESTORE DATABASE command. You need SYSADM, SYSCTRL, or SYSMAINT authority to perform the

ROLLFORWARD command.

During a roll forward, the transactions in the log files are applied. You can apply all the changes in the log files, that is, roll forward to the end of logs, or you can roll forward to a Point In Time. This means DB2 will traverse the logs and redo or undo all database operations recorded in the logs up to the specified PIT. However, you must roll forward the database to at least the minimum[View full width]

SQL1275N The stoptime passed to roll-forward must be greater than or equal to "

timestamp ",
because database "

dbname " on node(s) "0" contains information later than the specified time.

The

timestamp given in the error message is the minimum PIT to which you must roll forward the database.

Though we will not cover the

QUIESCE command in this chapter, it is worth mentioning that you can use this command during regular database operations to create consistency points. You can always perform a point in time recovery to any of these points and be assured your database will be consistent. (See section 11.5.1 Lock Attributes, for more information about the

QUIESCE command.)

During roll forward processing, DB2 does the following:

1. Looks for one log file at a time in the active log directory.

2. If found, reapplies transactions from the log file.

3. If the log file is not found in the active log directory, DB2 searches for the logs in the OVERFLOWLOGPATH, if specified in the

   ROLLFORWARD DATABASE command.

If DB2 does not find the log file in the active log directory, and you did not specify the

OVERFLOWLOGPATH , then the logs have to be retrieved from their archive location. The method used is determined by the LOGARCHMETH1 database configuration parameter. If it is set to LOGRETAIN, then you have to retrieve the logs manually. If it is set to USEREXIT, then the user exit program

db2uext2 is called to retrieve the log file. If it is set to DISK, TSM, or VENDOR, then DB2 automatically retrieves the log file from the respective archive locations.

Once the log is found in the active log directory or the

OVERFLOWLOGPATH option, DB2 reapplies the transactions it contains and then goes to retrieve the next file it needs.

The syntax of the

ROLLFORWARD command is:

ROLLFORWARD DATABASE 
database_alias  [USER 
username  [USING 
password ]]
[TO {
isotime  [ON ALL DBPARTITIONNUMS] [USING LOCAL TIME] | END OF LOGS
[On-DbPartitionNum-Clause]}] [AND {COMPLETE | STOP}] |
{COMPLETE | STOP | CANCEL | QUERY STATUS [USING LOCAL TIME]}
[On-DbPartitionNum-Clause] [TABLESPACE ONLINE | TABLESPACE (
tblspace-name 
[ {,
tblspace-name } ... ]) [ONLINE]] [OVERFLOW LOG PATH (
log-directory 
[{,
log-directory  ON DBPARTITIONNUM 
db-partition-number } ... ])] [NORETRIEVE]
[RECOVER DROPPED TABLE 
dropped-table-id  TO 
export-directory ]

You can use one or more of the following options for

On_DbPartitionNum_Clause :

ON {{DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number 
[TO db-partition-number] , ... ) | ALL DBPARTITIONNUMS [EXCEPT 
{DBPARTITIONNUM | DBPARTITIONNUMS} (db-partition-number 
[TO db-partition-number] , ...)]} 

To perform a roll forward of the

sample database you can use any of the following statements:

ROLLFORWARD DATABASE sample TO END OF LOGS AND COMPLETE (1)

ROLLFORWARD DATABASE sample TO

timestamp

AND COMPLETE (2)

ROLLFORWARD DATABASE sample TO

timestamp

USING LOCAL TIME AND COMPLETE (3)

Example (1) rolls forward to the end of the logs, which means that all archived and active logs are traversed. At the end DB2 completes the roll forward operation and brings the database from rollforward pending state to a usable state.

Example (2) rolls forward to the specified Point In Time. The timestamp used is in UTC (Universal Coordinated Time), which can be calculated as follows:

Local time
value in the CURRENT_TIMEZONE special register

For example, to look at the value of the CURRENT_TIMEZONE special register, connect to the database and issue the following SQL statement:

db2 "VALUES (CURRENT_TIMEZONE)" 

If the local time is 2004-05-29-14.42.38.000000, and

CURRENT_TIMEZONE is -5, then the corresponding UTC time is 2004-05-29-19.42.38.000000.

Example (3) is similar to example (2), but the timestamp can be expressed using local time.

Note that there is no keyword

OFFLINE in the syntax, as this is the default mode. In fact, for the

ROLLFORWARD command, this is the only mode allowed.

13.6.2. Table Space Roll Forward

You can perform table space roll forwards either online or offline, except for the system catalog table space (SYSCATSPACE) which can only be rolled forward offline. The following is an example of a table space ROLLFORWARD:

ROLLFORWARD DATABASE sample 
TO END OF LOGS AND COMPLETE 
TABLESPACE ( userspace1 ) ONLINE 

The options in this example have already been explained in section 13.6.1, Database Roll Forward. The only difference is the

TABLESPACE option, which specifies the table space to be rolled forward.

13.6.3. Table Space Roll Forward Considerations

If the registry variable DB2_COLLECT_TS_REC_INFO is enabled, only the log files required to recover the table space are processed. The

ROLLFORWARD command will skip over log files that are not required, which may speed recovery time.

You can use the

QUERY STATUS option of the

ROLLFORWARD command to list the log files that DB2 has rolled forward, the next archive log file required, and the timestamp of the last committed transaction since roll forward processing began. For example:

ROLLFORWARD DATABASE sample QUERY STATUS USING LOCAL TIME 

After a table space Point In Time roll forward operation completes, the table space is placed into backup pending state. A backup of the table space or database must be taken because all updates made to it between the PIT that the table space was recovered to and the current time have been lost.

13.6.4. Performing ROLLFORWARDs with the Control Center

You can use the Rollforward Wizard to perform a roll forward. In the Control Center, expand your database folder, right-click on the database name you want to roll forward, and select

Rollforward from the menu. Alternatively, you can click on the database name and then select

Rollforward from the

Selected pull-down menu. The database must be in roll forward pending state to invoke the Rollforward Wizard. Figure 13.9 shows that you can choose to roll forward to the end of logs or to a PIT. The Rollforward Wizard guides you through the roll forward command options.