lesson9 backup & recovery

®

IBM Software Group

© 2005 IBM Corporation

Lesson 9: Database logging and Backup and Recovery

(DB2 on Campus Lecture Series)

Raul F. ChongIBM Toronto Laboratory

IBM Software Group | DB2 Information Management Software

2

Agenda

Database logging

Backup

Recovery


3

Agenda

Database logging

Backup

Recovery


4

Logs record actions of transactions. If there is a crash, Logs are used to playback/redo committed transactions during recovery.

Logging is always on for regular tables in DB2 UDBƒPossible to mark some tables or columns as NOT LOGGEDƒPossible to declare and use USER temporary tables

Database Logging


5

Upon commit, DB2 guarantees data has been written to logs only

Bufferpool

T1 T2

tablespace

chngpgs_threshsoftmax

dirty page steal

commit logs

Database logging


6

Types of logs:Primary logs are PREALLOCATEDSecondary logs are ALLOCATED as needed (costly)

For day to day operations, ensure that you stay within you primary log allocation

Types of logs:Active logs:

Information has not been externalized (Not on the tablespace disk)Archive logs

All information externalized

Types of logs

P1 P2 P3 S1 S2


7

Circular loggingFor non-production systemsLogs that become archived, can be overwrittenWhat if information externalized to the tablespace was

wrong? (Human error). No logs to redo things!

Archival loggingFor Production systemsNo logs are deleted. Some are stored online (with active

logs), other offline in an external media

Types of logging


8

Logs can be reused when its contents have been externalized, and there is no need for them for crash recovery

P1 P2 P3 S1 S2

Circular logging


9

ONLINE ARCHIVE -Contains informationfor committed andexternalized transactions.Stored in the ACTIVElog subdirectory.

OFFLINE ARCHIVE -Archive moved fromACTIVE log subdirectory.(May also be on othermedia)

12

13

14

15

16

Manual orUserexit

ACTIVE - Containsinformation fornon-committed or non-externalizedtransactions

Enabled with LOGRETAIN parameter. Log files not deleted - kept online or off-line. USEREXIT routine needed to move archived log files to secondary storage (e.g. tape). - samples provided. Roll forward recovery and on-line backup possible.

Archival logging / Log Retain


10

Launching the Configuring Database Logging Wizard

Control Center > (expand) All Databases folder > (right-click) Database > Configure Database Logging


11

Database Logging Wizard


12

The CORE parametersƒlogbufsz

–the amount of memory to use as a buffer for log records before writing these records to disk

ƒlogfilsz–the size of each configured log, in number of 4-KB pages

ƒlogprimary–the number of primary logs of size logfilsz that will be created

ƒlogsecond–the number of secondary log files that are created and used for recovery, if needed.

ƒlogpath/newlogpath– the location in which active logs and future archived logs are placed

ƒmirrorlogpath–To protect the logs on the primary log path from disk failure or accidental deletion, you can specify that an identical set of logs be maintained on a secondary (mirror) log path

ƒloghead–the name of the log file that is currently active

DB2 logging parameters


13

Core Logging Parametersƒuserexit

–enable userexit program to copy logs offlineƒsoftmax

–limits cost of crash recoveryƒlogretain

–enables Archive Logging mode

Logging Parameters (cont’d...)


14

Additional logging parametersƒoverflowlogpath

–Similar to the OVERFLOW LOG PATH option of the ROLLFORWARD command; however, instead of specifying the OVERFLOW LOG PATH option for every ROLLFORWARD command issued, you can set this configuration parameter once.

ƒblk_log_dsk_ful–set to prevent disk full errors from being generated when DB2 cannot create a new log file in the active log path. Instead, DB2 will attempt to create the log file every five minutes until it succeeds. Unblocked, read-only SQL may continue.

ƒmax_log–Percent of max active log space by transaction

ƒnum_log_span –Num. of active log files for 1 active UOW

Logging Parameters (cont’d...)


15

Agenda

Database logging

Backup

Recovery


16

Point-in-time image of a database

Backup example (try it now!):

BACKUP DB sample TO C:\BACKUPS

BACKUP DATABASE <dbname>[ TO <path> ]

Database Backup


17

Linux/UNIX/Windows

DBALIAS.0.DB2INST.NODE0000.CATN0000.20060314131259.001

Alias Instance Year Day Minute

Sequence

Type Node Month Hour SecondCatalog Node

Example of Backup File Names


18

QuickLab #10 - Scheduling a Backup

In this Quicklab, you will create a Backup schedule for the Internet bookstore database

You will use the Backup Wizard to help configure the different backup parameters

Refer to the QuickLab #10 instructions


19

Agenda

Database logging

Backup

Recovery


20

A database restore will recreate the database from a backupƒThe database will exist as it did at the time the backup completed

If archival logging were used before the database crash, it would then be possible to roll forward through the log files to reapply any changes since the backup was taken.ƒIt is possible to roll forward either to the end of the logs or to a specific

point in time.

The granularity available on the last transaction needs to be weighed against database performance.

Database Recovery


21

Crash/Restart Recovery

Uses the RESTART DATABASE command or the automatic restart enable configuration parameter (autorestart) to protect a database from being left in an inconsistent, or unusable, state.

Version/Image Recovery

BACKUP & RESTORE commands to put the database in a state that was previously saved (data since last backup lost).

Rollforward Recovery

BACKUP & RESTORE and ROLLFORWARD commands to recover a database to a specified point in time (minimal data loss).

Recovery Types


22

Restore example:drop db sampleRESTORE DB sample FROM <path>TAKEN AT 20060314131259

restore database <dbname>[from <path>] [taken at <timestamp>]

Database Restore

SAMPLE.0.DB2INST.NODE0000.CATN0000.20060314131259.001

Alias Instance Year Day Minute

Sequence

Type Node Month Hour SecondCatalog Node


23

Restore over existing database

Clone database from a backup image (redirected restore)ƒhttp://www7b.boulder.ibm.com/dmdd/library/techarticle/0211melnyk/0211melnyk.html

Backup by tablespace

Restore only tablespace from full backup image

Delta and Incremental Backups

Backup from flash copy

Recovery of dropped tables

Also possible with BACKUP and RESTORE

lesson9 backup & recovery

Documents

recovery db2

db2 udbpossible

active logs

preallocatedsecondary

log records

configured log

archived log files

fromactive log subdirectory