DB2备份与恢复的精华内容

1、实战演练-012.实战演练-027.实战演练-0314实战演练-0417实战演练-0522实战演练-0626实战演练-0730实战演练-0835实战演练-0941实战演练-1044实战演练-1147以下要为大家推荐一些DB2备份与恢复的精华内容，本内容由cedarbird版主耗时数月而成，本次内容共分为两个部分一一环境构筑篇和实战演练篇，两个部分共有16篇文章组成,是大家学习和掌握DB2备份与恢复不可或缺的内容。有关本次内容的详细介绍如下：环境构筑篇：VMWARE上CentOS5.4（64bit）最小服务器环境构筑DB29.764bit在CentOS5.464bit上的安装DAS建立vmwar

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

3、模式db2getdbcfg>/mnt/hgfs/F/backup/database.cfg.bakdb2terminateLOGARCHMETH1=OFFLOGARCHMETH2=OFFLOGRETAIN=OFFUSEREXIT=OFF4 .全备份CMD:db2backupdatabasesampleto/mnt/hgfs/F/backup/RESULT:Backupsuccessful.Thetimestampforthisbackupimageis5 .检查备份镜像文件CMD:db2ckbkp-h/mnt/hgfs/F/backup/SAMPLE.0.db2inst1.NODE000

4、0.CATN0000.20100505160724.001RESULT:MEDIAHEADERREACHED:ServerDatabaseName-SAMPLEServerDatabaseAlias-SAMPLEClientDatabaseAliasSAMPLETimestamp-20100505160724DatabasePartitionNumber-0InstanceSequenceNumberReleaseIDDatabaseSeed-db2inst1-1-D00-BED6FFF2DBComment'sCodepage(Volume)-0DBComment(Volume)-DB

5、Comment'sCodepage(System)-0DBComment(System)-AuthenticationValueBackupModeIncludes1-0-0Compression-0BackupType-0BackupGran.-0StatusFlags-1SystemCatsinc-1CatalogPartitionNumber-0DBCodeset-UTF-8DBTerritory-LogID-1272190778LogPath-/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/BackupBufferSize-

6、4460544NumberofSessions-1Platform-1ETheproperimagefilenamewouldbe:SAMPLE.0.db2inst1.NODE0000.CATN0000.20100505160724.0011 Buffersprocessed:#ImageVerificationComplete-successful.6。查看恢复历史记录CMD:db2listhistoryallforsampleRESULT:部分OpObjTimestamp+SequenceTypeDevEarliestLogCurrentLogBackupIDBD2010050516072

7、4001FDS0000000.LOGS0000000.LOGContains5tablespace(s):00001SYSCATSPACE00002USERSPACE100003IBMDB2SAMPLEREL00004IBMDB2SAMPLEXML00005SYSTOOLSPACEComment:DB2BACKUPSAMPLEOFFLINEStartTime:20100505160724EndTime:20100505160735Status:AEID:5Location:/mnt/hgfs/F/backup7 .误删除障碍！删除前的备份db2"exporttofile01.delo

8、fdelselect*fromact"=>18rows删除数据db2"deletefromact"确认db2"selectcount(*)fromact"=>0row8 .恢复和结果确认CMD:db2restoredatabasesamplefrom/mnt/hgfs/F/backup/RESULT:DB20000ITheRESTOREATABASEommanobompletedsuccessfully.确认db2"exporttofile01.delofdelselect*fromact"difffile01.

9、delfile02.del恢复历史记录CMD:db2listhistoryallforsampleRESULT:部分OpObjTimestamp+SequenceTypeDevEarliestLogCurrentLogBackupIDRD20100505171611001FS0000000.LOGS0000000.LOG20100505160724Contains5tablespace(s):00001SYSCATSPACE00002USERSPACE100003IBMDB2SAMPLEREL00004IBMDB2SAMPLEXML00005SYSTOOLSPACEComment:RESTOR

