DB2学习过程--备份与恢复（二）

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

delta备份和恢复

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

 

【delta备份思想】

完全备份后在做N次delta备份，恢复的时候先恢复想要恢复的时间戳,然后一次恢复全备份，按照顺序恢复增量备份。

检验方法：

1、全备份

2、插入记录后，再做delta备份

3、删除记录，再做delta备份

4、再插入数据，再做delta备份

 

这时候备份的目录下有4个文件，一个完全备份，3个增量备份文件。

 

下面是操作步骤：

[db2inst1@fedora ~]$ db2 backup db testdb online to /home/db2inst1/delta/ include logs

 

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

test1 表 500条

 

插入500条后

[db2inst1@fedora ~]$ db2 backup db testdb  online incremental delta to /home/db2inst1/delta/ include logs

 

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

 

删除部分数据

 

[db2inst1@fedora ~]$ db2 -vf sql/del.sql

delete from  test1 where id=3 or id=5 or id =17

DB20000I  The SQL command completed successfully.

 

[db2inst1@fedora ~]$ db2 "select count(*) from test1"

 

1

-----------

        994

 

  1 record(s) selected.

 

[db2inst1@fedora ~]$ db2 backup db testdb  online incremental delta to /home/db2inst1/delta/ include logs

 

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

 

插入500条数据

 

[db2inst1@fedora ~]$ db2 "select count(*) from test1"

 

1

-----------

       1494

 

  1 record(s) selected.

 

[db2inst1@fedora ~]$

 

[db2inst1@fedora ~]$ db2 backup db testdb  online incremental delta to /home/db2inst1/delta/ include logs

 

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

 

查看备份文件

[db2inst1@fedora ~]$ ll -h delta/

total 81M

drwxr-xr-x  2 db2inst1 db2grp1 4.0K May 28 09:55 logs

-rw-r-----  1 db2inst1 db2grp1  39M May 28 09:56 TESTDB.0.db2inst1.NODE0000.CATN0000.20060528095621.001

-rw-r-----  1 db2inst1 db2grp1  15M May 28 09:58 TESTDB.0.db2inst1.NODE0000.CATN0000.20060528095815.001

-rw-r-----  1 db2inst1 db2grp1  15M May 28 10:05 TESTDB.0.db2inst1.NODE0000.CATN0000.20060528100510.001

-rw-r-----  1 db2inst1 db2grp1  15M May 28 10:07 TESTDB.0.db2inst1.NODE0000.CATN0000.20060528100655.001

 

开始恢复，命令如下：

 

[db2inst1@fedora delta]$ db2 restore db testdb incremental taken at 20060528100655 into testdb logtarget /home/db2inst1/delta/logs/

DB20000I  The RESTORE DATABASE command completed successfully.

[db2inst1@fedora delta]$ db2 restore db testdb incremental taken at 20060528095621 into testdb logtarget /home/db2inst1/delta/logs/

DB20000I  The RESTORE DATABASE command completed successfully.

[db2inst1@fedora delta]$ db2 restore db testdb incremental taken at 20060528095815 into testdb logtarget /home/db2inst1/delta/logs/

SQL2580W  Warning! Restoring logs to a path which contains existing log files. Attempting to overwrite an existing log file during restore will cause the restore operation to fail.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

[db2inst1@fedora delta]$ db2 restore db testdb incremental taken at 20060528100510 into testdb logtarget /home/db2inst1/delta/logs/

SQL2580W  Warning! Restoring logs to a path which contains existing log files. Attempting to overwrite an existing log file during restore will cause the restore operation to fail.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

[db2inst1@fedora delta]$ db2 restore db testdb incremental taken at 20060528100655 into testdb logtarget /home/db2inst1/delta/logs/

SQL2580W  Warning! Restoring logs to a path which contains existing log files. Attempting to overwrite an existing log file during restore will cause the restore operation to fail.

Do you want to continue ? (y/n) y

DB20000I  The RESTORE DATABASE command completed successfully.

 

日志也恢复了

 

[db2inst1@fedora delta]$ cd logs/

[db2inst1@fedora logs]$ pwd

/home/db2inst1/delta/logs

