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