Db2 Roll-Forward Pending SQLSTATE=57019 Solution. How to Restore Database from online backup

Assume that already Online Backup has been done for the database test with Time stamp (T1) . Steps to take Online Backup can be viewd at For DB2 Online Backup. Recovery may be required due to data loss for various resons or records might have been deleted by mistakenly . Now to restore from the online backup image taken with the Timestamp T1, issue the following commands. First close all the applications that uses the database by issuing the following command

db2 force applications all

now issue command to restore

For Linux : db2 restore db test from /db2onlinebkp taken at T1 into test without prompting

For Windows : db2 restore db test from d:/db2onlinebkp taken at T1 into test without promptingwhere db2onlinebkp is the folder where online backup image is kept. If it is kept in the current path , then no need to give from /db2onlinebkp . If we give the command without "without prompting" , then warning will be displayed . Here both online backup image name and target database name is same (test) . Suppose online backup image name is dbbkp , then the command will be db2 restore db dbbkp from /db2onlinebkp taken at T1 into test without prompting

Now assume that database backup is restored successfully , Let us connect to the data base.

db2 connect to test .

Now the following error will be displayed

SQL1117N A connection to or activation of database "TEST" cannot be made because of ROLL-FORWARD PENDING. SQLSTATE=57019

The above error is thrown , because any transaction occurred during the online backup is written into the database transaction logs. These transactions are not available with online backup image. So when we restore from online database backup image , the activities happened during online backup are not updated. So it is mandatory to do a Roll Forward operation to a minimum point in time or end of logs before the database will be made available.

So to roll forward to the end of logs , the following command may be given

db2 rollforward db test to end of logs and stop

the follwing status are displayed.

    
                                 Rollforward Status

 Input database alias                   = test
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000002.LOG - S0000005.LOG
 Last committed transaction             = 2011-10-10-20.28.50.000000

DB20000I  The ROLLFORWARD command completed successfully.

Note : The stop keyword at the end of rollforward command is very important that indicates the database should be made available for user connections after all the transaction logs have been applied to the database.