实战演练

zhuhichn

INCREMENTAL增量备份和恢复

增量备份只有在中大型，每天有很多更新的数据库中才能体现其价值。
这里只演示其用法。
INCREMENTAL的增量恢复需要
全备份+INCREMENTAL增量备份+增量备份后的LOG。

1.增量备份需要启动归档日志和设置TRACKMOD
为了看清归档日志状况，可以把归档日志放在单独的磁盘里
CMD: db2 connect to sample
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.1
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE
CMD: db2 update db cfg using LOGARCHMETH1 DISK:/mnt/hgfs/F/ARCHIVELOG/
CMD: db2 update db cfg using TRACKMOD ON
CMD: db2 get db cfg
（其余内容忽略）
       Database Configuration for Database
 First log archive method                 (LOGARCHMETH1) = DISK:/mnt/hgfs/F/ARCHIVELOG/
 Track modified pages                         (TRACKMOD) = ON

2。数据库备份
CMD: db2 backup database sample to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100628203915

3。创建测试用表并插入数据
CMD: db2 connect to sample
CMD: db2 "create table TEST_INCREMENTAL (ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE), MSG CHAR(20))"
db2 "insert into TEST_INCREMENTAL (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
CMD: db2 "insert into TEST_INCREMENTAL (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
CMD: db2 "select * from test_incremental"
ID          MSG
----------- --------------------
          1 db2china.net
          2 db2china.net
          3 db2china.net
          4 db2china.net
          5 db2china.net
          6 db2china.net
  6 record(s) selected.

4。在线增量备份。
CMD: db2 backup database sample online incremental to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100628204243

5。再插入数据
CMD: db2 connect to sample
CMD: db2 "insert into TEST_INCREMENTAL (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
DB20000I  The SQL command completed successfully.
CMD: db2 "select * from test_incremental"
ID          MSG
----------- --------------------
          1 db2china.net
          2 db2china.net
          3 db2china.net
          4 db2china.net
          5 db2china.net
          6 db2china.net
          7 db2china.net
          8 db2china.net
          9 db2china.net
  9 record(s) selected.

6。故障，误删除表空间
CMD: rm -fR db2inst1/NODE0000/SAMPLE/*
CMD: ls db2inst1/NODE0000/SAMPLE/
CMD:db2 connect to sample
SQL0293N  Error accessing a table space container.  SQLSTATE=57048

6。数据库自动增量恢复
CMD: db2 "restore db sample INCREMENTAL AUTOMATIC from /mnt/hgfs/F/backup/ taken at 20100628204243"
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.

7。恢复后数据库处在前滚恢复状态(ROLL-FORWARD PENDING)
CMD: db2 connect to sample
SQL1117N  A connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

8。可以恢复到日志结尾,全部数据恢复成功
CMD: db2 rollforward database sample to end of logs and stop

                                 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                    = S0000003.LOG - S0000006.LOG
 Last committed transaction             = 2010-06-28-12.47.53.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
CMD: db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.1
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

CMD: db2 "select * from TEST_INCREMENTAL"

ID          MSG
----------- --------------------
          1 db2china.net
          2 db2china.net
          3 db2china.net
          4 db2china.net
          5 db2china.net
          6 db2china.net
          7 db2china.net
          8 db2china.net
          9 db2china.net

  9 record(s) selected.

9.也可以恢复到备份时候的状态
CMD: db2 "restore db sample INCREMENTAL AUTOMATIC from /mnt/hgfs/F/backup/ taken at 20100628204243"
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.
CMD: db2 rollforward database sample to end of backup and stop

                                 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                    = S0000003.LOG - S0000005.LOG
 Last committed transaction             = 2010-06-28-12.42.48.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
CMD: db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.1
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

CMD: db2 "select * from TEST_INCREMENTAL"

ID          MSG
----------- --------------------
          1 db2china.net
          2 db2china.net
          3 db2china.net
          4 db2china.net
          5 db2china.net
          6 db2china.net

  6 record(s) selected.