Discusseshow userexitprogramscanbe usedwith databaselogfiles,and describes somesampleuser exitprograms.
AppendixJ,“BackupandrestoreAPIsforvendor products”
Describesthefunctionand useofAPIsthatenableDB2tointerfacewith othervendorsoftware.
Part 1. Data recovery
Chapter 1. Developing a good backup and recovery strategy
Thissectiondiscussesfactorstoconsider whenchoosingdatabase andtablespace recovery methods,includingbackingupandrestoringadatabaseor tablespace, and usingrollforward recovery.
Thefollowingtopics arecovered:
v “Developinga backupandrecoverystrategy”
v “Decidinghow oftentobackup”onpage6 v “Storageconsiderationsforrecovery”onpage8 v “Keepingrelateddatatogether”onpage9
v “Backupandrestoreoperationsbetweendifferentoperatingsystemsand hardwareplatforms”onpage9
v “Crashrecovery”onpage10 v “Disasterrecovery”onpage22 v “Versionrecovery”onpage23 v “Rollforwardrecovery”onpage24
v “Incrementalbackupandrecovery”onpage27
v “Monitoringtheprogressofbackup, restoreandrecovery operations”onpage 32
v “Understandingrecoverylogs”onpage33
v “Understandingtherecovery historyfile”onpage56 v “Understandingtablespacestates”onpage60 v “Enhancingrecoveryperformance”onpage61
Developing a backup and recovery strategy
Adatabasecanbecomeunusablebecauseof hardwareorsoftwarefailure,orboth.
Youmight,at onetimeoranother,encounterstorage problems,power
interruptions,or applicationfailures,andeachfailurescenariorequiresadifferent recovery action.Protectyour dataagainstthepossibilityoflossbyhavingawell rehearsedrecovery strategyinplace.Some ofthequestionsthatyoushouldanswer whendevelopingyour recoverystrategyare:
v Willthedatabaseberecoverable?
v Howmuchtimecanbespentrecoveringthedatabase?
v Howmuchtimewillpass betweenbackupoperations?
v Howmuchstorage spacecanbeallocatedforbackupcopiesandarchivedlogs?
v Willtablespacelevelbackupsbe sufficient,or willfulldatabasebackupsbe necessary?
v ShouldIconfigureastandby system,eithermanuallyorthroughhigh availabilitydisasterrecovery (HADR)?
Adatabaserecovery strategyshouldensurethatallinformationisavailablewhen it isrequiredfordatabaserecovery.Itshouldinclude aregularschedulefortaking database backupsand,inthecaseofpartitioneddatabase environments,include backupswhen thesystem isscaled(whendatabase partitionservers ornodesare addedor dropped).Youroverallstrategyshouldalsoinclude proceduresfor
recoveringcommandscripts,applications,user-definedfunctions(UDFs),stored procedurecodeinoperatingsystemlibraries,and loadcopies.
Different recoverymethodsarediscussedinthesectionsthatfollow,andyouwill discover whichrecoverymethodisbestsuitedtoyourbusinessenvironment.
The conceptofadatabase backupisthesameasanyotherdatabackup: takinga copy ofthedataand thenstoringitonadifferentmediumincaseoffailureor damagetotheoriginal.Thesimplestcaseofabackupinvolvesshuttingdownthe database toensurethatnofurthertransactionsoccur,andthen simplybacking it up.Youcanthenrecreatethedatabaseifitbecomesdamagedorcorruptedinsome way.
The recreationofthedatabaseiscalledrecovery. Versionrecoveryistherestorationof a previousversionofthedatabase,usinganimagethatwas createdduringa backupoperation.Rollforwardrecoveryisthereapplicationof transactionsrecorded in thedatabase logfiles afteradatabase oratablespacebackupimagehasbeen restored.
Crashrecoveryistheautomaticrecovery ofthedatabaseifa failureoccursbeforeall of thechanges thatarepartofoneormore unitsofwork(transactions)are
completedand committed.Thisisdonebyrollingbackincompletetransactions and completingcommittedtransactionsthatwere stillinmemorywhenthecrash occurred.
Recoverylogfilesand therecovery historyfileare createdautomaticallywhena database iscreated (Figure1 onpage5). Theselogfilesareimportantifyouneed torecover datathatislostordamaged.
Eachdatabase includesrecoverylogs,whichareusedtorecoverfromapplicationor system errors.Incombinationwith thedatabase backups,theyareusedtorecover theconsistencyof thedatabase rightuptothepointintimewhentheerror occurred.
The recoveryhistory filecontainsa summaryofthebackupinformationthatcanbe usedtodeterminerecoveryoptions, ifallorpart ofthedatabase mustbe
recovered toagivenpointintime. Itisusedtotrackrecovery-relatedeventssuch asbackupandrestoreoperations,amongothers.Thisfileislocatedinthedatabase directory.
The tablespacechangehistoryfile,whichisalsolocatedinthedatabasedirectory, contains informationthatcanbe usedtodeterminewhichlogfilesarerequiredfor therecovery ofaparticulartablespace.
Youcannotdirectlymodify therecovery historyfileorthetablespacechange historyfile;however,youcandeleteentriesfromthefilesusingthePRUNE HISTORY command.Youcanalso usetherec_his_retentndatabaseconfiguration parameter tospecifythenumber ofdaysthatthesehistoryfileswillberetained.
Data thatiseasilyrecreatedcanbe storedinanon-recoverable database.This includesdatafromanoutsidesourcethatisusedforread-onlyapplications,and tablesthatarenotoftenupdated,forwhichthesmallamountofloggingdoesnot justify theaddedcomplexityof managinglogfilesand rollingforwardafter a restoreoperation.Ifboththelogarchmeth1and logarchmeth2databaseconfiguration parametersareset to“OFF”thenthedatabaseisNon-recoverable.Thismeansthat theonlylogsthatarekeptare thoserequiredfor crashrecovery.Theselogsare known asactivelogs,andtheycontaincurrenttransactiondata.Versionrecovery usingoffline backupsistheprimarymeansofrecovery foranon-recoverable database.(An offlinebackupmeansthatnootherapplication canusethedatabase whenthebackupoperationisin progress.)Sucha databasecanonlybe restored offline. Itisrestoredtothestateitwasinwhenthebackupimagewas takenand rollforward recoveryisnotsupported.
Data thatcannotbeeasilyrecreatedshouldbestored inarecoverable database.
Thisincludesdatawhosesourceisdestroyedafter thedataisloaded,datathatis manuallyenteredintotables, anddatathatismodifiedbyapplicationprogramsor usersafter itisloadedintothedatabase.Recoverabledatabaseshavethelogarchmeth1 orlogarchmeth2 databaseconfigurationparametersset toavalue otherthan“OFF”.
Active logsarestillavailable forcrashrecovery,but youalsohavethearchivedlogs, whichcontaincommittedtransactiondata.Sucha databasecanonlybe restored offline. Itisrestoredtothestateitwasinwhenthebackupimagewas taken.
However, withrollforward recovery,youcanrollthedatabaseforward(thatis, past thetimewhenthebackupimagewastaken)byusingtheactiveandarchivedlogs toeither aspecific pointintime,ortotheendoftheactivelogs.
Recoverabledatabasebackupoperationscanbe performedeitheroffline oronline (online meaningthatotherapplicationscanconnecttothedatabaseduring the backupoperation).Onlinetablespacerestoreandrollforwardoperationsare supportedonlyifthedatabaseisrecoverable.Ifthedatabaseisnon-recoverable, database restoreandrollforwardoperationsmust beperformedoffline.Duringan online backupoperation,rollforward recoveryensuresthatalltablechanges are capturedand reappliedifthatbackupisrestored.
Ifyouhavearecoverable database,youcanbackup,restore,androllindividual table spacesforward,ratherthantheentiredatabase.Whenyoubackupa table
Equivalent physical object
Database
Table space change history file Recovery history file Recovery log files System
Instance
Database
object or concept
Figure1.Databaserecoveryfiles
spaceonline,itisstillavailable foruse,andsimultaneous updatesarerecordedin thelogs.Whenyouperform anonlinerestoreorrollforward operationona table space, thetablespaceitselfisnotavailableforuseuntiltheoperationcompletes, but usersare notprevented fromaccessingtablesinothertablespaces.
Automated backupoperations:
Sinceitcanbe time-consumingtodeterminewhetherand whentorun maintenanceactivitiessuchasbackupoperations,youcanusetheConfigure Automatic Maintenancewizardtodothisforyou. Withautomaticmaintenance, youspecifyyourmaintenanceobjectives,includingwhenautomaticmaintenance canrun. DB2®thenusesthese objectivestodetermineif themaintenanceactivities need tobedoneand thenrunsonlytherequiredmaintenanceactivitiesduring the next availablemaintenancewindow(a user-definedtimeperiodfortherunningof automatic maintenanceactivities).
Note: Youcanstillperformmanualbackupoperationswhenautomatic maintenanceisconfigured.DB2willonlyperform automaticbackup operationsiftheyare required.
Related concepts:
v “Crashrecovery”onpage10
v “Highavailabilitydisasterrecoveryoverview”onpage221 v “Rollforwardrecovery”onpage24
v “Versionrecovery”onpage23 Related reference:
v “logarchmeth1-Primarylogarchivemethod configurationparameter”in PerformanceGuide
v “rec_his_retentn-Recoveryhistoryretentionperiodconfigurationparameter”in PerformanceGuide
Deciding how often to back up
Yourrecovery planshouldallowforregularlyscheduledbackupoperations, because backingupadatabaserequirestimeandsystemresources.Yourplan might includea combinationoffulldatabasebackupsandincremental backup operations.
Youshouldtakefulldatabasebackupsregularly,evenifyouarchivethelogs (whichallows forrollforwardrecovery).Torecovera database,youcanuseeither a fulldatabasebackupimagethatcontainsall ofthetable spacebackupimages,or youcanrebuildthedatabase usingselectedtablespaceimages.Tablespacebackup imagesare alsousefulforrecoveringfroman isolateddisk failureoranapplication error. Inpartitioneddatabaseenvironments,youonlyneedtorestorethetable spacesthatresideondatabasepartitionsthathavefailed.Youdonotneedto restoreall ofthetablespacesorallofthedatabasepartitions.
Althoughfulldatabasebackupsare nolongerrequiredfordatabaserecoverynow thatyoucanrebuildadatabasefromtable spaceimages,itisstillgoodpracticeto occasionallytakea fullbackupofyour database.
Youshouldalsoconsider notoverwritingbackupimagesandlogs,savingatleast twofulldatabasebackupimagesandtheirassociatedlogsasanextraprecaution.
Iftheamount oftimeneededtoapplyarchivedlogswhenrecoveringand rollinga veryactivedatabaseforwardisamajor concern,consider thecostofbackingup thedatabasemore frequently.Thisreducesthenumberofarchivedlogsyouneed toapply whenrollingforward.
Youcaninitiatea backupoperationwhilethedatabaseiseitheronline oroffline.If it isonline, otherapplicationsorprocessescanconnecttothedatabase,aswell as read andmodifydatawhilethebackupoperationisrunning. Ifthebackup operationisrunningoffline,otherapplicationscannotconnecttothedatabase.
Toreducetheamountof timethatthedatabaseisnotavailable, considerusing online backupoperations.Onlinebackupoperationsare supportedonlyif
rollforward recoveryisenabled.Ifrollforwardrecovery isenabled andyouhavea completesetof recoverylogs,youcanrestorethedatabase,shouldtheneed arise.
Youcanonlyuseanonline backupimageforrecovery ifyouhavethelogsthat spanthetimeduring whichthebackupoperationwasrunning.
Offlinebackupoperationsare fasterthanonline backupoperations,sincethereis nocontention forthedatafiles.
Thebackuputilityletsyoubackupselectedtablespaces.IfyouuseDMStable spaces,youcanstore differenttypesofdataintheirown tablespacestoreducethe timerequiredforbackupoperations.Youcankeeptabledatainonetable space, longfieldandLOBdatainanothertablespace, andindexesinyetanothertable space. Ifyoudo thisanda diskfailureoccurs,itislikelytoaffectonlyone ofthe table spaces.Restoringorrollingforwardoneofthesetablespaceswilltakeless timethanitwouldhavetakentorestoreasingletablespacecontainingall ofthe data.
Youcanalsosave timebytakingbackupsofdifferenttablespacesatdifferent times,aslongasthechangestothemare notthesame.So,iflongfieldorLOB dataisnotchangedasfrequentlyastheotherdata,youcanbackupthesetable spacesless frequently.IflongfieldandLOBdataarenotrequiredforrecovery,you canalso considernotbackingupthetablespacethatcontains thatdata.IftheLOB datacanbe reproducedfroma separatesource,choosetheNOTLOGGED option whencreating oralteringatable toincludeLOBcolumns.
Note: Considerthefollowingifyoukeepyour longfielddata,LOBdata,and indexesin separatetablespaces,butdonotbackthemuptogether:Ifyou backupatablespacethatdoesnotcontainallof thetabledata,youcannot performpoint-in-timerollforwardrecovery onthattablespace.Allthetable spacesthatcontainanytype ofdataforatablemust berolledforward simultaneouslytothesame pointintime.
Ifyoureorganizea table,youshouldbackuptheaffectedtablespacesafter the operationcompletes.Ifyouhavetorestorethetablespaces,youwillnothaveto rollforwardthroughthedatareorganization.
Thetimerequiredtorecoveradatabaseismadeupoftwoparts:thetimerequired tocompletetherestorationof thebackup; and,ifthedatabaseisenabledfor forwardrecovery,thetimerequiredtoapplythelogsduringtherollforward operation.Whenformulatingarecovery plan,youshouldtaketheserecovery costs and theirimpactonyour businessoperationsintoaccount. Testingyour overall recovery planwillassistyouindeterminingwhetherthetimerequiredtorecover thedatabaseisreasonablegivenyourbusinessrequirements.Following eachtest, youmightwanttoincreasethefrequencywith whichyoutakeabackup. If
rollforward recoveryispartofyour strategy,thiswillreducethenumber oflogs thatare archivedbetweenbackupsand,asa result,reducethetimerequiredtoroll thedatabaseforwardafter arestoreoperation.
Related concepts:
v “Developinga backupandrecoverystrategy”onpage3 v “Incrementalbackupandrecovery”onpage27
Related reference:
v “Configurationparametersfordatabase logging”onpage37 v AppendixI,“Userexit fordatabaserecovery,”onpage409
Storage considerations for recovery
Whendecidingwhichrecoverymethodtouse, considerthestoragespacerequired.
The versionrecoverymethodrequiresspacetoholdthebackupcopyofthe database andtherestoreddatabase.Therollforwardrecovery methodrequires spacetoholdthebackupcopyofthedatabaseortablespaces,therestored database,and thearchiveddatabaselogs.
Ifa tablecontainslongfieldorlargeobject(LOB)columns,youshouldconsider placingthis dataintoaseparate tablespace.Thiswillaffectyourstorage space considerations, aswell asaffectyour planforrecovery.Withaseparate tablespace for longfieldandLOBdata,andknowingthetimerequiredtobackuplongfield and LOBdata,youmightdecidetousearecovery planthatonlyoccasionally savesa backupof thistablespace.Youcanalsochoose,whencreatingoralteringa table toincludeLOBcolumns,nottologchangestothose columns.Thiswill reducethesizeoftherequiredlogspaceandthecorresponding logarchive space.
Topreventmedia failurefromdestroyingadatabase andyourabilitytorestoreit, keepthedatabasebackup, thedatabase logs,and thedatabaseitselfondifferent devices.For thisreason,it ishighlyrecommendedthatyouusethenewlogpath configurationparametertoputdatabase logsonaseparatedeviceoncethe database iscreated.
The databaselogscanuseupa largeamountofstorage.Ifyouplantousethe rollforward recoverymethod,youmustdecidehowtomanage thearchivedlogs.
Yourchoicesarethefollowing:
v SpecifyalogarchivingmethodusingtheLOGARCHMETH1or LOGARCHMETH2configurationparameters.
v Manuallycopythelogs toastoragedeviceordirectory otherthanthedatabase logpath directoryaftertheyare nolongerintheactivesetoflogs.
v Useauser exitprogramtocopytheselogstoanotherstoragedeviceinyour environment.
Related concepts:
v “Logfilemanagementthroughlogarchiving”onpage49 Related reference:
v “Configurationparametersfordatabase logging”onpage37
v “logarchmeth1-Primarylogarchivemethod configurationparameter”in PerformanceGuide
|
|
|
|
|
v “logarchmeth2-Secondarylogarchive methodconfigurationparameter”in PerformanceGuide
Keeping related data together
Intheprocessofdesigning yourdatabase,youwilldevelop anunderstandingof therelationshipsthatexistbetweentables.Theserelationshipscanbeexpressed:
v Attheapplicationlevel, whentransactionsupdatemorethanonetable
v Atthedatabaselevel,wherereferentialintegrityexistsbetweentables,orwhere triggersononetableaffectanothertable.
Youshouldconsiderthese relationshipswhendevelopingarecovery plan.Youwill wanttobackuprelatedsetsofdatatogether.Suchsetscanbeestablished ateither thetablespaceorthedatabaselevel.By keepingrelatedsetsofdatatogether,you canrecovertoa pointwhereallofthedataisconsistent.Thisisespecially
importantifyouwanttobe abletoperformpoint-in-timerollforward recoveryon table spaces.
Related concepts:
v “Crashrecovery”onpage10
v “Developinga backupandrecoverystrategy”onpage3 v “Disasterrecovery”onpage22
v “Versionrecovery”onpage23 Related tasks:
v “Usingrollforward”onpage157
Backup and restore operations between different operating systems and hardware platforms
DB2 databasesystemssupport somebackupand restoreoperationsbetween differentoperatingsystemsandhardwareplatforms.
ThesupportedplatformsforDB2 backupand restoreoperationscanbegrouped into oneofthreefamilies:
v Big-endianLinux®and UNIX® v Little-endianLinuxandUNIX v Windows
Adatabasebackupfromoneplatformfamilycanberestoredonanysystemwithin thesameplatformfamily.For Windowsoperatingsystems,youcanrestorea database createdonDB2UDBV8ona DB2Version9database system.ForLinux and UNIXoperatingsystems,aslongastheendianness(bigendianorlittle endian)ofthebackupandrestoreplatformsisthesame,youcanrestorebackups thatwere producedonDB2UDBV8onDB2Version9.
Thefollowingtableshows eachoftheLinuxand UNIXplatformsDB2supports and indicateswhethertheplatformsarebigendianorlittleendian:
Table1.EndiannessofsupportedLinuxandUNIXoperatingsystemsDB2supports
Platform Endianness
AIX® bigendian
Table1.EndiannessofsupportedLinuxandUNIXoperatingsystemsDB2
Table1.EndiannessofsupportedLinuxandUNIXoperatingsystemsDB2