[db2inst1@fedora logs]$ ll

total 144

-rw-------  1 db2inst1 db2grp1 12288 May 28 12:42 S0000025.LOG

-rw-------  1 db2inst1 db2grp1 57344 May 28 12:42 S0000026.LOG

-rw-------  1 db2inst1 db2grp1 12288 May 28 12:43 S0000027.LOG

-rw-------  1 db2inst1 db2grp1 57344 May 28 12:44 S0000028.LOG

 

进行日志回顾

[db2inst1@fedora logs]$ db2 "rollforward db testdb to end of logs and stop overflow log path(/home/db2inst1/delta/logs)"

SQL1268N  Roll-forward recovery stopped due to error "24" while retrieving log

file "S0000029.LOG" for database "TESTDB" on node "0".

 

提示缺少"S0000029.LOG"日志，必须恢复该日志，如不恢复该日志，该数据库就处在回滚挂起状态。

 

[db2inst1@fedora logs]$ ll

total 144

-rw-------  1 db2inst1 db2grp1 12288 May 28 12:42 S0000025.LOG

-rw-------  1 db2inst1 db2grp1 57344 May 28 12:42 S0000026.LOG

-rw-------  1 db2inst1 db2grp1 12288 May 28 12:43 S0000027.LOG

-rw-------  1 db2inst1 db2grp1 57344 May 28 12:44 S0000028.LOG

 

[db2inst1@fedora logs]$ scp db2inst2@10.4.5.222:~/delta/logs/S0000029.LOG .

db2inst2@10.4.5.222's password:

S0000029.LOG                                                                                                              100% 4008KB   3.9MB/s   00:01

 

[db2inst1@fedora logs]$ ll

total 4156

-rw-------  1 db2inst1 db2grp1   12288 May 28 12:42 S0000025.LOG

-rw-------  1 db2inst1 db2grp1   57344 May 28 12:42 S0000026.LOG

-rw-------  1 db2inst1 db2grp1   12288 May 28 12:43 S0000027.LOG

-rw-------  1 db2inst1 db2grp1   57344 May 28 12:44 S0000028.LOG

-rw-------  1 db2inst1 db2grp1 4104192 May 28 12:51 S0000029.LOG

 

得到"S0000029.LOG"日志后继续恢复

 

[db2inst1@fedora logs]$ db2 "rollforward db testdb to end of logs and stop overflow log path(/home/db2inst1/delta/logs)"

 

                                 Rollforward Status

 

 Input database alias                   = testdb

 Number of nodes have returned status   = 1

 

 Node number                            = 0

 Rollforward status                     = not pending

 Next log file to be read               =

 Log files processed                    = S0000028.LOG - S0000029.LOG

 Last committed transaction             = 2006-05-28-02.37.54.000000

 

DB20000I  The ROLLFORWARD command completed successfully.

[db2inst1@fedora logs]$ db2 connect to testdb

 

   Database Connection Information

 

 Database server        = DB2/LINUX 8.2.0

 SQL authorization ID   = DB2INST1

 Local database alias   = TESTDB

 

[db2inst1@fedora logs]$ db2 list tables

 

Table/View                      Schema          Type  Creation time

------------------------------- --------------- ----- --------------------------

ABC                             DB2INST1        T     2006-05-27-15.28.54.515684

CL_SCHED                        DB2INST1        T     2006-05-26-09.20.25.850316

DEPARTMENT                      DB2INST1        T     2006-05-26-09.20.24.143790

EMPLOYEE                        DB2INST1        T     2006-05-26-09.20.24.176826

EMP_ACT                         DB2INST1        T     2006-05-26-09.20.24.313541

EMP_PHOTO                       DB2INST1        T     2006-05-26-09.20.24.632435

EMP_RESUME                      DB2INST1        T     2006-05-26-09.20.25.572518

IN_TRAY                         DB2INST1        T     2006-05-26-09.20.25.858343

ORG                             DB2INST1        T     2006-05-26-09.20.23.478292

PROJECT                         DB2INST1        T     2006-05-26-09.20.24.564050

SALES                           DB2INST1        T     2006-05-26-09.20.25.697360

