DB2备份与恢复的精华内容

1、目录实战演练 - 01 2.实战演练 - 02 7.实战演练 - 03 1.4.实战演练 - 04 1.7.实战演练 - 05 2.2.实战演练 - 06 2.6.实战演练 - 07 3.0.实战演练 - 08 3.5.实战演练 - 09 4.1.实战演练 - 10 4.4.实战演练 - 11 4.7.以下要为大家推荐一些DB2备份与恢复的精华内容，本内容由cedarbird版主耗时数月而成，本次内容共分为两个部分 一一环境构筑篇和实战演练篇，两个部分共有16篇文章组成,是大家学习和掌握 DB2备份与恢复不可或缺的内容。有关本次内容的详细介绍如下： 环境构筑篇：VMWARE上CentOS5.4

2、 （64bit）最小服务器环境构筑http:/www.db2chi na. net/home/spa . do=blog&id=8113DB2 9.7 64bit 在 CentOS5.4 64bit上的安装http:/www.db2ch ina.n et/home/spa . do=blog&id=8114DAS建立http:/www.db2ch ina.n et/home/spa . do=blog&id=8115vmware-tools 安装http:/www.db2chi na. net/home/spa . do=blog&id=8116无废话DB2备份

3、和恢复-基础篇（上）无废话DB2备份和恢复-基础篇（上 ）.pdf （672.49 KB）实战演练-01循环日志下的版本恢复概要:（图例略）最简单最基本的一个例子循环日志下的离线备份和恢复要点：做了动作后，别忘了做检查来验证动作的正确性。认真、细心是DBA最基本的素质。很多时候能救命的并不是多高级的技术。问题：问题1:实际上这里还故意遗漏一些在生产环境中必须要做的重要步骤，那是?问题2:这一过程的操作流程图？ 操作过程:1. 注册login( user: db2 in stl)2. 连接db2 connect to sample3. 查看备份数据库设定&确认日志模式db2 get db

4、 cfg > /mn t/hgfs/F/backup/database.cfg.bak db2 term in ateL0GARCHMETH1=0FFL0GARCHMETH2=0FFLOGRETAIN=OFFUSEREXIT=OFF4.全备份CMD:db2 backup database sample to /mn t/hgfs/F/backup/RESULT:Backup successful. The timestamp for this backup image is5.检查备份镜像文件CMD: db2ckbkp -h/mn t/hgfs/F/backup/SAMPLE.0.db2

5、i nst1.NODEOOOO.CATNOOOO.2O1OO5O516O724.001RESULT:MEDIA HEADER REACHED:Server Database NameServer Database Alias Clie nt Database Alias TimestampDatabase Partiti on NumberIn sta neeSeque nee NumberRelease IDDatabase Seed-SAMPLE-SAMPLE-SAMPLE-20100505160724-0-db2i nst1-1-DOO-BED6FFF2DB Comme nt's

6、 Codepage (Volume) - 0 DB Comme nt (Volume)-DB Comme nt's Codepage (System) - 0 DB Comme nt (System) Authe nticatio n Value Backup Mode In cludes Logs-1-0-0Compressi on-0Backup Type-0Backup Gran.-0Status Flags- 1System Cats inc- 1Catalog Partiti on Number - 0DB Codeset- UTF-8DB Territory-LogID-

7、1272190778LogPath-/home/db2i nst1/db2i nst1/N0DE0000/SQL00001/SQL0GDIR/Backup Buffer Size- 4460544Number of Sessi ons- 1Platform- 1EThe proper image file n ame would be:SAMPLE.0.db2i nst1.N0DE0000.CATN0000.20100505160724.0011 Buffers processed: #Image Verificati on Complete - successful.6。查看恢复历史记录CM

8、D:db2 list history all for sampleRESULT :部分Op Obj Timestamp+Seque nee Type Dev Earliest Log Curre nt Log Backup IDB D 20100505160724001 F D S0000000.L0G S0000000.L0GContains 5 tablespace(s):00001SYSCATSPACE00002USERSPACE100003IBMDB2SAMPLEREL00004IBMDB2SAMPLEXML00005SYSTOOLSPACEComme nt: DB2 BACKUP S

