实战演练
88504018喜欢 db2 java的朋友进来了在线备份和数据库时间点恢复
1.启动了归档日志后可以进行在线备份
例中同时在备份文件中包含了日志
CMD: db2 backup database sample online to /mnt/hgfs/F/backup/ include logs
Backup successful. The timestamp for this backup image is : 20100626203608
2.检查备份日志
显示了在线备份的Timestamp，这个是最小的恢复时间点
CMD: db2 list history backup all for sample
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20100626203608001 N D S0000004.LOG S0000004.LOG
----------------------------------------------------------------------------
Contains 5 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 IBMDB2SAMPLEREL
00004 IBMDB2SAMPLEXML
00005 SYSTOOLSPACE
----------------------------------------------------------------------------
Comment: DB2 BACKUP SAMPLE ONLINE
Start Time: 20100626203608
End Time: 20100626203618
Status: A
----------------------------------------------------------------------------
EID: 19 Location: /mnt/hgfs/F/backup
3.连接数据库，进行测试用的数据插入，同时显示本地时间用来跟踪时间点恢复
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 act values(901, 'TEST', 'TESTDESC')"
CMD: date
Sat Jun 26 20:37:12 CST 2010
CMD: db2 "insert into act values(901, 'TEST', 'TESTDESC')"
DB20000I The SQL command completed successfully.
CMD: date
Sat Jun 26 20:37:17 CST 2010
CMD: db2 "insert into act values(902, 'TEST', 'TESTDESC')"
DB20000I The SQL command completed successfully.
CMD: date
Sat Jun 26 20:37:24 CST 2010
CMD: db2 "insert into act values(903, 'TEST', 'TESTDESC')"
DB20000I The SQL command completed successfully.
CMD: date
Sat Jun 26 20:37:30 CST 2010
CMD: db2 "insert into act values(904, 'TEST', 'TESTDESC')"
DB20000I The SQL command completed successfully.
CMD: date
Sat Jun 26 20:37:35 CST 2010
CMD: db2 "insert into act values(905, 'TEST', 'TESTDESC')"
DB20000I The SQL command completed successfully.
CMD: date
Sat Jun 26 20:37:49 CST 2010
4.检查插入结果
CMD: db2 "select * from act"
ACTNO ACTKWD ACTDESC
------ ------ --------------------
10 MANAGE MANAGE/ADVISE
20 ECOST ESTIMATE COST
30 DEFINE DEFINE SPECS
40 LEADPR LEAD PROGRAM/DESIGN
50 SPECS WRITE SPECS
60 LOGIC DESCRIBE LOGIC
70 CODE CODE PROGRAMS
80 TEST TEST PROGRAMS
90 ADMQS ADM QUERY SYSTEM
100 TEACH TEACH CLASSES
110 COURSE DEVELOP COURSES
120 STAFF PERS AND STAFFING
130 OPERAT OPER COMPUTER SYS
140 MAINT MAINT SOFTWARE SYS
150 ADMSYS ADM OPERATING SYS
160 ADMDB ADM DATA BASES
170 ADMDC ADM DATA COMM
180 DOC DOCUMENT
901 TEST TESTDESC
902 TEST TESTDESC
903 TEST TESTDESC
904 TEST TESTDESC
905 TEST TESTDESC
23 record(s) selected.
5.故障，表空间误删除
CMD:
cd /home/db2inst1/db2inst1/NODE0000/SAMPLE
mv * /mnt/hgfs/F/TS
ls
db2 connect reset
DB20000I The SQL command completed successfully.
db2 connect to sample
SQL0293N Error accessing a table space container. SQLSTATE=57048
6.数据库恢复
CMD: db2 restore database sample from /mnt/hgfs/F/backup/
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 db sample to 2010-06-26-20.36.00 using local time and complete
SQL1275N The stoptime passed to roll-forward must be greater than or equal to
"2010-06-26-20.36.17.000000 Local", because database "SAMPLE" on node(s) "0"
contains information later than the specified time.
9.时间点前滚恢复
CMD: db2 rollforward db sample to 2010-06-26-20.37.17 using local time 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 = S0000004.LOG - S0000005.LOG
Last committed transaction = 2010-06-26-20.37.15.000000 Local
DB20000I The ROLLFORWARD command completed successfully.
10.结果的确认，选取的时间点刚好在第一条和第二条记录之间，所以结果显示ACT表中只有第一条记录。
结果正确！
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 act"
ACTNO ACTKWD ACTDESC
------ ------ --------------------
10 MANAGE MANAGE/ADVISE
20 ECOST ESTIMATE COST
30 DEFINE DEFINE SPECS
40 LEADPR LEAD PROGRAM/DESIGN
50 SPECS WRITE SPECS
60 LOGIC DESCRIBE LOGIC
70 CODE CODE PROGRAMS
80 TEST TEST PROGRAMS
90 ADMQS ADM QUERY SYSTEM
100 TEACH TEACH CLASSES
110 COURSE DEVELOP COURSES
120 STAFF PERS AND STAFFING
130 OPERAT OPER COMPUTER SYS
140 MAINT MAINT SOFTWARE SYS
150 ADMSYS ADM OPERATING SYS
160 ADMDB ADM DATA BASES
170 ADMDC ADM DATA COMM
180 DOC DOCUMENT
901 TEST TESTDESC
19 record(s) selected.
页:
[1]