10、ESAMPLENORFStartTime:20100505171611EndTime:20100505171639Status:A实战演练-02分享循环日志下的数据库重定向可以利用备份镜像重定向功能建立新的数据库1.重定向恢复CMD:db2"restoredatabasesamplefrom/mnt/hgfs/F/backup/INTOSAMPLE2REDIRECTWITHOUTROLLINGFORWARD"RESULT:SQL1277WAredirectedrestoreoperationisbeingperformed.Tablespaceconfigurationca

11、nnowbeviewedandtablespacesthatdonotuseautomaticstoragecanhavetheircontainersreconfigured.DB20000ITheRESTOREDATABASEcommandcompletedsuccessfully.因为是数据库是自动存储，不需要指定容器。确认容器的状态，此时表空间处在恢复暂挂(Restorepending)下，需要进一步进行恢复处理。CMD:db2listtablespacesRESULT:TablespacesforCurrentDatabaseTablespaceIDNameTypeContentst

12、ablespace.State=0=SYSCATSPACE=Databasemanagedspace=Allpermanentdata.Regular=0x2000100TablespaceIDNameTypeContentsStateDetailedexplanation:RestorependingStoragemaybedefined=1=TEMPSPACE1=Systemmanagedspace=SystemTemporarydata=0x2000100Detailedexplanation:RestorependingStoragemaybedefinedName=USERSPACE

13、1Type=DatabasemanagedspaceContents=Allpermanentdata.Largetablespace.State=0x2000100Detailedexplanation:RestorependingTablespaceIDNameTypeContentsspace.StateStoragemaybedefined=3=IBMDB2SAMPLEREL=Databasemanagedspace=Allpermanentdata.Largetable=0x2000100Detailedexplanation:RestorependingStoragemaybede

14、finedTablespaceIDNameTypeContentsspace.State=4=IBMDB2SAMPLEXML=DatabasemanagedspaceLargetableLargetable=Allpermanentdata.=0x2000100Detailedexplanation:RestorependingTablespaceIDNameTypeContentsspace.StateStoragemaybedefined=5=SYSTOOLSPACE=Databasemanagedspace=Allpermanentdata.=0x2000100Detailedexpla

15、nation:RestorependingStoragemaybedefined2.继续恢复操作，数据库完全恢复正常。表空间处于NORMAL态SAMPLE数据库被建立。CMD:db2"restoredatabasesamplecontinue"RESULT:DB20000ITheRESTOREDATABASEcommandcompletedsuccessfully3.可以利用选项GENERATESCRI肝动生成重定向恢复脚本CMD:db2"restoredatabasesamplefrom/mnt/hgfs/F/backup/INTOSAMPLE2REDIRECT

16、GENERATESCRIPTredirectscript"redirectscript内容：-*- -*automaticallycreatedredirectrestorescript-*UPDATECOMMANDOPTIONSUSINGSONZONSAMPLE_NODE0000.outVON;SETCLIENTATTACH_DBPARTITIONNUM0;SETCLIENTCONNECT_DBPARTITIONNUM0;-*- -*automaticallycreatedredirectrestorescript-*RESTOREDATABASESAMPLE- -USER<

17、username>- -USING'password'FROM'/mnt/hgfs/F/backup/'TAKENAT20100509155627- -ON'/home/db2inst1'- -DBPATHON'<target-directory>'INTOSAMPLE2-NEWLOGPATH'/home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/'- -WITH<num-buff>BUFFERS- -BUFFER<buffer-s

18、ize>- -REPLACEHISTORYFILE- -REPLACEEXISTINGREDIRECT- -PARALLELISM<n>WITHOUTROLLINGFORWARD- -WITHOUTPROMPTING；*- -*tablespacedefinition-*-*Tablespacename=SYSCATSPACE*TablespaceID=Databasemanaged=Allpermanentdata.=8192=4=Yes=Yes=12288=12284=12256- -*TablespaceTypespace- -*TablespaceContentTyp

