DB2备份与恢复

原创 zhou 2024-08-06

220

DB2备份与恢复

DB2提供全量备份，增量备份，差异备份等多种备份方式

备份需要权限：必须具有 SYSADM、SYSCTRL 或 SYSMAINT 权限才能使用 BACKUP 实用程序

备份兼容性：备份与一些操作不兼容

ONLINE CREATE INDEX

ONLINE INDEX REORG IN SMS

REBALANCE

IMPORT REPLACE 选项

TRUNCATE

COPY NO 选项的 LOAD 命令时 ALLOW READ ACCESS LOAD

ONLINE TABLE REORG

联机 SMS 表空间备份必须等待 Z 锁定被释放

DMS 表空间的联机备份操作与下列操作不兼容：

装入
重组（联机和脱机）
删除表空间
截断表
创建索引
最初未记录任何内容（与 CREATE TABLE 和 ALTER TABLE 语句配合使用）

备份

备份类型

联机备份：热备份或在线备份

脱机备份：冷备份或离线备份

备份范围

完全备份：备份数据库中的所有数据

增量备份：备份数据库中的部分数据

备份方式

TSM

本地备份

离线备份

db2 deactivate db testdb;
db2 list active databases
db2 backup db testdb to /tmp/

db2ckbkp -h /tmp/TESTDB.0.db2inst1.DBPART000.20240806152728.001
=====================
MEDIA HEADER REACHED:
=====================
        Server Database Name           -- TESTDB
        Server Database Alias          -- TESTDB
        Client Database Alias          -- TESTDB
        Timestamp                      -- 20240806152728
        Database Partition Number      -- 0
        Instance                       -- db2inst1
        Database Configuration Type    -- 0 (Non-shared data)
        Sequence Number                -- 1
        Database Member ID             -- 0
        Release ID                     -- 0x1000 (DB2 v10.5)
        AL version                     -- V:10 R:5 M:0 F:11 I:0 SB:0
        Database Seed                  -- 0x8FCCA87E
        DB Comment's Codepage (Volume) -- 0
        DB Comment (Volume)            --
        DB Comment's Codepage (System) -- 0
        DB Comment (System)            --
        Authentication Value           -- 255 (Not specified)
        Backup Mode                    -- 0 (Offline)
        Includes Logs                  -- 0 (No)
        Compression                    -- 0 (No Library Applied)
        Backup Type                    -- 0 (Database-level)
        Backup Granularity             -- 0 (Non-incremental)
        Merged Backup Image            -- 0 (No)
        Status Flags                   -- 0x21
                                          Consistent on this member
                                          LOGARCHMETH1 is set
        System Catalogs in this image  -- 1 (Yes)
        Catalog Partition Number       -- 0
        DB Codeset                     -- UTF-8
        DB Territory                   -- CN
        LogID                          -- 1722019496
        LogPath                        -- /db2log/actlog/NODE0000/LOGSTREAM0000/
        Backup Buffer Size             -- 6295552 (1537 4K pages)
        Number of Sessions             -- 1
        Platform                       -- 0x1E (Linux-x86-64)
        Encrypt Info Flags             -- 0x0

 The proper image file name would be:
TESTDB.0.db2inst1.DBPART000.20240806152728.001

在线全备

db2 backup db testdb online to /tmp

增量备份

#开启trackmod
db2 get db cfg |grep -i trackmod
Track modified pages                         (TRACKMOD) = NO
db2 update db cfg using trackmod yes
db2 terminate
db2stop force 
db2start 
db2 activate database testdb

开启trackmod需要至少做一次全量备份才能做增量备份，否则报错SQL2426N

db2 backup database testdb online  to /tmp/ compress include logs

online incremental

db2 backup database testdb online incremental to /tmp/ compress include logs

online incremental delta

#差异备份
db2 backup database testdb online incremental delta to /tmp/ compress include logs

备份查询

db2 list history backup all for testdb
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20240806153928001   N    D  S0000003.LOG S0000003.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTDB ONLINE
 Start Time: 20240806153928
   End Time: 20240806153933
     Status: A
 ----------------------------------------------------------------------------
  EID: 12 Location: /tmp


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20240806153958001   O    D  S0000004.LOG S0000004.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP TESTDB ONLINE
 Start Time: 20240806153958
   End Time: 20240806153959
     Status: A
 ----------------------------------------------------------------------------
  EID: 14 Location: /tmp

