本文详细介绍了IBM DB2数据库的备份恢复流程，包括权限设置、备份类型（全备、增量、差异）、压缩备份、归档模式、在线备份、空间管理、恢复策略等，并通过实例展示了不同备份类型的执行与验证。强调了增量备份和压缩备份在性能和空间利用上的优势，以及如何通过RESTORE DATABASE命令进行数据库恢复。
RDBMS备份恢复开篇10问：
1）做备份所需前置条件？（空间评估，所需权限）
2）备份什么？（db、tablespace、log ..）
3）备份集存放在哪里，归档日志在哪里？
4）支持的增量备份的模式有哪些？
5）开启增量备份是否能提高备份性能？ 如何提升的？ 如何开启增量备份？
6）能否通过压缩备份集节省空间？压缩比率？
7）如何删除冗余的备份集或者归档日志防止爆盘？
8）能否通过排除表空间的方式缩短备份过程；在增量备份时此项是否要考虑此因素？
9）本机/异机恢复需要注意什么？
10）基于上面9个问题，如何实施最佳备份恢复策略？
首先，任何抛开软件版本去谈结论的行为都是不严谨的，本文演示的结论仅适合以下版本，其他版本需自行验证：
[ctginst1@maximo02 ~]$ db2ls
Install Path Level Fix Pack Special Install Number Install Date Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/ibm/db2/V11.1 11.1.0.0 0 Mon Oct 21 10:57:07 2019 CST 0
[ctginst1@maximo02 ~]$ db2level
DB21085I This instance or install (instance name, where applicable:
"ctginst1") uses "64" bits and DB2 code release "SQL11010" with level
identifier "0201010F".
Informational tokens are "DB2 v11.1.0.0", "s1606081100", "DYN1606081100AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V11.1".
#完成备份恢复所需权限
One of the following:
SYSADM
SYSCTRL
SYSMAINT
#所需权限
One of the following:
SYSADM
SYSCTRL
SYSMAINT
# 至少需要 SYSMAINT，就可以执行backup， 虽不能删库，但是却可以执行restore从而覆盖database的，要注意！‘
# DB2中的SYSADM权限为操作最大权限，只允许SYSADM用户更新DBM CFG文件;
# SYSCTRL权限不能访问数据库中的任何数据，除非他们被授予了访问数据所需的特权;
# SYSMAINT权限是SYSCTRL权限的子集，SYSMAINT权限用户不能创建或删除数据库或者表空间;
[root@maximo02 ~]# groupadd db2_maint
[root@maximo02 ~]# useradd maxdb76_oper -g db2_maint -G db2_maint
[root@maximo02 ~]# id maxdb76_oper
uid=1004(maxdb76_oper) gid=1007(db2_maint) groups=1007(db2_maint)
[ctginst1@maximo02 ~]$ db2 get dbm cfg | grep SYS
SYSADM group name (SYSADM_GROUP) = CTGIADM1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =
Priority of agents (AGENTPRI) = SYSTEM
[ctginst1@maximo02 ~]$ db2 update dbm cfg using SYSMAINT_GROUP db2_maint
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
SQL1362W One or more of the parameters submitted for immediate modification
were not changed dynamically. Client changes will not be effective until the
next time the application is started or the TERMINATE command has been issued.
Server changes will not be effective until the next DB2START command.
# db2stop & db2start
[ctginst1@maximo02 ~]$ db2 get dbm cfg | grep SYS
SYSADM group name (SYSADM_GROUP) = CTGIADM1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) = DB2_MAINT
SYSMON group name (SYSMON_GROUP) =
Priority of agents (AGENTPRI) = SYSTEM
[maxdb76_oper@maximo02 ~]$ . /home/ctginst1/sqllib/db2profile
[maxdb76_oper@maximo02 ~]$ db2 connect to maxdb76
Database Connection Information
Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = MAXDB76_...
Local database alias = MAXDB76
[maxdb76_oper@maximo02 ~]$
[maxdb76_oper@maximo02 ~]$ db2 backup db maxdb76 online incremental delta to /home/ctginst1/backup/maxdb76/ compress include logs
Backup successful. The timestamp for this backup image is : 20210912230130
# 虽不能删库，但是由于可以执行restore，会导致database被覆盖。
[maxdb76_oper@maximo02 ~]$ db2 drop database maxdb76
SQL1092N The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation. User
ID: "MAXDB76_OPER".
[maxdb76_oper@maximo02 ~]$ db2 backup db maxdb76 online incremental delta to /home/ctginst1/backup/maxdb76/ compress include logs
Backup successful. The timestamp for this backup image is : 20210913115016
[maxdb76_oper@maximo02 ~]$ db2 restore database maxdb76 incremental automatic from /home/ctginst1/backup/maxdb76/ taken at 20210913115016 on /home/ctginst1/
SQL2539W The specified name of the backup image to restore is the same as the
name of the target database. Restoring to an existing database that is the
same as the backup image database will cause the current database to be
overwritten by the backup version.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
#查看归档模式
[ctginst1@maximo02 ~]$ db2 get db cfg | grep logarc
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
##开启归档模式：
[ctginst1@maximo02 ~]$ db2 update database configuration for maxdb76 using logarchmeth1 DISK:/home/ctginst1/logs/maxdb76
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, the database
must be shutdown and reactivated before the configuration parameter changes
become effective.
#查看增量备份模式
[ctginst1@maximo02 ~]$ db2 get db cfg for maxdb76 |grep -i trackmod
Track modified pages (TRACKMOD) = NO
#开启增量备份模式：
db2 update database configuration for maxdb76 using trackmod on
#重启生效
#此时db进入backup pending状态
[ctginst1@maximo02 ~]$ db2 backup db maxdb76 to /home/ctginst1/backup/maxdb76/
Backup successful. The timestamp for this backup image is : 20210911212916
#查看备份集
[ctginst1@maximo02 ~]$ db2 list backup all for maxdb76
List History File for maxdb76
Number of matching file entries = 2
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911212748000 N S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911212748
End Time: 20210911212749
Status: A
----------------------------------------------------------------------------
EID: 203 Location:
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2413 sqlerrml: 0
sqlerrmc:
sqlerrp : sqlubIni
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911212916001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 OFFLINE
Start Time: 20210911212916
End Time: 20210911213006
Status: A
----------------------------------------------------------------------------
EID: 204 Location: /home/ctginst1/backup/maxdb76
[ctginst1@maximo02 ~]$
#查看备份集所在目录与尺寸
[ctginst1@maximo02 ~]$ ls -lrt /home/ctginst1/backup/maxdb76
total 5533232
drwxr-xr-x 2 ctginst1 ctgiadm1 6 Sep 11 21:28 offline
-rw------- 1 ctginst1 ctgiadm1 5666029568 Sep 11 21:30 MAXDB76.0.ctginst1.DBPART000.20210911212916.001
[ctginst1@maximo02 ~]$ du -sh /home/ctginst1/backup/maxdb76
5.3G /home/ctginst1/backup/maxdb76
[ctginst1@maximo02 ~]$
#此时db已经支持online backup
[ctginst1@maximo02 ~]$ db2 backup db maxdb76 online to /home/ctginst1/backup/maxdb76/
Backup successful. The timestamp for this backup image is : 20210911213348
[ctginst1@maximo02 ~]$ db2 list backup all for maxdb76
List History File for maxdb76
Number of matching file entries = 3
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911212748000 N S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911212748
End Time: 20210911212749
Status: A
----------------------------------------------------------------------------
EID: 203 Location:
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2413 sqlerrml: 0
sqlerrmc:
sqlerrp : sqlubIni
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911212916001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 OFFLINE
Start Time: 20210911212916
End Time: 20210911213006
Status: A
----------------------------------------------------------------------------
EID: 204 Location: /home/ctginst1/backup/maxdb76
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911213348001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911213348
End Time: 20210911213434
Status: A
----------------------------------------------------------------------------
EID: 207 Location: /home/ctginst1/backup/maxdb76
[ctginst1@maximo02 ~]$
#online backup with log
[ctginst1@maximo02 ~]$ db2 backup db maxdb76 online to /home/ctginst1/backup/maxdb76/ include logs
Backup successful. The timestamp for this backup image is : 20210911213710
[ctginst1@maximo02 ~]$ db2 list backup all for maxdb76
List History File for maxdb76
Number of matching file entries = 4
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911212748000 N S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911212748
End Time: 20210911212749
Status: A
----------------------------------------------------------------------------
EID: 203 Location:
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2413 sqlerrml: 0
sqlerrmc:
sqlerrp : sqlubIni
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911212916001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 OFFLINE
Start Time: 20210911212916
End Time: 20210911213006
Status: A
----------------------------------------------------------------------------
EID: 204 Location: /home/ctginst1/backup/maxdb76
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911213348001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911213348
End Time: 20210911213434
Status: A
----------------------------------------------------------------------------
EID: 207 Location: /home/ctginst1/backup/maxdb76
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911213710001 N D S0000002.LOG S0000002.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911213710
End Time: 20210911213752
Status: A
----------------------------------------------------------------------------
EID: 210 Location: /home/ctginst1/backup/maxdb76
[ctginst1@maximo02 ~]$
#在线压缩备份对比尺寸
[ctginst1@maximo02 ~]$ db2 backup db maxdb76 online to /home/ctginst1/backup/maxdb76/ compress include logs
Backup successful. The timestamp for this backup image is : 20210911214509
[ctginst1@maximo02 ~]$ db2 list backup all for maxdb76
List History File for maxdb76
Number of matching file entries = 5
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911212748000 N S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911212748
End Time: 20210911212749
Status: A
----------------------------------------------------------------------------
EID: 203 Location:
SQLCA Information
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2413 sqlerrml: 0
sqlerrmc:
sqlerrp : sqlubIni
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911212916001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 OFFLINE
Start Time: 20210911212916
End Time: 20210911213006
Status: A
----------------------------------------------------------------------------
EID: 204 Location: /home/ctginst1/backup/maxdb76
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911213348001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911213348
End Time: 20210911213434
Status: A
----------------------------------------------------------------------------
EID: 207 Location: /home/ctginst1/backup/maxdb76
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911213710001 N D S0000002.LOG S0000002.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911213710
End Time: 20210911213752
Status: A
----------------------------------------------------------------------------
EID: 210 Location: /home/ctginst1/backup/maxdb76
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911214509001 N D S0000004.LOG S0000004.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911214509
End Time: 20210911214602
Status: A
----------------------------------------------------------------------------
EID: 213 Location: /home/ctginst1/backup/maxdb76
# 压缩的备份集体积明显减小，本例 1/34
[ctginst1@maximo02 ~]$ du -sh /home/ctginst1/backup/maxdb76/*
5.3G /home/ctginst1/backup/maxdb76/MAXDB76.0.ctginst1.DBPART000.20210911212916.001
5.3G /home/ctginst1/backup/maxdb76/MAXDB76.0.ctginst1.DBPART000.20210911213348.001
5.3G /home/ctginst1/backup/maxdb76/MAXDB76.0.ctginst1.DBPART000.20210911213710.001
157M /home/ctginst1/backup/maxdb76/MAXDB76.0.ctginst1.DBPART000.20210911214509.001
##### 以下使用该压缩备份做整库还原
# 删除数据库
[ctginst1@maximo02 ~]$ db2 list tablespaces show detail
......
[ctginst1@maximo02 ~]$ db2 list tablespace containers for 0
Tablespace Containers for Tablespace 0
Container ID = 0
Name = /home/ctginst1/ctginst1/NODE0000/MAXDB76/T0000000/C0000000.CAT
Type = File
............
[ctginst1@maximo02 NODE0000]$ db2 force all application
SQL0104N An unexpected token "all" was found following "FORCE". Expected
tokens may include: "APPLICATION". SQLSTATE=42601
[ctginst1@maximo02 NODE0000]$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[ctginst1@maximo02 NODE0000]$ db2 drop database maxdb76
DB20000I The DROP DATABASE command completed successfully.
[ctginst1@maximo02 NODE0000]$ cd ..
[ctginst1@maximo02 ctginst1]$ ls -lrt
total 0
[ctginst1@maximo02 ctginst1]$ db2 list db directory
SQL1057W The system database directory is empty. SQLSTATE=01606
# 还原数据库
[ctginst1@maximo02 ctginst1]$ cd ~
[ctginst1@maximo02 ~]$ ls -lrt backup/maxdb76/
total 16742688
drwxr-xr-x 2 ctginst1 ctgiadm1 6 Sep 11 21:28 offline
-rw------- 1 ctginst1 ctgiadm1 5666029568 Sep 11 21:30 MAXDB76.0.ctginst1.DBPART000.20210911212916.001
-rw------- 1 ctginst1 ctgiadm1 5657116672 Sep 11 21:34 MAXDB76.0.ctginst1.DBPART000.20210911213348.001
-rw------- 1 ctginst1 ctgiadm1 5657116672 Sep 11 21:37 MAXDB76.0.ctginst1.DBPART000.20210911213710.001
-rw------- 1 ctginst1 ctgiadm1 164249600 Sep 11 21:46 MAXDB76.0.ctginst1.DBPART000.20210911214509.001
[ctginst1@maximo02 ~]$ db2 restore database maxdb76 from /home/ctginst1/backup/maxdb76/ taken at 20210911214509 on /home/ctginst1/
DB20000I The RESTORE DATABASE command completed successfully.
[ctginst1@maximo02 ~]$ ls -lrt ~/ctginst1/NODE0000/MAXDB76/
total 0
drwx--x--x 2 ctginst1 ctgiadm1 45 Sep 11 22:23 T0000000
drwx--x--x 3 ctginst1 ctgiadm1 45 Sep 11 22:23 T0000001
drwx--x--x 2 ctginst1 ctgiadm1 45 Sep 11 22:23 T0000002
drwx--x--x 2 ctginst1 ctgiadm1 45 Sep 11 22:23 T0000003
drwx--x--x 3 ctginst1 ctgiadm1 45 Sep 11 22:23 T0000004
drwx--x--x 2 ctginst1 ctgiadm1 45 Sep 11 22:23 T0000005
drwx--x--x 2 ctginst1 ctgiadm1 45 Sep 11 22:23 T0000006
drwx--x--x 3 ctginst1 ctgiadm1 45 Sep 11 22:23 T0000007
#前滚日志
[ctginst1@maximo02 ~]$ db2 rollforward db maxdb76 to end of logs and stop
Rollforward Status
Input database alias = maxdb76
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000004.LOG - S0000004.LOG
Last committed transaction = 2021-09-11-13.46.01.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
#验证被还原数据库的可用性
[ctginst1@maximo02 ~]$ db2 "select count(1) from maximo.maxuser"
1
-----------
79
1 record(s) selected.
#删除备份集
[ctginst1@maximo02 maxdb76]$ ls -lrt ~/backup/maxdb76/
total 16742688
-rw------- 1 ctginst1 ctgiadm1 5666029568 Sep 11 21:30 MAXDB76.0.ctginst1.DBPART000.20210911212916.001
-rw------- 1 ctginst1 ctgiadm1 5657116672 Sep 11 21:34 MAXDB76.0.ctginst1.DBPART000.20210911213348.001
-rw------- 1 ctginst1 ctgiadm1 5657116672 Sep 11 21:37 MAXDB76.0.ctginst1.DBPART000.20210911213710.001
-rw------- 1 ctginst1 ctgiadm1 164249600 Sep 11 21:46 MAXDB76.0.ctginst1.DBPART000.20210911214509.001
db2 "prune history 20210911214508 and delete"
db2 list history archive log all for db maxdb76
# 增量备份（启用压缩）
db2 connect to maxdb76
db2 "create schema bond"
db2 "create table bond.t1(id bigint)"
db2 "insert into bond.t1 (id) values (1)"
[ctginst1@maximo02 ~]$ db2 "select * from bond.t1"
ID
--------------------
1
1 record(s) selected.
# 执行一次增量备份的命令（cumulative）
[ctginst1@maximo02 ~]$ db2 backup db maxdb76 online incremental to ~/backup/maxdb76/ compress include logs
Backup successful. The timestamp for this backup image is : 20210912210204
# 注意到 O备份 的 End Time 与 Start Time 表示的备份集生成时间只有30s，远小于 N备份 的 92s。 增量备份确实缩短的备份过程。
[ctginst1@maximo02 ~]$ db2 list backup all for maxdb76
List History File for maxdb76
Number of matching file entries = 4
.........
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210911225027001 N D S0000006.LOG S0000006.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210911225027
End Time: 20210911225119
Status: A
----------------------------------------------------------------------------
EID: 219 Location: /home/ctginst1/backup/maxdb76
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210912210204001 O D S0000009.LOG S0000009.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210912210204
End Time: 20210912210234
Status: A
----------------------------------------------------------------------------
EID: 223 Location: /home/ctginst1/backup/maxdb76
[ctginst1@maximo02 ~]$ ls -lrt ~/backup/maxdb76/
total 365036
-rw------- 1 ctginst1 ctgiadm1 164249600 Sep 11 21:46 MAXDB76.0.ctginst1.DBPART000.20210911214509.001
-rw------- 1 ctginst1 ctgiadm1 164249600 Sep 11 22:51 MAXDB76.0.ctginst1.DBPART000.20210911225027.001
-rw------- 1 ctginst1 ctgiadm1 45297664 Sep 12 21:02 MAXDB76.0.ctginst1.DBPART000.20210912210204.001
# 如果想查看数据库的历史备份还原情况，可以使用 带有 db2 list history backup all for maxdb76 命令， 输出内容与 db2 list backup all for maxdb76 完全一致。
# 对于备份集信息来说，类型N代表在线全备，O 代表在线增量备份，Ｅ代表在线差异备份，类型F代表脱机全备，I代表脱机增量备份，D代表脱机差异备份。
# 所以正常情况下，N,O,E 应该最常见。
##### 破坏db后再还原，观察bond.t1
db2 force application all
db2 drop database maxdb76
# 尝试直接使用最后一次O备份恢复整库，发现默认情况下必须先使用全备恢复
[ctginst1@maximo02 ~]$ db2 restore database maxdb76 from /home/ctginst1/backup/maxdb76/ taken at 20210912210204 on /home/ctginst1/
SQL2573N An incremental backup image must be restored as part of an
incremental RESTORE operation.
# 先使用全备恢复
[ctginst1@maximo02 ~]$ db2 restore database maxdb76 from /home/ctginst1/backup/maxdb76/ taken at 20210911225027 on /home/ctginst1/
DB20000I The RESTORE DATABASE command completed successfully.
# 前滚日志
[ctginst1@maximo02 ~]$ db2 rollforward db maxdb76 to end of logs and stop
Rollforward Status
Input database alias = maxdb76
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000006.LOG - S0000009.LOG
Last committed transaction = 2021-09-12-13.02.34.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
# 注意到，因为日志完成了前滚，本质上是替代了最后一次增量备份。
[ctginst1@maximo02 ~]$ db2 "select * from bond.t1"
ID
--------------------
1
1 record(s) selected.
# 前滚恢复后，会出现一条 Comment: RESTORE MAXDB76 WITH RF 的备份集，表示db经历了一次 ROLLFORWARD 完成了恢复。
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
R D 20210912211732001 F 20210911225027
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: RESTORE MAXDB76 WITH RF
Start Time: 20210912211732
End Time: 20210912211804
Status: A
----------------------------------------------------------------------------
EID: 218 Location:
##### 再次破坏db， 先还原F备，再还原O背，观察bond.t1
db2 force application all
db2 drop database maxdb76
# 还原N备份
[ctginst1@maximo02 ~]$ db2 restore database maxdb76 from /home/ctginst1/backup/maxdb76/ taken at 20210911225027 on /home/ctginst1/
DB20000I The RESTORE DATABASE command completed successfully.
# 还原O备份，失败
[ctginst1@maximo02 ~]$ db2 restore db maxdb76 incremental taken at 20210912210204
SQL2542N No match for a database image file was found based on the source
database alias "MAXDB76" and timestamp "20210912210204" provided.
##### 再次破坏db，指定AUTOMATIC选项，直接使用O备还原，观察bond.t1
db2 force application all
db2 drop database maxdb76
# 指定AUTOMATIC选项，则将对数据库进行自动还原工作。只要执行一次还原操作就可以了。
# 对于自动增量还原，只在指定要使用的目标映像（最后一个映像）时发出带AUTOMATIC选项的RESTORE命令一次。然后DB2使用数据库历史记录来确定余下的必需备份映像并还原它们。
[ctginst1@maximo02 ~]$ db2 restore database maxdb76 incremental automatic from /home/ctginst1/backup/maxdb76/ taken at 20210912210204 on /home/ctginst1/
DB20000I The RESTORE DATABASE command completed successfully.
[ctginst1@maximo02 ~]$ db2 rollforward db maxdb76 to end of logs and stop
Rollforward Status
Input database alias = maxdb76
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000009.LOG - S0000009.LOG
Last committed transaction = 2021-09-12-13.02.34.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[ctginst1@maximo02 ~]$ db2 connect to maxdb76
Database Connection Information
Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = CTGINST1
Local database alias = MAXDB76
[ctginst1@maximo02 ~]$ db2 "select * from bond.t1"
ID
--------------------
1
1 record(s) selected.
# 执行一次增量备份的命令（delta）
db2 "insert into bond.t1 (id) values (2)"
[ctginst1@maximo02 ~]$ db2 backup db maxdb76 online incremental delta to ~/backup/maxdb76/ compress include logs
Backup successful. The timestamp for this backup image is : 20210912220429
# 可以看到一个 Ｅ代表在线差异备份
[ctginst1@maximo02 ~]$ db2 list backup all for maxdb76
List History File for maxdb76
Number of matching file entries = 6
..............
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
R D 20210912215013001 I 20210912210204
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: RESTORE MAXDB76 WITH RF INCR
Start Time: 20210912215013
End Time: 20210912215048
Status: A
----------------------------------------------------------------------------
EID: 222 Location:
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20210912220429001 E D S0000012.LOG S0000012.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 MAXDATA
00005 MAXINDEX
----------------------------------------------------------------------------
Comment: DB2 BACKUP MAXDB76 ONLINE
Start Time: 20210912220429
End Time: 20210912220446
Status: A
----------------------------------------------------------------------------
EID: 230 Location: /home/ctginst1/backup/maxdb76
# Following is a sample weekly incremental backup strategy for a recoverable database.
# It includes a weekly full database backup operation, a daily non-cumulative (delta) backup operation, and a mid-week cumulative (incremental) backup operation:
(Sun) db2 backup db sample use tsm
(Mon) db2 backup db sample online incremental delta use tsm
(Tue) db2 backup db sample online incremental delta use tsm
(Wed) db2 backup db sample online incremental use tsm
(Thu) db2 backup db sample online incremental delta use tsm
(Fri) db2 backup db sample online incremental delta use tsm
(Sat) db2 backup db sample online incremental use tsm
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
