db2恢复误drop的表
--先备份数据库$ db2 backup db sample to /tmpBackup successful. The timestamp for this backup image is : 20101117125927--分别建立t1,t2两个表：$ d "create table scott.t1(a int,b varchar(10))"DB20000IThe SQL command completed successfully.$ d "create table scott.t2(a int)"DB20000IThe SQL command completed successfully.$ d "insert into scott.t1 values (1,'aaa')"DB20000IThe SQL command completed successfully.$ d "insert into scott.t1 values (1,'aaa')"DB20000IThe SQL command completed successfully.$ d "commit"DB20000IThe SQL command completed successfully.$ d "insert into scott.t2 values (123)"DB20000IThe SQL command completed successfully.$ d "insert into scott.t2 values (111)"DB20000IThe SQL command completed successfully.$ d "insert into scott.t2 values (222)"DB20000IThe SQL command completed successfully.$ select count$ d "select * from scott.t2"A ----------- 123 111 2223 record(s) selected.--drop t2表$ d "drop table scott.t2"DB20000IThe SQL command completed successfully.$ d "select * from scott.t2"SQL0204N"SCOTT.T2" is an undefined name.SQLSTATE=42704--恢复数据库db2 => restore db sample from /tmp/ taken at 20101117125927 into sampleSQL2539WWarning!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) yDB20000IThe RESTORE DATABASE command completed successfully.--现在数据库处于ROLL-FORWARD PENDING状态db2 => connect to sampleSQL1117NA 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 LogBackup ID -- --- ------------------ ---- --- ------------ ------------ --------------DT20101117134249 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 LogBackup ID -- --- ------------------ ---- --- ------------ ------------ --------------DT20101117134550 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 UTCDB20000IThe ROLLFORWARD command completed successfully.--看看roll出来的数据$ 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.t2SQL0204N"SCOTT.T2" is an undefined name.SQLSTATE=42704--根据list history dropped table里面的ddl重建t2：db2 => CREATE TABLE "SCOTT "."T2" ( "A" INTEGER )IN "TS1" DB20000IThe SQL command completed successfully.--开始倒回表的数据db2 => import from /tmp/NODE0000/data of del insert into scott.t2 SQL3109NThe utility is beginning to load data from file "/tmp/NODE0000/data".SQL3110NThe 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 2223 record(s) selected.--ok
页:
[1]