19、eRegulartablespace.- -*TablespacePagesize(bytes)- -*TablespaceExtentsize(pages)- -*Usingautomaticstorage- -*Auto-resizeenabled- -*Totalnumberofpages- -*Numberofusablepages- -*Highwatermark(pages)*=TEMPSPACE1=1=Systemmanaged=SystemTemporary=8192=32=Yes=1- -*Tablespacename- -*TablespaceID- -*Tablespac

20、eTypespace- -*TablespaceContentTypedata- -*TablespacePagesize(bytes)- -*TablespaceExtentsize(pages)- -*Usingautomaticstorage- -*Totalnumberofpages*-*Tablespacename=USERSPACE1*TablespaceID=2*TablespaceType=Databasemanagedspace-*TablespaceContentType=Allpermanentdata.Largetablespace.-*TablespacePagesi

21、ze(bytes)=8192-*TablespaceExtentsize(pages)=32-*Usingautomaticstorage=Yes-*Auto-resizeenabled=Yes-*Totalnumberofpages=4096-*Numberofusablepages=4064-*Highwatermark(pages)=1824*-*Tablespacename=IBMDB2SAMPLEREL-*TablespaceID=3-*TablespaceTypespace=Databasemanaged-*TablespaceContentTypeLargetablespace.

22、=Allpermanentdata.-*TablespacePagesize(bytes)=8192-*TablespaceExtentsize(pages)=32-*Usingautomaticstorage=Yes-*Auto-resizeenabled=Yes-*Totalnumberofpages=4096-*Numberofusablepages=4064-*Highwatermark(pages)=608*space-*TablespaceContentTypeLargetablespace.- -*Tablespacename- -*TablespaceID- -*Tablesp

23、aceType=IBMDB2SAMPLEXML=4=Databasemanaged=Allpermanentdata.=8192* *TablespaceExtentsize(pages)=32* *Usingautomaticstorage=Yes* *Auto-resizeenabled=Yes* *Totalnumberofpages=4096* *Numberofusablepages=4064* *Highwatermark(pages)=1440*- -*Tablespacename=SYSTOOLSPACE- -*TablespaceID=5- -*TablespaceType=

24、Databasemanagedspace- -*TablespaceContentType=Allpermanentdata.Largetablespace.*TablespacePagesize(bytes)=8192*TablespaceExtentsize(pages)=4*Usingautomaticstorage=Yes*Auto-resizeenabled=Yes*Totalnumberofpages=4096*Numberofusablepages=4092*Highwatermark(pages)=72*- -*-*startredirectedrestore-*RESTORE

