DB2備份與恢復的精華內容

1、目錄實戰(zhàn)演練 - 01 2.實戰(zhàn)演練 - 02 7.實戰(zhàn)演練 - 03 1.4.實戰(zhàn)演練 - 04 1.7.實戰(zhàn)演練 - 05 2.2.實戰(zhàn)演練 - 06 2.6.實戰(zhàn)演練 - 07 3.0.實戰(zhàn)演練 - 08 3.5.實戰(zhàn)演練 - 09 4.1.實戰(zhàn)演練 - 10 4.4.實戰(zhàn)演練 - 11 4.7.以下要為大家推薦一些DB2備份與恢復的精華內容，本內容由cedarbird版主耗時數(shù)月而成，本次內容共分為兩個部分 一一環(huán)境構筑篇和實戰(zhàn)演練篇，兩個部分共有16篇文章組成,是大家學習和掌握 DB2備份與恢復不可或缺的內容。有關本次內容的詳細介紹如下： 環(huán)境構筑篇：VMWARE上CentOS5.4

2、 （64bit）最小服務器環(huán)境構筑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）實戰(zhàn)演練-01循環(huán)日志下的版本恢復概要:（圖例略）最簡單最基本的一個例子循環(huán)日志下的離線備份和恢復要點：做了動作后，別忘了做檢查來驗證動作的正確性。認真、細心是DBA最基本的素質。很多時候能救命的并不是多高級的技術。問題：問題1:實際上這里還故意遺漏一些在生產環(huán)境中必須要做的重要步驟，那是?問題2:這一過程的操作流程圖？ 操作過程:1. 注冊login( user: db2 in stl)2. 連接db2 connect to sample3. 查看備份數(shù)據(jù)庫設定&確認日志模式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刪除數(shù)據(jù) 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實戰(zhàn)演練-02分享循環(huán)日志下的數(shù)據(jù)庫重定向可以利用備份鏡像重定向功能建立新的數(shù)據(jù)庫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.因為是數(shù)據(jù)庫是自動存儲，不需要指定容器。確認容器的狀態(tài)，此時表空間處在恢復暫掛(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.繼續(xù)恢復操作，數(shù)據(jù)庫完全恢復正常。表空間處于 NORMA狀態(tài) SAMPLE數(shù)據(jù)庫被建立。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.可以根據(jù)需要定制修改重定向腳本redirectscript，然后再用db2 -tvfredirectscript 執(zhí)行。這個功能很實用。實戰(zhàn)演練-03分享數(shù)據(jù)庫的歸檔日志設置和數(shù)據(jù)庫前滾恢復1. 設置歸檔日志可以有多種設置方法，具體參考In foCe

29、 nterCMD:login (user: db2 in st1)db2 connect to sampledb2 update db cfg usi ng LOGARCHMETH1 logretain2. 設置成歸檔日志后的備份設置成歸檔日志后的數(shù)據(jù)庫處在備份暫掛(BACKUP PENDIN狀態(tài)，需要進行備份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 備份后數(shù)據(jù)庫恢復正常。3. 數(shù)據(jù)修改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. 回復后數(shù)據(jù)庫處于前滾暫掛(ROLL-FORWARD PENDING態(tài)db2 connect to sampleSQL1117N A conn ection to or activati on of database "SAMPLE" cannot be madebecause of ROLL-FORWARD PENDING. SQLSTATE=570197. 執(zhí)行前滾恢復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. 數(shù)據(jù)

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備份后的數(shù)據(jù)變化被前滾恢復了。實戰(zhàn)演練-04分享在線備份和數(shù)據(jù)庫時間點恢復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. 連接數(shù)據(jù)庫，進行測試用的數(shù)據(jù)插入，同時顯示本地時間用來跟蹤時間點恢復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. 數(shù)據(jù)庫恢復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. 恢復后的數(shù)據(jù)庫處在前滾暫掛狀態(tài) (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