Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the rollforward command in DB2. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Introduction

When the database becomes the carrier of storing enterprise data, how to recover the data has become a topic of concern. There are many kinds of data recovery in DB2. This paper mainly introduces the instructions for the use of data DB2 rollforward, and uses DB2 V97 to do some example analysis, so that readers can better understand and master the use of rollforward.

Recovery introduction

There are four ways to restore a database in DB2, which are described below:

Crash Recovery refers to being interrupted during a transaction, which may result in data inconsistency and unavailability. At this point, Crash Recovery will protect the database from data inconsistency and unavailability.

High Availability Disaster Recovery refers to High availability disaster recovery (HADR), which prevents catastrophic data loss by replicating data from the source database to the target database.

Version Recovery is the recovery of backup media, mainly the restore command.

Rollforward Recovery, usually when the database is backed up, some new transactions are executed. If storage failure or misoperation occurs at this time, rollforward can be used to repair these new transactions after restore. This paper mainly introduces this recovery method.

Rollforward preparation work

Rollforward can only be used in the database recoverable state, that is, the archive log is used, and the parameter logarchmeth2 or logarchmeth3 is non-OFF.

First, you need to modify the parameters so that the database is in the recoverable state.

Listing 1. Modify parameter LOGARCHMETH1

one

Db2 "update db cfg for $db using logfilsiz 4 LOGARCHMETH1 disk:$archivePath"

Undefined

If the logarchmeth2 is OFF before the parameter is modified, and the database is in the backup pending state after the parameter is modified, offline backup is required to make the database available.

Listing 2. Backup database

1db2 "backup db $db to $backupPath"

Undefined

After backing up the database, perform some transactions, such as simple transaction operation create table,insert,update,delete and so on. Then the Restore database, if it is a tablespace-level rollforward, can also use other ways to put the tablespace in the rollforward-pending state.

Listing 3. Restore the database

1db2 "restore db $db from $backdir without prompting"

Undefined

If you specify the WITHOUT ROLLING FORWARD parameter when using restore db, you cannot leave the database in the rollforward-pending state and therefore cannot use rollforward. The WITHOUT ROLLING FORWARD parameter is not specified here, and when the database or tablespace is in the rollforward-pending state, it can be rollforward.

Introduction to use of Rollforward

In the database log, all operations on the database are recorded. With rollforward, you can roll forward the log to restore the database to a certain point in time where there is a log after backup, or to the end of the activity log.

The common rollforward parameters are as follows:

QUERY STATUS, which mainly queries the current status of the database.

STOP, specifically rollforward completion, so that no other rollforward operations can be performed unless you re-restore and then rollforward.

CANCEL, cancel the rollforward operation and make the database in the rollforward exit the rollforward pending state.

POINT IN TIME,rollforward to a specific point in time.

END OF LOGS,rollforward to the end of the activity log.

ONLINE, which makes the tablespace-level rollforward in the online state at execution time, allowing other connections to the database.

In a partitioned data environment, rollforward operations must be performed on a partition, which is briefly described as follows:

Point-in-time rollforward is executed on all partition.

END OF LOGS rollforward, if there is an ON DATABASE PARTITION parameter, rollforward only runs on the specified partition, and if no partition is specified, it will take effect on all partition.

End of backup affects all partition.

Combining the above parameters, rollforward can be divided into two types, database-level rollforward and tablespace-level rollforward. Database-level rollforward requires that the database first restore, so that the database is in the rollforward pending state. The table space level of rolllforward is not only restore can become a rollforward pending state, such as a sudden power outage or other conditions may also cause the table space to perform rollforward operations.

When rollforward is executed, database-level rollforward is performed if the database is in the rollforward pending state. If some tablespaces are still in rollforward pending state after execution, rollforward at the tablespace level is executed to restore those tablespaces. In tablespace-level rollforward, tablespaces can be specified for rollforward, and if not specified, all tablespaces in this state will be rollforward. If you change the name of a tablespace after backup, you need to use the new tablespace name when rollforward that tablespace.

Another difference between database-level rollforward and tablespace level is that when database-level rollforward occurs, the database cannot perform other connection operations, that is, other connections must be disconnected in order to perform database-level rollforward. The table space level can choose whether other operations can be carried out.