9、AMPLE OFFLINEStart Time: 20100505160724End Time: 20100505160735Status: AEID: 5 Locati on: /mn t/hgfs/F/backup7. 误删除障碍！删除前的备份 db2 "export to file01.del of del select * from act" =>18rows删除数据 db2 "delete from act"确认 db2 "select count(*) from act" =>0 row8. 恢复和结果确认CM

10、D: db2 restore database sample from /mn t/hgfs/F/backup/RESULT: DB20000I The RESTORDATABASEommanobompleted successfully. 确认db2 "export to file01.del of del select * from act"diff file01.del file02.del恢复历史记录CMD: db2 list history all for sampleRESULT:部分Op Obj Timestamp+Seque nee Type Dev Ear

11、liest Log Curre nt Log Backup IDR D 20100505171611001 F S0000000.LOG S0000000.LOG20100505160724Contains 5 tablespace(s):00001 SYSCATSPACE00002 USERSPACE100003 IBMDB2SAMPLEREL00004 IBMDB2SAMPLEXML00005 SYSTOOLSPACEComme nt: RESTORE SAMPLE NO RF Start Time: 20100505171611End Time: 20100505171639Status

12、: A实战演练-02分享循环日志下的数据库重定向可以利用备份镜像重定向功能建立新的数据库1.重定向恢复CMD: db2 "restore database sample from /mn t/hgfs/F/backup/ INTO SAMPLE2 REDIRECT WITHOUT ROLLING FORWARD"RESULT:SQL1277W A redirected restore operati on is being performed. Tablespaceconfigurationcan now be viewed and table spaces that do

13、 not use automaticstorage can have their containers rec on figured.DB20000I The RESTORE DATABASE comma nd completed successfully.因为是数据库是自动存储，不需要指定容器。确认容器的状态，此时表空间处在恢复暂挂(Restore pending)下，需要进一步 进行恢复处理。CMD: db2 list tablespacesRESULT:Tablespaces for Curre nt DatabaseTablespace ID=2Tablespace ID=2=SYSC

14、ATSPACE=Database man aged space=All perma nent data. RegularTablespace IDNameTypeCon te nts table space.State=0x2000100Tablespace ID=2Tablespace IDNameTypeCon te ntsStateDetailed expla natio n:Restore pendingStorage may be defi ned=1=TEMPSPACE1=System man aged space=System Temporary data=0x2000100De

15、tailed expla natio n:Restore pendingStorage may be defi nedTablespace ID=2NameTypeCon te ntsspace.State= 0x2000100Detailed expla natio n:Restore pendingStorage may be defi ned=USERSPACE1=Database man aged space=All permanent data.LargetableTablespace ID Name Type Con te nts space.StateDetailed expla

16、 natio n: Restore pending Storage may be defi ned=3=IBMDB2SAMPLEREL=Database man aged space=All permanent data.=0x2000100Tablespace ID Name Type Con te nts space.StateDetailed expla natio n: Restore pending Storage may be defi ned=4=IBMDB2SAMPLEXML=Database man aged space=All permanent data.=0x20001

17、00Tablespace ID Name Type Con te nts space.StateDetailed expla natio n: Restore pending Storage may be defi ned=5=SYSTOOLSPACE=Database man aged space=All permanent data.=0x20001002.继续恢复操作，数据库完全恢复正常。表空间处于 NORMA状态 SAMPLE数据库被建立。LargeLargeLargetabletabletableCMD: db2 "restore database sample conti

18、nue"RESULT: DB20000I The RESTORE DATABASE comma nd completed successfully3.可以利用选项GENERATE SCRIPT动生成重定向恢复脚本CMD: db2 "restore database sample from /mn t/hgfs/F/backup/INTOSAMPLE2REDIRECT GENERATE SCRIPT redirectscript"redirectscript 内容:*-* automatically created redirect restore script*U

19、PDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODEOOOO.out V ON; SET CLIENT ATTACH_DBPARTITIONNUM 0;SET CLIENT CONNECT_DBPARTITIONNUM 0;*-* automatically created redirect restore script*RESTORE DATABASE SAMPLE-USER <user name>-USING '<password>'FROM '/mn t/hgfs/F/backup/'T

