db2恢复误drop的表

--先备份数据库
[db2inst1@server1 ~]$ db2 backup db sample to /tmp

Backup successful. The timestamp for this backup image is : 20101117125927

--分别建立t1,t2两个表：
[db2inst1@server1 ~]$ d "create table scott.t1(a int,b varchar(10))"
DB20000I  The SQL command completed successfully.
[db2inst1@server1 ~]$ d "create table scott.t2(a int)"
DB20000I  The SQL command completed successfully.
[db2inst1@server1 ~]$ d "insert into scott.t1 values (1,'aaa')"
DB20000I  The SQL command completed successfully.
[db2inst1@server1 ~]$ d "insert into scott.t1 values (1,'aaa')"
DB20000I  The SQL command completed successfully.
[db2inst1@server1 ~]$ d "commit"
DB20000I  The SQL command completed successfully.

[db2inst1@server1 ~]$ d "insert into scott.t2 values (123)"
DB20000I  The SQL command completed successfully.
[db2inst1@server1 ~]$ d "insert into scott.t2 values (111)"
DB20000I  The SQL command completed successfully.
[db2inst1@server1 ~]$ d "insert into scott.t2 values (222)"
DB20000I  The SQL command completed successfully.
[db2inst1@server1 ~]$ select count
[db2inst1@server1 ~]$ d "select * from scott.t2"

A          
-----------
        123
        111
        222

  3 record(s) selected.

--drop t2表
[db2inst1@server1 ~]$ d "drop table scott.t2"
DB20000I  The SQL command completed successfully.
[db2inst1@server1 ~]$ d "select * from scott.t2"
SQL0204N  "SCOTT.T2" is an undefined name.  SQLSTATE=42704

--恢复数据库
db2 => restore db sample from /tmp/ taken at 20101117125927 into sample
SQL2539W  Warning!  Restoring to an existing database that is the same as the 
backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

--现在数据库处于ROLL-FORWARD PENDING状态
db2 => connect to sample
SQL1117N  A connection to or activation of database "SAMPLE" cannot be made 
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

--开始恢复
--首先查看drop表的历史
db2 => list history dropped table all for db sample

                    List History File for sample

Number of matching file entries = 2


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  D  T  20101117134249                                        000000000000dc2500040004 
 ----------------------------------------------------------------------------
  "SCOTT   "."T1" resides in 1 tablespace(s):

  00001 TS1                                                                   
 ----------------------------------------------------------------------------
    Comment: DROP TABLE                                                       
 Start Time: 20101117134249
   End Time: 20101117134249
     Status: A
 ----------------------------------------------------------------------------
  EID: 26

 DDL: CREATE TABLE "SCOTT   "."T1" ( "A" INTEGER , "B" VARCHAR(10) )  IN "TS1" ;         
 ----------------------------------------------------------------------------

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  D  T  20101117134550                                        000000000000ff2500040005 
 ----------------------------------------------------------------------------
  "SCOTT   "."T2" resides in 1 tablespace(s):

  00001 TS1                                                                   
 ----------------------------------------------------------------------------
    Comment: DROP TABLE                                                       
 Start Time: 20101117134550
   End Time: 20101117134550
     Status: A
 ----------------------------------------------------------------------------
  EID: 27

 DDL: CREATE TABLE "SCOTT   "."T2" ( "A" INTEGER )  IN "TS1" ;   
 ----------------------------------------------------------------------------


--roll forward数据库
db2 => rollforward db sample to end of logs and stop recover dropped table 000000000000ff2500040005 to /tmp/

                                 Rollforward Status

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

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000000.LOG - S0000001.LOG
 Last committed transaction             = 2010-11-17-05.45.50.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.


--看看roll出来的数据
[db2inst1@server1 /tmp/NODE0000]$ cat data 
123
111
222

--现在t1已经回来，但是没有t2:
db2 => connect to sample

   Database Connection Information

 Database server        = DB2/LINUX 9.5.2
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2 => select * from scott.t1

A           B         
----------- ----------
          1 aaa       
          1 aaa       

  2 record(s) selected.


db2 => select * from scott.t2  
SQL0204N  "SCOTT.T2" is an undefined name.  SQLSTATE=42704

--根据list history dropped table里面的ddl重建t2：
db2 => CREATE TABLE "SCOTT   "."T2" ( "A" INTEGER )  IN "TS1"           
DB20000I  The SQL command completed successfully.

--开始倒回表的数据
db2 => import from /tmp/NODE0000/data of del insert into scott.t2 
SQL3109N  The utility is beginning to load data from file 
"/tmp/NODE0000/data".

SQL3110N  The utility has completed processing.  "3" rows were read from the 
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "3".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "3" rows were processed from the input file.  "3" rows were 
successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 3
Number of rows skipped      = 0
Number of rows inserted     = 3
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 3

--检查结果
db2 => select * from scott.t2

A          
-----------
        123
        111
        222

  3 record(s) selected.


--ok