DB2 bakcup online 中的include log参数调查

    进行数据库online备份的时候，在备份结束的时候，会强制归档日志，然后完成online备份过程。这样对一个online的数据库备份，查看其history

$ db2 list history backup since 20130824 for leotest

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

 

  00001 SYSCATSPACE                                                           
  00002 USERSPACE1                                                            
  00003 SYSTOOLSPACE                                                          
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP LEOTEST ONLINE                                        
 Start Time: 20130824233334
   End Time: 20130824233527
     Status: A
 ----------------------------------------------------------------------------
  EID: 67 Location: /home/db2inst1/backup_data/online_bak

 

可以看到其中标明的

Earliest Log即对应FIRST_LOG

Current Log即对应LAST_LOG

对于一个online备份，在这两个日志号（含）的日志，必须进行完整保存，无论是放在活动日志目录或者是归档日志目录，否则，一旦损失了其中的任意一个，这个备份就是不可用的（不可前滚到备份结束点？）。

于是引发了一个问题，既然是一次备份，为什么还要把数据和日志分开，既要保证备份的可用，又要保证日志的可用，维护成本是不是有点高？

幸好db2在备份的时候提供了这样一个选项：include logs。这个选项从9.7开始已经成为默认选项（看来，db2也推荐这种将数据和日志一起保存的做法）。

这个选项的深层含义，就是：

 

        将开始online备份时的第一个日志（FIRST_LOG）和备份结束时的最后一个日志（LAST_LOG），这之间的全部的日志，全部打包到备份文件中。这样，用一个备份文件，就可以将数据库恢复到这次online备份结束的时候（剩余的部分需要使用备份结束后的日志进行前滚）。

 

下面开始验证：

1.启动一个大数据量的import，模拟备份过程中的应用并发事务：

db2 import from test.ixf of ixf commitcount 100 insert into leotest

2.查看开始备份前的日志号：

$ db2pd -logs -db leotest

 

Database Partition 0 -- Database LEOTEST -- Active -- Up 0 days 07:47:54

 

Logs:
Current Log Number            22        
Pages Written                 273       
Cur Commit Disk Log Reads     0                   
Cur Commit Total Log Reads    0                   
Method 1 Archive Status       n/a
Method 1 Next Log to Archive  22        
Method 1 First Failure        n/a
Method 2 Archive Status       n/a
Method 2 Next Log to Archive  n/a
Method 2 First Failure        n/a

 

Address            StartLSN         State      Size       Pages      Filename
0x00002B1878861550 0000000007740010 0x00000000 1024       1024       S0000022.LOG
0x00002B1878861DB0 0000000007B40010 0x00000000 1024       1024       S0000023.LOG
0x00002B187E5D8750 0000000007F40010 0x00000000 1024       1024       S0000024.LOG

3.启动一个online backup

db2 backup database leotest online

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

4.备份成功，查看备份的历史记录

$ db2 list history backup since 20130824 for leotest

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

 

  00001 SYSCATSPACE                                                           
  00002 USERSPACE1                                                            
  00003 SYSTOOLSPACE                                                          
 ----------------------------------------------------------------------------
    Comment: DB2 BACKUP LEOTEST ONLINE                                        
 Start Time: 20130824233334
   End Time: 20130824233527
     Status: A
 ----------------------------------------------------------------------------
  EID: 67 Location: /home/db2inst1/backup_data/online_bak

5.从备份中，将备份中德日志释放出来（注意，这需要很长的时间，基本和使用这个备份进行restore数据库的时间一样）

$ db2 restore db leotest logs logtarget  /home/db2inst1/backup_data/online_bak
DB20000I  The RESTORE DATABASE command completed successfully.

6.查看从备份中释放出来的数据库日志：
$ ls -rtl
total 3392836
-rw------- 1 db2inst1 db2iadm1 3377524736 Aug 24 23:35 LEOTEST.0.db2inst1.NODE0000.CATN0000.20130824233334.001
-rw------- 1 db2inst1 db2iadm1     884736 Aug 25 00:10 S0000048.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000045.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000047.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000046.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000044.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000043.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000042.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000041.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000040.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000039.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000038.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000037.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000036.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000035.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000033.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000034.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000032.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000031.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000030.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000029.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000028.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000027.LOG
-rw------- 1 db2inst1 db2iadm1    4202496 Aug 25 00:10 S0000026.LOG

注意释放出来的日志的开始日志号和结束日志号：

这和

$ db2 list history backup since 20130824 for leotest

 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log  Backup ID
 -- --- ------------------ ---- --- ------------ ------------ --------------
  B  D  20130824233334001   N    D  S0000026.LOG S0000048.LOG  
 ----------------------------------------------------------------------------
中的第一个日志和最后一个日志是完全一样的。

7.在数据库中插入一些数据：

$ db2 "select count(*) from test"

 

1         

-----------

       1921

 

  1 record(s) selected.

 

$ db2 "select count(*) from test"

$ sh insert.sh

$ db2 "select count(*) from test"

 

1         

-----------

       1940

 

  1 record(s) selected.

8.保留此备份，然后，将数据库的活动日志，归档日志，全部删除

活动日志：

$ cd /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/

$ ls

S0000021.LOG  S0000064.LOG  S0000065.LOG  S0000066.LOG

