db2 恢复误删表
oracle如果误删了表，可以很方便的flash back最重要的是不会影响业务
但是如果你在DB2中招，就目前来说，你就不那么走运了
--首先设置归档模式
$ db2 update db cfg using logretain on
DB20000IThe UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363WOne 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.
$ db2 get db cfg | grep -i logre
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
First log archive method (LOGARCHMETH1) = LOGRETAIN
--然后理所当然的full backup一次
$ db2 connect to sample
SQL1116NA connection to or activation of database "SAMPLE" cannot be made
because of BACKUP PENDING.SQLSTATE=57019
$ db2 backup db sample
Backup successful. The timestamp for this backup image is : 20110424143713
--然后确认表空间是DROP_RECOVERY的
select TBSPACE, DROP_RECOVERY from SYSCAT.TABLESPACES
TBSPACE DROP_RECOVERY
-------------------------------------------------------------------------------------------------------------------------------- -------------
SYSCATSPACE N
TEMPSPACE1 N
USERSPACE1 Y
SYSTOOLSPACE Y
--否则可以使用以下语句打开：
ALTER TABLESPACE <tablespace-name> DROPPED TABLE RECOVERY ON
--建立测试表
--T3表是准备被模拟误drop，并进行恢复的
--T4表是模拟其他不相关的表，看恢复操作是否有影响
$ db2 "create table T3 (C1 INT)"
DB20000IThe SQL command completed successfully.
$ db2 "create table T4 (C1 INT)"
DB20000IThe SQL command completed successfully.
$ db2 "insert into t3 values (333)"
DB20000IThe SQL command completed successfully.
$ db2 "select * from t3"
C1
-----------
333
--开始模拟误删除
$ db2 drop table t3
DB20000IThe SQL command completed successfully.
$ db2 "select * from t3"
SQL0204N"DB2INST2.T3" is an undefined name.SQLSTATE=42704
--这时候模拟在发现前，t4表的业务继续
$ db2 "insert into t4 values (444)"
DB20000IThe SQL command completed successfully.
$ db2 "select * from t4"
C1
-----------
444
1 record(s) selected.
--使用list history看到刚刚删除的表
$ db2 list history dropped table all for sample
List History File for sample
Number of matching file entries = 1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current LogBackup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
DT20110424193315 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
$ db2 restore db sample
SQL2539WWarning!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
DB20000IThe RESTORE DATABASE command completed successfully.
--开始rollforward,关键的一步
$ 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 UTC
DB20000IThe ROLLFORWARD command completed successfully.
--这时候看到指定目录下多了一个以节点为名字的目录，里面有名为data的一个文件
$ ll NODE0000/
total 4
-rw-r----- 1 db2inst2 db2iadm1 4 Apr 24 19:40 data
$ pwd
/home/db2inst2
--开始重建表t3
--先建表结构，使用list history里面的DDL
$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUX 9.7.2
SQL authorization ID = DB2INST2
Local database alias = SAMPLE
$ db2 "CREATE TABLE "DB2INST2"."T3" ( "C1" INTEGER )IN "USERSPACE1""
DB20000IThe SQL command completed successfully.
$ db2
$ db2 "select * from t3"
C1
-----------
0 record(s) selected.
--看到现在是没有数据的
--然后使用刚才rollforward出来的data import
$ db2 import from /home/db2inst2/NODE0000/data of del insert into t3
SQL3109NThe utility is beginning to load data from file
"/home/db2inst2/NODE0000/data".
SQL3110NThe 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 = 1
Number of rows skipped = 0
Number of rows inserted = 1
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1
$ db2 "select * from t3"
C1
-----------
333
1 record(s) selected.
--看到数据回来了
--因为是end of logs，所以T4的数据应该也是最新的
$ db2 "select * from t4"
C1
-----------
444
1 record(s) selected.
--完成
页:
[1]