CommVault针对DB2增量备份恢复测试记录

1、DB2增量备份恢复测试纪录朱晓凯2013年9月30日一、环境介绍l 备份服务器：IP地址：192.168.1.3主机名：cs操作系统： windows2008 X64 CommVault版本：Simpana 9.0 SP10bl 介质服务器：IP地址:192.168.1.3主机名：cs操作系统： windows2008 X64 CommVault版本：Simpana 9.0 SP10b l 测试服务器：IP地址：192.168.1.21主机名：linux-tesu操作系统： redhat 5.4数据库：DB2 9.7.0.1CommVault版本：Simpana 9.0 SP10二、数据环境准

2、备及备份纪录2.1、第一次全备份份时的数据1、新建表tb100db2 create table t100(col1 char(100), col char(100)db2 begin atomic declare i int default 0;while (i10000) do insert into t100 values(知行合一 | char(i), 王守仁 | char(i) );set i=i+1; end while; enddb2inst1db2-2 $2、查看表纪录db2inst1linux-tesu C0000002$ db2 list tables;Table/View

3、Schema Type Creation time - - - -MYTAB DB2INST1 T 2013-01-10-19.19.06.148500T100 DB2INST1 T 2013-09-29-17.40.29.579708T11 DB2INST1 T 2013-02-16-14.20.04.642974T12 DB2INST1 T 2013-02-16-14.20.52.366899TB1 DB2INST1 T 2013-01-06-11.09.38.101713TEST DB2INST1 T 2013-01-05-13.18.56.6727073、采用CommVault进行备份

4、2.2、第一次增量备份1、插入表tb200db2 create table t200(col1 char(100), col char(100)db2 begin atomic declare i int default 0;while (i10000) do insert into t200 values(知行合一 | char(i), 王守仁 | char(i) );set i=i+1; end while; end2、查看表纪录db2inst1linux-tesu C0000002$ db2 list tables;Table/View Schema Type Creation time

5、 - - - -MYTAB DB2INST1 T 2013-01-10-19.19.06.148500T100 DB2INST1 T 2013-09-29-17.40.29.579708T11 DB2INST1 T 2013-02-16-14.20.04.642974T12 DB2INST1 T 2013-02-16-14.20.52.366899T200 DB2INST1 T 2013-09-29-17.44.42.100103TB1 DB2INST1 T 2013-01-06-11.09.38.101713TEST DB2INST1 T 2013-01-05-13.18.56.672707

6、 7 record(s) selected.3、采用CommVault进行备份：2.3、第二次增量备份1、插入表tb300db2 create table t300(col1 char(100), col char(100)db2 begin atomic declare i int default 0;while (i10000) do insert into t300 values(知行合一 | char(i), 王守仁 | char(i) );set i=i+1; end while; end2、查看表纪录db2inst1linux-tesu C0000002$ db2 list tab

7、les;Table/View Schema Type Creation time - - - -MYTAB DB2INST1 T 2013-01-10-19.19.06.148500T100 DB2INST1 T 2013-09-29-17.40.29.579708T11 DB2INST1 T 2013-02-16-14.20.04.642974T12 DB2INST1 T 2013-02-16-14.20.52.366899T200 DB2INST1 T 2013-09-29-17.44.42.100103T300 DB2INST1 T 2013-09-29-17.49.43.269689T

8、B1 DB2INST1 T 2013-01-06-11.09.38.101713TEST DB2INST1 T 2013-01-05-13.18.56.6727078 record(s) selected.3、采用CommVault进行备份2.4、第二次全全备份1、插入表tb400db2 create table t400(col1 char(100), col char(100)db2 begin atomic declare i int default 0;while (i10000) do insert into t400 values(知行合一 | char(i), 王守仁 | cha

9、r(i) );set i=i+1; end while; end2、查看表纪录db2inst1linux-tesu C0000002$ db2 list tables;Table/View Schema Type Creation time - - - -MYTAB DB2INST1 T 2013-01-10-19.19.06.148500T100 DB2INST1 T 2013-09-29-17.40.29.579708T11 DB2INST1 T 2013-02-16-14.20.04.642974T12 DB2INST1 T 2013-02-16-14.20.52.366899T200

10、DB2INST1 T 2013-09-29-17.44.42.100103T300 DB2INST1 T 2013-09-29-17.49.43.269689T400 DB2INST1 T 2013-09-29-17.56.38.437426TB1 DB2INST1 T 2013-01-06-11.09.38.101713TEST DB2INST1 T 2013-01-05-13.18.56.672707 9 record(s) selected.3、采用CommVault进行全备份三、数据库损坏模拟删除库mydb1、停止数据库 db2inst1db2-2 $ db2stop force;SQ

11、L1064N DB2STOP processing was successful.db2inst1db2-2 $ db2startSQL1063N DB2START processing was successful.2、删除myddb数据库db2inst1db2-2 $ db2 drop db mydb;DB20000I The DROP DATABASE command completed successfully.四、数据恢复4.1、恢复到第二次新增量备份时的数据4.1.1、恢复数据文件1、首先需要创建一个新的数据库db2inst1linux-tesu C0000002$ db2 cre

12、ate db mydbDB20000I The CREATE DATABASE command completed successfully.db2inst1linux-tesu C0000002$ 2、修改CommVault参数db2inst1linux-tesu C0000002$ db2 update db cfg for mydb using VENDOROPT CvSrcClientName=linux-tesu,CvSrcDb2InstanceName=db2inst1,CvClientName=linux-tesu,CvInstanceName=Instance001DB2000

13、0I The UPDATE DATABASE CONFIGURATION command completed successfully.注：如果不创建数据库，不修改参数，只能进行全备份的恢复，不能进行增量+全备份的恢复即把数据直接恢复到第二个增量备份3、取消勾选“恢复DB” 3、设置恢复参数，选择需要恢复的映像注：这里需要把最新的增量备份和与之对应的全备份进行选择。4、设置恢复的目标计算机，这里是本机恢复选择默认即可5、点击高级选项，在前滚选项中，取消勾选“前滚”，并勾选“保持前滚未决状态”6、在日志文件中，取消勾选“恢复日志文件”注：这里先不恢复日志，下面单独进行日志的恢复7、设置完成后，点

14、击“OK”，开始恢复4.1.2、恢复日志文件1、查看第一个活动日志db2inst1linux-tesu C0000002$ db2 get db cfg for mydb |grep -i log Log retain for recovery status = RECOVERY User exit for logging status = YES Catalog cache size (4KB) (CATALOGCACHE_SZ) = 203 Log buffer size (4KB) (LOGBUFSZ) = 256 Log file size (4KB) (LOGFILSIZ) = 10

15、24 Number of primary log files (LOGPRIMARY) = 13 Number of secondary log files (LOGSECOND) = 4 Changed path to log files (NEWLOGPATH) = Path to log files = /log/cv/db2_arch/db2inst1/MYDB/NODE0000/C0000001/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = First active log file

16、= S0000145.LOG Block log on disk full (BLK_LOG_DSK_FUL) = NO Block non logged operations (BLOCKNONLOGGED) = NO Percent max primary log space by transaction (MAX_LOG) = 0 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 Percent log file reclaimed before soft chckpt (SOFTMAX) = 520 Log reta

17、in for recovery enabled (LOGRETAIN) = RECOVERY User exit for logging enabled (USEREXIT) = OFF HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC First log archive method (LOGARCHMETH1) = VENDOR:/opt/simpana/Base/libDb2Sbt.so Options for logarchmeth1 (LOGARCHOPT1) = CvClientName=linux-tes

18、u,CvInstanceName=Instance001 Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Failover log archive path (FAILARCHPATH) = Number of log archive retries on error (NUMARCHRETRY) = 5 Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20 Log pages during index build

19、(LOGINDEXBUILD) = OFF2、取消勾选“恢复DB”3、在常规选项中，取消“恢复数据”4、点击高级选项，在前滚选项卡中，取消勾选“前滚”，并勾选“保持前滚未决状态”。5、在日志文中，设置按日志序列号，进行恢复6、设置完成后，点击“OK”开始恢复7、查看恢复出来的日志 db2inst1linux-tesu C0000004$ lsS0000147.LOGdb2inst1linux-tesu C0000004$ pwd/log/cv/db2_ret/db2inst1/MYDB/NODE0000/C00000044.1.3、前滚数据库1、利用归档日志文件进行数据库的前滚db2inst1

20、linux-tesu C0000002$ db2 rollforward db mydb to end of logs and stop overflow log path(/log/cv/db2_ret/db2inst1/MYDB/NODE0000/C0000004) Rollforward Status Input database alias = mydb Number of nodes have returned status = 1 Node number = 0 Rollforward status = not pending Next log file to be read =

21、Log files processed = S0000147.LOG - S0000151.LOG Last committed transaction = 2013-09-29-10.00.42.000000 UTCDB20000I The ROLLFORWARD command completed successfully.2、连接数据库db2inst1linux-tesu C0000004$ db2 connect to mydb; Database Connection Information Database server = DB2/LINUX 9.7.1 SQL authoriz

22、ation ID = DB2INST1 Local database alias = MYDB3、查看表纪录db2inst1linux-tesu C0000004$ db2 list tables;Table/View Schema Type Creation time - - - -MYTAB DB2INST1 T 2013-01-10-19.19.06.148500T100 DB2INST1 T 2013-09-29-17.40.29.579708T11 DB2INST1 T 2013-02-16-14.20.04.642974T12 DB2INST1 T 2013-02-16-14.20

23、.52.366899T200 DB2INST1 T 2013-09-29-17.44.42.100103T300 DB2INST1 T 2013-09-29-17.49.43.269689T400 DB2INST1 T 2013-09-29-17.56.38.437426TB1 DB2INST1 T 2013-01-06-11.09.38.101713TEST DB2INST1 T 2013-01-05-13.18.56.672707注：可以看到，数据恢复到第二次全备份的状态，因为我们是按日志滚的，最后一个日志是同样是第二次全备份时的数据。4.2、恢复到第一次增量备份时的数据前面的恢复步骤相同

24、这一次采用按时间点恢复1、利用日志进行数据库前滚db2 rollforward db mydb to 2013-09-29-17.44.42.100103 using local timedb2inst1linux-tesu C0000004$ db2 rollforward db mydb to 2013-09-29-17.44.42.100103 using local timeSQL1275N The stoptime passed to roll-forward must be greater than or equal to 2013-09-29-17.45.49.000000 Lo

25、cal, because database MYDB on node(s) 0 contains information later than the specified time.db2inst1linux-tesu C0000004$ db2 rollforward db mydb to 2013-09-29-17.45.49.000000 using local time Rollforward Status Input database alias = mydb Number of nodes have returned status = 1 Node number = 0 Rollf

26、orward status = DB working Next log file to be read = S0000147.LOG Log files processed = S0000145.LOG - S0000146.LOG Last committed transaction = 2013-09-29-17.45.49.000000 LocalDB20000I The ROLLFORWARD command completed successfully.db2inst1linux-tesu C0000004$2、连接数据库db2inst1linux-tesu C0000004$ db

27、2 connect to mydb;SQL1117N A connection to or activation of database MYDB cannot be made because of ROLL-FORWARD PENDING. SQLSTATE=57019db2inst1linux-tesu C0000004$ db2 connect to mydb;SQL1117N A connection to or activation of database MYDB cannot be made because of ROLL-FORWARD PENDING. SQLSTATE=57019注：因为我们还没有结束日志前滚状态，我们需要结束日志前滚状态，才能正常连接数据库。3、结束日志前滚状态db2inst1linux-tesu C0000004$ db2 rollforward db mydb stop Rollforward Status Input databa