Db2 简明教程

DB2 - Backup and Recovery

本章介绍数据库的备份和恢复方法。

backup

Introduction

备份和恢复方法旨在保证我们信息的安全性。在命令行界面 (CLI) 或图形用户界面 (GUI) 中使用备份和恢复实用程序，您可以在 DB2 UDB 中备份或恢复数据库的数据。

Logging

日志文件包含错误日志，这些错误日志用于从应用程序错误中恢复。日志保留数据库中更改的记录。如下所述，日志记录有两种类型：

Circular logging

这是一种方法，即在需要分配新事务日志文件时，覆盖旧的事务日志，从而擦除日志文件序列并重新使用它们。您只允许在脱机模式下进行完全备份。即，数据库必须脱机才能进行完全备份。

Archive logging

此模式支持使用称为向前恢复的日志文件进行在线备份和数据库恢复。可以通过将 logretain 或 userexit 设置为 ON，将备份模式从循环更改为归档。对于归档日志记录，备份设置数据库需要一个对 DB2 进程可写的目录。

Backup

使用 Backup 命令，您可以复制整个数据库。此备份副本包括数据库系统文件、数据文件、日志文件、控制信息等。

可以同时在脱机和联机时进行备份。

Offline backup

Syntax: [列出活动应用程序/数据库]

db2 list application

Output:

Auth Id  Application    Appl.      Application Id
DB       # of
         Name           Handle
Name    Agents
-------- -------------- ---------- ---------------------
----------------------------------------- -------- -----
DB2INST1 db2bp          39
*LOCAL.db2inst1.140722043938
ONE      1

Syntax: [强制应用程序使用应用程序。已处理 ID]

db2 "force application (39)"

Output:

DB20000I  The FORCE APPLICATION command completed
successfully.

DB21024I  This command is asynchronous and may not
be effective immediately.

Syntax: [要终止数据库连接]

db2 terminate

Syntax: [要停用数据库]

db2 deactivate database one

Syntax: [要获取备份文件]

db2 backup database <db_name> to <location>

Example:

db2 backup database one to /home/db2inst1/

Output:

Backup successful. The timestamp for this backup image is :
20140722105345

Online backup

首先，您需要将模式从 Circular logging 更改为 Archive Logging 。

Syntax: [要检查数据库是否使用循环记录还是存档记录]

db2 get db cfg for one | grep LOGARCH

Output:

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] 处于关闭模式，这意味着当前数据库处于“循环记录”模式。如果您需要使用“存档记录”模式，则需要更改或在配置文件中存在的变量 logarchmeth1 和 logarchmeth2 中添加路径。

Updating logarchmeth1 with required archive directory

Syntax: [要创建目录]

mkdir backup
mkdir backup/ArchiveDest

Syntax: [要为文件夹提供用户权限]

chown db2inst1:db2iadm1 backup/ArchiveDest

Syntax: [要更新配置 LOGARCHMETH1]

db2 update database configuration for one using LOGARCHMETH1
'DISK:/home/db2inst1/backup/ArchiveDest'

您可以进行离线备份以确保安全，激活数据库并连接到数据库。

Syntax: [要执行联机备份]

db2 backup database one online to
/home/db2inst1/onlinebackup/ compress include logs

Output:

db2 backup database one online to
/home/db2inst1/onlinebackup/ compress include logs

使用以下命令验证备份文件：

Syntax:

db2ckbkp <location/backup file>

Example:

db2ckbkp
/home/db2inst1/ONE.0.db2inst1.DBPART000.20140722112743.001

列出备份文件的历史记录

Syntax:

db2 list history backup all for one

Output:

                    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:

Restoring the database from backup

要从备份文件恢复数据库，您需要按照给定的语法进行操作：

Syntax:

db2 restore database <db_name> from <location>
taken at <timestamp>

Example:

db2 restore database one from /home/db2inst1/ taken at
20140722112743

Output:

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.

向前滚动日志目录中位于的所有日志，包括磁盘驱动器故障前的最新更改。

Syntax:

db2 rollforward db <db_name> to end of logs and stop

Example:

db2 rollforward db one to end of logs and stop

Output:

                                 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.