DB2备份恢复(不完全恢复)

全备数据库

[db2inst1@xifenfei ~]$ db2 backup db xff online to /tmp  include logs

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

[db2inst1@xifenfei ~]$ db2 list history backup all for xff

                    List History File for xff

Number of matching file entries = 1


 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20120411213218001   N    D  S0000021.LOG S0000021.LOG  
 ----------------------------------------------------------------------------
  Contains 3 tablespace(s):

 00001 SYSCATSPACE
 00002 USERSPACE1
 00003 SYSTOOLSPACE
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP XFF ONLINE
 Start Time: 20120411213218
   End Time: 20120411213229
     Status: A
 ----------------------------------------------------------------------------
  EID: 42 Location: /tmp

数据操作(包括误操作)

[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 list tables

Table/View                      Schema          Type  Creation time             
------------------------------- --------------- ----- --------------------------
T_01XFF                         DB2INST1        T     2012-04-11-18.23.05.723478
T_02XFF                         DB2INST1        T     2012-04-11-18.30.26.639326

  2 record(s) selected.

[db2inst1@xifenfei ~]$ db2 "create table t_03xff like t_01xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into t_03xff select * from t_01xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ date
Wed Apr 11 21:33:42 CST 2012
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"

1          
-----------
        370

  1 record(s) selected.

--以下是错误操作,需要回滚
[db2inst1@xifenfei ~]$ date
Wed Apr 11 21:36:38 CST 2012
[db2inst1@xifenfei ~]$ db2 "insert into t_03xff select * from t_01xff"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"

1          
-----------
        740

  1 record(s) selected.

还原数据库

db2inst1@xifenfei ~]$ db2 restore db xff from /tmp taken at 20120411213218
SQL2539W  Warning!  Restoring to an existing database that is the same as the 
backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

恢复数据库

[db2inst1@xifenfei ~]$ db2 rollforward db xff to 2012-04-11-21.36.00.00000 using local time

                                 Rollforward Status

 Input database alias                   = xff
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = DB  working
 Next log file to be read               = S0000023.LOG
 Log files processed                    = S0000021.LOG - S0000021.LOG
 Last committed transaction             = 2012-04-11-21.33.27.000000 Local

DB20000I  The ROLLFORWARD command completed successfully.
[db2inst1@xifenfei ~]$ db2 connect to xff
SQL1117N  A connection to or activation of database "XIFENFEI" cannot be made 
because of ROLL-FORWARD PENDING.  SQLSTATE=57019

--停止前滚
[db2inst1@xifenfei ~]$ db2 rollforward db xff stop

                                 Rollforward Status

 Input database alias                   = xff
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000021.LOG - S0000022.LOG
 Last committed transaction             = 2012-04-11-21.33.27.000000 Local

DB20000I  The ROLLFORWARD command completed successfully.

验证数据

[db2inst1@xifenfei ~]$ db2 connect to xff

   Database Connection Information

 Database server        = DB2/LINUX 9.5.9
 SQL authorization ID   = DB2INST1
 Local database alias   = XFF

[db2inst1@xifenfei ~]$ db2 "select count(*) from t_03xff"

1          
-----------
        370

  1 record(s) selected.