恢复

全量+增量+差异备份如何恢复，可以通过db2ckrst 来提示

db2ckrst -d testdb -t 20240806153958  
Suggested restore order of images using timestamp 20240806153958 for
database testdb.
====================================================================
 restore db testdb incremental taken at 20240806153958
 restore db testdb incremental taken at 20240806153928
 restore db testdb incremental taken at 20240806153958
====================================================================

restore:

db2 restore database testdb from /tmp/ taken at 20240806152503 to /db2data logtarget /db2databackup/log

rollforward:

db2 "rollforward db testdb to end of logs overflow log path ('/db2databackup/log')"

实验验证

备份目录：

mkdir -p /db2databackup/log
chown db2inst1.db2iadm1 -R /db2databackup

创建表，插入数据，做在线全备，增量备份，差异备份

db2 connect to testdb;
db2 "create table t1(id int ,col1 int,col2 int)"
db2 "insert into t1 values(1,1,1)"
#全量备份
db2 backup db testdb online to /db2databackup/ include logs
Backup successful. The timestamp for this backup image is : 20240806174057
db2 connect to testdb;
db2 "insert into t1 values(2,2,2)"
db2 "insert into t1 values(3,3,3)"
db2 "insert into t1 values(4,4,4)"
db2 "insert into t1 values(5,5,5)"
#增量备份
db2 backup db testdb online incremental to /db2databackup/ include logs
Backup successful. The timestamp for this backup image is : 20240806175023

#差异备份
db2 connect to testdb;
db2 "insert into t1 values(6,6,6)"
db2 "insert into t1 values(7,7,7)"
db2 backup db testdb online incremental delta to /db2databackup/ include logs
Backup successful. The timestamp for this backup image is : 20240806175100

删除数据库

db2 terminate
db2 deactivate db testdb;
db2 "drop db testdb"

恢复数据库

查看备份文件

ls -lrt
total 187736
drwxr-xr-x 2 db2inst1 db2iadm1      4096 Aug  6 17:39 log
-rw------- 1 db2inst1 db2iadm1 173273088 Aug  6 17:41 TESTDB.0.db2inst1.DBPART000.20240806174057.001
-rw------- 1 db2inst1 db2iadm1   9482240 Aug  6 17:50 TESTDB.0.db2inst1.DBPART000.20240806175023.001
-rw------- 1 db2inst1 db2iadm1   9482240 Aug  6 17:51 TESTDB.0.db2inst1.DBPART000.20240806175100.001

恢复步骤

db2ckrst -d testdb -t 20240806175100 -r database

Suggested restore order of images using timestamp 20240806175100 for
database testdb.
====================================================================
 restore db testdb incremental taken at 20240806175100   差异
 restore db testdb incremental taken at 20240806174057   全量
 restore db testdb incremental taken at 20240806175023   增量
 restore db testdb incremental taken at 20240806175100   差异
====================================================================

恢复

自动恢复

db2 restore database testdb incremental automatic \
from /db2databackup taken at 20240806175100  
db2 "rollforward db testdb query status"
 Input database alias                   = testdb
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = DB  pending
 Next log file to be read               = S0000007.LOG
 Log files processed                    =  -
 Last committed transaction             = 2024-08-06-09.51.00.000000 UTC

db2 "rollforward db testdb to end of logs 
and complete overflow log path ('/db2databackup/log')"
kup/log

1.指向要复原的最后一个增量备份映像

2.指向初始完整数据库（或表空间）映像

3.指向按创建顺序排列的每个增量备份映像

手动恢复

db2 restore database testdb incremental \
from /db2databackup taken at 20240806175100

db2 restore database testdb incremental  \
from /db2databackup taken at 20240806174057

db2 restore database testdb incremental   \
from /db2databackup taken at 20240806175023 

db2 restore database testdb incremental   \
from /db2databackup taken at 20240806175100 

db2 "rollforward db testdb query status"


db2 "rollforward db testdb to end of logs 
and complete overflow log path ('/db2databackup/log')"

rollforward

db2 "rollforward db testdb to end of logs 
and complete overflow log path ('/db2databackup/log')"

查询

db2 "select * from t1"

ID          COL1        COL2
----------- ----------- -----------
          1           1           1
          2           2           2
          3           3           3
          4           4           4
          5           5           5
          6           6           6
          7           7           7