2.5 - db2 backup and recovery.odp

7/23/2019 2.5 - DB2 Backup and Recovery.odp

http://slidepdf.com/reader/full/25-db2-backup-and-recoveryodp 1/21

© 2010 IBM Corporation

Information Management

Information Management Ecosystem PartnershipsIBM Canada Lab

Summer/Fall 2010

DB2®

Backup and Recovery



2 © 2010 IBM Corporation

Inormation Mana!ement

"#y Backup my Data$

■ Backing up data is vital for businesses

% &o't inormation can cau'e a ma(or cri'i' or)or'e* lead to +u'ine'' ailure,

■ Common problems:

% Sy'tem outa!e

- .o)er ailure

- ard)are ailure

% ran'action ailure- 'er' may inadvertently corrupt t#e data+a'e

% Media ailure

- Di'k drive +ecome' unu'a+le

% Di'a'ter

- Data+a'e acility dama!ed +y ire* loodin!or ot#er cata'trop#e

DB2 backup and recovery methods are designed tohelp you keeping your information safe!





Ba'ic Concept o Data+a'e Backup and Recovery

■ At t a database backup operation is performed

■ At t2 a problem that damages the database occurs

■ At t" all committed data is recovered

lo!'Data+a'e

at

t1

Data+a'e

at

t1

data+a'e

Backup

Ima!e

.erorm adata+a'e+ackup

t1

Data+a'e continue' toproce'' tran'action',ran'action' arerecorded in lo! ile'

Di'a'ter 'trike'* Data+a'ei' dama!ed

t2

.erorm a data+a'e re'toreu'in! t#e +ackup ima!e, #ere'tored data+a'e i'identical to t#e data+a'e att1

t

3ter re'tore* reapply t#etran'action' committed+et)een t1 and t2 u'in! t#elo! ile',





DB2 ran'action &o!'

■ #eep track of changes made to database ob$ects and their data

■ During the recovery process DB2 e%amines these logs and decides &hichchanges to redo or undo

■ Can be stored in files or on ra& devices

■ 'he transactions in the logbuffer are recorded in thelog device upon one of thefollo&ing events:

% &o! +uer i' ull

% 5um+er o commit'

reac# MI(C)MMI' value % 6ne 'econd #a' lap'ed

.acka!e cac#e

Buerpool

Di'k or t#edata+a'e

.a!e inde7e'

Inormation to +eupdated i' retrieved rom

di'k 8i needed9

pdatetran'action

&o! Buer

pdatetran'action

6ldtran'action'

Di'k orlo!'

DB2 Memory 8in R3M9



: © 2010 IBM Corporation


&o! File State'

■ Active logs

% ran'action' t#at #ave not +een committed or rolled +ack

■ )nline archive logs % Committed and e7ternali;ed lo!' in t#e active lo! directory

■ )ffline archive logs % Committed and e7ternali;ed lo!' in a 'eparate repo'itory

.acka!e cac#e

Buerpool

Di'k ort#e

data+a'e

.a!einde7e'Inormation to +e

updated i' retrievedrom di'k 8i needed9

pdatetran'action

&o! Buer

pdatetran'action

6ldtran'action'

3ctive&o!

Directory

DB2 Memory 8in R3M9

6FF&I5< 3rc#ive &o!'

DB2 Memory 8in R3M9



= © 2010 IBM Corporation


Circular &o!!in!

■ *rimary log files used to record all transactions+ reused &hen transactions are

committed■ ,econdary log files allocated &hen ne%t primary log file is not available due to active

transactions

■ If both primary and secondary log limit are full and can not be reused a log fullcondition occurs and ,-./01C error message is returned

■ )nly full offline backups of the database are allo&ed■ Cannot have roll3for&ard recovery

.rimarylo!'

ran'action' DB2 ,erver

1

Secondary lo!'

1

2

4

n

n

3ctive lo! pat#

3ctive lo!

ile


http://slidepdf.com/reader/full/25-db2-backup-and-recoveryodp 7/21> © 2010 IBM Corporation


3rc#ival &o!!in!

■ 4nable &ith .)5A6C7M4'7 database configuration parameter

■ 7istory of log files is maintained in order to allo& roll for&ard recoveryand online backup