$ pwd

/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR

$ mv * /home/db2inst1/backup_data/

$ ls -l

total 0

$

归档日志：

$ cd /home/db2inst1/archlog/
$ ls 
db2inst1  db2inst1_old
$ mv db2inst1 db2inst1_old_2
$ ls 
db2inst1_old  db2inst1_old_2
$

9.使用刚才的备份进行数据库restore，同时，将log释放到活动日志目录下：

$ db2 restore db leotest logtarget /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR

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

10.restore完毕，进行查看日志并前滚

$ ls

S0000026.LOG  S0000030.LOG  S0000034.LOG  S0000038.LOG  S0000042.LOG  S0000046.LOG

S0000027.LOG  S0000031.LOG  S0000035.LOG  S0000039.LOG  S0000043.LOG  S0000047.LOG

S0000028.LOG  S0000032.LOG  S0000036.LOG  S0000040.LOG  S0000044.LOG  S0000048.LOG

S0000029.LOG  S0000033.LOG  S0000037.LOG  S0000041.LOG  S0000045.LOG

$ pwd

/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR

日志已经全部从备份中抽出并放到了活动日志目录中

 

开始前滚：

$ db2 rollforward db leotest query status

 

                                 Rollforward Status

 

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

 

 Node number                            = 0
 Rollforward status                     = DB  pending
 Next log file to be read               = S0000026.LOG
 Log files processed                    =  -
 Last committed transaction             = 2013-08-24-15.35.22.000000 UTC

 

$ db2 rollforward db leotest to end of logs and stop

 

                                 Rollforward Status

 

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

 

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000026.LOG - S0000049.LOG
 Last committed transaction             = 2013-08-24-15.35.22.000000 UTC

 

DB20000I  The ROLLFORWARD command completed successfully.
$ db2 rollforward db leotest query status          

 

                                 Rollforward Status

 

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

 

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000026.LOG - S0000049.LOG
 Last committed transaction             = 2013-08-24-15.35.22.000000 UTC

 

$ db2 connect to leotest

 

   Database Connection Information

 

 Database server        = DB2/LINUXX8664 9.7.0
 SQL authorization ID   = DB2INST1
 Local database alias   = LEOTEST

 

前滚成功。

 

11.查看数据情况：

$ db2 "select count(*) from test"

 

1         

-----------

       1921

 

  1 record(s) selected.

 

$

发现备份之后的操作，都没有了，这和我们的预期一样。

 

12.将备份之后的日志（活动和归档）拷回到活动日志目录，然后对数据库进行强制前滚到日志结尾：

$  cd backup_data/

$ ls

offline_bak  online_bak  S0000021.LOG  S0000064.LOG  S0000065.LOG  S0000066.LOG

$ mv S* /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR

$ cd /home/db2inst1/archlog/
$ ls
db2inst1  db2inst1_old  db2inst1_old_2
$ cd db2inst1_old_2
$ ls
LEOTEST
$ cd LEOTEST/
$ ls
NODE0000
$ cd NODE0000/
$ ls
C0000004  C0000005
$ cd C0000004
$ ls
$ ls
$ cd ..
$ ls
C0000004  C0000005
$ cd C0000005
$ ls
S0000022.LOG  S0000029.LOG  S0000036.LOG  S0000043.LOG  S0000050.LOG  S0000057.LOG  S0000064.LOG
S0000023.LOG  S0000030.LOG  S0000037.LOG  S0000044.LOG  S0000051.LOG  S0000058.LOG
S0000024.LOG  S0000031.LOG  S0000038.LOG  S0000045.LOG  S0000052.LOG  S0000059.LOG
S0000025.LOG  S0000032.LOG  S0000039.LOG  S0000046.LOG  S0000053.LOG  S0000060.LOG
S0000026.LOG  S0000033.LOG  S0000040.LOG  S0000047.LOG  S0000054.LOG  S0000061.LOG
S0000027.LOG  S0000034.LOG  S0000041.LOG  S0000048.LOG  S0000055.LOG  S0000062.LOG
S0000028.LOG  S0000035.LOG  S0000042.LOG  S0000049.LOG  S0000056.LOG  S0000063.LOG

 

$ ls /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR
S0000021.LOG  S0000028.LOG  S0000035.LOG  S0000042.LOG  S0000049.LOG  S0000056.LOG  S0000063.LOG
S0000022.LOG  S0000029.LOG  S0000036.LOG  S0000043.LOG  S0000050.LOG  S0000057.LOG  S0000064.LOG
S0000023.LOG  S0000030.LOG  S0000037.LOG  S0000044.LOG  S0000051.LOG  S0000058.LOG  S0000065.LOG
S0000024.LOG  S0000031.LOG  S0000038.LOG  S0000045.LOG  S0000052.LOG  S0000059.LOG  S0000066.LOG
S0000025.LOG  S0000032.LOG  S0000039.LOG  S0000046.LOG  S0000053.LOG  S0000060.LOG
S0000026.LOG  S0000033.LOG  S0000040.LOG  S0000047.LOG  S0000054.LOG  S0000061.LOG
S0000027.LOG  S0000034.LOG  S0000041.LOG  S0000048.LOG  S0000055.LOG  S0000062.LOG

 

将数据块强制设置为rollforward pending，然后进行前滚