How to Recover records deleted by mistake in db2 using Roll forward to a Point in Time recovery

          The following tutorial explains about how to roll forward a database to a point in time. In one of my earlier postings , i have covered how to restore database from online backup and to roll forward to the end of logs.  Once the database is restored from the online backup, it is required to do a Roll Forward operation to a point in time or end of logs depending upon the situation before the database will be made available. Now let us see, when to use "rollforward to end of logs " and when to use "rollforward to point in time" .

End of logs option is useful when a database is lost, and a recovery is needed through all available logs after the online backup has been made to ensure all transactions have been recovered. For example , last online backup has been taken on 10/25/2011, database is corrupted on 10/27/2011. To recover the database, first restore the last backup taken on 10/25/2011 then to apply the transactions happened after the online backup(i.e. using active logs), we have to roll forward to End of logs

Point in Time option is useful when the following situation occurs. Suppose by mistake, lot of records are deleted from the database by a user. Now how to recover the deleted records?. The answer is rolling forward the logs to a Point in Time before the deletion took place. Now let me explain Point in Time recovery option with example.

Please note the number of rows in the sales_trans table

select count(*) from sales_trans

Suppose the records in the sales_trans are 21000

Now let us take Online backup of a database Sales.

db2 connect to sales
db2 backup db sales online to d:\db2onlinebkp

Backup successful. The timestamp for this backup image is : 20111105224708 , where 20111105224708 is timestamp for the last online backup.

After online backup , some transactions are committed

Again note the number of rows in the sales_trans table by executing the query Select count(*) from sales_trans

Suppose now the records in the sales_trans are 21050

Suppose one user has deleted some of the records by mistakenly by issuing the statement delete from sales_trans where sales_date <=date('01/03/2011') . Now how to recover those records ?

Please note the date & time before issuing the delete statement . This date & time will be required later for point in time recovery. For example : date is 11/05/2011 and time is 23.11.10 .

Note the count by issuing the statement Select count(*) from sales_trans which is now 11000 . Almost 10050 records are deleted.

Steps to recover:

db2 force applications all
db2 terminate
db2 restore db sales from d:\db2onlinebkp taken at 20111105224708 without prompting

           The above command restores the database from the last online backup made. Now we need to roll forward to a point in time (in our example : 2011-11-05-23.11.10 ) before the delete statement was issued .The statement to rollforward to a point in time.

db2 rollforward db sales to 2011-11-05-23.11.10 using local time

In the above output, Roll forward status is DB Working. Please Issue the statement db2 rollforward db sales stop which will make the roll forward status to not pending.

Now you can check the number of rows in the sales_trans which is 21050.

Now the deleted records have been recovered by doing Point in Time recovery. Roll forward has been done upto the time before the delete statement was issued. Delete transactions are recorded after this point in time. So delete statements will not be repeated when we do roll forward. But if you do roll forward to end of logs , the delete statements will also be repeated which will again delete the records.