重定向恢复数据库

0.前提准备：
解压缩备份镜像文件
检查备份镜像文件：
dst33lp14:/db/a3inew1d/db2backup$ db2ckbkp -h EPMEVENT.0.a3inew2p.DBPART000.20141208000533.001


=====================
MEDIA HEADER REACHED:
=====================
        Server Database Name           -- EPMEVENT
        Server Database Alias          -- EPMEVENT
        Client Database Alias          -- EPMEVENT
        Timestamp                      -- 20141208000533
        Database Partition Number      -- 0
        Instance                       -- a3inew2p
        Database Configuration Type    -- 0
        Sequence Number                -- 1
        Database Member ID             -- 0
        Release ID                     -- F00
        Database Seed                  -- 4BAC8CA0
        DB Comment's Codepage (Volume) -- 0
        DB Comment (Volume)            --                              
        DB Comment's Codepage (System) -- 0
        DB Comment (System)            --                              
        Authentication Value           -- 255
        Backup Mode                    -- 1
        Includes Logs                  -- 1
        Compression                    -- 0
        Backup Type                    -- 0
        Backup Gran.                   -- 0
        Merged Backup Image            -- 0
        Status Flags                   -- 20
        System Cats inc                -- 1
        Catalog Partition Number       -- 0
        DB Codeset                     -- UTF-8
        DB Territory                   -- US
        LogID                          -- 1226419802
        LogPath                        -- /db/a3inew2p/actlog_EPMEVENT/NODE0000/LOGSTREAM0000/
        Backup Buffer Size             -- 16781312
        Number of Sessions             -- 1
        Platform                       -- 14

 The proper image file name would be:
EPMEVENT.0.a3inew2p.DBPART000.20141208000533.001


[1] Buffers processed:  ##########################################################

Image Verification Complete - successful.

从中可以看出：该备份文件为在线全备份，包含log. 备份完整可用。


1. 进入到镜像文件所在路径执行：
 db2 restore db EPMEVENT redirect generate script restore_EPMEVENT.ddl
自动产生restore的文件restore_EPMEVENT.ddl，然后编辑该文件，注意在SET TABLESPACE CONTAINERS部分，可以根据需要定义容器，自动存储的表空间不能改修改，即注释掉的不能用。

2. db2 -tvf restore_EPMEVENT.ddl > restore_EPMEVENT.ddl.out
RESTORE DATABASE EPMEVENT FROM '/db/a3inew1d/db2backup' TAKEN AT 20141208000533  ON '/db/a3inew3d/db2data/EPMEVENT' DBPATH ON '/db/a3inew3d/db2data/EPMEVENT' INTO EPMEVENT LOGTARGET '/db/a3inew3d/actlog_EPMEVENT' REDIRECT
SQL1277W  A redirected restore operation is being performed. During a table
space restore, only table spaces being restored can have their paths
reconfigured. During a database restore, storage group storage paths and DMS
table space containers can be reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.