20、AKEN AT 20100509155627-ON 7home/db2i nstl'-DBPATH ON '<target-directory>'INTO SAMPLE2-NEWLOGPATH 7home/db2i nst1/db2i nst1/NODE0000/SQL00001/SQLOGDIR/'-WITH <nu m-buff> BUFFERS-BUFFER <buffer-size>-REPLACE HISTORY FILE-REPLACE EXISTINGREDIRECT-PARALLELISM <n>W

21、ITHOUT ROLLING FORWARD-WITHOUT PROMPTINGJ*-* table space defi niti on*-* Tablespace n ame=SYSCATSPACE* Tablespace ID-* Tablespace Typespace-* Tablespace Content Type Regular table space.-* Tablespace Page size (bytes)-* Tablespace Exte nt size (pages)-* Usi ng automatic storage-* Auto-resize en able

22、d-* Total nu mber of pages-* Number of usable pages-* High water mark (pages)=Database man aged=All perma nent data.=8192=4=Yes=Yes=12288=12284=12256*=TEMPSPACE1=1-* Tablespace n ame-* Tablespace ID-* Tablespace Typespace-* Tablespace Content Type data-* Tablespace Page size (bytes)-* Tablespace Ext

23、e nt size (pages)-* Usi ng automatic storage-* Total nu mber of pages=System man aged=System Temporary=8192=32=Yes=1* Tablespace n ame=USERSPACE1* Tablespace ID* Tablespace Type=2=Database man aged=All perma nent data.=8192=32=Yes=Yes=4096=4064=1824-* Tablespace n ame=IBMDB2SAMPLEREL* Tablespace ID*

24、 Tablespace Type=3=Database man agedspace-* Tablespace Content Type Large table space.-* Tablespace Page size (bytes)-* Tablespace Exte nt size (pages)-* Usi ng automatic storage-* Auto-resize en abled-* Total nu mber of pages-* Number of usable pages-* High water mark (pages)*=All perma nent data.=

25、8192=32=Yes=Yes=4096=4064=608space-* Tablespace Content Type Large table space.-* Tablespace Page size (bytes)-* Tablespace Exte nt size (pages)-* Usi ng automatic storage-* Auto-resize en abled-* Total nu mber of pages-* Number of usable pages-* High water mark (pages)*space-* Tablespace Content Ty

26、pesize-* Tablespace n ame-* Tablespace ID-* Tablespace TypeLarge table space.=IBMDB2SAMPLEXML=4=Database man aged=All perma nent data.=8192* Tablespace Exte nt size (pages)* Usi ng automatic storage* Auto-resize en abled* Total nu mber of pages* Number of usable pages* High water mark (pages)=32=Yes

27、=Yes=4096=4064=1440*-* Tablespace n ame-* Tablespace ID=SYSTOOLSPACE*-* Tablespace Typespace-* Tablespace Content Type Large table space.-* Tablespace Page size (bytes)-* Tablespace Exte nt size (pages)-* Usi ng automatic storage-* Auto-resize en abled-* Total nu mber of pages-* Number of usable pag

28、es-* High water mark (pages)=Database man aged=All perma nent data.=8192=4=Yes=Yes=4096=4092=72*-* start redirected restore*RESTORE DATABASE SAMPLE CONTINUE;*-* end of file*4.可以根据需要定制修改重定向脚本redirectscript，然后再用db2 -tvfredirectscript 执行。这个功能很实用。实战演练-03分享数据库的归档日志设置和数据库前滚恢复1. 设置归档日志可以有多种设置方法，具体参考In foCe

