DB2 - 备份和恢复
-
简述
本章介绍数据库的备份和恢复方法。
-
介绍
备份和恢复方法旨在确保我们的信息安全。在命令行界面 (CLI) 或图形用户界面 (GUI) 中使用备份和恢复实用程序，您可以备份或恢复 DB2 UDB 中的数据库数据。 -
日志记录
日志文件由错误日志组成，用于从应用程序错误中恢复。日志记录数据库中的更改。有两种类型的日志记录，如下所述：循环记录
这是一种在需要分配新的事务日志文件时覆盖旧事务日志的方法，从而擦除日志文件的序列并重新使用它们。您只能在离线模式下进行完整备份。即，数据库必须脱机才能进行完整备份。归档日志
此模式支持使用称为前滚恢复的日志文件进行联机备份和数据库恢复。通过将 logretain 或 userexit 设置为 ON，可以将备份模式从循环更改为存档。对于归档日志，备份设置数据库需要一个对 DB2 进程可写的目录。 -
备份
使用Backup命令您可以复制整个数据库。该备份副本包括数据库系统文件、数据文件、日志文件、控制信息等。您可以在离线和在线工作时进行备份。离线备份
语法:[列出活动的应用程序/数据库]db2 list application输出:Auth Id Application Appl. Application Id DB # of Name Handle Name Agents -------- -------------- ---------- --------------------- ----------------------------------------- -------- ----- DB2INST1 db2bp 39 *LOCAL.db2inst1.140722043938 ONE 1语法:[使用应用程序强制应用程序。处理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在线备份
首先，您需要将模式从Circular logging至Archive Logging.语法:[检查数据库是否使用循环或归档日志记录]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] in off 模式，这意味着当前数据库处于“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.