How to restore db2 database to specified date and restore the deleted records from certain table

Jephe Wu - http://linuxtechres.blogspot.com

Scenario: single DB2 instance and database db1, database transaction log is enabled, partial data of some tables in schema 'jephe' were deleted accidently. As this is a production database used by many different clients/schemas, we cannot restore back as the client only realized this accident one week later after deletion.

Solution: restore monthly database full backup plus the transaction logs just before the time which deletion happened on another server(DR?). Then use db2 export and db2 import to import back those deleted data.

Environment: RHEL5, IBM DB2 UDB 9.1 fixpack 3.


Steps:

1. restore the monthly full online database backup to /db2/db2inst1 directory
db2 "restore database db1 from /data to /db2/db2inst1 into db1dr with 2 buffers buffer 1024 parallelism 1 without prompting"

note: /data is the directory where the database full backup image exists.

2. get all the transaction log files after that full backup and before the deletion time

3. copy all the necessary log files to the /data/db2log/DB1/logs, then run the following command:
db2 "rollforward database db1dr to 2011-04-19-11.30.00.000000 using local time and complete overflow log path (\"/data/db2log/DB1/logs\")"

PLease refer to my last time blog at http://linuxtechres.blogspot.com/2010/07/how-to-onlineoffline-backup-and-restore.html

4. backup those tables on production database first in case they are destroyed during import process


# more backup.sh
cd /db2/db2inst1/scripts/20110420_restore/backup
db2 connect to db1
db2 set schema = jephe
db2 "export to \"./table1\" of del messages \"./table1.msg\" select * from table1"
db2 "export to \"./table2\" of del messages \"./table2.msg\" select * from table2"
db2 "export to \"./table3\" of del messages \"./table3.msg\" select * from table3"
db2 terminate

5. extract those deleted data first from restored DR database server


# more extract.sh
cd /home/db2inst1
db2 connect to db1dr
db2 set schema = jephe
db2 "export to \"./table1\" of del messages \"./table1.msg\" select * FROM table1 WHERE balabala-same statement used during deletion"
db2 "export to \"./table2\" of del messages \"./table2.msg\" select * FROM table2 WHERE balabala-same statement used during deletion"
db2 "export to \"./table3\" of del messages \"./table3.msg\" select * FROM table3 where balabala-same statement used during deletion"   

6. import back to the production database (the sequence for importing might be different from the original deletion sequence as it might depends on foreign key or something)

# more import.sh
cd /db2/db2inst1/scripts/20110420_restore/
db2 connect to db1
db2 set schema = jephe
db2 "import from \"./table3\" of del messages \"./table1.imp\" insert into table1"
db2 "import from \"./table2\" of del messages \"./table2.imp\" insert into table2"
db2 "import from \"./table1\" of del messages \"./table3.imp\" insert into table3"
db2 terminate