■ .ogs can be optionally archived to an archive location &hen no longeractive to avoid e%haustion of log directory

3rc#ive &o! Directory 3ctive &o! Directory

AC'I84 % Contain' inormationor non?committed tran'action',

"#en all preallocated lo! ile' areilled*more lo! ile' are allocated

and u'ed,Filled lo! ile' may +e moved to a

dierent 'tora!e location

)(.I(4 A6C7I84Contain' inormation orcommitted tran'action',

Stored in t#e 3CI@<lo! 'u+directory,


http://slidepdf.com/reader/full/25-db2-backup-and-recoveryodp 8/21A © 2010 IBM Corporation


Ininite &o!!in!

■ Infinite logging provides infinite active log space

%<na+led +y 'ettin! .)5,4C)(D to 3

■ ,econdary log files are allocated until the unit of &ork commits orstorage is e%hausted

■ Archived logs can hinder performance for rollback and crash

recovery■ Database must be configured to use archival logging

■ 9p to 21 log files ;primary < secondary=

■ Control parameters

%(9M>.)5>,*A( % num+er o lo! ile' an active tran'action can'pan

%MA?>.)5 % .ercenta!e o active primary lo! ile 'pace t#at a'in!le tran'action could con'ume


http://slidepdf.com/reader/full/25-db2-backup-and-recoveryodp 9/21 © 2010 IBM Corporation


Data+a'e Backup

■ Copy of a database or table space

%'er data %DB2 catalo!'

%3ll control ile'* e,!, +uer pool ile'*ta+le 'pace ile* data+a'e coni!urationile

■ Backup modes: %)ffline Backup

- Doe' not allo) ot#er application' or proce''e' to acce''t#e data+a'e

- 6nly option )#en u'in! circular lo!!in!

%)nline Backup - 3llo)' ot#er application' or proce''e' to acce'' t#e

data+a'e

- 3vaila+le to u'er' durin! +ackup

- Can +ackup to di'k* tape* SM and ot#er 'tora!e vendor'


http://slidepdf.com/reader/full/25-db2-backup-and-recoveryodp 10/2110 © 2010 IBM Corporation


Data+a'e Backup % Synta7

db2 backup database <db_name> <online> to <dest_path>

)nline backup e%ample

db2 backup database mydb online to /home/db2inst1/backups

)ffline backup e%ample

db2 backup database mydb to /home/db2inst1/backups





Data+a'e Backup % File 5amin! Convention

SAMPLE..!"2#$S%.$&!E.'A%$.21(1)1(12*+.1

3lia' In'tance Catalo! 5ode Minuteear

ype 5ode Mont#

Day

our Second

Seuence

Backup ypeE0 Full Backup a+le'pace Backup





a+le 'pace Backup

■ 4nables user to backup a subset of database

■ Multiple table spaces can be specified

■ 'able space backup can run in both online and offline backup

■ 'able space can be restored from either a database backup or tablespace backup of the given table space

■ 9se the key&ord 'AB.4,*AC4 to specify table spaces

db2 backup database mydb1 %A"LESPA'E ,%"SP1- &$L#$E to

/home/db2inst1/backup





Incremental Backup'

■ Incremental ;a@k@a@ cumulative= 3 Backup of all database data that has changed since themost recent successful full backup operation

■

Incremental Delta 3 Backup of all database data that has changed since the lastsuccessful backup ;full incremental or delta= operation@

■ (eed to have '6AC#M)D database configuration parameter )(

■ ,upports both database and table space backups@

■ ,uitable for large databases considerable savings by only backing up incrementalchanges@

DeltaBackups

Full

Full

Full

Full

CumulativeBackups

,unday ,undayMon 'ue ed 'hu ri ,at





Data+a'e Backup % Compre''ion

■ DB2 backups can no& be automatically compressed

% Si!niicantly reduce +ackup 'tora!e co't'

■ *erformance characteristics

% C. co't' typically increa'ed 8due to compre''ion computation9

% Media I/6 time typically decrea'ed 8due to decrea'ed ima!e 'i;e9

% 6verall +ackup/re'tore perormance can increa'e or decrea'eG dependin!on )#et#er C. or media I/6 i' a +ottleneck

<7ampleE

db2 backup database !S2 to /home/db2inst1/backups compess



1: © 2010 IBM Corporation


3utomatic Data+a'e Backup

■ ,implifies database backup management tasks for the DBA

by al&ays ensuring that a recent full backup of the databaseis performed as needed

■ 'o configure automatic backup

%5raphical user interface tools

- Coni!ure 3utomatic Maintenance)i;ard

