五、数据库备份的查询和验证

　　对数据库进行了备份之后，我们需要查询有哪些数据库已经被备份在TSM上。同时，对这些备份进行有效性验证也是必要的。

　　我们可以使用带有BACKUP选项的LIST HISTORY命令来查询有哪些备份映像：

[db2inst1@db2tsmdrill ~]$ db2 list history backup all for ZSHWL
                    List History File for ZSHWL
Number of matching file entries = 146
 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20150616131903001   F    D  S0000000.LOG S0000000.LOG
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):
 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP ZSHWL OFFLINE
 Start Time: 20150616131903
   End Time: 20150616131944
     Status: A
 ----------------------------------------------------------------------------
  EID: 751 Location: /home/db2inst1
……

　　DB2还提供了一个工具db2adutl用来管理TSM上的数据库备份映像以及事务日志归档，这个工具会通过TSM Client API来访问TSM上的备份数据。db2adutl可以用来完成查询、取回、验证和删除数据库在TSM上的备份映像、事务日志归档等对象，也可以完成特定对象对指定节点的指定用户进行访问授权。

　　可以通过db2adutl que-ry命令来查询当前数据库在TSM上的备份，登录到数据库实例用户，发起以下命令来查询：

[db2inst1@db2tsmdrill ~]$ db2adutl query FULL db ZSHWL
Query for database ZSHWL
Retrieving FULL DATABASE BACKUP information.
    1 Time: 20150616134107  Oldest log: S0000001.LOG  DB Partition Number: 0    Sessions: 2
    2 Time: 20150616132313  Oldest log: S0000000.LOG  DB Partition Number: 0    Sessions: 2
    3 Time: 20150616112801  Oldest log: S0000000.LOG  DB Partition Number: 0    Sessions: 2
    4 Time: 20150616112700  Oldest log: S0000000.LOG  DB Partition Number: 0    Sessions: 1
    5 Time: 20150616104649  Oldest log: S0000000.LOG  DB Partition Number: 0    Sessions: 1
Retrieving INCREMENTAL DATABASE BACKUP information.
    1 Time: 20150616134147  Oldest log: S0000002.LOG  DB Partition Number: 0    Sessions: 2
Retrieving DELTA DATABASE BACKUP information.
  No DELTA DATABASE BACKUP images found for ZSHWL

　　从上面命令的输出我们可以看到，对于恢复演练DB2服务器上db2inst1实例内的ZSHWL数据库，在TSM上存在5个全量备份，1个增量备份。

　　可以使用db2adutl的verify选项来对TSM上备份的数据库映像进行验证：

[db2inst1@db2tsmdrill ~]$ db2adutl verify full taken at 20150616112700 db ZSHWL
Query for database ZSHWL
Retrieving FULL DATABASE BACKUP information.  Please wait.
   FULL DATABASE BACKUP image:
      ./ZSHWL.0.db2inst1.NODE0000.CATN0000.20150616112700.001, DB Partition Number: 0
Do you wish to verify this image? (Y/N) Y
   Verifying file: ./ZSHWL.0.db2inst1.NODE0000.CATN0000.20150616112700.001
#######################
Warning: only partial image read, bytes read: 16384 of 16781312
Read 0 bytes, assuming we are at the end of the image
Image Verification Complete - successful.
Retrieving INCREMENTAL DATABASE BACKUP information.
  No INCREMENTAL DATABASE BACKUP images found for ZSHWL
Retrieving DELTA DATABASE BACKUP information.
  No DELTA DATABASE BACKUP images found for ZSHWL

　　我们可以看到，db2adutl从TSM上把相应时间备份的数据库映像取回到本地，并且读取了部分数据进行正确性验证。

　　六、数据库的恢复

　　本节在恢复演练DB2服务器上演示其中db2inst1实例上的ZSHWL数据库的恢复。分别进行离线全量备份、在线全量备份和在线增量备份这3种不同备份的恢复。