STAFF                           DB2INST1        T     2006-05-26-09.20.23.909951

STAFFG                          DB2INST1        T     2006-05-26-09.20.24.042238

TEST1                           DB2INST1        T     2006-05-26-11.48.46.446307

 

  14 record(s) selected.

 

[db2inst1@fedora logs]$ db2 "select count(*) from test1"

 

1

-----------

       1494

 

  1 record(s) selected.

 

[db2inst1@fedora logs]$

 

恢复成功了！

 

 

突发奇想，如果把书库恢复到另外一个实例下会有啥效果呢，试试！

 

开始恢复，恢复到另外一个实例下，命令如下：

 

db2 restore db testdb incremental taken at 20060528100655 into testdb logtarget /home/db2inst2/delta/logs/

db2 restore db testdb incremental taken at 20060528095621 into testdb logtarget /home/db2inst2/delta/logs/

db2 restore db testdb incremental taken at 20060528095815 into testdb logtarget /home/db2inst2/delta/logs/

db2 restore db testdb incremental taken at 20060528100510 into testdb logtarget /home/db2inst2/delta/logs/

db2 restore db testdb incremental taken at 20060528100655 into testdb logtarget /home/db2inst2/delta/logs/

 

 

[db2inst2@fedora logs]$ db2 "rollforward db testdb to end of logs and stop overflow log path(/home/db2inst2/delta/logs/)"

SQL1268N  Roll-forward recovery stopped due to error "24" while retrieving log

file "S0000029.LOG" for database "TESTDB" on node "0".

[db2inst2@fedora logs]$ ll

total 144

-rw-------  1 db2inst2 db2grp1 12288 May 28 10:54 S0000025.LOG

-rw-------  1 db2inst2 db2grp1 57344 May 28 10:54 S0000026.LOG

-rw-------  1 db2inst2 db2grp1 12288 May 28 10:55 S0000027.LOG

-rw-------  1 db2inst2 db2grp1 57344 May 28 10:56 S0000028.LOG

想办法把 S0000029.LOG 日志搞来，

[db2inst2@fedora logs]$ ll

total 4156

-rw-------  1 db2inst2 db2grp1   12288 May 28 10:54 S0000025.LOG

-rw-------  1 db2inst2 db2grp1   57344 May 28 10:54 S0000026.LOG

-rw-------  1 db2inst2 db2grp1   12288 May 28 10:55 S0000027.LOG

-rw-------  1 db2inst2 db2grp1   57344 May 28 10:56 S0000028.LOG

-rw-------  1 db2inst2 db2grp1 4104192 May 28 11:00 S0000029.LOG

[db2inst2@fedora logs]$ db2 "rollforward db testdb to end of logs and stop overflow log path(/home/db2inst2/delta/logs/)"

 

                                 Rollforward Status

 

 Input database alias                   = testdb

 Number of nodes have returned status   = 1

 

 Node number                            = 0

 Rollforward status                     = not pending

 Next log file to be read               =

 Log files processed                    = S0000028.LOG - S0000029.LOG

 Last committed transaction             = 2006-05-28-02.37.54.000000

 

DB20000I  The ROLLFORWARD command completed successfully.

 

检查：

 

[db2inst2@fedora logs]$ db2 list tables

 

Table/View                      Schema          Type  Creation time

------------------------------- --------------- ----- --------------------------

 

  0 record(s) selected.

 

[db2inst2@fedora logs]$

 

 

？？？？，提示成功恢复了，表咋没有了呢？

 

问Paolo，表名是按照原来的实例进行恢复的，如果想使用表，必须在表前面加上原来的实例名称。

因此我们要恢复数据的时候一定要恢复成原来的实例名。

 

 

[db2inst1@fxtest3 backlogs]$ db2 rollforward db db2fxs query status

 

                                 Rollforward Status

 

 Input database alias                   = db2fxs

 Number of nodes have returned status   = 1

 

 Node number                            = 0

 Rollforward status                     = DB  working

 Next log file to be read               = S0011735.LOG

 Log files processed                    =  -

 Last committed transaction             = 2006-05-27-01.59.25.000000

 

[db2inst1@fxtest3 backlogs]$