29、 nterCMD:login (user: db2 in st1)db2 connect to sampledb2 update db cfg usi ng LOGARCHMETH1 logretain2. 设置成归档日志后的备份设置成归档日志后的数据库处在备份暂挂(BACKUP PENDIN状态，需要进行备份CMD: db2 connect to sampleSQL1116N A conn ection to or activatio n of database "SAMPLE" cannot bemadebecause of BACKUP PENDING. SQLSTA

30、TE=57019CMD: db2 backup database sample to /mn t/hgfs/F/backup/Backup successful. The timestamp for this backup image is : 20100624210336 备份后数据库恢复正常。3. 数据修改CMD: db2 "i nsert into act values(999, 'TEST', 'TESTDESC')"db2 "select * from act"ACTNO ACTKWD ACTDESC10 MAN

31、AGE MANAGE/ADVISE20 ECOST ESTIMATE COST30 DEFINE DEFINE SPECS40 LEADPR LEAD PROGRAM/DESIGN50 SPECS WRITE SPECS60 LOGIC DESCRIBE LOGIC70 CODE CODE PROGRAMS80 TEST TEST PROGRAMS90 ADMQS ADM QUERY SYSTEM100 TEACH TEACH CLASSES110 COURSE DEVELOP COURSES120 STAFF PERS AND STAFFING130 OPERAT OPER COMPUTER

32、 SYS140 MAINT MAINT SOFTWARE SYS150 ADMSYS ADM OPERATING SYS160 ADMDB ADM DATA BASES170 ADMDC ADM DATA COMM180 DOC DOCUMENT999 TEST TESTDESC4. 误操作删除表空间CMD:cd /home/db2 in st1/db2i nstl/NODEOOOO/SAMPLEmv * /mn t/hgfs/F/TSIsdb2 connect resetdb2 connect to sampleSQL0293N Error accessi ng a table space

33、contain er. SQLSTATE=570485. 备份中恢复db2 restore database sample from /mn t/hgfs/F/backup/SQL2539W Warning! Restoring to an existing database that is the same as thebackup image database. The database files will be deleted.Do you want to con ti nue ? (y/n) YDB20000I The RESTORE DATABASE comma nd comple

34、ted successfully.6. 回复后数据库处于前滚暂挂(ROLL-FORWARD PENDING态db2 connect to sampleSQL1117N A conn ection to or activati on of database "SAMPLE" cannot be madebecause of ROLL-FORWARD PENDING. SQLSTATE=570197. 执行前滚恢复CMD: db2 rollforward database sample to end of logs and stopRollforward StatusIn pu

35、t database alias= sampleNumber of no des have retur ned status = 1=not pending=S0000000.LOG - S0000000.LOG=2010-06-25-000000Node nu mberRollforward statusNext log file to be readLog files processedLast committed tran sact ion UTCDB20000I The ROLLFORWARD comma nd completed successfully.8. 数据

36、确认CMD:db2 connect to sampledb2 "select * from act" ACTNO ACTKWD ACTDESC10 MANAGE MANAGE/ADVISE20 ECOST ESTIMATE COST30 DEFINE DEFINE SPECS40 LEADPR LEAD PROGRAM/DESIGN50 SPECS WRITE SPECS60 LOGIC DESCRIBE LOGIC70 CODE CODE PROGRAMS80 TEST TEST PROGRAMS90 ADMQS ADM QUERY SYSTEM100 TEACH TEA

37、CH CLASSES110 COURSE DEVELOP COURSES120 STAFF PERS AND STAFFING130 OPERAT OPER COMPUTER SYS140 MAINT MAINT SOFTWARE SYS150 ADMSYS ADM OPERATING SYS160 ADMDB ADM DATA BASES170 ADMDC ADM DATA COMM180 DOC DOCUMENT999 TEST TESTDESC备份后的数据变化被前滚恢复了。实战演练-04分享在线备份和数据库时间点恢复1. 启动了归档日志后可以进行在线备份例中同时在备份文件中包含了日志CM

38、D: db2 backup database sample on li ne to /mn t/hgfs/F/backup/ i nclude logsBackup successful. The timestamp for this backup image is : 201006262036082. 检查备份日志显示了在线备份的Timestamp,这个是最小的恢复时间点CMD: db2 list history backup all for sampleOp Obj Timestamp+Seque nee Type Dev Earliest Log Curre nt Log Backup

39、IDB D 20100626203608001 N D S0000004.LOG S0000004.LOGContains 5 tablespace(s):00001 SYSCATSPACE00002 USERSPACE100003 IBMDB2SAMPLEREL00004 IBMDB2SAMPLEXML00005 SYSTOOLSPACEComme nt: DB2 BACKUP SAMPLE ONLINE Start Time: 20100626203608End Time: 20100626203618Status: AEID: 19 Locatio n: /mn t/hgfs/F/bac

40、kup3. 连接数据库，进行测试用的数据插入，同时显示本地时间用来跟踪时间点恢复CMD: db2 connect to sampleDatabase Conn ecti on In formatio nDatabase server = DB2/LINUXX8664 9.7.1SQL authorization ID = DB2INST1Local database alias = SAMPLECMD: db2 "insert into act values(901, 'TEST', 'TESTDESC')"CMD: dateSat Jun

41、26 20:37:12 CST 2010CMD: db2 "insert into act values(901, 'TEST', 'TESTDESC')"DB20000I The SQL comma nd completed successfully.CMD: dateSat Jun 26 20:37:17 CST 2010CMD: db2 "insert into act values(902, 'TEST', 'TESTDESC')"DB20000I The SQL comma nd

42、completed successfully.CMD: dateSat Jun 26 20:37:24 CST 2010CMD: db2 "insert into act values(903, 'TEST', 'TESTDESC')"DB20000I The SQL comma nd completed successfully.CMD: dateSat Jun 26 20:37:30 CST 2010CMD: db2 "insert into act values(904, 'TEST', 'TESTDE

43、SC')"DB20000I The SQL comma nd completed successfully.CMD: dateSat Jun 26 20:37:35 CST 2010CMD: db2 "insert into act values(905, 'TEST', 'TESTDESC')"DB20000I The SQL comma nd completed successfully. CMD: dateSat Jun 26 20:37:49 CST 20104. 检查插入结果CMD: db2 "selec

44、t * from act"ACTNO ACTKWD ACTDESC10 MANAGE MANAGE/ADVISE20 ECOST ESTIMATE COST30 DEFINE DEFINE SPECS40 LEADPR LEAD PROGRAM/DESIGN50 SPECS WRITE SPECS60 LOGIC DESCRIBE LOGIC70 CODE CODE PROGRAMS80 TEST TEST PROGRAMS90 ADMQS ADM QUERY SYSTEM100 TEACH TEACH CLASSES110 COURSE DEVELOP COURSES120 STA

45、FF PERS AND STAFFING130 OPERAT OPER COMPUTER SYS140 MAINT MAINT SOFTWARE SYS150 ADMSYS ADM OPERATING SYS160 ADMDB ADM DATA BASES170 ADMDC ADM DATA COMM180 DOC DOCUMENT901 TEST TESTDESC902 TEST TESTDESC903 TEST TESTDESC904 TEST TESTDESC905 TEST TESTDESC23 record(s) selected.5. 故障，表空间误删除CMD:cd /home/d

46、b2 in st1/db2i nst1/NODEOOOO/SAMPLEmv * /mn t/hgfs/F/TSIsdb2 connect resetDB20000I The SQL comma nd completed successfully.db2 connect to sampleSQL0293N Error accessi ng a table space contain er. SQLSTATE=570486. 数据库恢复CMD: db2 restore database sample from /mn t/hgfs/F/backup/SQL2539W Warning! Restor

47、ing to an existing database that is the same as thebackup image database. The database files will be deleted.Do you want to con ti nue ? (y/n) yDB20000I The RESTORE DATABASE comma nd completed successfully.7. 恢复后的数据库处在前滚暂挂状态 (ROLL-FORWARD PENDING)CMD: db2 connect to sampleSQL1117N A conn ection to o

48、r activatio n of database "SAMPLE" cannot be madebecause of ROLL-FORWARD PENDING. SQLSTATE=570198. 时间点前滚恢复，当小于最小恢复时间点时，无法恢复CMD: db2 rollforward db sample to 2010-06-26-20.36.00 using local time and completeSQL1275N The stoptime passed to roll-forward must be greater tha n or equal to"

49、2010-06-26-00000 Local", because database "SAMPLEb n 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 completeRollforward StatusIn put database alias= sampleNumber of no des have retur ned status = 1=0=not pending=S0000004.L0G - S0000005.L0G=2010-06-26-00000Node nu mberRollforward statusNext log file to be readLog files processedLast committed tran sact ion LocalDB20000I The ROLL