db2 表被误删（drop）

确定开启归档
确认表空间为drop_recovery
--确认表空间是drop_recovery的状态
select tbspace ,drop_recovery from syscat.tablespaces;
--若不是，可以使用以下语句打开
alter tablespace tablespace_name dropped table recovery on
[db2inst2@baktest130 shell]$ ./cms_backup_online.sh 


Backup successful. The timestamp for this backup image is : 20150624140258


db2 => select count(1) from testa


1          
-----------
       1525


  1 record(s) selected.


db2 => select count(1) from testb


1          
-----------
       1525


  1 record(s) selected.


db2 => select count(1) from testc
SQL0204N  "DB2INST2.TESTC" is an undefined name.  SQLSTATE=42704
db2 => create table testc like testa
DB20000I  The SQL command completed successfully.
db2 => insert into testc select * from testa
DB20000I  The SQL command completed successfully.
db2 => commit
DB20000I  The SQL command completed successfully.


db2 => list history dropped table all for cms 


                    List History File for cms


Number of matching file entries = 10


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  D  T  20150624142536                                        00000000e90015420002080f 
 ----------------------------------------------------------------------------
  "DB2INST2"."TESTA" resides in 1 tablespace(s):


 00001 USERSPACE1
 ----------------------------------------------------------------------------
    Comment: DROP TABLE
 Start Time: 20150624142536
   End Time: 20150624142536
     Status: A
 ----------------------------------------------------------------------------
  EID: 934


 DDL: CREATE TABLE "DB2INST2"."TESTA" ( …… )  IN "USERSPACE1" ;
 查看backup id
 
 开始恢复误删的表testa
 
 [db2inst2@baktest130 backup_dir]$ db2 restore db cms taken at 20150624140258
SQL2539W  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) y
DB20000I  The RESTORE DATABASE command completed successfully.


[db2inst2@baktest130 backup_dir]$ db2 "rollforward db cms to end of logs and stop recover dropped table 00000000e90015420002080f to /home/db2inst2"


                                 Rollforward Status


 Input database alias                   = cms
 Number of nodes have returned status   = 1


 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000565.LOG - S0000566.LOG
 Last committed transaction             = 2015-06-24-14.25.37.000000 Local


DB20000I  The ROLLFORWARD command completed successfully.


查看在指定目录下 /home/db2inst2 下新建了一个NODE0000文件夹，其下有个data文件


此时查看，testc表存在，testa不存在
db2 => select count(1) from testc


1          
-----------
       1525


  1 record(s) selected.


db2 => select count(1) from testa
SQL0204N  "DB2INST2.TESTA" is an undefined name.  SQLSTATE=42704
db2 => 


根据 db2 list history dropped table all for cms，查看误删的表testa的表结构，重建testa表，然后将data文件的内容import到testa中
--执行创建表testa后
db2 => select count(1) from testa


1          
-----------
          0


  1 record(s) selected.
--import表
--由于未指定字符集，默认导入后，有120条数据无法导入，且中文字符为乱码
db2 => import from /home/db2inst2/NODE0000/data of del insert into testa
SQL3149N  "1525" rows were processed from the input file.  "1405" rows were 
successfully inserted into the table.  "120" rows were rejected.




Number of rows read         = 1525
Number of rows skipped      = 0
Number of rows inserted     = 1405
Number of rows updated      = 0
Number of rows rejected     = 120
Number of rows committed    = 1525
--再次进行导入，增加modified by codepage=1386，查看导入成功
[db2inst2@baktest130 backup_dir]$ db2 import from /home/db2inst2/NODE0000/data of del modified by codepage=1386 insert into testa 
SQL3109N  The utility is beginning to load data from file 
"/home/db2inst2/NODE0000/data".


SQL3110N  The utility has completed processing.  "1525" rows were read from 
the input file.


SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1525".


SQL3222W  ...COMMIT of any database changes was successful.


SQL3149N  "1525" rows were processed from the input file.  "1525" rows were 
successfully inserted into the table.  "0" rows were rejected.




Number of rows read         = 1525
Number of rows skipped      = 0
Number of rows inserted     = 1525
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 1525


codepage=1386 为GBK,1208 为UTF-8。


如果含有中文字符，可能需要设置字符集，否则可能导入数据会出问题
db2的表误删或数据误删没有Oracle灵活，短期内也可以使用闪回恢复。


对于误删数据（delete）大约需要将备份在异机还原，然后rollforward到指定时间点，再将相应的数据导出、导入

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28258625/viewspace-1710731/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28258625/viewspace-1710731/