%Command line interface

- auto>db>backup

- auto>maint

%,tored procedure

- A9')MAI('>,4'>*).IC 'y'tem 'tored procedure



1= © 2010 IBM Corporation


6ptimi;in! Backup .erormance

■ DB2 automatically configures these parameters for performance

% *arallelism- 5um+er o ta+le 'pace' +acked up in parallel

% num>buffers- 5um+er o +uer' u'ed- 'e at lea't t&ice as many buffers a' +ackup tar!et' 8or

'e''ion'9 to en'ure t#at t#e +ackup tar!et device' do not #ave to

)ait or data, % Buffer

- Backup +uer 'i;e

■ Allocate more memory to backup utility by increasing utility heap sie;9'I.>74A*>,E= configuration parameter@

■ Backup subset of data &here possible: % a+le 'pace +ackup' % Incremental +ackup'

■ 9se multiple target devices



1> © 2010 IBM Corporation


Data+a'e Recovery

■ 6ecovery is the rebuilding of a database or

table space after a problem such as mediaor storage failure po&er interruption or application failure@

'ypes of 6ecovery

%Crash or restart recovery

- .rotect' t#e data+a'e rom +ein! let incon'i'tent 8po)erailure9

%6oll for&ard recovery

- <7tend' recovery +y u'in! ull data+a'e and ta+le 'pace

+ackup in con(unction )it# t#e data+a'e lo! ile'



1A © 2010 IBM Corporation


DB2 Re'tore tility

■ 6estore utility is the complement of backup utility

■ 6estores database or table space from a previously takenbackup

■ 'A#4( A' 3 ,pecify the time stamp of the database backupimage@ Backup image timestamp is displayed after

successful completion of a backup■ ithout prompting F )verrides any &arnings@

Eample0

SAMPLE..!"2#$S%.$&!E.'A%$.211(121.1

3ES%&3E !A%A"ASE dbalias 43&M <db_path> %A5E$ A% 2//G/HG"2/





a+le 'pace Re'tore 6peration

■ 6estored table space is in 6oll or&ard *ending state and can be either

rolled for&ard to 4nd of .ogs or a *oint In 'ime@ % In ca'e o .oint in ime roll or)ard* ta+le 'pace mu't +e rolled or)ard to

at lea't t#e minimum *oint in 'ime

■ Minimum recovery time can be checked using % db2 list tablespaces sho& detail

■ 9ser table space must be in line &ith catalog table space % e,! i catalo! indicate' ta+le 1 e7i't' in ta+le 'pace S.1* ta+le 1 mu't

e7i't in t#e S.1 ta+le 'pace* ot#er)i'e data+a'e +ecome' incon'i'tent

■ 4very time there is a DD. changed minimum recovery time for the tablespace is revised to indicate the last DD. change@

■ 6ecommended to take a table space backup after a table space has beenrestore to a point in time@

■ 'ransactions that came after the point in time are lost therefore take atable space backup as ne& point of reference for future recoveries@





Incremental Re'tore

■ 6estore a database &ith incremental backup images

■ A9')MA'IC ;recomended= 3 All reuired backup images &ill be appliedautomatically by restore utility

■ MA(9A. F 9ser applies the reuired backups manually

% db2ckrst can provide t#e 'euence or applyin! +ackup'

■ AB)6' 3 aborts an in3progress manual cumulative restore

■ 64,')64 DA'ABA,4 sample I(C64M4('A. A9')MA'IC 6)M Jdb2backupJdir+

■ 6)..)6A6D DA'ABA,4 sample ') 4(D ) .)5, A(D C)M*.4'4+




Information Management

Information Management Ecosystem PartnershipsIBM Canada Lab

Summer/Fall 2010Questions?

E-mail: [email protected]: “DB2 Academic Wo!sho"#

2.5 - db2 backup and recovery.odp

Documents