db2日志原理及案例

DB2日志管理徐明偉三種恢復(fù)方法Backup(1AM)VERSION

-or-RESTORENow(3PM)CRASHROLL-FORWARDLOGRECORDS

...日志原理BufferPoolCurrentRowInsertLogBufferNewRowOldRowLOGSTABLESDeleteExternalizedCommitLogBufferFullUpdateRequests

forReads

andWrites循環(huán)日志(Circularlogging)PRIMARYSECONDARY1"n"231"n"歸檔日志(Archivallogging)ONLINEARCHIVE—Containsinformationforcommittedandexternalizedtransactions.StoredintheACTIVElogsubdirectory.1213141516ManualorAutomaticArchiveOFFLINEARCHIVE—ArchivemovedfromACTIVElogsubdirectory(mayalsobeonothermediaACTIVE—Containsinformationfornon-committedornon-externalizedtransactions日志存放位置SQL0000nSQLOGDIRS~0.LOGS~1.LOGS~2.LOG/your/choiceS~5.LOGS~3.LOGS~4.LOGnewlogpathInstanceNODE0000mirrorlogpath/2nd/choiceS~5.LOGS~3.LOGS~4.LOGDifferentdiskdrive

andpotentiallyadifferentdiskcontrollerunit數(shù)據(jù)庫日志相關(guān)的一些參數(shù)

PrimaryLog

ArchiveMethod

(LOGARCHMETH1)DEFAULT+-YESNO????Newlogpath

(NEWLOGPATH)Primarylogfiles

(LOGPRIMARY)Secondarylogfiles

(LOGSECOND)Log

size

(LOGFILSIZ)LogBuffer

(LOGBUFSIZ)Numberofcommitstogroup

(MINCOMMIT)Mirrorlogpath

(MIRRORLOGPATH)SecondaryLog

ArchiveMethod

(LOGARCHMETH2)Logrecordstowrite

beforesoftcheckpoint

(SOFTMAX)通過命令行查看和配置數(shù)據(jù)庫日志參數(shù)Windows: db2getdbcfgformusicdb|find/i"log"Linux/UNIX: db2getdbcfgformusicdb|grep–ilogUpdate: db2updatedbcfgformusicdbusinglogprimary15Logretainforrecoverystatus=NOUserexitforloggingstatus=NOCatalogcachesize(4KB)(CATALOGCACHE_SZ)=260Logbuffersize(4KB)(LOGBUFSZ)=98Logfilesize(4KB)(LOGFILSIZ)=1024Numberofprimarylogfiles(LOGPRIMARY)=13Numberofsecondarylogfiles(LOGSECOND)=4Changedpathtologfiles(NEWLOGPATH)=Pathtologfiles=C:\DB2\NODE0000\SQL00003\SQLOGDIR\Overflowlogpath(OVERFLOWLOGPATH)=Mirrorlogpath(MIRRORLOGPATH)=Firstactivelogfile=Blocklogondiskfull(BLK_LOG_DSK_FUL)=NOPercentmaxprimarylogspacebytransaction(MAX_LOG)=0Num.ofactivelogfilesfor1activeUOW(NUM_LOG_SPAN)=0Percentlogfilereclaimedbeforesoftchckpt(SOFTMAX)=520Logretainforrecoveryenabled(LOGRETAIN)=OFFUserexitforloggingenabled(USEREXIT)=OFFHADRlogwritesynchronizationmode(HADR_SYNCMODE)=NEARSYNCFirstlogarchivemethod(LOGARCHMETH1)=OFFOptionsforlogarchmeth1(LOGARCHOPT1)=Secondlogarchivemethod(LOGARCHMETH2)=OFFOptionsforlogarchmeth2(LOGARCHOPT2)=Failoverlogarchivepath(FAILARCHPATH)=Numberoflogarchiveretriesonerror(NUMARCHRETRY)=5LogarchiveretryDelay(secs)(ARCHRETRYDELAY)=20Logpagesduringindexbuild(LOGINDEXBUILD)=OFF日志規(guī)劃日志空間大小規(guī)劃初始設(shè)置為數(shù)據(jù)量大小的10-20%，然后監(jiān)控調(diào)整設(shè)置logprimary,logsecond,logfilsiz參數(shù)規(guī)劃路徑活動(dòng)日志路徑：newlogpath鏡像日志路徑：mirrorlogpath歸檔日志設(shè)置：logarchmeth1:off/disk/tsm/logretain歸檔日志設(shè)置2：logarchmeth2日志管理維護(hù)活動(dòng)日志：用來維護(hù)數(shù)據(jù)庫一致性，不能更改，刪除，移動(dòng)歸檔日志：用作前滾恢復(fù)，需要人工管理日志可能出現(xiàn)的問題活動(dòng)日志滿(sql0964)導(dǎo)致事務(wù)回滾rollback原因事務(wù)日志設(shè)置太小長事務(wù)一直不提交活動(dòng)日志破壞被刪除日志所在磁盤損害解決數(shù)據(jù)庫恢復(fù)Db2dartPatch日志控制文件崩潰恢復(fù)(crashrecovery)DB2異常終止操作系統(tǒng)異常需要思考的問題？日志文件和數(shù)據(jù)