db2恢复误drop的表

lb5645284

--先备份数据库
[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