CNDBA社区
1.环境准备
[root@db01 ~]# cat /etc/hosts
192.168.2.113 db01
192.168.2.123 db02
2.在db01上面创建测试库
创建测试库
db2 "CREATE DB testdb on /db2data/testdb using codeset UTF-8 territory us PAGESIZE 8192"
db2 "UPDATE DB CFG FOR testdb USING applheapsz 4096"
db2 "UPDATE DB CFG FOR testdb USING app_ctl_heap_sz 1024"
db2 "UPDATE DB CFG FOR testdb USING stmtheap 32768"
db2 "UPDATE DB CFG FOR testdb USING dbheap 2400"
db2 "UPDATE DB CFG FOR testdb USING locklist 1000"
db2 "UPDATE DB CFG FOR testdb USING logfilsiz 4000"
db2 "UPDATE DB CFG FOR testdb USING logprimary 12"
db2 "UPDATE DB CFG FOR testdb USING logsecond 20"
db2 "UPDATE DB CFG FOR testdb USING logbufsz 32"
db2 "UPDATE DB CFG FOR testdb USING avg_appls 5"
db2 "UPDATE DB CFG FOR testdb USING locktimeout 30"
db2 "UPDATE DB CFG FOR testdb using AUTO_MAINT off"
启用归档
[db2inst1@db01 db2data]$ db2 update db cfg for testdb using LOGARCHMETH1 DISK:/archivelogs/
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
启用增量备份
[db2inst1@db01 db2data]$ db2 update db cfg for testdb using TRACKMOD YES
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
解除backup pending
[db2inst1@db01 db2data]$ db2 backup db testdb to /dev/null
Backup successful. The timestamp for this backup image is : 20190812214559
创建测试表，并插入数据
[db2inst1@db01 db2data]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 10.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB
[db2inst1@db01 db2data]$ db2 "CREATE TABLE t1(id int, val varchar(20))"
db2 "insert into t1 values(1, 'aaa')"
db2 "insert into t1 values(2, 'bbb')"
DB20000I The SQL command completed successfully.
[db2inst1@db01 db2data]$ db2 "insert into t1 values(1, 'aaa')"
DB20000I The SQL command completed successfully.
[db2inst1@db01 db2data]$ db2 "insert into t1 values(2, 'bbb')"
DB20000I The SQL command completed successfully.
[db2inst1@db01 db2data]$
2.全量备份
[db2inst1@db01 db2data]$ db2 backup db testdb online to /db2data/backup/ include logs
Backup successful. The timestamp for this backup image is : 20190812215227
3.插入数据再进行增量备份
[db2inst1@db01 db2data]$ db2 connect to testdb
[db2inst1@db01 db2data]$ db2 "insert into t1 values(3, 'ccc')"
[db2inst1@db01 db2data]$ db2 "insert into t1 values(4, 'ddd')"
[db2inst1@db01 db2data]$ db2 backup db testdb online INCREMENTAL to /db2data/backup/ include logs
Backup successful. The timestamp for this ackup image is : 20190812215450
4.插入数据并进行差异备份
[db2inst1@db01 db2data]$ db2 connect to testdb
[db2inst1@db01 db2data]$ db2 "insert into t1 values(5, 'eee')"
[db2inst1@db01 db2data]$ db2 "insert into t1 values(6, 'fff')"
[db2inst1@db01 db2data]$ db2 backup db testdb ONLINE INCREMENTAL DELTA to /db2data/backup/ include logs
Backup successful. The timestamp for this backup image is : 20190812220039
5.活动日志
[db2inst1@db01 db2data]$ db2 connect to testdb
[db2inst1@db01 db2data]$ db2 "insert into t1 values(7, 'ggg')"
[db2inst1@db01 db2data]$ db2 "insert into t1 values(8, 'hhh')"
6.查看所有数据
[db2inst1@db01 db2data]$ db2 "select * from t1"
ID VAL
----------- --------------------
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
6 fff
7 ggg
8 hhh
8 record(s) selected.
7.将三个备份文件拷贝至需要恢复的节点
[db2inst1@db01 backup]$ ll
total 278692
-rw------- 1 db2inst1 db2iadm1 184627200 Aug 12 21:52 TESTDB.0.db2inst1.DBPART000.20190812215227.001
-rw------- 1 db2inst1 db2iadm1 50376704 Aug 12 21:54 TESTDB.0.db2inst1.DBPART000.20190812215450.001
-rw------- 1 db2inst1 db2iadm1 50376704 Aug 12 22:00 TESTDB.0.db2inst1.DBPART000.20190812220039.001
[db2inst1@db01 db2data]$ scp -r backup/ 192.168.2.123:/db2data/
8.备份恢复步骤
全备：TESTDB.0.db2inst1.DBPART000.20190812215227.001
增量1：TESTDB.0.db2inst1.DBPART000.20190812215450.001
差异增量：TESTDB.0.db2inst1.DBPART000.20190812220039.001
.指定通过增量备份恢复的形式最后要恢复到 20190812220039
[db2inst1@db02 backup]$ db2 restore db testdb incremental from /db2data/backup/ taken at 20190812220039
DB20000I The RESTORE DATABASE command completed successfully.
第一步恢复全备
[db2inst1@db02 backup]$ db2 restore db testdb incremental from /db2data/backup/ taken at 20190812215227 logtarget /db2data/logs/
DB20000I The RESTORE DATABASE command completed successfully
第二步恢复增备1
[db2inst1@db02 backup]$ db2 restore db testdb incremental from /db2data/backup/ taken at 20190812215450 logtarget /db2data/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.
第三步恢复差异备份2
[db2inst1@db02 backup]$ db2 restore db testdb incremental from /db2data/backup/ taken at 20190812220039 logtarget /db2data/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.
查看logtarget下的日志
[db2inst1@db02 LOGSTREAM0000]$ pwd
/db2data/logs/NODE0000/LOGSTREAM0000
[db2inst1@db02 LOGSTREAM0000]$ ls
S0000001.LOG S0000004.LOG S0000007.LOG
3.确定所需的活动日志
在源数据库执行
[db2inst1@db01 backup]$ db2 get db cfg for testdb | grep -i 'First active log'
First active log file = S0000009.LOG
在目标数据库执行
[db2inst1@db02 backup]$ db2 get db cfg for testdb | grep -i 'First active log'
First active log file = S0000007.LOG
所以恢复的话是需要S0000008.LOG和S0000009.LOG的，DB01上的第一个活动日志是S0000009.LOG，故S0000008.LOG已经归档了，要把该日志拷贝到归档日志的目录中（恢复时的overflow log path
中，在此我们指定overflow log path为/data/db2data/logs下，刚刚restore时的logtarget）：
[db2inst1@db01 backup]$ cd /archivelogs/db2inst1/TESTDB/NODE0000/LOGSTREAM0000/C0000000
[db2inst1@db01 C0000000]$ scp S0000008.LOG db02:/db2data/logs/NODE0000/LOGSTREAM0000
拷贝归档日志，活动日志在/home/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/下面
[db2inst1@db02 LOGSTREAM0000]$ db2 get db cfg for testdb | grep -i log
Log retain for recovery status = NO
User exit for logging status = YES
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
Log buffer size (4KB) (LOGBUFSZ) = 32
Log file size (4KB) (LOGFILSIZ) = 4000
Number of primary log files (LOGPRIMARY) = 12
Number of secondary log files (LOGSECOND) = 20
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0000007.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
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = 0
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
First log archive method (LOGARCHMETH1) = DISK:/archivelogs/
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) =
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 (LOGINDEXBUILD) = OFF
Log DDL Statements (LOG_DDL_STMTS) = NO
Log Application Information (LOG_APPL_INFO) = NO
拷贝活动日志
[db2inst1@db01 LOGSTREAM0000]$ scp -r /db2data/testdb/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/ db02:/home/db2inst1/db2inst1/NODE0000/SQL00001
db2inst1@db02's password:
S0000020.LOG 100% 16MB 15.6MB/s 00:00
S0000012.LOG 100% 16MB 15.6MB/s 00:01
S0000014.LOG 100% 16MB 15.6MB/s 00:00
S0000019.LOG 100% 16MB 15.6MB/s 00:00
S0000013.LOG 100% 16MB 15.6MB/s 00:01
S0000009.LOG 100% 16MB 15.6MB/s 00:00
S0000018.LOG 100% 16MB 15.6MB/s 00:00
S0000017.LOG 100% 16MB 15.6MB/s 00:01
S0000011.LOG 100% 16MB 15.6MB/s 00:00
S0000010.LOG 100% 16MB 15.6MB/s 00:00
S0000016.LOG 100% 16MB 15.6MB/s 00:00
S0000015.LOG 100% 16MB 15.6MB/s 00:01
S0000008.LOG 100% 12KB 12.0KB/s 00:00
执行前滚恢复
[db2inst1@db02 LOGSTREAM0000]$ db2 "rollforward db testdb to end of logs overflow log path (/db2data/logs)"
Rollforward Status
Input database alias = testdb
Number of members have returned status = 1
Member ID = 0
Rollforward status = DB working
Next log file to be read = S0000010.LOG
Log files processed = S0000007.LOG - S0000009.LOG
Last committed transaction = 2019-08-12-14.32.48.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[db2inst1@db02 LOGSTREAM0000]$ db2 "rollforward db testdb complete"
Rollforward Status
Input database alias = testdb
Number of members have returned status = 1
Member ID = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000007.LOG - S0000009.LOG
Last committed transaction = 2019-08-12-14.32.48.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
验证
[db2inst1@db02 LOGSTREAM0000]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUXX8664 10.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB
[db2inst1@db02 LOGSTREAM0000]$ db2 "select * from t1"
ID VAL
----------- --------------------
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
6 fff
7 ggg
8 hhh
8 record(s) selected.
db2 “rollforward db sample to 2005-12-23-12.09.02.000000 using local time and stop overflow log path(/home/db2inst1/db2log/)”
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle,linux
- 上一篇：DB2分区表测试
- 下一篇：使用IBM cdc迁移数据(db2->db2)
