Technical Blog Post
Abstract
75 ways to demystify DB2 #44: Techtip : How to recover multiple dropped tables
Body
Abstract:
In some circumstances, there is a requirement to recover multiple dropped tables. DB2 provides a function in rollforward command which can help to recover a single dropped table in one command. In this article we present a hands-on exercise to illustrate how to achieve this goal.
Here is the scenario:
Suppose the tables(t1,t2) are dropped at time T1< T2. In rollforward command, using rollfoward to PIT without the complete option to T1+1 for t1 and T2+1 for t2.
There is a backup image before dropping the tables t1 and t2.
How to:
1. Restore database from the backup image.
db2 restore db hadr97 from /TMP/zxianing taken at 20120224014315
2. Identify the dropped table IDs and corresponding DDLs from list history command. Pay attention to the Backup ID and Timestamp.
$ db2 list history dropped table all for hadr97
List History File for hadr97
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20120224014916 0000000000014c8a00020004
----------------------------------------------------------------------------
"ZXIANING"."T1" resides in 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20120224014916
End Time: 20120224014916
Status: A
----------------------------------------------------------------------------
EID: 19
DDL: CREATE TABLE "ZXIANING"."T1" ( "C1" INTEGER ) IN "USERSPACE1" ;
----------------------------------------------------------------------------
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20120224015031 0000000000014ca300020005
----------------------------------------------------------------------------
"ZXIANING"."T2" resides in 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20120224015031
End Time: 20120224015031
Status: A
----------------------------------------------------------------------------
EID: 20
DDL: CREATE TABLE "ZXIANING"."T2" ( "C1" INTEGER ) IN "USERSPACE1" ;
----------------------------------------------------------------------------
3. Rollforward to PIT specifying the time with one second ahead to the timestamp when the table was dropped. Note that do not specify the complete or stop option in rollforward command before recovering all the tables. The recovered table data will be exported.
db2 "rollforward db hadr97 to 2012-02-24.01.49.17 using local time recover dropped table 0000000000014c8a00020004 to $HOME/t1"
Rollforward Status
Input database alias = hadr97
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000006.LOG
Log files processed = -
Last committed transaction = 2012-02-24-01.49.17.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
db2 "rollforward db hadr97 to 2012-02-24.01.50.32 using local time recover dropped table 0000000000014ca300020005 to $HOME/t2"
Rollforward Status
Input database alias = hadr97
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000006.LOG
Log files processed = -
Last committed transaction = 2012-02-24-01.50.31.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
4. After recovered all the tables and specify the complete option to complete the command.
db2 "rollforward db hadr97 to end of logs and complete"
Rollforward Status
Input database alias = hadr97
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000006.LOG - S0000006.LOG
Last committed transaction = 2012-02-24-01.51.15.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
5. Recreate the dropped tables.
db2 "CREATE TABLE "ZXIANING"."T1" ( "C1" INTEGER ) IN "USERSPACE1""
db2 "import from ./data of del insert into zxianing.t1"
Number of rows read = 4
Number of rows skipped = 0
Number of rows inserted = 4
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 4
db2 "CREATE TABLE "ZXIANING"."T2" ( "C1" INTEGER ) IN "USERSPACE1""
db2 "import from ./data of del insert into zxianing.t2"
Number of rows read = 4
Number of rows skipped = 0
Number of rows inserted = 4
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 4
UID
ibm11140958