You cannot cancel the rollfoward operation in progress. You can only use rollforward cancel to cancel the rollforward without stop after the rollfoward is completed. If you already have stop, you cannot cancel.

Rollforward case study Tablespace rollforward instance

When the amount of data in the database is large, the full backup and recovery of the database are very time-consuming, so the database can be quickly restored through tablespace backup.

In this example, Restore three table spaces, use rollforward to the end of the logs to roll forward one table space to the end of the activity log, and then use to the end of the logs and stop to roll forward the other two table spaces to the end of the activity log.

As a prerequisite for restore and rollforward, you first need to backup each tablespace, as shown in listing 4.

Listing 4. Backup tablespace

1 2 3 4 5 6 7 8 9db2 "update db cfg for test using logfilsiz 4 LOGARCHMETH1 disk:$archivePath" db2 "backup db test to $backupPath" db2 "connect to test" db2 "create tablespace tbs1" db2 "create tablespace tbs2" db2 "create tablespace tbs3" db2 "backup db test tablespace (tbs1) to $backupPath/tbs1" backup db test tablespace (tbs2) to $backupPath/tbs2 "db2" backup db test tablespace (tbs3) to $backupPath/tbs3 "

Undefined

Figure 1 shows the backup results.

Figure 1. Backup tablespace

After the backup, you can see the corresponding backup image in the specified directory.

Listing 5 shows a list of operations for creating tables and inserting data for three tablespaces.

Listing 5. Operation tablespace

1 2 3 4 5 6 7db2 "connect to test" db2 "create table T1 (an int) in tbs1" db2 "create table T2 (an int) in tbs2" db2 "create table T3 (an int) in tbs3" db2 "insert int T1 values (1)" db2 "insert int T2 values (2)" db2 "insert int T3 values (3)"

Undefined

After the operation is complete, you can restore the three tablespaces through the backup image you just made. Note that the restored backup image must correspond to the restored tablespace, otherwise the restore will fail, as shown in listing 6.

Listing 6. Restore three tablespaces

1 2 3db2 "restore db test tablespace (tbs1) from $backupPath/tbs1 without prompting" db2 "restore db test tablespace (tbs2) from $backupPath/tbs2 without prompting" db2 "restore db test tablespace (tbs3) from $backupPath/tbs3 without prompting"

Undefined

Figure 2 shows the results of manipulating and restoring tablespaces.

Figure 2. Restore tablespaces after inserting data

After a successful restore, all three tablespaces are in the rollforward pending state and cannot be accessed by the user, as shown in figure 3.

Figure 3. Verify tablespace rollforward-pending status

Listing 7 shows the command to access the tablespace.

Listing 7. Access tablespace

1 2 3 4db2 "connect to test" db2 "select * from T1" db2 "select * from T2" db2 "select * from T3"

Undefined

At this point, you need to roll forward to make the tablespace available, as shown in listing 8.

Listing 8. Roll forward the tablespace and verify the results

1 2 3 45 6db2 "rollforward db test to end of logs tablespace (tbs1) online" db2 "rollforward db test to end of logs and stop tablespace (tbs2, tbs3) online" db2 "connect to test" db2 "select * from T1" db2 "select * from T2" db2 "select * from T3"

Undefined

Figure 4 correctly returns the results of the operation on the table, indicating that rollforward is successful.

Figure 4. Verification result

Rollforward query status instance

The query status option of the rollforward command can be used to list some of the following current database information.

Log files that DB2 has rolled forward.

The next archive log file required.

The timestamp of the most recently committed transaction since the roll-forward process began.

Let's use listing 9 to query the database status.

Listing 9. Roll forward the database and query the status

1db2 "rollforward db test query status"

Undefined

The database that shows the DB pending status in figure 5 is actually in the rollforward pending state. The database needs to be rolled forward to bring the database back to normal.

Figure 5. Check Rollforward Pending Status

Rollforward cancel instance

Before performing the cancel operation, perform the most basic operation, db2 rollforward db $db to end of logs, after which all log files written after the database backup are rolled forward, as shown in figure 6.

Figure 6. Rollforward to end of logs

