DB2数据库备份恢复

DB2数据库恢复：
一次意外的存储故障，导致表空间损坏。然而最近一次成功的完全备份是在8天前，最近8天内只有归档日志的备份。

查看表空间状态：有两个表空间无法回滚。

db2 list tablespaces show detail;

 Tablespace ID                        = 7
 Name                                 = REPLICAS
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0080
   Detailed explanation:
     Roll forward pending
 Total pages                          = 959104
 Useable pages                        = 959072
 Used pages                           = 0
 Free pages                           = 0
 High water mark (pages)              = 0
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Minimum recovery time                = 2012-08-29-06.28.54.000000

 Tablespace ID                        = 8
 Name                                 = TRACKING
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0080
   Detailed explanation:
     Roll forward pending
 Total pages                          = 731648
 Useable pages                        = 731616
 Used pages                           = 0
 Free pages                           = 0
 High water mark (pages)              = 0
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Minimum recovery time                = 2012-09-29-08.33.08.000000

然而，笔者是个手贱的菜鸟！没做过数据库恢复，想也没想，就直接敲了 “db2 restore db rmdb11”，并没有加”use tsm”，高高兴兴的等待着数据恢复。结果当命令执行完之后发现悲剧了，df -g看到数据盘使用率变成0%了！！！

好吧，原本只需要恢复归档日志，然后rollforward就能解决。现在真的要全库恢复了！

列出所有成功的备份

$ db2adutl query      


Query for database RMDB11


Retrieving FULL DATABASE BACKUP information.
    1 Time: 20150419205821  Oldest log: S0029102.LOG  DB Partition Number: 0    Sessions: 2  
    2 Time: 20150418205817  Oldest log: S0029098.LOG  DB Partition Number: 0    Sessions: 2  
    3 Time: 20150311205822  Oldest log: S0027842.LOG  DB Partition Number: 0    Sessions: 2  
    4 Time: 20150307205821  Oldest log: S0027705.LOG  DB Partition Number: 0    Sessions: 2  
    5 Time: 20150306213640  Oldest log: S0027645.LOG  DB Partition Number: 0    Sessions: 2  
    6 Time: 20150215215435  Oldest log: S0027134.LOG  DB Partition Number: 0    Sessions: 2  
    7 Time: 20150213205848  Oldest log: S0027077.LOG  DB Partition Number: 0    Sessions: 2  

恢复数据到最近的时间点

db2 restore db rmdb11 use tsm taken at 20150419205821

前滚数据库到最近的归档日志

db2 rollforward db rmdb11 to end of logs and stop

假如找不到归档日志，首先要恢复需要的归档日志：

#dsmc
tsm>retrieve /rmdb11data/rminst11/NODE0000/SQL00001/arch/rminst11/RMDB11/NODE0000/C0000002/S002910*.LOG

重新前滚数据库：

并检查表空间状态，确认表空间状态正常。

备份
脚本

$ cat db_backup.sh
#!/bin/ksh
. $HOME/.profile > /dev/null 2>&1
DBNM=$1

db2adutl delete full keep 7 without prompting

db2 "backup db $DBNM online use tsm open 1 sessions without prompting"

===========================================

备份命令

db2 backup db rmdb11 online use tsm open 1 sessions without prompting