SET TABLESPACE CONTAINERS FOR 3 USING ( PATH   '/db/a3inew3d/db2data/EPMEVENT/EPMEVENT1/epm.epmbase' )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 4 USING ( FILE   '/db/a3inew3d/db2data/EPMEVENT/EPMEVENT1/epm.epmevthist.f1'       85248 , FILE   '/db/a3inew3d/db2data/EPMEVENT/EPMEVENT2/epm.epmevthist.f2'       852
48 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 5 USING ( FILE   '/db/a3inew3d/db2data/EPMEVENT/EPMEVENT1/epm.epmnothist.f1'       85248 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 6 USING ( PATH   '/db/a3inew3d/db2data/EPMEVENT/SYSTOOLSPACE' )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 8 USING ( PATH   '/db/a3inew3d/db2data/EPMEVENT/tempspace/tmp1' )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 9 USING ( PATH   '/db/a3inew3d/db2data/EPMEVENT/QP_tbsp' )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

3.
dst33lp14:/db/a3inew1d/db2backup$ db2 RESTORE DATABASE EPMEVENT CONTINUE
DB20000I  The RESTORE DATABASE command completed successfully.

4.
dst33lp14:/db/a3inew1d/db2backup$ db2 get db cfg for EPMEVENT|grep -i LOGARCHMETH
 First log archive method                 (LOGARCHMETH1) = TSM
 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 值为TSM:STANDARD，但机器上未安装TSM软件，所以改成本地的一个绝对路径来作为归档日志的路径：

5.
dst33lp14:/db/a3inew1d/db2backup$ db2 update db cfg for EPMEVENT using LOGARCHMETH1  DISK:/db/a3inew3d/arclog_EPMEVENT/
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

6.修改过之后：
dst33lp14:/db/a3inew1d/db2backup$ db2 get db cfg for EPMEVENT|grep -i LOGARCHMETH                                     
 First log archive method                 (LOGARCHMETH1) = DISK:/db/a3inew3d/arclog_EPMEVENT/
 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) =

7. 连接db，提示让回滚(因为restore语句里没有加WITHOUT ROLLING FORWARD)：
dst33lp14:/db/a3inew1d/db2backup$ db2 connect to EPMEVENT
SQL1117N  A connection to or activation of database "EPMEVENT" cannot be made
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

8.
dst33lp14:/db/a3inew1d/db2backup$ db2 "ROLLFORWARD DATABASE EPMEVENT to end of logs and stop overflow log path('/db/a3inew3d/actlog_EPMEVENT')"

                                 Rollforward Status

 Input database alias                   = EPMEVENT
 Number of members have returned status = 1

 Member ID                              = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0017385.LOG
 Log files processed                    = S0017379.LOG - S0017384.LOG
 Last committed transaction             = 2014-12-08-05.05.46.000000 UTC

DB20000I  The ROLLFORWARD command completed successfully.
注意，此处的log path需和restore语句中的logtarget相同。

9.
dst33lp14:/db/a3inew1d/db2backup$ db2 connect to EPMEVENT

   Database Connection Information

 Database server        = DB2/AIX64 10.1.3
 SQL authorization ID   = A3INEW1D
 Local database alias   = EPMEVENT

-----------------------注意事项及我遇到的问题：
1.修改表空间的时候(step 1)，对于系统表空间, 是Path类型的，如果该path下有文件，就会报错：
SET TABLESPACE CONTAINERS FOR 8 USING ( PATH   '/db/a3inew3d/db2data/EPMEVENT/tempspace' )
SQL0298N  Bad container path.  SQLSTATE=428B2
处理方法，可以将该文件删掉，或者在该路径下建子路径，子路径加到命令里如果不存在则会自动创建：
SET TABLESPACE CONTAINERS FOR 8 USING ( PATH   '/db/a3inew3d/db2data/EPMEVENT/tempspace/tmp1' )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

2.如果是在线备份的镜像文件(包含log)，在restore语句中要加LOGTARGET选项(step 1)，不然就不会产生日志。在执行回滚的时候就会报错：
db2 ROLLFORWARD DATABASE EPMEVENT to end of logs and complete
SQL1273N  An operation reading the logs on database "EPMEVENT" cannot continue
because of a missing log file "S0017379.LOG" on database partition "0" and log
stream "0".
因为没有在镜像文件中产生log。
3.在线备份的镜像文件恢复，在回滚(step 8)的时候，需要指定overflow log，且与restore中的logtarget相同。

4. LOGARCHMETH1 值为TSM:STANDARD，但机器上未安装TSM软件，如果不修改LOGARCHMETH1，会报错：
dst33lp14:/db/a3inew1d/db2backup$ db2 ROLLFORWARD DATABASE EPMEVENT to end of logs and complete
SQL2071N  An error occurred while accessing the shared library
"/home/a3inew3d/sqllib/adsm/libtsm.a". Reason code: "1".

5.因为是在线且包含log的备份，所以要到log末尾； 因为是重定向备份，所以ROLLFORWARD的时候要加上overflow log path选项。 不然就会报错：
dst33lp14:/db/a3inew1d/db2backup$ db2 ROLLFORWARD DATABASE EPMEVENT to end of backup and stop   
SQL1274N  The database "EPMEVENT" requires roll-forward recovery and the
point-in-time must be to the end of logs.

dst33lp14:/db/a3inew3d/actlog_EPMEVENT/NODE0000/LOGSTREAM0000/LOGSTREAM0000$ db2 ROLLFORWARD DATABASE EPMEVENT stop                    
SQL1276N  Database "EPMEVENT" cannot be brought out of rollforward pending
state until roll-forward has passed a point in time greater than or equal to
"2014-12-08-05.05.46.000000 UTC", because node "0" contains information later
than the specified time.

5.回滚要指定stop/complete，不然即使回滚成功，数据库也还是ROLL-FORWARD PENDING，不能连。