　　首先，我们来尝试将备份时间为20150616104649的离线全量备份恢复到db2inst1实例上。

　　先将原有数据库删除：

[db2inst1@db2tsmdrill ~]$ db2 drop db ZSHWL
DB20000I  The DROP DATABASE command completed successfully.
[db2inst1@db2tsmdrill ~]$ db2 list db directory
SQL1057W  The system database directory is empty.  SQLSTATE=01606

　　使用带use tsm选项的db2 restore命令进行恢复：

[db2inst1@db2tsmdrill ~]$ db2 restore db ZSHWL use tsm taken at 20150616104649
DB20000I  The RESTORE DATABASE command completed successfully.

　　由于是离线全量备份，所以没有事务日志前滚的问题，一旦恢复成功就可以直接连接数据库进行使用了：

[db2inst1@db2tsmdrill ~]$ db2 connect to ZSHWL
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = ZSHWL

　　第二，我们来恢复在线全量备份。我们来尝试将备份时间为20150616134107的在线全量备份恢复到db2inst1实例上。

　　先将原有数据库删除：

[db2inst1@db2tsmdrill ~]$ db2 drop db ZSHWL
DB20000I  The DROP DATABASE command completed successfully.
[db2inst1@db2tsmdrill ~]$ db2 list db directory
SQL1057W  The system database directory is empty.  SQLSTATE=01606

　　使用带use tsm选项的db2 restore命令进行恢复：

[db2inst1@db2tsmdrill ~]$ db2 restore db ZSHWL use tsm taken at 20150616134107
DB20000I  The RESTORE DATABASE command completed successfully.

　　由于我们恢复的是一个在线的全量备份，所以当恢复成功后，还不能直接连接和使用数据库，因为还需要进行事务日志的前滚操作。此时去连接数据库会得到以下的错误提示：

[db2inst1@db2tsmdrill ~]$ db2 connect to ZSHWL
SQL1117N  A connection to or activation of database "ZSHWL" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

　　这个提示告诉我们，数据库处于ROLL-FORWARD PENDING状态，不能使用。

　　使用db2 rollforward命令进行事务日志前滚操作：

[db2inst1@db2tsmdrill ~]$ db2 rollforward db ZSHWL to end of logs and stop
                                 Rollforward Status
 Input database alias                   = ZSHWL
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000001.LOG - S0000002.LOG
 Last committed transaction             = 2015-06-16-05.41.52.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.

　　现在，可以数据库可以正常被连接和使用了：

[db2inst1@db2tsmdrill ~]$ db2 connect to ZSHWL
   Database Connection Information
 Database server        = DB2/LINUXX8664 9.7.9
 SQL authorization ID   = DB2INST1
 Local database alias   = ZSHWL

　　DB2和TSM Client API对事务日志到底是怎么处理的呢?DB2 ROLLFOR-WARD会按顺序在以下位置查找所需要的事务日志：

　　1.事务日志目录

[db2inst1@db2tsmdrill SQL00001]$ db2 get db cfg for ZSHWL | grep "Path to log files"
 Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

　　2.事务日志镜像目录

[db2inst1@db2tsmdrill SQL00001]$ db2 get db cfg for ZSHWL | grep MIRRORLOGPATH
 Mirror log path                         (MIRRORLOGPATH) =

　　3.事务日志溢出目录

[db2inst1@db2tsmdrill SQL00001]$ db2 get db cfg for ZSHWL | grep OVERFLOWLOGPATH
 Overflow log path                     (OVERFLOWLOGPATH) =

　　4.事务日志归档方式1(LOGARCHMETH1)

[db2inst1@db2tsmdrill SQL00001]$ db2 get db cfg for ZSHWL | grep LOGARCHMETH1
 First log archive method                 (LOGARCHMETH1) = TSM

　　5.事务日志归档方式2(LOGARCHMETH2)

[db2inst1@db2tsmdrill SQL00001]$ db2 get db cfg for ZSHWL | grep LOGARCHMETH2
 Second log archive method                (LOGARCHMETH2) = OFF

　　6.事务日志归档故障备用目录(FAILARCHPATH)

[db2inst1@db2tsmdrill SQL00001]$ db2 get db cfg for ZSHWL | grep FAILARCHPATH
 Failover log archive path                (FAILARCHPATH) =

　　以上所有位置都无法找到需要的日志时，DB2 ROLLFORWARD会报错误。本例中由于 LOG-ARCHMETH1设置为TSM，而且DB2 ROLLFOR-WARD所需要的事务日志全部都已经归档到TSM中，所以DB2可以从TSM中把事务日志取回到事务日志目录/home/db2inst1/db2inst1/NODE0000 /SQL00001/SQLOGDIR/中，然后再进行前滚操作。

　　那么如果事务日志没有归档，或者事务日志损坏了应该怎么对在线备份进行恢复呢?由于在线备份时，默认会将备份期间归档的事务日志包含到备份映像中，所以我们可以使用logtarget选项，从备份映像中将事务日志取到指定的位置。下面我们再次恢复这个在线备份，不同的是在恢复在线备份同时，取出事务日志放到指定的路径~/logretrieve下以便前滚操作时使用：

[db2inst1@db2tsmdrill ~]$ db2 drop db ZSHWL
DB20000I  The DROP DATABASE command completed successfully.
[db2inst1@db2tsmdrill ~]$ rmdir logretrieve/
[db2inst1@db2tsmdrill ~]$ db2 restore db ZSHWL use tsm taken at 20150616134107 logtarget ~/logretrieve
DB20000I  The RESTORE DATABASE command completed successfully.

　　我们可以看到备份期间产生的事务日志已经放置到~/logretrieve路径下：

[db2inst1@db2tsmdrill ~]$ ls logretrieve/
S0000001.LOG

　　注意，这里仅包括在线备份期间产生的事务日志，如果需要前滚到备份之后更接近当前的时间，就需要更多的事务日志，如果事务日志已经归档到TSM，则可以用db2adutl extract logs命令将后续的事务日志取回并且前滚，在第6节有详细的步骤说明。

　　在这个例子里，我们只前滚在线备份期间产生的事务日志，以使数据库恢复到在线备份完成那一刻的状态：

[db2inst1@db2tsmdrill ~]$ db2 "rollforward db ZSHWL to end of logs and stop overflow log path ('/home/db2inst1/logretrieve')"
                                 Rollforward Status
 Input database alias                   = ZSHWL
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000001.LOG - S0000002.LOG
 Last committed transaction             = 2015-06-16-05.41.52.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.

　　这里我们用了overflow log path选项来指定了前滚时所需要的事务日志的路径。

　　最后，我们进行在线增量备份的恢复，在db2 restore中使用选项incremental automat-ic可以让db2从TSM备份序列中自动按顺序恢复所有必须的备份，包括最近一次的全量备份以及后续直到被恢复的增量备份之间的所有增量备份，下面我们进行这种操作来恢复20150616134147这个在线增量备份：

[db2inst1@db2tsmdrill ~]$ db2 drop db ZSHWL
DB20000I  The DROP DATABASE command completed successfully.
[db2inst1@db2tsmdrill ~]$ db2 restore db ZSHWL incremental automatic use tsm taken at 20150616134147
DB20000I  The RESTORE DATABASE command completed successfully.
[db2inst1@db2tsmdrill ~]$ db2 rollforward db ZSHWL to end of logs and stop
                                 Rollforward Status
 Input database alias                   = ZSHWL
 Number of nodes have returned status   = 1
 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000002.LOG - S0000002.LOG
 Last committed transaction             = 2015-06-16-05.41.52.000000 UTC
DB20000I  The ROLLFORWARD command completed successfully.

　　至此，我们已经完成在相同节点上的3种类型的数据库恢复。