DB2误删表的恢复

实例db2inst1下的testdb数据库其中TS2表空间有2张表tt1和tt2，在一次全备份后，
误删除了tt2表，如何恢复tt2表，且能不影响和丢失tt1和其他表空间的数据呢？
第一：循环日志模式，直接恢复到全备份时间点，那么则丢失备份点后的全部数据；
第二：归档日志模式，数据库恢复+rollforward，恢复数据库到备份时间点，并且
前滚到tb2删除时间前，那么则丢失tb2表删除时间点后的数据；
第三：归档日志模式下的，表空间恢复+rollforward，但表空间的恢复最小的时间点是系统目录
对表空间或其中表最后一次更新操作时间，也就是表空间或其中表最后的一次DDL语句
(create/alter/delete等)时间，所以上面情况表空间最多只能rollforward到tt2表删除后
的时间点，所以表空间恢复+rollforward不能帮助恢复tt2表。https://www.cndba.cn/hbhe0316/article/4808https://www.cndba.cn/hbhe0316/article/4808

1.创建ts2表空间

db2 "create tablespace ts2 managed by database using (file '/db2data/datafile/ts2file' 100M)"

2.在表空间ts2中创建tt1和tt2表两张表https://www.cndba.cn/hbhe0316/article/4808

https://www.cndba.cn/hbhe0316/article/4808

db2 "create table tt1(id int, name varchar(10)) in ts2"
db2 "create table tt2(id int, name varchar(10)) in ts2"
db2 "insert into tt1 values (1, 'aaa'),(2, 'bbb'),(3, 'ccc')"
db2 "insert into tt2 values (1, 'aaa'),(2, 'bbb'),(3, 'ccc')"

3.可以在syscat.tables中查看表属于哪个表空间

db2 "select tabname, tbspace from syscat.tables"|grep -E '^TT'

4.打开表空间的dropped table recovery，默认是打开的，如果没打开，需要打开如下

db2 "alter tablespace tt2 dropped table recovery on "

5.发起online备份

https://www.cndba.cn/hbhe0316/article/4808

db2 "backup db testdb online to /db2data/backup include logs"

6.往tt2表中加入内容，然后删除该表

db2 connect to testdb
db2 "insert into tt2 values (4, 'ddd'),(5, 'eee'),(6, 'fff')"
db2 drop table tt2
db2 list tables

7.往tt1中加入数据https://www.cndba.cn/hbhe0316/article/4808

db2 "insert into tt1 values (4, 'ddd'),(5, 'eee'),(6, 'fff')"

8.查看历史文件中关于dropped table的信息， 获取误删表的定义和 Backup ID

#db2 list history dropped table all for testdb

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  D  T  20190913181451                                        000000000000610400030005 
 ----------------------------------------------------------------------------
  "DB2INST1"."TT2" resides in 1 tablespace(s):

 00001 TS2
 ----------------------------------------------------------------------------
    Comment: DROP TABLE
 Start Time: 20190913181451
   End Time: 20190913181451
     Status: A
 ----------------------------------------------------------------------------
  EID: 22

 DDL: CREATE TABLE "DB2INST1"."TT2" ( "ID" INTEGER , "NAME" VARCHAR(10 OCTETS) )  IN "TS2"              ORGANIZE BY ROW;         
 ----------------------------------------------------------------------------

9.从备份里面恢复单个表空间ts2，这个20190913180932是备份的时间戳https://www.cndba.cn/hbhe0316/article/4808

[db2inst1@db04 backup]$ db2 "restore db testdb tablespace(ts2) taken at 20190913180932"
DB20000I  The RESTORE DATABASE command completed successfully.

10.前滚表空间时指定从上面获取到的backup id，并把表数据导出到/db2data/ts2backup目录下https://www.cndba.cn/hbhe0316/article/4808

[db2inst1@db04 db2data]$ db2 "rollforward db testdb to end of logs tablespace(ts2) recover dropped table 000000000000610400030005 to /db2data/ts2backup"

                                 Rollforward Status

 Input database alias                   = testdb
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    =  -
 Last committed transaction             = 2019-09-13-10.15.44.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@db04 db2data]$

11.前滚结束后，查看tt1表https://www.cndba.cn/hbhe0316/article/4808

[db2inst1@db04 db2data]$ db2 connect to testdb

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.1
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB

 [db2inst1@db04 db2data]$ db2 "select * from tt1"

ID          NAME      
----------- ----------
          1 aaa       
          2 bbb       
          3 ccc       
          4 ddd       
          5 eee       
          6 fff       

  6 record(s) selected.

12.由上面的获取tt2的表定义https://www.cndba.cn/hbhe0316/article/4808

[db2inst1@db04 NODE0000]$ cat tt2.ddl 
CREATE TABLE "DB2INST1"."TT2" ( "ID" INTEGER , "NAME" VARCHAR(10 OCTETS) )  IN "TS2";
[db2inst1@db04 NODE0000]$ db2 -tvf tt2.ddl 
CREATE TABLE "DB2INST1"."TT2" ( "ID" INTEGER , "NAME" VARCHAR(10 OCTETS) )  IN "TS2"
DB20000I  The SQL command completed successfully.
[db2inst1@db04 NODE0000]$ db2 "import from /db2data/ts2backup/NODE0000/data of del insert into tt2"
SQL3109N  The utility is beginning to load data from file 
"/db2data/ts2backup/NODE0000/data".

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

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

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

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


Number of rows read         = 6
Number of rows skipped      = 0
Number of rows inserted     = 6
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 6
[db2inst1@db04 NODE0000]$ db2 "select * from tt2"

ID          NAME      
----------- ----------
          1 aaa       
          2 bbb       
          3 ccc       
          4 ddd       
          5 eee       
          6 fff       

  6 record(s) selected.

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle