实战演练
88504018喜欢 db2 java的朋友进来了DELTA增量备份和恢复
增量备份只有在中大型，每天有很多更新的数据库中才能体现其价值。
这里只演示其用法。
DELTA的增量恢复需要
全备份(或者+INCREMENTAL备份)+全部DELTA增量备份+增量备份后的LOG。
1.增量备份需要启动归档日志和设置TRACKMOD
设置参考实战演练 - 06
2.备份
CMD: db2 backup database sample to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100630154106
3.建立测试表
CMD: db2 connect to sample
CMD: db2 "create table TEST_DELTA (ID INT NOT NULL GENERATED ALWAYS A
S IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE), MSG CHAR(20))"
DB20000I The SQL command completed successfully.
4.插入数据并进行DELTA增量备份
CMD: db2 "insert into TEST_DELTA (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
DB20000I The SQL command completed successfully.
CMD: db2 "select * from test_delta"
ID MSG
----------- --------------------
1 db2china.net
2 db2china.net
3 db2china.net
3 record(s) selected.
CMD: db2 backup database sample incremental delta to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100630154408
CMD: db2 connect to sample
CMD: db2 "insert into TEST_DELTA (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
DB20000I The SQL command completed successfully.
CMD: db2 "select * from test_delta"
ID MSG
----------- --------------------
1 db2china.net
2 db2china.net
3 db2china.net
4 db2china.net
5 db2china.net
6 db2china.net
6 record(s) selected.
CMD: db2 backup database sample incremental delta to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100630154505
CMD: db2 connect to sample
CMD: db2 "insert into TEST_DELTA (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
DB20000I The SQL command completed successfully.
CMD: db2 "insert into TEST_DELTA (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
DB20000I The SQL command completed successfully.
CMD: db2 "select * from test_delta"
ID MSG
----------- --------------------
1 db2china.net
2 db2china.net
3 db2china.net
4 db2china.net
5 db2china.net
6 db2china.net
7 db2china.net
8 db2china.net
9 db2china.net
10 db2china.net
11 db2china.net
12 db2china.net
12 record(s) selected.
CMD: db2 backup database sample incremental delta to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100630154611
CMD: db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.1
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
CMD: db2 "insert into TEST_DELTA (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
DB20000I The SQL command completed successfully.
CMD: db2 "select * from test_delta"
ID MSG
----------- --------------------
1 db2china.net
2 db2china.net
3 db2china.net
4 db2china.net
5 db2china.net
6 db2china.net
7 db2china.net
8 db2china.net
9 db2china.net
10 db2china.net
11 db2china.net
12 db2china.net
13 db2china.net
14 db2china.net
15 db2china.net
15 record(s) selected.
CMD: db2 connect reset
DB20000I The SQL command completed successfully.
5.故障，表空间误删除
CMD: rm -fR db2inst1/NODE0000/SAMPLE/*
CMD: db2 connect to sample
SQL0293N Error accessing a table space container. SQLSTATE=57048
6.把 SAMPLE.0.db2inst1.NODE0000.CATN0000.20100630154505.001 备份镜像移动到另外
的目录，并进行数据库恢复，出现错误提示,恢复无法进行
CMD: db2 "restore db sample INCREMENTAL AUTOMATIC from /mnt/hgfs/F/backup/ taken at 20100630154611"
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
SQL2542N No match for a database image file was found based on the source
database alias "SAMPLE" and timestamp "20100630154505" provided.
7.放弃恢复
CMD: db2 "restore db sample INCREMENTAL ABORT from /mnt/hgfs/F/backup
/ taken at 20100630154611"
SQL2001N The utility was interrupted. The output data may be incomplete.
8.把 SAMPLE.0.db2inst1.NODE0000.CATN0000.20100630154505.001 备份镜像移回
备份后的所在目录，并重新启动恢复
CMD: db2 "restore db sample INCREMENTAL AUTOMATIC from /mnt/hgfs/F/backup/ taken at 20100630154611"
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.
9.恢复后数据库处于前滚恢复状态(ROLL-FORWARD PENDING)
CMD: db2 connect to sample
SQL1117N A connection to or activation of database "SAMPLE" cannot be made
because of ROLL-FORWARD PENDING. SQLSTATE=57019
10.到LOG末尾的前滚恢复
CMD: db2 rollforward database sample to end of logs and complete
Rollforward Status
Input database alias = sample
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = S0000012.LOG - S0000012.LOG
Last committed transaction = 2010-06-30-07.46.35.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
11.检查数据，结果正常
CMD: db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 9.7.1
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
CMD: db2 "select * from test_delta"
ID MSG
----------- --------------------
1 db2china.net
2 db2china.net
3 db2china.net
4 db2china.net
5 db2china.net
6 db2china.net
7 db2china.net
8 db2china.net
9 db2china.net
10 db2china.net
11 db2china.net
12 db2china.net
13 db2china.net
14 db2china.net
15 db2china.net
15 record(s) selected.
页:
[1]