Recovery Techniques for the New DBA

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 shops 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
Broken Chains

• Invalid pointers

6500711
Does not exist
9
Broken Chains

• Set errors Multiple owners

6500801
10
Broken Chains

• Set errors Loop

6500703
11
Broken Chains

• Integrated index errors

Walters
Invalid symb key
12
Broken Chains

• Fragmented record errors

630251
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 Cant 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 650071 LI RECORD LENGTH NE SS
RECORD LENGTH (FLR) 598516 - DBKEY
650078 LI RECORD LENGTH NE SS RECORD LENGTH
(FLR) 598516 - DBKEY 650081 LI
RECORD LENGTH NE SS RECORD LENGTH (FLR)
598516 - DBKEY 650091 LI RECORD
LENGTH NE SS RECORD LENGTH (FLR) 598516 -
DBKEY 650101 LI RECORD LENGTH NE SS
RECORD LENGTH (FLR) 598516 - DBKEY
6501010 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
SETEMP-EMPOSITION CHAIN START OF LINK
650072 OWNER IS AT 650071

CHAIN LENGTH 1
LINK POINTS TO 650071 PRIOR IN
PFX 650073

599702 - NEXT LINK NOT FOUND
SETEMP-EMPOSITION CHAIN START OF LINK
650073 OWNER IS AT 650071

CHAIN LENGTH 1
LINK POINTS TO 6500711 PRIOR IN
PFX 650071
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


R
EPT034I 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


FFFFDCECCDCDC4D7CCCD
D44444444FF4CCEE4EDCC
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 REPT063
E 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
• FIXSIMULATE (proposes how to fix problem)

S I M U L A T E D A
F T E R I M A G E


R
EPT035I FIXING PERFORMED ON SET...EMP-EMPOSITION
--OWNER
RECORD-- -ID- -------DBKEY------- -----NEXT
DBKEY---- ----PRIOR DBKEY---- ---------------RECO
RD DATA--------------- EMPLOYEE
0415 65,007-0001
65,007-0003 65,007-0002
0023KATHERINE O HEARN 12 EAST SPEE



FFFFDCECCDCDC4D7CCCDD44444444FF4CCEE4EDC
C
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



FFFFFFFFFFFFFFFFFF04
5
00FDEF03 00FDEF02
00FDEF01
00FDEF01 197905050000000044020
EMPOSITION 0420 65,007-0002
65,007-0001
65,007-0003 65,007-0001
197805041979050443



FFFFFFFFFFFFFFFFFF03
0
00FDEF02 00FDEF01
00FDEF03
00FDEF01 197805041979050443080

25
How to Fix Integrity Problems

• Advantage CA-IDMS/DB Audit
• FIXUPDATE (fixes problem)

A F T E R
I M A G E



REPT035I FIXING PERFORMED ON SET...EMP-EMPOSITIO
N --OWNER
RECORD-- -ID- -------DBKEY------- -----NEXT
DBKEY---- ----PRIOR DBKEY---- ---------------RECO
RD DATA--------------- EMPLOYEE
0415 65,007-0001
65,007-0003 65,007-0002
0023KATHERINE O HEARN 12 EAST SPEE



FFFFDCECCDCDC4D7CCCDD44444444FF4CCEE4ED
CC
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 065007
PAGE 65,007
PAGE GROUP 0 AVAILABLE SPACE 3,452
000000 0000FDEF 00FDEF01
00FDEF08 0D7C0000 00FDEF08 00FDEF00
00FDF501 00FE1001 ............._at_..
..........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 00FDEF01 00FE0202 00FDF402
00FA0809 lt............... ..........4....
000120 F1F9F7F9 F0F5F0F5 F0F0F0F0
F0F0F0F0 F4F40042 50000C00 7C000C00
0C000000 1979050500000000 44....._at_.......
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......lt.
...... 420 32 3
0108 65,007-011 65,007-001
65,007-001 65,026-002 65,012-002
64,008-009
197905050000000044....._at_.......
27
How to Fix Integrity Problems

• FIX PAGE

FIX PAGE 065007
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
............._at_.. ..........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
lt............... ..........4.... 000120
F1F9F7F9 F0F5F0F5 F0F0F0F0 F0F0F0F0
F4F40042 50000C00 7C000C00 0C000000
1979050500000000 44....._at_....... 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......lt.......
420 32 3 0108 65,007-002
65,007-001 65,007-001 65,026-002
65,012-002 64,008-009
197905050000000044....._at_
.......
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 gt Rollback
• If not gt One-time program to fix data
  gt ????

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
  youre 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 (contd)
• 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 (contd)
• 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 (contd)
• 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 (contd)
• 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