实验实录
******创建误删除表的环境*********
db2inst4@DB2@ONE ~]$ db2 "create table t3 (id integer not null,name char(15))"
DB20000I The SQL command completed successfully.
[db2inst4@DB2@ONE ~]$ db2 "insert into t3 values(1,'Wang'),(2,'Lee'),(3,'Zhang'),(4,'Zhou')"
DB20000I The SQL command completed successfully.select * from t3"
ID NAME
----------- ---------------
1 Wang
2 Lee
3 Zhang
4 Zhou
4 record(s) selected.
[db2inst4@DB2@ONE ~]$ db2 "backup db db1 tablespace(userspace1) online to /home/backup"
Backup successful. The timestamp for this backup image is : 20130606002148
[db2inst4@DB2@ONE ~]$ db2 drop table t3
DB20000I The SQL command completed successfully.
******开始恢复误删除表*********
[db2inst4@DB2@ONE ~]$ db2 "list history dropped table all for db db1"
List History File for db1
----------------------------------------------------------------------------
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
D T 20130606002250 000000000000901000020006
----------------------------------------------------------------------------
"DB2INST4"."T3" resides in 1 tablespace(s):
00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DROP TABLE
Start Time: 20130606002250
End Time: 20130606002250
Status: A
----------------------------------------------------------------------------
EID: 48
DDL: CREATE TABLE "DB2INST4"."T3" ( "ID" INTEGER NOT NULL , "NAME" CHAR(15) ) IN "USERSPACE1" ;
----------------------------------------------------------------------------
[db2inst4@DB2@ONE ~]$ db2 "restore db db1 tablespace(userspace1) online from /home/backup taken at 20130606002148 "
DB20000I The RESTORE DATABASE command completed successfully.
[db2inst4@DB2@ONE ~]$ db2 "rollforward db db1 to end of logs and stop tablespace(userspace1) recover dropped table 000000000000901000020006 to /home/wjc "
Rollforward Status
Input database alias = db1
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 1970-01-01-00.00.00.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst4@DB2@ONE NODE0000]$ pwd
/home/wjc/NODE0000
[db2inst4@DB2@ONE NODE0000]$ more data
1,"Wang "
2,"Lee "
3,"Zhang "
4,"Zhou "
[db2inst4@DB2@ONE NODE0000]$ db2 "CREATE TABLE "DB2INST4"."T3" ( "ID" INTEGER NOT NULL , "NAME" CHAR(15) ) IN "USERSPACE1" "
DB20000I The SQL command completed successfully.
[db2inst4@DB2@ONE NODE0000]$ db2 "load from data of del insert into t3 copy yes to /home/backup"
SQL3109N The utility is beginning to load data from file
"/home/wjc/NODE0000/data".
SQL3500W The utility is beginning the "LOAD" phase at time "06/06/2013
00:31:18.378503".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "4" rows were read from the
input file.
SQL3519W Begin Load Consistency Point. Input record count = "4".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "06/06/2013
00:31:18.441130".
Number of rows read = 4
Number of rows skipped = 0
Number of rows loaded = 4
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 4
[db2inst4@DB2@ONE NODE0000]$ db2 "select * from t3"
ID NAME
----------- ---------------
1 Wang
2 Lee
3 Zhang
4 Zhou
添加新评论12 条评论
2013-12-19 09:48
2013-12-19 09:47
2013-12-19 09:30
2013-12-19 09:28
2013-12-18 16:49
2013-12-18 16:40
2013-06-26 15:22
2013-06-17 18:20
如果没有备份,估计只能删除前 先把表备份了 如果出问题了 再还原吧
2013-06-13 08:53
2013-06-09 17:01
2013-06-07 00:22
2013-06-06 15:51