25、DATABASESAMPLECONTINUE;-*-*endoffile-*4.可以根据需要定制修改重定向脚本redirectscript,然后再用db2-tvfredirectscript执行。这个功能很实用。分享数据库的归档日志设置和数据库前滚恢复1 .设置归档日志可以有多种设置方法，具体参考InfoCenterCMD:login(user:db2inst1)db2connecttosampledb2updatedbcfgusingLOGARCHMETH1logretain2 .设置成归档日志后的备份设置成归档日志后的数据库处在备份暂挂(BACKUPPENDING态,需要进行备份CMD:d

26、b2connecttosampleSQL1116NAconnectiontooractivationofdatabase"SAMPLE"cannotbemadebecauseofBACKUPPENDING.SQLSTATE=57019CMD:db2backupdatabasesampleto/mnt/hgfs/F/backup/Backupsuccessful.Thetimestampforthisbackupimageis:20100624210336备份后数据库恢复正常。3 .数据修改CMD:db2"insertintoactvalues(999,'T

27、EST','TESTDESC')"db2"select*fromact"ACTNOACTKWDACTDESC10MANAGEMANAGE/ADVISE20ECOSTESTIMATECOST30DEFINEDEFINESPECS40LEADPRLEADPROGRAM/DESIGN50SPECSWRITESPECS60LOGICDESCRIBELOGIC70CODECODEPROGRAMS80TESTTESTPROGRAMS90ADMQSADMQUERYSYSTEM100TEACHTEACHCLASSES110COURSEDEVELOPCOUR

28、SES120STAFFPERSANDSTAFFING130OPERATOPERCOMPUTERSYS140MAINTMAINTSOFTWARESYS150ADMSYSADMOPERATINGSYS160ADMDBADMDATABASES170ADMDCADMDATACOMM180DOCDOCUMENT999TESTTESTDESC4 .误操作删除表空问CMD:cd/home/db2inst1/db2inst1/NODE0000/SAMPLEmv*/mnt/hgfs/F/TSIsdb2connectresetdb2connecttosampleSQL0293NErroraccessingatab

29、lespacecontainer.SQLSTATE=570485 .备份中恢复db2restoredatabasesamplefrom/mnt/hgfs/F/backup/SQL2539WWarning!Restoringtoanexistingdatabasethatisthesameasthebackupimagedatabase.Thedatabasefileswillbedeleted.Doyouwanttocontinue?(y/n)YDB20000ITheRESTOREDATABASEcommandcompletedsuccessfully.6 .回复后数据库处于前滚暂挂(ROLL

30、-FORWARDPENDING)db2connecttosampleSQL1117NAconnectiontooractivationofdatabase"SAMPLE"cannotbemadebecauseofROLL-FORWARDPENDING.SQLSTATE=570197 .执行前滚恢复CMD:db2rollforwarddatabasesampletoendoflogsandstopRollforwardStatusInputdatabasealias=sampleNumberofnodeshavereturnedstatus=1Nodenumber=0Roll

31、forwardstatus=notpendingNextlogfiletoberead=Logfilesprocessed=S0000000.LOG-S0000000.LOGLastcommittedtransaction=2010-06-25-04.28.39.000000UTCDB20000ITheROLLFORWARDcommandcompletedsuccessfully.8 .数据确认CMD:db2connecttosampledb2"select*fromact"ACTNOACTKWDACTDESC10MANAGEMANAGE/ADVISE20ECOSTESTI

32、MATECOST30DEFINEDEFINESPECS40LEADPRLEADPROGRAM/DESIGN50SPECSWRITESPECS60LOGICDESCRIBELOGIC70CODECODEPROGRAMS80TESTTESTPROGRAMS90ADMQSADMQUERYSYSTEM100TEACHTEACHCLASSES110COURSEDEVELOPCOURSES120STAFFPERSANDSTAFFING130OPERATOPERCOMPUTERSYS140MAINTMAINTSOFTWARESYS150ADMSYSADMOPERATINGSYS160ADMDBADMDATA

33、BASES170ADMDCADMDATACOMM180DOCDOCUMENT999TESTTESTDESC备份后的数据变化被前滚恢复了。分享在线备份和数据库时间点恢复1 .启动了归档日志后可以进行在线备份例中同时在备份文件中包含了日志CMD:db2backupdatabasesampleonlineto/mnt/hgfs/F/backup/includelogsBackupsuccessful.Thetimestampforthisbackupimageis:201006262036082 .检查备份日志显示了在线备份的Timestamp,这个是最小的恢复时间点CMD:db2listhisto

34、rybackupallforsampleOpObjTimestamp+SequenceTypeDevEarliestLogCurrentLogBackupIDBD20100626203608001NDS0000004.LOGS0000004.LOGContains5tablespace(s):00001SYSCATSPACE00002USERSPACE100003IBMDB2SAMPLEREL00004IBMDB2SAMPLEXML00005SYSTOOLSPACEComment:DB2BACKUPSAMPLEONLINEStartTime:20100626203608EndTime:2010

35、0626203618Status:AEID:19Location:/mnt/hgfs/F/backup3 .连接数据库，进行测试用的数据插入，同时显示本地时间用来跟踪时间点恢复CMD:db2connecttosampleDatabaseConnectionInformationDatabaseserver=DB2/LINUXX86649.7.1SQLauthorizationID=DB2INST1Localdatabasealias=SAMPLECMD:db2"insertintoactvalues(901,'TEST','TESTDESC')CMD:

36、dateSatJun2620:37:12CST2010CMD:db2"insertintoactvalues(901,'TEST','TESTDESC')DB20000ITheSQLcommandcompletedsuccessfully.CMD:dateSatJun2620:37:17CST2010CMD:db2"insertintoactvalues(902,'TEST','TESTDESC')DB20000ITheSQLcommandcompletedsuccessfully.CMD:dateSatJun

37、2620:37:24CST2010CMD:db2"insertintoactvalues(903,'TEST','TESTDESC')DB20000ITheSQLcommandcompletedsuccessfully.CMD:dateSatJun2620:37:30CST2010CMD:db2"insertintoactvalues(904,'TEST','TESTDESC')DB20000ITheSQLcommandcompletedsuccessfully.CMD:dateSatJun2620:37:35

38、CST2010CMD:db2"insertintoactvalues(905,'TEST','TESTDESC')DB20000ITheSQLcommandcompletedsuccessfully.CMD:dateSatJun2620:37:49CST20104 .检查插入结果CMD:db2"select*fromact"ACTNOACTKWDACTDESC10MANAGEMANAGE/ADVISE20ECOSTESTIMATECOST30DEFINEDEFINESPECS40LEADPRLEADPROGRAM/DESIGN50S

39、PECSWRITESPECS60LOGICDESCRIBELOGIC70CODECODEPROGRAMS80TESTTESTPROGRAMS90ADMQSADMQUERYSYSTEM100TEACHTEACHCLASSES110COURSEDEVELOPCOURSES120STAFFPERSANDSTAFFING130OPERATOPERCOMPUTERSYS140MAINTMAINTSOFTWARESYS150ADMSYSADMOPERATINGSYS160ADMDBADMDATABASES170ADMDCADMDATACOMM180DOCDOCUMENT901TESTTESTDESC902

40、TESTTESTDESC903TESTTESTDESC904TESTTESTDESC905TESTTESTDESC23record(s)selected.5 .故障，表空间误删除CMD:cd/home/db2inst1/db2inst1/NODE0000/SAMPLEmv*/mnt/hgfs/F/TSIsdb2connectresetDB20000ITheSQLcommandcompletedsuccessfully.db2connecttosampleSQL0293NErroraccessingatablespacecontainer.SQLSTATE=570486 .数据库恢复CMD:db

41、2restoredatabasesamplefrom/mnt/hgfs/F/backup/SQL2539WWarning!Restoringtoanexistingdatabasethatisthesameasthebackupimagedatabase.Thedatabasefileswillbedeleted.Doyouwanttocontinue?(y/n)yDB20000ITheRESTOREDATABASEcommandcompletedsuccessfully.7 .恢复后的数据库处在前滚暂挂状态(ROLL-FORWARDPENDING)CMD:db2connecttosample

42、SQL1117NAconnectiontooractivationofdatabase"SAMPLE"cannotbemadebecauseofROLL-FORWARDPENDING.SQLSTATE=570198 .时间点前滚恢复，当小于最小恢复时间点时，无法恢复CMD:db2rollforwarddbsampleto2010-06-26-20.36.00usinglocaltimeandcompleteSQL1275NThestoptimepassedtoroll-forwardmustbegreaterthanorequalto"2010-06-26-20.

43、36.17.000000Local",becausedatabase"SAMPLE'bnnode(s)"0"containsinformationlaterthanthespecifiedtime.9 .时间点前滚恢复CMD:db2rollforwarddbsampleto2010-06-26-20.37.17usinglocaltimeandcompleteRollforwardStatusInputdatabasealias=sampleNumberofnodeshavereturnedstatus=1Nodenumber=0Rollforw

44、ardstatus=notpendingNextlogfiletoberead=Logfilesprocessed=S0000004.LOG-S0000005.LOGLastcommittedtransaction=2010-06-26-20.37.15.000000LocalDB20000ITheROLLFORWARDcommandcompletedsuccessfully.10 .结果的确认，选取的时间点刚好在第一条和第二条记录之间，所以结果显示ACT表中只有第一条记录。结果正确！CMD:db2connecttosampleDatabaseConnectionInformationData

45、baseserver=DB2/LINUXX86649.7.1SQLauthorizationID=DB2INST1Localdatabasealias=SAMPLECMD:db2"select*fromact"ACTNOACTKWDACTDESC10MANAGEMANAGE/ADVISE20ECOSTESTIMATECOST30DEFINEDEFINESPECS40LEADPRLEADPROGRAM/DESIGN50SPECSWRITESPECS60LOGICDESCRIBELOGIC70CODECODEPROGRAMS80TESTTESTPROGRAMS90ADMQSAD

46、MQUERYSYSTEM100TEACHTEACHCLASSES110COURSEDEVELOPCOURSES120STAFFPERSANDSTAFFING130OPERATOPERCOMPUTERSYS140MAINTMAINTSOFTWARESYS150ADMSYSADMOPERATINGSYS160ADMDBADMDATABASES170ADMDCADMDATACOMM180DOCDOCUMENT901TESTTESTDESC19record(s)selected.分享前滚恢复误删除表1 .归档日志的设置参照实战演练-32 .对表空间设置删除表恢复CMD:db2altertablespa

47、ceuserspaceldroppedtablerecoveryon3 .备份CMD:db2backupdatabasesampleonlineto/mnt/hgfs/F/backup/Backupsuccessful.Thetimestampforthisbackupimageis:201006271800454 .表的创建，数据插入和表删除CMD:db2connecttosampleDatabaseConnectionInformationDatabaseserver=DB2/LINUXX86649.7.1SQLauthorizationID=DB2INST1Localdatabaseal

48、ias=SAMPLECMD:db2"CREATETABLETEST_DROPTABLE(COL1INTEGER)INUSERSPACE1"DB20000ITheSQLcommandcompletedsuccessfully.CMD:db2"INSERTINTOTEST_DROPTABLEVALUES(1),(2),(3),(4),(5)”DB20000ITheSQLcommandcompletedsuccessfully.CMD:db2"SELECT*FROMTEST_DROPTABLE"COL1123455record(s)selected.

49、CMD:db2"DROPTABLETEST_DROPTABLE"DB20000ITheSQLcommandcompletedsuccessfully.5 .从删除表历史里找到表的DDLCMD:db2listhistorydroppedtableallforsampleListHistoryFileforsampleNumberofmatchingfileentries=1OpObjTimestamp+SequenceTypeDevEarliestLogCurrentLogBackupIDDT20100627180224000000000100477f00020013&quo

50、t;DB2INST1"."TEST_DROPTABLE"residesin1tablespace(s):00001USERSPACE1Comment:DROPTABLEStartTime:20100627180224EndTime:20100627180224Status:AEID:31DDL:CREATETABLE"DB2INST1"."TEST_DROPTABLE"("COL1"INTEGER)IN"USERSPACE1"6 .表的恢复CMD:db2"restoredbs

51、ampletablespace(userspace1)from/mnt/hgfs/F/backup/"DB20000ITheRESTOREDATABASEcommandcompletedsuccessfully.7 .前滚恢复的同时导出被删除的表数据，注意需要提供表ID(可以在删除表历史记录里获取)CMD:db2"rollforwarddbsampletoendoflogsandstoptablespace(userspace1)recoverdroppedtable000000000100477f00020013to/mnt/hgfs/F/TESTDATA/"Ro