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到指定時間點，再將相應的資料匯出、匯入