How to recover database to a point-in-time in DB2

Hello viewers, welcome toDB Tutorials. So many of the junior DBAs are commonly asking for the database point in time recovery in these times. Lets discuss and see practical test scenario on it today.

Here I have a database called “COMMDB”. As we all know for the point in time recovery we must enable archive log mode. For this “COMMDB” database I’ve already enabled “LOGARCHMETH1”

Now let me explain the scenario. In “COMMDB”, I’ll create a table with name test and then ‘ll take an online backup.And then I’ll insert data in test table and then commit. Then I’ll restore the database to the point-in-time by using RECOVER DATABASE command.

Lets implement the scenario practically.

*First lets see the list of databases in my instance:
db2 list db directory

* Then connect to database
db2 connect to commdb

* Create table test
db2 create table test(id int, name varchar(20))

* Now lets take the backup
db2 backup db commdb online compress include logs

* Lets insert data into test
db2 insert into test values(1,’db2′),(2,’dba’)

* Lets recover the database using “RECOVER DATABASE” command
db2 recover db commdb to 2017-11-18-19.20.00 using local time

* lets test the result
connect to database — db2 connect to commdb
fetch the data from test table — db2 select * from test