DB2 Online Incremental Backup Restore Incremental backup and Rollback Recovery

This article mainly describes the actual operation procedures of Online Incremental DB2 backup and restoration of Incremental backup and Rollback Recovery. The following is a detailed description of these contents, we hope you will have a better understanding of the Online Incremental backup of DB2 and the actual operation steps for restoring the Incremental backup and Rollback Recovery.

Online Incremental Backup prerequisites:

1. Change the database parameter logretain, userexit, and trackmod to on2. After the parameter is changed, the database is backed up completely offline. 3. Online and Online Incremental Backup can be performed.

Note: prerequisites for Online Incremental Backup:

1. Change the database parameter logretain, userexit, and trackmod to on.

2. Change the parameters and back up the database offline once.

3. Now you can perform online and DB2 Online Incremental backup.

The test result is passed. The script is as follows.

Important! The archived database logs must be backed up to another place on time. In this example, C: \ TESTDB.0 \ SQLOGDIR.

C: \ Documents ents and Settings \ Administrator> cd \

C: \> db2 drop db TestDB

The DB20000I drop database Command is successfully completed.

C: \> db2 create db TestDB

The DB20000I create database Command is successfully completed.

C: \> db2 connect to TestDB

Database connection information

Database Server = DB2/NT 8.2.0

SQL authorization id = ADMINIST...

Local Database alias = TESTDB

Modify database parameters to enable online backup

C: \> db2 update db cfg using logretain on userexit on trackmod on

The DB20000I update database configuration command is successfully completed.

One or more parameters submitted for immediate modification by SQL1363W are not dynamically changed. For these configuration parameters

After all applications are disconnected from this database, the changes take effect.

Before incremental and online backup, you must perform an offline full backup once.

C: \> db2 backup db TestDB

The backup is successful. The time stamp of this backup image is: 20070419144951

C: \> db2 connect to TestDB

Database connection information

Database Server = DB2/NT 8.2.0

SQL authorization id = ADMINIST...

DB2 Online Incremental Backup Restore Incremental backup and Rollback Recovery

Local Database alias = TESTDB

Create a test table and insert Test Data

C: \> db2 create table T1 (C1 int)

The DB20000I SQL command is successfully completed.

C: \> db2 insert into T1 (C1) values (1)

The DB20000I SQL command is successfully completed.

Perform online incremental database backup

C: \> db2 backup db TestDB online incremental

The backup is successful. The time stamp of this backup image is: 20070419145112

Simulate a disaster and delete a database! (Note: You must back up the archived log file to another path before saving it)

C: \> db2 drop db TestDB

The DB20000I drop database Command is successfully completed.

Recover a database based on the DB2 Online Incremental Backup

C: \> db2 restore db TestDB incremental automatic from "c:" taken at 2007041914511

2

The DB20000I restore database command is successfully completed.

The recovered database is in the rollback and suspension status and is unavailable.

C: \> db2 connect to TestDB

SQL1117N cannot connect to or activate the Database "TESTDB" because of ROLL-forward pending ".

SQLSTATE = 57019

It is important to roll back the database and specify the location for archiving logs!

C: \> db2 ROLLFORWARD DATABASE TESTDB TO END OF LOGS AND COMPLETE OVERFLOW LOG PAT

H ("C: \ TESTDB.0 \ SQLOGDIR ")

Roll-forward status

Input Database alias = TESTDB

Number of nodes returned status = 1

Node number = 0

Roll Forward status = not suspended

Next log file to be read =

Processed log file = S0000001.LOG-S0000001.LOG

Last implemented transaction = 2007-04-19-06.51.22.000000

The DB20000I ROLLFORWARD command is successfully completed.

Data Warehouse recovery is complete, rollback is complete, and test data is queried

C: \> db2 connect to TestDB

Database connection information

Database Server = DB2/NT 8.2.0

SQL authorization id = ADMINIST...

Local Database alias = TESTDB

C: \> db2 select * from T1

C1

1

1 record selected.

Test all normal, exit

C: \> db2 connect reset

The DB20000I SQL command is successfully completed.

C: \> db2 terminate

The DB20000I TERMINATE command is successfully completed.