1 / 50

Recovery Techniques for the New DBA

Recovery Techniques for the New DBA. Abstract.

taipa
Download Presentation

Recovery Techniques for the New DBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Recovery Techniques for the New DBA

  2. Abstract • This session focuses on the procedures and utilities that an Advantage™ CA-IDMS®/DB Database Administrator can use to identify and correct a database integrity problem. Attendees learn the recovery methods available and when they are appropriate to use. Also covered are techniques for preventing integrity problems. Attend this sessions if you are responsible for the integrity of your shop’s database.

  3. Biography • Steve RundleBT • Steve has been an employee of BT for 28 years and has 10 years experience with IDMS. Steve is co-chair of the UK IDMS Users Association and is on the board of directors of the international IUA. He is a frequent presenter at CA WorldSM and at IDMS conferences both in the UK and internationally.

  4. Thanks to…. • Laura RochonCompuware Corporation of CanadaPresident Quebec IDMS User GroupInternational Chair IUA

  5. Agenda • Types of Integrity Problems • Causes of Integrity Problems • How to Detect Integrity Problems • How to Fix Integrity Problems • How to Avoid Integrity Problems • Backup and Recovery Procedures • Summary

  6. Types of Integrity Problems • Physical database integrity problem • Broken chains • Pointer errors • Set errors • Integrated index errors • Fragmented record errors • Compressed record errors • Page errors

  7. Types of Integrity Problems • Logical database integrity problem • Business rules not respected • Orphaned records with no owner • 1-to-n relation not respected • Execution of program at wrong time

  8. 65007:1 65007:3 65007:2 Employee 65007:2 65007:1 65007:3 65007:1 Emposition 65007:3 65007:2 65007:1 65007:1 Emposition Broken Chains • Invalid pointers 65007:11 Does not exist

  9. 65007:1 65007:3 65007:2 Employee 65007:2 65007:1 65007:3 65007:1 Emposition 65007:3 65007:2 65007:1 65007:1 Emposition 65008:1 65008:4 65008:2 Employee Broken Chains • Set errors — Multiple owners 65008:01

  10. 65007:1 65007:3 65007:4 Employee 65007:3 65007:2 65007:1 65007:1 Emposition 65007:2 65007:4 65007:3 65007:1 Emposition 65007:4 65007:1 65007:2 65007:1 Emposition Broken Chains • Set errors — Loop 65007:03

  11. Index structure 63016:1 James 63007:3 Jasper 63023:1 Jones 63016:1 63023:1 Employee James Employee Jones 63007:3 Employee Jasper Broken Chains • Integrated index errors Walters Invalid symb key

  12. 63014:4 prefix 63016:7 Dental-Claim 63016:7 65014:4 Dental-Claim Fragment Broken Chains • Fragmented record errors 63025:1 Does not exist

  13. Runtime Indicators of Broken Chains • Loop • Error-Status • 0226 ERASE • xx60 Inconsistent record in set • xx61 Invalid dbkey • Abend Codes • 1117 Can’t adjust space available on page • 1123 Broken fragment

  14. Indicators of Broken Chains • Messages • DB002304 Invalid record in set • DB002305 Dbkey in set not found • DB002423 Dbkey not found • DB002424 SR8 record not found • DC598203 SR2 pointer no good • UT003012 Possible broken chain/invalid ssc • UT016018 SR8 orphan count

  15. Agenda • Types of Integrity Problems • Causes of Integrity Problems • How to Detect Integrity Problems • How to Fix Integrity Problems • How to Avoid Integrity Problems • Backup and Recovery Procedures • Summary

  16. Causes of Integrity Problems • Physical integrity problem: • Improper recovery from: • Program failure • System failure • Hardware failure • Hardware malfunction • Improper use of FIX PAGE, UNLOCK • Occasional software problem

  17. Causes of Integrity Problems • Logical integrity problem: • Application program bug • Execution of program at wrong time

  18. Agenda • Types of Integrity Problems • Causes of Integrity Problems • How to Detect Integrity Problems • How to Fix Integrity Problems • How to Avoid Integrity Problems • Backup and Recovery Procedures • Summary

  19. How to Detect Integrity Problems • IDMSDBAN • Report 2 — Area report • Checks page integrity IDMSDBAN - DATA BASE ANALYSIS 598601 - AREA PROCESSING BEGINNING: EMPDEMO.EMP-DEMO-REGION 598505 - PAGE 65004 PAGE CORNERS INCORRECT

  20. How to Detect Integrity Problems • IDMSDBAN • Report 4 — Record report • Checks record definition IDMSDBAN - DATA BASE ANALYSIS 598601 - AREA PROCESSING BEGINNING: EMPDEMO.EMP-DEMO-REGION 598516 - DBKEY 65007:1 LI RECORD LENGTH NE SS RECORD LENGTH (FLR) 598516 - DBKEY 65007:8 LI RECORD LENGTH NE SS RECORD LENGTH (FLR) 598516 - DBKEY 65008:1 LI RECORD LENGTH NE SS RECORD LENGTH (FLR) 598516 - DBKEY 65009:1 LI RECORD LENGTH NE SS RECORD LENGTH (FLR) 598516 - DBKEY 65010:1 LI RECORD LENGTH NE SS RECORD LENGTH (FLR) 598516 - DBKEY 65010:10 LI RECORD LENGTH NE SS RECORD LENGTH (FLR)

  21. How to Detect Integrity Problems • IDMSDBAN • Report 5 — Set report • Check set integrity 599703 - PRIOR LINK NOT FOUND SET=EMP-EMPOSITION CHAIN START OF LINK 65007:2 OWNER IS AT 65007:1 CHAIN LENGTH 1 LINK POINTS TO 65007:1 PRIOR IN PFX 65007:3 599702 - NEXT LINK NOT FOUND SET=EMP-EMPOSITION CHAIN START OF LINK 65007:3 OWNER IS AT 65007:1 CHAIN LENGTH 1 LINK POINTS TO 65007:11 PRIOR IN PFX 65007:1

  22. How to Detect Integrity Problems • AdvantageTM CA-IDMS®/DB Database Audit Option • AUDIT — Checks set integrity ************************************************************************************************************************************ **************************************************** B E F O R E I M A G E ***************************************************** ************************************************************************************************************************************ REPT034I INTEGRITY ERRORS DETECTED IN SET...EMP-EMPOSITION --OWNER RECORD-- -ID- -------DBKEY------- -----NEXT DBKEY---- ----PRIOR DBKEY---- ---------------RECORD DATA--------------- EMPLOYEE 0415 65,007-0001 65,007-0003 65,007-0002 0023KATHERINE O HEARN 12 EAST SPEE FFFFDCECCDCDC4D7CCCDD44444444FF4CCEE4EDCC 00FDEF01 00FDEF03 00FDEF02 002321385995506D8519500000000120512302755 REPT036I RECORDS DETECTED WALKING NEXT FROM OWNER . . . ---RECORD NAME-- -ID- -------DBKEY------- -----NEXT DBKEY---- ----PRIOR DBKEY---- ----OWNER DBKEY---- -----RECORD DATA----- EMPOSITION 0420 65,007-0003 65,007-0011 65,007-0001 65,007-0001 197905050000000044 FFFFFFFFFFFFFFFFFF045 00FDEF03 00FDEF0B 00FDEF01 00FDEF01 197905050000000044020 REPT063E NEXT DBKEY POINTS TO NON-EXISTENT RECORD REPT037I RECORDS DETECTED WALKING PRIOR FROM OWNER . . . EMPOSITION 0420 65,007-0002 65,007-0001 65,007-0003 65,007-0001 197805041979050443 FFFFFFFFFFFFFFFFFF030 00FDEF02 00FDEF01 00FDEF03 00FDEF01 197805041979050443080 EMPOSITION 0420 65,007-0003 65,007-0011 65,007-0001 65,007-0001 197905050000000044 FFFFFFFFFFFFFFFFFF045 00FDEF03 00FDEF0B 00FDEF01 00FDEF01 197905050000000044020 REPT063E NEXT DBKEY POINTS TO NON-EXISTENT RECORD

  23. Agenda • Types of Integrity Problems • Causes of Integrity Problems • How to Detect Integrity Problems • How to Fix Integrity Problems • How to Avoid Integrity Problems • Backup and Recovery Procedures • Summary

  24. How to Fix Integrity Problems • Advantage CA-IDMS/DB Audit • FIX=SIMULATE (proposes how to fix problem) ************************************************************************************************************************************ ******************************************* S I M U L A T E D A F T E R I M A G E ******************************************** ************************************************************************************************************************************ REPT035I FIXING PERFORMED ON SET...EMP-EMPOSITION --OWNER RECORD-- -ID- -------DBKEY------- -----NEXT DBKEY---- ----PRIOR DBKEY---- ---------------RECORD DATA--------------- EMPLOYEE 0415 65,007-0001 65,007-0003 65,007-0002 0023KATHERINE O HEARN 12 EAST SPEE FFFFDCECCDCDC4D7CCCDD44444444FF4CCEE4EDCC 00FDEF01 00FDEF03 00FDEF02 002321385995506D8519500000000120512302755 ---RECORD NAME-- -ID- -------DBKEY------- -----NEXT DBKEY---- ----PRIOR DBKEY---- ----OWNER DBKEY---- -----RECORD DATA----- EMPOSITION 0420 65,007-0003 65,007-0002 65,007-0001 65,007-0001 197905050000000044 FFFFFFFFFFFFFFFFFF045 00FDEF03 00FDEF02 00FDEF01 00FDEF01 197905050000000044020 EMPOSITION 0420 65,007-0002 65,007-0001 65,007-0003 65,007-0001 197805041979050443 FFFFFFFFFFFFFFFFFF030 00FDEF02 00FDEF01 00FDEF03 00FDEF01 197805041979050443080

  25. How to Fix Integrity Problems • Advantage CA-IDMS/DB Audit • FIX=UPDATE (fixes problem) ************************************************************************************************************************************* ***************************************************** A F T E R I M A G E ******************************************************* ************************************************************************************************************************************* REPT035I FIXING PERFORMED ON SET...EMP-EMPOSITION --OWNER RECORD-- -ID- -------DBKEY------- -----NEXT DBKEY---- ----PRIOR DBKEY---- ---------------RECORD DATA--------------- EMPLOYEE 0415 65,007-0001 65,007-0003 65,007-0002 0023KATHERINE O HEARN 12 EAST SPEE FFFFDCECCDCDC4D7CCCDD44444444FF4CCEE4EDCC 00FDEF01 00FDEF03 00FDEF02 002321385995506D8519500000000120512302755 ---RECORD NAME-- -ID- -------DBKEY------- -----NEXT DBKEY---- ----PRIOR DBKEY---- ----OWNER DBKEY---- -----RECORD DATA----- EMPOSITION 0420 65,007-0003 65,007-0002 65,007-0001 65,007-0001 197905050000000044 FFFFFFFFFFFFFFFFFF045 00FDEF03 00FDEF02 00FDEF01 00FDEF01 197905050000000044020 EMPOSITION 0420 65,007-0002 65,007-0001 65,007-0003 65,007-0001 197805041979050443 FFFFFFFFFFFFFFFFFF030 00FDEF02 00FDEF01 00FDEF03 00FDEF01 197805041979050443080

  26. How to Fix Integrity Problems • PRINT PAGE PRINT PAGE 0:65007; PAGE 65,007 PAGE GROUP 0 AVAILABLE SPACE 3,452 000000 0000FDEF 00FDEF01 00FDEF08 0D7C0000 00FDEF08 00FDEF00 00FDF501 00FE1001 *.............@.. ..........5.....* 000020 00FA1001 00FDEA02 00FA080A 00FA0802 00F61E02 00F61E01 00FDEF03 00FDEF02 *.:.......:...:.. .6...6..........* 000040 00FDEF06 00FDEF07 00FDEF01 00FDEF01 00FDFA0A 00FDFE0F F0F0F2F3 D2C1E3C8 *................ ..:.....0023KATH* 000060 C5D9C9D5 C540D67D C8C5C1D9 D5404040 40404040 40F1F240 C5C1E2E3 40E2D7C5 *ERINE O'HEARN 12 EAST SPE* 000080 C5D540E2 E3404040 40D5C1E3 C9C3D240 40404040 40404040 D4C1F0F2 F3F6F440 *EN ST NATICK MA02364 * 0000A0 404040F6 F1F7F8F8 F9F7F1F3 F4F0F1F0 F1F9F5F5 F6F7F1F2 F1F9F7F8 F0F5F0F4 * 6178897134010 1955671219780504* 0000C0 F0F0F0F0 F0F0F0F0 F1F9F5F4 F0F4F0F7 00FDEF01 00FDEF03 00FDEF01 00FDF502 *0000000019540407 ..............5.* 0000E0 00FA1107 00FA1107 F1F9F7F8 F0F5F0F4 F1F9F7F9 F0F5F0F4 F4F30038 00000C00 *.:...:..19780504 1979050443......* 000100 4C000C00 0C000000 00FDEF0B 00FDEF01 00FDEF0100FE0202 00FDF402 00FA0809 *<............... ..........4..:..* 000120 F1F9F7F9 F0F5F0F5 F0F0F0F0 F0F0F0F0 F4F40042 50000C00 7C000C00 0C000000 *1979050500000000 44..&...@.......* 000140 00FDEF07 00FDEF05 00FDEF01 00FA180B 00FA1803 F0F3F1F9 F7F5F1F2 F2F80000 *.............:.. .:..0319751228..* ... 1 4 0 0004 65,007-001 65,007-008 415 120 1 0010 65,007-008 65,007-000 65,013-001 65,040-001 64,016-001 65,002-002 64,008-010 64,008-002 63,006-002 63,006-001 65,007-003 65,007-002 65,007-006 65,007-007 65,007-001 65,007-001 65,018-010 65,022-015 *0023KATHERINE O'HEARN 12 EAST SPEEN ST NATICK MA02364 617889713401019* *556712197805040000000019540407* 420 32 2 00D0 65,007-001 65,007-003 65,007-001 65,013-002 64,017-007 64,017-007 *197805041979050443......<.......* 420 32 3 0108 65,007-011 65,007-001 65,007-001 65,026-002 65,012-002 64,008-009 *197905050000000044..&...@.......*

  27. How to Fix Integrity Problems • FIX PAGE FIX PAGE 0:65007 VER 0108 00FD,EF0B REP 0108 00FD,EF02 PAGE 65,007 PAGE GROUP 0 AVAILABLE SPACE 3,452 000000 0000FDEF 00FDEF01 00FDEF08 0D7C0000 00FDEF08 00FDEF00 00FDF501 00FE1001 *.............@.. ..........5.....* 000020 00FA1001 00FDEA02 00FA080A 00FA0802 00F61E02 00F61E01 00FDEF03 00FDEF02 *.:.......:...:.. .6...6..........* 000040 00FDEF06 00FDEF07 00FDEF01 00FDEF01 00FDFA0A 00FDFE0F F0F0F2F3 D2C1E3C8 *................ ..:.....0023KATH* 000060 C5D9C9D5 C540D67D C8C5C1D9 D5404040 40404040 40F1F240 C5C1E2E3 40E2D7C5 *ERINE O'HEARN 12 EAST SPE* 000080 C5D540E2 E3404040 40D5C1E3 C9C3D240 40404040 40404040 D4C1F0F2 F3F6F440 *EN ST NATICK MA02364 * 0000A0 404040F6 F1F7F8F8 F9F7F1F3 F4F0F1F0 F1F9F5F5 F6F7F1F2 F1F9F7F8 F0F5F0F4 * 6178897134010 1955671219780504* 0000C0 F0F0F0F0 F0F0F0F0 F1F9F5F4 F0F4F0F7 00FDEF01 00FDEF03 00FDEF01 00FDF502 *0000000019540407 ..............5.* 0000E0 00FA1107 00FA1107 F1F9F7F8 F0F5F0F4 F1F9F7F9 F0F5F0F4 F4F30038 00000C00 *.:...:..19780504 1979050443......* 000100 4C000C00 0C000000 00FDEF02 00FDEF01 00FDEF01 00FE0202 00FDF402 00FA0809 *<............... ..........4..:..* 000120 F1F9F7F9 F0F5F0F5 F0F0F0F0 F0F0F0F0 F4F40042 50000C00 7C000C00 0C000000 *1979050500000000 44..&...@.......* 000140 00FDEF07 00FDEF05 00FDEF01 00FA180B 00FA1803 F0F3F1F9 F7F5F1F2 F2F80000 *.............:.. .:..0319751228..* ... 1 4 0 0004 65,007-001 65,007-008 415 120 1 0010 65,007-008 65,007-000 65,013-001 65,040-001 64,016-001 65,002-002 64,008-010 64,008-002 63,006-002 63,006-001 65,007-003 65,007-002 65,007-006 65,007-007 65,007-001 65,007-001 65,018-010 65,022-015 *0023KATHERINE O'HEARN 12 EAST SPEEN ST NATICK MA02364 617889713401019* *556712197805040000000019540407* 420 32 2 00D0 65,007-001 65,007-003 65,007-001 65,013-002 64,017-007 64,017-007 *197805041979050443......<.......* 420 32 3 0108 65,007-002 65,007-001 65,007-001 65,026-002 65,012-002 64,008-009 *197905050000000044..&...@.......*

  28. How to Fix Integrity Problems • Logical integrity problem: • Application program bug: • Fix the bug • Might need one-time program to fix data • Execution of program at wrong time: • What do you do? • Depends on when problem is discovered • If right after => Rollback • If not => One-time program to fix data => ????

  29. Agenda • Types of Integrity Problems • Causes of Integrity Problems • How to Detect Integrity Problems • How to Fix Integrity Problems • How to Avoid Integrity Problems • Backup and Recovery Procedures • Summary

  30. How to Avoid Integrity Problems • Proper backup and recovery procedures • Never run UNLOCK on locked databases unless you’re 150% certain there are no broken chains • Database maintenance done under covers by DBMS if area opened in update mode • Apply HYPER apars regarding data integrity

  31. Agenda • Types of Integrity Problems • Causes of Integrity Problems • How to Detect Integrity Problems • How to Fix Integrity Problems • How to Avoid Integrity Problems • Backup and Recovery Procedures • Summary

  32. Backup and Recovery Procedures • Frequently scheduled backups • Quiesced backups • CV down • CV up - Areas quiesced • DCMT VARY AREA RETRIEVAL/OFFLINE • DCMT VARY SEGMENT RETRIEVAL/OFFLINE • DCMT QUIESCE AREA • DCMT QUIESCE SEGMENT • DCMT QUIESCE DBNAME

  33. Backup and Recovery Procedures • Frequently scheduled backups (cont’d) • Hot backups • Quiesce update activity on areas • Note date/time of quiesce point • Restart update activity on areas • Backup the areas • Optionally, get another quiesce point on areas

  34. Backup and Recovery Procedures • Frequently scheduled backups (cont’d) • Local mode update jobs • Backup before job • Backup after job

  35. Backup and Recovery Procedures • Recovery after warmstart failure • Offload all journal files • ROLLBACK ACTIVE • UNLOCK areas not affected by ROLLBACK • FORMAT journals

  36. Backup and Recovery Procedures • ROLLBACK ROLLBACK AREA EMPDEMO.EMP-DEMO-REGION ALL STOP AT '2003-05-19-21.53.30.0000' ; ROLLBACK STARTED 2003-05-20-08.32.18.478122 NODE SYST0060 RU_ID 0000109554 PGM_ID USDMAIN0 QUIESCE LEVELS 00 UPD 00 ENDJ 2003-05-19-21.57.08.594829 NODE SYST0060 RU_ID 0000109554 PGM_ID USDMAIN0 QUIESCE LEVELS 01 UPD 00 BGIN 2003-05-19-21.54.25.881953 RECORDS RESTORED TO AREA EMPDEMO.EMP-DEMO-REGION 3 TOTAL RECORDS RESTORED 3 JOURNAL INPUT COUNTS: BLOCK COUNT FORWARD 0 BACKWARD 2 RECORD COUNT FORWARD 0 BACKWARD 14 Status = 0 SQLSTATE = 00000

  37. Backup and Recovery Procedures • Recovery from database I/O error • If transactions recover successfully • DCMT V AREA xxx OFFLINE • Fix problem • DCMT V AREA xxx ONLINE

  38. Backup and Recovery Procedures • Recovery from database I/O error (cont’d) • If transactions’ recovery is unsuccessful • DCMT V AR TRANSIENT RETRIEVAL/OFFLINE • DCMT V JOURNAL • DCMT V FILE DEALLOCATE • Restore backup • ROLLFORWARD FILE • Rename files • DCMT V FILE ALLOCATE • DCMT V FILE ACTIVE • DCMT V AR ONLINE

  39. Backup and Recovery Procedures • ROLLFORWARD ROLLFORWARD AREA EMPDEMO.EMP-DEMO-REGION ALL ; ROLLFORWARD STARTED 2003-05-20-09.24.03.048045 NODE SYST0060 RU_ID 0000109552 PGM_ID USDMAIN0 QUIESCE LEVELS 01 UPD 00 BGIN 2003-05-20-08.57.28.904026 NODE SYST0060 RU_ID 0000109552 PGM_ID USDMAIN0 QUIESCE LEVELS 00 UPD 00 ENDJ 2003-05-20-08.59.37.495137 RECORDS RESTORED TO AREA EMPDEMO.EMP-DEMO-REGION 20 TOTAL RECORDS RESTORED 20 JOURNAL INPUT COUNTS: BLOCK COUNT FORWARD 1 BACKWARD 0 RECORD COUNT FORWARD 71 BACKWARD 0 Status = 0 SQLSTATE = 00000

  40. Backup and Recovery Procedures • Recovery from journal I/O error • Quiesce update activity • If all update transactions finish normally • Backup areas • Format affected journal file • DCMT VARY AREA ONLINE

  41. Backup and Recovery Procedures • Recovery from journal I/O error (cont’d) • If you get SUSPENDED TRANSACTIONS • Cancel the system • Restore backups of all update areas • ROLLFORWARD COMPLETE for all those areas • FORMAT JOURNAL ALL • Backup areas • Restart system

  42. Backup and Recovery Procedures • Recovery from local mode operations • Not journaling • Restore backup taken before job • Journaling to tape • ROLLBACK with local tape journal • Journal to disk • Copy disk journal to tape • ROLLBACK with tape journal • If using incomplete journal file • FIX JOURNAL

  43. Backup and Recovery Procedures • Mixed mode recovery • When database area is updated by both CV and local mode job (at different times) • Will need CV and local journals • Can use MERGE ARCHIVE or • Can run separate ROLLFORWARD jobs

  44. Agenda • Types of Integrity Problems • Causes of Integrity Problems • How to Detect Integrity Problems • How to Fix Integrity Problems • How to Avoid Integrity Problems • Backup and Recovery Procedures • Summary

  45. Session Summary • Important to have backup and recovery procedures. • Run IDMDBAN regularly. • Be VERY cautious when using UNLOCK and FIX PAGE

  46. Questions & Answers

  47. Session Evaluation Form After completing your session evaluation form ... UKIUA ... please place it in the basket at the back of the room.

  48. Notes

  49. Notes

  50. Notes

More Related