-
Notifications
You must be signed in to change notification settings - Fork 0
/
DB2_Crash_Recovery_from_Tablespace_Backups_Big_Online_Incremental.bash
654 lines (617 loc) · 33.4 KB
/
DB2_Crash_Recovery_from_Tablespace_Backups_Big_Online_Incremental.bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
#!/usr/bin/bash
#############################################################################
## Licensed Materials - Property of BRZ
##
## Governed under the terms of the International
## License Agreement for Non-Warranted Sample Code.
##
## (C) COPYRIGHT Bundesrechenzentrum
## All Rights Reserved.
## Author : Wolfgang Brandl
#############################################################################
# This Script should test a specific backup / restore scenario
# There a several steps which could be executed
#Die folgenden Szenarien sollen mit diesem Skript nachgestellt werden können.
#Es gibt eine Datenbank in der zwei Tablespaces alte Daten enthalten. Diese Archived - Data sind sehr grosz und werden nicht mehr verändert und stehen für die laufenden Transaktionen nur mehr lesend zur Verfügung.
#1. In der Datenbank wird eine Tabelle zerstört
# a. Restore des Tablespaces welcher die Tabelle enthält in die active Datenbank to point in time RESTOREINTOEXIST
# b. Restore des Tablespaces welcher die zerstörte Tabelle enthält in eine neue Datenbank (REDIRECT)
#2. Datenbank ist zerstört
# a. Archive Logs sind noch vorhanden RESTOREWITHARCHLOGS
# b. Archive logs sind nicht vorhanden. Dadurch kann nicht vollständig recovered werden. RESTOREWITHOUTARCHLOGS
CONTAINERPATH=/node1/data0/db2/S2T01/IT99
LOGCONTAINERPATH=/node1/logs/db2/S2T01/IT99
REPLACEPATH=/node1/data0/db2/S2T01/OPTIM
REPLACELOGPATH=/node1/logs/db2/S2T01/OPTIM
BACKUPPATH=/node1/data0/db2/S2T01/IT99/backup
QUAL=QTEST
LOCALINSTANCE=db2it99
RESTOREINSTANCE=db2opt
source util.bash
#-------------------------------------------------------
# HELP Message
# ------------------------------------------------------
print_help ()
{
printf "Usage: test_tablespace_restore.bash -m <MODE> -s <Databasename Source> \n"
printf " -m <MODE>\n"
printf " MODE can be:\n"
printf " INITDB: Initialisieren, befÃuellen und Backup der Datenbank\n"
printf " DROPDB: Loeschen der Datenbank\n"
printf " WORK: Make additional work and backup\n"
printf " WORKWITHREORGRUNSTATBIG: Make additional work and run reorg and runstat on the big tables to generate additional log entries for BIG tablespaces\n"
printf " WORKTIME: Make additional work make a timestamp and delete a table TABLEM\n"
printf " BACKUPBIG: If Backup is needed for the big tablespaces\n"
printf " CHECK: Check records of Database and tablespace State\n"
printf " DROPED TABLE recover Test\n"
printf " WORKDROP: Drop Table TABLEM \n"
printf " CREATEDROPEDTABLE: Create TABLEM \n"
printf " IMPORTTABLEM: Import droped table \n"
printf " RESTOREDROPEDTABLE: Restore one tablespace into other instance and generate import File\n"
printf " Load Test\n"
printf " EXPORTTABLEA: Export content of TABLEA\n"
printf " FILLTABLEA: Fill table TABLEA with data\n"
printf " CHGTABLEANOGENERATEDCOLUMN: Export/Drop/Create witout generated column/import TABLEA\n"
printf " BACKUPFULLONLINE: Backup Database Full Online\n"
printf " BACKUPINCONLINE: Backup Database incremental Online\n"
printf " EMPTYTABLEA : Empty Table TABLEA without Transaction Logs\n"
printf " LOADTABLEANONRECOVERABLE: Load data of TABLEA nonrecoverable\n"
printf " LOADTABLEACOPYYES: Load data of TABLEA with copy yes into BACKUP Directory\n"
printf " RECOVERDATABASEFROMFULL: Recover Database: Restore/Rollforward from Full Backup to end of logs\n"
printf " RECOVERDATABASEFROMINC: Recover Database: Restore/Rollforward from Incremental Backup to end of logs\n"
printf " Crash Recovery Datenbank ist zerstoert\n"
printf " RESTOREWITHARCHLOGSREDIRECT: Restore Database to end of archlogs in other Instance\n"
printf " RESTOREWITHARCHLOGS: Restore Database to end of archlogs\n"
printf " RESTOREWITHOUTARCHLOGS: Restore Crash recovery\n"
printf " Human errort\n"
printf " RESTOREONETABLESPACE: Restore one tablespace into other instance and rollforward to timestamp . You have to run WORKTIME before \n"
printf " RESTOREINTOEXIST: Restore one tablespace into existing DB with rollforward to timestamp You have to run WORKTIME before \n"
printf " -s Database name\n"
printf "Please add the name of the Databases\n"
}
#-------------------------------------------------------
# MAIN
# ------------------------------------------------------
while [[ $# -gt 1 ]]
do
key="$1"
case $key in
-m|--mode)
MODE="$2"
shift # past argument
;;
-s|--sourcedb)
DB="$2"
shift # past argument
;;
-h|--help)
print_help
exit 4
;;
*)
print_help
exit 4
;;
esac
countbig=30000
count=1000
shift # past argument or value
done
if [ "$DB" == "" ]
then
print_help
exit 8
fi
case $MODE in
INITDB)
rm -f $BACKUPPATH/"$DB".*.001
rm -f $BACKUPPATH/"$DB"ONLINE*.out
rm -f $BACKUPPATH/"$DB"LOAD*.out
smooth_drop "$DB" $CONTAINERPATH $LOGCONTAINERPATH
create_db "$DB" $CONTAINERPATH $LOGCONTAINERPATH
printf "Aufsetzen der Datenbanknk %s incremental online Backups\n" "$DB"
db2 -v "update db cfg for $DB using logarchmeth1 disk:$LOGCONTAINERPATH/log/archlog/$DB"
db2 -v "update db cfg for $DB using TRACKMOD YES"
db2 +o connect to "$DB"
db2 +o connect reset
db2 +o backup database "$DB" to /dev/null
db2 +o backup database "$DB" to /dev/null
db2 +o backup database "$DB" online to /dev/null
db2 +o backup database "$DB" online incremental to /dev/null
# CREATE SYSTOOLSPACE by calling procedure get_dbsize_info
db2 +o connect to "$DB"
db2 -v "call get_dbsize_info(?,?,?,-1)"
db2 +o connect reset
#
db2 +o connect to "$DB"
db2 -v "CREATE STOGROUP TS_U ON '$CONTAINERPATH/TS_U_SPACE/$DB' "
db2 -v "CREATE STOGROUP TS_M ON '$CONTAINERPATH/TS_M_SPACE/$DB' "
db2 -v "CREATE STOGROUP TS_B ON '$CONTAINERPATH/TS_B_SPACE/$DB' "
db2 -v "CREATE STOGROUP TS_N ON '$CONTAINERPATH/TS_N_SPACE/$DB' "
db2 -v create tablespace NSPACE USING STOGROUP TS_N
db2 -v create tablespace BSPACE USING STOGROUP TS_B
db2 -v create tablespace URSPACE USING STOGROUP TS_U
db2 -v create tablespace MYSPACE USING STOGROUP TS_M
db2 +o connect reset
create_table "$DB" TABLEN NSPACE
create_table "$DB" TABLEB BSPACE
insert_into_table "$DB" $QUAL.TABLEB $countbig &
insert_into_table "$DB" $QUAL.TABLEN $countbig &
wait
db2 -v "backup database $DB tablespace (SYSCATSPACE,NSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE.out
RC=$?
ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE.out)
db2 -v "backup database $DB tablespace (SYSCATSPACE,BSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE.out
RC=$?
ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE.out)
rm -rf $LOGCONTAINERPATH/log/archlog/"$DB"/*
ls -l $LOGCONTAINERPATH/log/archlog/"$DB"/*
db2 get db cfg for "$DB"|grep "First active log file"
create_table "$DB" TABLEA USERSPACE1
create_table "$DB" TABLEC URSPACE
create_table "$DB" TABLEM MYSPACE
create_table "$DB" TABLEG USERSPACE1
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULL.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSFULL=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULL.out)
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
wait
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online incremental to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2.out)
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
wait
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
db2 get db cfg for "$DB"|grep "First active log file"
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online incremental to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2.out)
chmod g+r $BACKUPPATH/"$DB"*.001
wait
;;
DROPDB)
rm -f $BACKUPPATH/"$DB".*.001
rm -f $BACKUPPATH/"$DB".ONLINE*.out
rm -f $BACKUPPATH/"$DB".LOAD*.out
smooth_drop "$DB" $CONTAINERPATH $LOGCONTAINERPATH
;;
WORK)
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK.out)
wait
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
mon_table "$DB"
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online incremental to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2.out)
chmod g+r $BACKUPPATH/"$DB"*.001
wait
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
wait
;;
WORKWITHREORGRUNSTATBIG)
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK.out)
wait
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
db2 connect to "$DB"
db2 -v "runstats on table $QUAL.TABLEN with distribution and sampled detailed indexes all ALLOW WRITE ACCESS"
db2 -v "runstats on table $QUAL.TABLEB with distribution and sampled detailed indexes all ALLOW WRITE ACCESS"
db2 -v "REORG INDEXES ALL FOR TABLE $QUAL.TABLEN "
db2 -v "REORG INDEXES ALL FOR TABLE $QUAL.TABLEB "
db2 terminate
mon_table "$DB"
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online incremental to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2.out)
wait
chmod g+r $BACKUPPATH/"$DB"*.001
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
wait
;;
WORKTIME)
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
db2 +v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORKTIME.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORKTIME=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORKTIME.out)
chmod g+r $BACKUPPATH/"$DB"*.001
wait
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
db2 +o connect to "$DB"
wait
printf "Für den Restore brauchen wir dn UTC - Timestamp (Coordinated Universal Time, formely know as GMT) \n"
db2 -x "select current timestamp - current timezone from sysibm.sysdummy1" | tee $BACKUPPATH/WORKTIMESTAMP.out
db2 +o connect reset
sleep 5
printf "Löschen der Inhalte von TABLEM \n"
db2 +o connect to "$DB"
db2 -v "select max(ind) FROM $QUAL.tablem"
db2 -v "delete from $QUAL.TABLEM"
db2 +o connect reset
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
wait
;;
BACKUPBIG)
db2 -v "backup database $DB tablespace (SYSCATSPACE,NSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE.out
RC=$?
ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE.out)
db2 -v "backup database $DB tablespace (SYSCATSPACE,BSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE.out
RC=$?
ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE.out)
;;
CHECK)
mon_table "$DB"
exit 0
;;
# End of DB Section
# Restore a droped table
WORKDROP)
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORK.out)
wait
insert_into_table "$DB" $QUAL.TABLEM $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEM &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEG &
mon_table "$DB"
db2 -v "backup database $DB tablespace (SYSCATSPACE,SYSTOOLSPACE,USERSPACE1,URSPACE,MYSPACE) online incremental to $BACKUPPATH include logs" | tee $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK.out
RC=$?
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2=$(check_backup $RC $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2.out)
chmod g+r $BACKUPPATH/"$DB"*.001
wait
db2 connect to $DB
db2 drop table $QUAL.TABLEM
db2 connect reset
db2 list history dropped table since `/opt/freeware/bin/date --date "2 minutes ago" "+%Y%m%d%H%M"` for db $DB|/opt/freeware/bin/grep -A2 "Backup ID"|tail -n1| tee $BACKUPPATH/DROPBACKUPID.out
insert_into_table "$DB" $QUAL.TABLEA $count &
insert_into_table "$DB" $QUAL.TABLEC $count &
insert_into_table "$DB" $QUAL.TABLEG $count &
update_table "$DB" $QUAL.TABLEA &
update_table "$DB" $QUAL.TABLEC &
update_table "$DB" $QUAL.TABLEG &
wait
;;
RESTOREDROPEDTABLE)
printf " ---------------------------------------------------------------------- \n"
printf " Create database with just one tablespace to recover droped table \n"
printf " ---------------------------------------------------------------------- \n"
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
DROPBACKUPID=$( awk '{print $4}' $BACKUPPATH/DROPBACKUPID.out )
smooth_drop "$DB" $REPLACEPATH $REPLACELOGPATH
db2 -v "RESTORE DATABASE $DB REBUILD WITH tablespace(SYSCATSPACE,MYSPACE) incremental auto FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK
ON '$REPLACEPATH/tablespace/$DB' DBPATH ON '$REPLACEPATH/metadata/$DB' NEWLOGPATH $REPLACELOGPATH/log/$DB REDIRECT WITHOUT PROMPTING "
RC=$?
check_RC $RC "Redirect Restore incremental pre Work failed Return"
db2 -v "SET STOGROUP PATHS FOR IBMSTOGROUP ON '$REPLACEPATH/tablespace/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_U ON '$REPLACEPATH/TS_U_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_M ON '$REPLACEPATH/TS_M_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_B ON '$REPLACEPATH/TS_B_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_N ON '$REPLACEPATH/TS_N_SPACE/$DB' "
db2 -v "RESTORE DATABASE $DB CONTINUE"
RC=$?
check_RC $RC "Redirect Restore incremental pre Work failed Return"
db2 -v "update db cfg for $DB using logarchmeth1 disk:$REPLACELOGPATH/log/archlog/$DB"
rm -rf "$PWD"/logretain/*
mkdir -p "$PWD"/dropedtable
rm -rf "$PWD"/dropedtable/*
find $LOGCONTAINERPATH/log/archlog/"$DB" -name "S*.LOG" -exec cp '{}' "$PWD"/logretain/ \;
db2 -v "rollforward database $DB to end of logs and complete overflow log path ('$PWD/logretain') RECOVER DROPPED TABLE $DROPBACKUPID to $PWD/dropedtable"
RC=$?
check_RC $RC "Rollforward failed Returns"
db2 connect to "$DB" user db2it99
db2 grant dbadm on database to user db2opt
db2 connect reset
;;
CREATEDROPEDTABLE)
create_tablenon_generated "$DB" TABLEM MYSPACE
;;
EXPORTTABLEA)
db2 +o connect to "$DB"
db2 "EXPORT TO $BACKUPPATH/TABLEA.IXF OF IXF select * from $QUAL.TABLEA"
db2 +o connect reset
;;
FILLTABLEA)
insert_into_table "$DB" $QUAL.TABLEA $count
;;
CHGTABLEANOGENERATEDCOLUMN)
db2 +o connect to "$DB"
db2 "EXPORT TO $BACKUPPATH/TABLEA.IXF OF IXF select * from $QUAL.TABLEA"
db2 drop table $QUAL.TABLEA
create_tablenon_generated "$DB" TABLEA USERSPACE1
db2 -v import FROM $BACKUPPATH/TABLEA.IXF of IXF COMMITCOUNT 2000 INSERT INTO $QUAL.TABLEA
db2 +o connect reset
;;
BACKUPFULLONLINE)
db2 +v "backup database $DB online to $BACKUPPATH compress include logs" | tee $BACKUPPATH/"$DB"LOADTESTFULL.out
RC=$?
LOADTESTFULL=$(check_backup $RC $BACKUPPATH/"$DB"LOADTESTFULL.out)
;;
BACKUPINCONLINE)
db2 +v "backup database $DB online incremental to $BACKUPPATH compress include logs" | tee $BACKUPPATH/"$DB"LOADTESTINC.out
RC=$?
LOADTESTINC=$(check_backup $RC $BACKUPPATH/"$DB"LOADTESTINC.out)
;;
EMPTYTABLEA)
db2 +o connect to "$DB"
db2 LOAD FROM /dev/null of DEL REPLACE INTO $QUAL.TABLEA NONRECOVERABLE
db2 +o connect reset
;;
LOADTABLEANONRECOVERABLE)
db2 +o connect to "$DB"
db2 LOAD FROM $BACKUPPATH/TABLEA.IXF of IXF REPLACE INTO $QUAL.TABLEA NONRECOVERABLE
db2 +o connect reset
;;
LOADTABLEACOPYYES)
db2 +o connect to "$DB"
db2 LOAD FROM $BACKUPPATH/TABLEA.IXF of IXF REPLACE INTO QTEST.TABLEA COPY YES TO $BACKUPPATH
db2 +o connect reset
;;
RECOVERDATABASEFROMFULL)
LOADTESTFULL=$(< $BACKUPPATH/"$DB"LOADTESTFULL.out grep "The timestamp for this backup image is" |awk 'BEGIN{FS=":"}{print $2}')
db2 -v "restore database $DB from $BACKUPPATH taken at $LOADTESTFULL"
RC=$?
check_RC $RC "Restore from Full after LOAD failed\n"
db2 -v "rollforward db TESTS to end of logs and stop"
RC=$?
check_RC $RC "Rollforward failed"
;;
RECOVERDATABASEFROMINC)
LOADTESTINC=$(< $BACKUPPATH/"$DB"LOADTESTINC.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
db2 -v "restore database $DB incremental auto from $BACKUPPATH taken at $LOADTESTINC"
RC=$?
check_RC $RC "Restore from Full after LOAD failed\n"
db2 -v "rollforward db TESTS to end of logs and stop"
RC=$?
check_RC $RC "Rollforward failed"
;;
# End of Load Test
RESTOREINTOEXIST)
printf " ---------------------------------------------------------------------- \n"
printf " Restore just tablespace MYSPACE into existing database \n"
printf " Prerequisits is that you have to run WORKTIME before \n"
printf " ---------------------------------------------------------------------- \n"
ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORKTIME=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORKTIME.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
Time=$(< $BACKUPPATH/WORKTIMESTAMP.out)
db2 -v "RESTORE DATABASE $DB tablespace(MYSPACE) FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMENORMALWTIHLOGSFULLWORKTIME"
RC=$?
check_RC $RC "Restore just MYSPACE tablespace into existing database \n"
db2 -v "rollforward database $DB to $Time and complete tablespace(MYSPACE)"
RC=$?
check_RC $RC "Rollforward failed Returns"
;;
IMPORTTABLEM)
printf " ---------------------------------------------------------------------- \n"
printf " Import droped table TABLEM \n"
printf " ---------------------------------------------------------------------- \n"
db2 connect to $DB
db2 import from "$PWD"/dropedtable/N*/data of DEL insert into $QUAL.TABLEM
db2 connect reset
;;
RESTOREONETABLESPACE)
printf " ---------------------------------------------------------------------- \n"
printf " Create database with just one tablespace and rollforward to timestamp \n"
printf " in another DB2 Instance \n"
printf " ---------------------------------------------------------------------- \n"
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
Time=$(< $BACKUPPATH/WORKTIMESTAMP.out)
smooth_drop "$DB" $REPLACEPATH $REPLACELOGPATH
db2 -v "RESTORE DATABASE $DB REBUILD WITH tablespace(SYSCATSPACE,MYSPACE) incremental auto FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK
ON '$REPLACEPATH/tablespace/$DB' DBPATH ON '$REPLACEPATH/metadata/$DB' NEWLOGPATH $REPLACELOGPATH/log/$DB REDIRECT WITHOUT PROMPTING "
RC=$?
check_RC $RC "Redirect Restore incremental pre Work failed Return"
db2 -v "SET STOGROUP PATHS FOR IBMSTOGROUP ON '$REPLACEPATH/tablespace/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_U ON '$REPLACEPATH/TS_U_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_M ON '$REPLACEPATH/TS_M_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_B ON '$REPLACEPATH/TS_B_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_N ON '$REPLACEPATH/TS_N_SPACE/$DB' "
db2 -v "RESTORE DATABASE $DB CONTINUE"
RC=$?
check_RC $RC "Redirect Restore incremental pre Work failed Return"
db2 -v "update db cfg for $DB using logarchmeth1 disk:$REPLACELOGPATH/log/archlog/$DB"
echo "$Time"
rm -rf "$PWD"/logretain/*
find $LOGCONTAINERPATH/log/archlog/"$DB" -name "S*.LOG" -exec cp '{}' "$PWD"/logretain/ \;
db2 -v "rollforward database $DB to $Time and complete overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
db2 connect to "$DB" user db2it99
db2 grant dbadm on database to user db2opt
db2 connect reset
;;
RESTOREWITHARCHLOGSREDIRECT)
printf " ---------------------------------------------------------------------------- \n"
printf "RESTORE original Database with still existing archival logs in other Instance \n"
printf " ---------------------------------------------------------------------------- \n"
smooth_drop "$DB" $REPLACEPATH $REPLACELOGPATH
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
db2 -v "restore db $DB rebuild with all tablespaces in image incremental auto from $BACKUPPATH taken at $ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK DBPATH ON $REPLACEPATH/metadata/$DB logtarget $PWD/logretain NEWLOGPATH $REPLACELOGPATH/log/$DB REDIRECT WITHOUT PROMPTING"
RC=$?
check_RC $RC "Redirect Restore incremental pre Work failed Return"
db2 -v "SET STOGROUP PATHS FOR IBMSTOGROUP ON '$REPLACEPATH/tablespace/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_U ON '$REPLACEPATH/TS_U_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_M ON '$REPLACEPATH/TS_M_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_B ON '$REPLACEPATH/TS_B_SPACE/$DB' "
db2 -v "SET STOGROUP PATHS FOR TS_N ON '$REPLACEPATH/TS_N_SPACE/$DB' "
db2 -v "RESTORE DATABASE $DB CONTINUE"
RC=$?
check_RC $RC "Redirect Restore incremental pre Work failed Return"
find $LOGCONTAINERPATH/log/archlog/"$DB" -name "S*.LOG" -exec cp '{}' "$PWD"/logretain/ \;
db2 -v "rollforward database $DB to end of logs and stop overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
db2 connect to "$DB"
db2 connect reset
mon_table "$DB"
rm -rf "$PWD"/logretain/*
db2 -v "RESTORE DATABASE $DB tablespace (BSPACE) FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE LOGTARGET $PWD/logretain WITHOUT PROMPTING"
RC=$?
check_RC $RC "Restore failed Returns"
echo "List log Files after restore tablespace:" $(ls -R "$PWD"/logretain)
db2 -v "rollforward database $DB to end of backup and stop tablespace(BSPACE) overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
rm -rf "$PWD"/logretain/*
db2 -v "RESTORE DATABASE $DB tablespace (NSPACE) FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE LOGTARGET $PWD/logretain WITHOUT PROMPTING"
RC=$?
check_RC $RC "Restore failed Returns"
echo "List log Files after restore tablespace:" $(ls -R "$PWD"/logretain)
db2 -v "rollforward database $DB to end of backup and stop tablespace(NSPACE) overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
db2 connect to "$DB" user db2it99
db2 grant dbadm on database to user db2opt
db2 connect reset
;;
RESTOREWITHARCHLOGS)
printf " ---------------------------------------------------------------------- \n"
printf "RESTORE original Database with still existing archival logs \n"
printf " ---------------------------------------------------------------------- \n"
smooth_drop_without_archive_logs "$DB" $CONTAINERPATH $LOGCONTAINERPATH
db2 connect reset
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
db2 -v "restore db $DB rebuild with all tablespaces in image incremental auto from $BACKUPPATH taken at $ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK DBPATH ON $CONTAINERPATH/metadata/$DB LOGTARGET $PWD/logretain"
RC=$?
check_RC $RC "Restore incremental full failed Returns"
db2 -v "rollforward db $DB to end of logs and stop overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward db $DB failed Returns"
db2 -v "RESTORE DATABASE $DB tablespace (BSPACE) FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE LOGTARGET $PWD/logretain WITHOUT PROMPTING"
RC=$?
check_RC $RC "Restore failed Returns"
db2 -v "rollforward database $DB to end of backup and stop tablespace (BSPACE) overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
db2 -v "RESTORE DATABASE $DB tablespace (NSPACE) FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE LOGTARGET $PWD/logretain WITHOUT PROMPTING"
RC=$?
check_RC $RC "Restore failed Returns"
db2 -v "rollforward database $DB to end of backup and stop tablespace (NSPACE) overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
;;
RESTOREWITHOUTARCHLOGS)
printf " ---------------------------------------------------------------------- \n"
printf "RESTORE original Database without Archival logs left \n"
printf " ---------------------------------------------------------------------- \n"
smooth_drop "$DB" $CONTAINERPATH $LOGCONTAINERPATH
ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE=$(< $BACKUPPATH/"$DB"ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE.out grep "The timestamp for this backup image is"|awk 'BEGIN{FS=":"}{print $2}')
db2 -v "restore db $DB rebuild with all tablespaces in image incremental auto from $BACKUPPATH taken at $ONLINEBACKUPTIMENORMALWTIHLOGSINCREMENTAL2WORK DBPATH ON $CONTAINERPATH/metadata/$DB LOGTARGET $PWD/logretain"
RC=$?
check_RC $RC "Recover from inceremental images"
db2 -v "rollforward db $DB to end of logs and stop overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
db2 -v "RESTORE DATABASE $DB tablespace (BSPACE) FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMEBIGWTIHLOGSBSPACE LOGTARGET $PWD/logretain WITHOUT PROMPTING"
RC=$?
check_RC $RC "Restore failed Returns"
db2 -v "rollforward database $DB to end of backup and stop tablespace (BSPACE) overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
db2 -v "RESTORE DATABASE $DB tablespace (NSPACE) FROM $BACKUPPATH TAKEN AT $ONLINEBACKUPTIMEBIGWTIHLOGSNSPACE LOGTARGET $PWD/logretain WITHOUT PROMPTING"
RC=$?
check_RC $RC "Restore failed Returns"
db2 -v "rollforward database $DB to end of backup and stop tablespace (NSPACE) overflow log path ('$PWD/logretain')"
RC=$?
check_RC $RC "Rollforward failed Returns"
;;
*)
echo wrong mode
exit 8
;;
esac
mon_table "$DB"
mon_tablespace "$DB"
mon_container "$DB"
db2 terminate