Next, perform the db2 rollforward db $db cancel operation, which cancels the roll forward operation. After execution, all previous rollforward operations are rolled back, the database is back in the restore-pending state, and any connections are rejected.

Listing 10 lists the command to cancel roll forward and restore.

Listing 10. Cancel rollforward and restore

1 3 4db2 "rollforward db test cancel" db2 "connect to test" db2 "restore db test from $backupPath without prompting" db2 "connect to test"

Undefined

When you just cancel the roll forward, you cannot connect to the database because the database is in the restore pending state, as shown in figure 7.

Figure 7. Restore Pending status

Re-execute the restore operation, db2 restore db $db from $backdir without prompting

The database is in the rollforward-pending state and any connection is denied, as shown in figure 8

Figure 8. Rollforward Pending status

Rollforward to end of logs and complete instance

Following the above example, a roll forward operation is performed so that the database can return to normal state. Perform the db2 rollforward db $db to end of logs and complete operation, which rolls forward to the end of the log, which means that all archived logs and activity logs are traversed. This is shown in listing 11 and figure 9.

Listing 11. Roll forward to the end of the activity log and complete the roll forward

1db2 "rollforward db test to end of logs and complete"

Undefinedundefined

Rollforward interrupted instance

If the roll forward operation is accidentally interrupted by a user misoperation or other reasons during the execution of the rollforward to end of logs, the execution of the rollforward to end of logs will continue and complete after the last interrupted state. For example, db2 rollforward db $db to end of logs interrupts the roll forward operation by pressing Ctrl+C during execution, while the database is still in the rollforward-pending state. Listing 12 lists the commands for this example.

Listing 12. Roll forward interrupt

1 23 4db2 "rollforward db test to end of logs" Push Ctrl+C db2 "rollforward db test to end of logs and stop" db2 "connect to test"

Undefined

Figure 10 shows the result of interrupting the rollfoward command.

Figure 10. Rollforward Pending status

At this point, the roll forward operation db2 rollforward db $db to end of logs and stop,rollforward continues to complete, and the database connection is successful, as shown in figure 11.

Figure 11. Rollforward Completed

Rollforward noretrieve instance

The noretrieve parameter indicates that there is no need to retrieve archived logs, so the success of rollforward db $db to end of logs and stop noretrieve depends on whether rollforward needs to retrieve archived log files. If necessary, because the roll forward parameter specifies noretrieve, the roll forward will fail because these log files are missing. Listing 13 and figure 12 list this example.

Listing 13. Rollforward that does not need to retrieve archive logs

1db2 "rollforward db test to end of logs and stop noretrieve"

Undefinedundefined

Rollforward overflow log path instance

The parameter overflowlogpath overrides the previously set overflowlogpath, and if db2 update db cfg for test using overflowlogpath $path is previously set, using the rollforward db $db to end of logs and complete overflowlogpath ($overflow) command, $overflow replaces $path as the new overflowlogpath. Listing 14 and figure 13 show this example.

Listing 14. Roll forward overwriting logpath

1db2 "rollforward db test to end of logs and complete overflow log path ($overflowPath3)"

Undefinedundefined

You can see the log file in the new directory $overflow during rollforward execution, and you can see that the parameters are in effect, as shown in figure 14.

Figure 14. Overflow log list

Rollforward backup instance

The database can also perform rollforward backup operations, as shown in listing 15.

Listing 15. Roll forward backup

12 3db2 "backup db test online to $backupPath include logs" db2 "restore db test from $backupPath taken at 20091101125919 without prompting" db2 "rollforward db test to end of backup"

Undefined

Figure 15 shows the results of online backup.

Figure 15. Online backup

And then execute

1db2 restore db $db from $backupPath taken at $backupNum without prompting

Undefined

And

1db2 rollforward db $db to end of backup

Undefined

You can recover. Rollforward to the end of backup can roll forward all partitions in the partitioned database to the nearest recovery point. It is difficult to locate this recovery point manually, especially for partitioned databases, while it is easy to locate using end of backup. Figure 16 shows the final result.

Figure 16. Rollforward to end of backup

This is the end of the article on "how to use rollforward commands in DB2". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.