实战演练
INCREMENTAL增量备份和恢复增量备份只有在中大型，每天有很多更新的数据库中才能体现其价值。
这里只演示其用法。
INCREMENTAL的增量恢复需要
全备份+INCREMENTAL增量备份+增量备份后的LOG。
1.增量备份需要启动归档日志和设置TRACKMOD
为了看清归档日志状况，可以把归档日志放在单独的磁盘里
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 update db cfg using LOGARCHMETH1 DISK:/mnt/hgfs/F/ARCHIVELOG/
CMD: db2 update db cfg using TRACKMOD ON
CMD: db2 get db cfg
（其余内容忽略）
Database Configuration for Database
First log archive method (LOGARCHMETH1) = DISK:/mnt/hgfs/F/ARCHIVELOG/
Track modified pages (TRACKMOD) = ON
2。数据库备份
CMD: db2 backup database sample to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100628203915
3。创建测试用表并插入数据
CMD: db2 connect to sample
CMD: db2 "create table TEST_INCREMENTAL (ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE), MSG CHAR(20))"
db2 "insert into TEST_INCREMENTAL (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
CMD: db2 "insert into TEST_INCREMENTAL (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
CMD: db2 "select * from test_incremental"
ID MSG
----------- --------------------
1 db2china.net
2 db2china.net
3 db2china.net
4 db2china.net
5 db2china.net
6 db2china.net
6 record(s) selected.
4。在线增量备份。
CMD: db2 backup database sample online incremental to /mnt/hgfs/F/backup/
Backup successful. The timestamp for this backup image is : 20100628204243
5。再插入数据
CMD: db2 connect to sample
CMD: db2 "insert into TEST_INCREMENTAL (MSG) VALUES('db2china.net'),('db2china.net'),('db2china.net')"
DB20000I The SQL command completed successfully.
CMD: db2 "select * from test_incremental"
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
9 record(s) selected.
6。故障，误删除表空间
CMD: rm -fR db2inst1/NODE0000/SAMPLE/*
CMD: ls db2inst1/NODE0000/SAMPLE/
CMD:db2 connect to sample
SQL0293N Error accessing a table space container. SQLSTATE=57048
6。数据库自动增量恢复
CMD: db2 "restore db sample INCREMENTAL AUTOMATIC from /mnt/hgfs/F/backup/ taken at 20100628204243"
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.
7。恢复后数据库处在前滚恢复状态(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
8。可以恢复到日志结尾,全部数据恢复成功
CMD: db2 rollforward database sample to end of logs and stop
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 = S0000003.LOG - S0000006.LOG
Last committed transaction = 2010-06-28-12.47.53.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
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_INCREMENTAL"
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
9 record(s) selected.
9.也可以恢复到备份时候的状态
CMD: db2 "restore db sample INCREMENTAL AUTOMATIC from /mnt/hgfs/F/backup/ taken at 20100628204243"
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.
CMD: db2 rollforward database sample to end of backup and stop
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 = S0000003.LOG - S0000005.LOG
Last committed transaction = 2010-06-28-12.42.48.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
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_INCREMENTAL"
ID MSG
----------- --------------------
1 db2china.net
2 db2china.net
3 db2china.net
4 db2china.net
5 db2china.net
6 db2china.net
6 record(s) selected.
页:
[1]