db2 恢复误删表
oracle如果误删了表，可以很方便的flash back
最重要的是不会影响业务
但是如果你在DB2中招，就目前来说，你就不那么走运了
--首先设置归档模式[db2inst2@localhost ~]$ db2 update db cfg using logretain onDB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.SQL1363W One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, all applications must disconnect from this database before the changes become effective.[db2inst2@localhost ~]$ db2 get db cfg | grep -i logre Log retain for recovery enabled (LOGRETAIN) = RECOVERY First log archive method (LOGARCHMETH1) = LOGRETAIN--然后理所当然的full backup一次[db2inst2@localhost ~]$ db2 connect to sampleSQL1116N A connection to or activation of database "SAMPLE" cannot be made because of BACKUP PENDING. SQLSTATE=57019[db2inst2@localhost ~]$ db2 backup db sampleBackup successful. The timestamp for this backup image is : 20110424143713--然后确认表空间是DROP_RECOVERY的select TBSPACE, DROP_RECOVERY from SYSCAT.TABLESPACESTBSPACE DROP_RECOVERY-------------------------------------------------------- -------------SYSCATSPACE N TEMPSPACE1 N USERSPACE1 Y SYSTOOLSPACE Y --否则可以使用以下语句打开：ALTER TABLESPACE <tablespace-name> DROPPED TABLE RECOVERY ON--建立测试表--T3表是准备被模拟误drop，并进行恢复的--T4表是模拟其他不相关的表，看恢复操作是否有影响[db2inst2@localhost ~]$ db2 "create table T3 (C1 INT)"DB20000I The SQL command completed successfully.[db2inst2@localhost ~]$ db2 "create table T4 (C1 INT)"DB20000I The SQL command completed successfully.[db2inst2@localhost ~]$ db2 "insert into t3 values (333)"DB20000I The SQL command completed successfully.[db2inst2@localhost ~]$ db2 "select * from t3"C1 ----------- 333--开始模拟误删除[db2inst2@localhost ~]$ db2 drop table t3DB20000I The SQL command completed successfully.[db2inst2@localhost ~]$ db2 "select * from t3"SQL0204N "DB2INST2.T3" is an undefined name. SQLSTATE=42704--这时候模拟在发现前，t4表的业务继续[db2inst2@localhost ~]$ db2 "insert into t4 values (444)"DB20000I The SQL command completed successfully.[db2inst2@localhost ~]$ db2 "select * from t4"C1 ----------- 444 1 record(s) selected.--使用list history看到刚刚删除的表[db2inst2@localhost ~]$ db2 list history dropped table all for sample List History File for sampleNumber of matching file entries = 1 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- ------------------ ---- --- ------------ ------------ -------------- D T 20110424193315 000000000000521200020014 ---------------------------------------- "DB2INST2"."T3" resides in 1 tablespace(s): 00001 USERSPACE1 ---------------------------------------- Comment: DROP TABLE Start Time: 20110424193315 End Time: 20110424193315 Status: A ---------------------------------------- EID: 33 DDL: CREATE TABLE "DB2INST2"."T3" ( "C1" INTEGER ) IN "USERSPACE1" ; ------------------------------------------记住backup id,稍后有用 000000000000521200020014--开始恢复--先restore[db2inst2@localhost ~]$ db2 restore db 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.--开始rollforward,关键的一步[db2inst2@localhost ~]$ db2 rollforward db sample to end of logs and stop recover dropped table 000000000000521200020014 to /home/db2inst2 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 - S0000003.LOG Last committed transaction = 2011-04-24-11.34.03.000000 UTCDB20000I The ROLLFORWARD command completed successfully.--这时候看到指定目录下多了一个以节点为名字的目录，里面有名为data的一个文件[db2inst2@localhost ~]$ ll NODE0000/total 4-rw-r----- 1 db2inst2 db2iadm1 4 Apr 24 19:40 data[db2inst2@localhost ~]$ pwd/home/db2inst2--开始重建表t3--先建表结构，使用list history里面的DDL[db2inst2@localhost ~]$ db2 connect to sample Database Connection Information Database server = DB2/LINUX 9.7.2 SQL authorization ID = DB2INST2 Local database alias = SAMPLE[db2inst2@localhost ~]$ db2 "CREATE TABLE "DB2INST2"."T3" ( "C1" INTEGER ) IN "USERSPACE1""DB20000I The SQL command completed successfully.[db2inst2@localhost ~]$ db2[db2inst2@localhost ~]$ db2 "select * from t3"C1 ----------- 0 record(s) selected.--看到现在是没有数据的--然后使用刚才rollforward出来的data import[db2inst2@localhost ~]$ db2 import from /home/db2inst2/NODE0000/data of del insert into t3SQL3109N The utility is beginning to load data from file "/home/db2inst2/NODE0000/data".SQL3110N The utility has completed processing. "1" rows were read from the input file.SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".SQL3222W ...COMMIT of any database changes was successful.SQL3149N "1" rows were processed from the input file. "1" rows were successfully inserted into the table. "0" rows were rejected.Number of rows read = 1Number of rows skipped = 0Number of rows inserted = 1Number of rows updated = 0Number of rows rejected = 0Number of rows committed = 1[db2inst2@localhost ~]$ db2 "select * from t3"C1 ----------- 333 1 record(s) selected.--看到数据回来了--因为是end of logs，所以T4的数据应该也是最新的[db2inst2@localhost ~]$ db2 "select * from t4"C1 ----------- 444 1 record(s) selected.--完成
