扫码一下
查看教程更方便
DB2 备份和恢复
本章介绍数据库的备份和恢复方法。
简介
备份和恢复方法旨在确保我们的信息安全。在命令行界面 (CLI) 或图形用户界面 (GUI) 中，使用备份和恢复实用程序，我们可以备份或恢复 DB2 UDB 中的数据库数据。
日志
日志文件由错误日志组成，用于从应用程序错误中恢复。 日志保留数据库中更改的记录。 有两种类型的日志记录，如下所述：
循环日志
这是一种在需要分配新的事务日志文件时覆盖旧事务日志的方法，从而擦除日志文件的序列并重新使用它们。 您只能在离线模式下进行完整备份。 即，数据库必须离线才能进行完整备份。
存档日志
此模式支持使用称为前滚恢复的日志文件进行联机备份和数据库恢复。 通过将 logretain 或 userexit 设置为 ON，可以将备份模式从循环更改为归档。 对于归档日志记录，备份设置数据库需要一个 DB2 进程可写的目录。
备份
使用备份命令，我们可以获取整个数据库的副本。 这个备份副本包括数据库系统文件、数据文件、日志文件、控制信息等。
我们可以在离线和在线工作时进行备份。
离线备份
语法
列出活动的应用程序/数据库
db2 list application
输出
Auth Id Application Appl. Application Id
DB # of
Name Handle
Name Agents
-------- -------------- ---------- ---------------------
----------------------------------------- -------- -----
DB2INST1 db2bp 39
*LOCAL.db2inst1.140722043938
ONE 1
语法
强制应用程序使用 app. 处理的id
db2 "force application (39)"
输出
DB20000I The FORCE APPLICATION command completed
successfully.
DB21024I This command is asynchronous and may not
be effective immediately.
语法
终止数据库连接
db2 terminate
停用数据库
db2 deactivate database one
获取备份文件
db2 backup database <db_name> to <location>
示例
db2 backup database one to /home/db2inst1/
输出
Backup successful. The timestamp for this backup image is :
20140722105345
在线备份
首先，我们需要将模式从循环日志记录更改为存档日志记录。
语法
检查数据库是使用循环日志记录还是归档日志记录
db2 get db cfg for one | grep LOGARCH
输出
First log archive method (LOGARCHMETH1) = OFF
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
在上面的输出中，突出显示的值是处于关闭模式的 [logarchmeth1 和 logarchmeth2]，这意味着当前数据库处于 CIRCULLAR LOGGING 模式。 如果我们需要使用 ARCHIVE LOGGING 模式，则需要更改或添加配置文件中存在的变量 logarchmeth1 和 logarchmeth2 中的路径。
使用所需的存档目录更新 logarchmeth1
语法
创建目录
mkdir backup
mkdir backup/ArchiveDest
为文件夹提供用户权限
chown db2inst1:db2iadm1 backup/ArchiveDest
更新配置 LOGARCHMETH1
db2 update database configuration for one using LOGARCHMETH1
'DISK:/home/db2inst1/backup/ArchiveDest'
为了安全起见，我们可以进行离线备份，激活数据库并连接到它。
进行在线备份
db2 backup database one online to
/home/db2inst1/onlinebackup/ compress include logs
输出
db2 backup database one online to
/home/db2inst1/onlinebackup/ compress include logs
使用以下命令验证备份文件：
语法
db2ckbkp <location/backup file>
示例
db2ckbkp
/home/db2inst1/ONE.0.db2inst1.DBPART000.20140722112743.001
列出备份文件的历史
语法
db2 list history backup all for one
输出
List History File for one
Number of matching file entries = 4
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
B D 20140722105345001 F D S0000000.LOG S0000000.LOG
------------------------------------------------------------
----------------
Contains 4 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS1
------------------------------------------------------------
----------------
Comment: DB2 BACKUP ONE OFFLINE
Start Time: 20140722105345
End Time: 20140722105347
Status: A
------------------------------------------------------------
----------------
EID: 3 Location: /home/db2inst1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
Backup ID
-- --- ------------------ ---- --- ------------ ------------
--------------
B D 20140722112239000 N S0000000.LOG S0000000.LOG
------------------------------------------------------------
-------------------------------------------------------------
-------------------------------
Comment: DB2 BACKUP ONE ONLINE
Start Time: 20140722112239
End Time: 20140722112240
Status: A
------------------------------------------------------------
----------------
EID: 4 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 20140722112743001 F D S0000000.LOG S0000000.LOG
------------------------------------------------------------
----------------
Contains 4 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS1
-------------------------------------------------------------
----------------
Comment: DB2 BACKUP ONE OFFLINE
Start Time: 20140722112743
End Time: 20140722112743
Status: A
-------------------------------------------------------------
----------------
EID: 5 Location: /home/db2inst1
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log
Backup ID
-------------------------------------------------------------
----------------
R D 20140722114519001 F
20140722112743
------------------------------------------------------------
----------------
Contains 4 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS1
------------------------------------------------------------
----------------
Comment: RESTORE ONE WITH RF
Start Time: 20140722114519
End Time: 20140722115015
Status: A
------------------------------------------------------------
----------------
EID: 6 Location:
从备份中恢复数据库
要从备份文件恢复数据库，我们需要遵循给定的语法：
语法
db2 restore database <db_name> from <location>
taken at <timestamp>
示例
db2 restore database one from /home/db2inst1/ taken at
20140722112743
输出
SQL2523W Warning! Restoring to an existing database that is
different from
the database on the backup image, but have matching names.
The target database
will be overwritten by the backup version. The Roll-forward
recovery logs
associated with the target database will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
前滚位于日志目录中的所有日志，包括磁盘驱动器故障之前的最新更改。
语法
db2 rollforward db <db_name> to end of logs and stop
示例
db2 rollforward db one to end of logs and stop
输出
Rollforward Status
Input database alias = one
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000000.LOG -
S0000001.LOG
Last committed transaction = 2014-07-22-
06.00.33.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
