db2恢复误drop的表

guoyanxi

--先备份数据库[db2inst1@server1 ~]$ db2 backup db sample to /tmpBackup 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 sampleSQL2539W  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) yDB20000I  The RESTORE DATABASE command completed successfully.--现在数据库处于ROLL-FORWARD PENDING状态db2 => connect to sampleSQL1117N  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 sampleNumber 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 UTCDB20000I  The ROLLFORWARD command completed successfully.--看看roll出来的数据[db2inst1@server1 /tmp/NODE0000]$ cat data 123111222--现在t1已经回来，但是没有t2:db2 => connect to sample   Database Connection Information Database server        = DB2/LINUX 9.5.2 SQL authorization ID   = DB2INST1 Local database alias   = SAMPLEdb2 => select * from scott.t1A           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         = 3Number of rows skipped      = 0Number of rows inserted     = 3Number of rows updated      = 0Number of rows rejected     = 0Number of rows committed    = 3--检查结果db2 => select * from scott.t2A          -----------        123        111        222  3 record(s) selected.--ok