1 / 41

Backup & Recovery

Backup & Recovery. Agenda. Backup Types Backup Process Model Backup Performance Tunning Restore Types Restore Process Model Restore Performance Tunning RollForward Performance Tunning. Backup. Backup Types Online or Offline Database Level backup or Tablespace Level backup

Download Presentation

Backup & Recovery

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. Backup & Recovery

  2. Agenda • Backup Types • Backup Process Model • Backup Performance Tunning • Restore Types • Restore Process Model • Restore Performance Tunning • RollForward Performance Tunning

  3. Backup Backup Types • Online or Offline • Database Level backup or Tablespace Level backup • Full, Incremental or Delta

  4. Offline Backup • Database needs to be offline • Internally establishes connection to the specified database • Faster than online • Default option

  5. Online Backup • Database is accessible • Needs archival logging enabled • Slower than offline backup • Log files are included by default • Incremental and delta

  6. Incremental Backup • Copy of all data that has been updated since the last full successful backup • Cumulative backup • Need to maintain only the last incremental backup and last full backup • Database parameter TRACKMOD should be enabled • Incremental backup is not permitted until a full backup is taken to set a base from which it can recover

  7. Incremental Delta Backup • Copy of all data that has been updated since the last any successful backup (full, incremental or delta) • Non cumulative • Need to maintain all backup images since the last full backup

  8. Incremental Backup • Granularity of tracking is at Tablespace level • Entire Large Objects are backed up if any other object in that Tablespace is modified • Normal data is backed up only if it has changed • In addition to the changed data incremental backup also includes the database's metadata • Not enabled by default due to minimal performance impact while tracking changes

  9. Backup Process Model • db2bm Buffer Manipulator Reads data from the disk and writes into shared memory buffer • db2med Media Controller Reads from shared memory buffer and writes the pages out to the target devices • db2agent Controls the flow between db2bm and db2med

  10. Backup Process Model db2bm • Controlled by PARALLELISM option • Reads data from disk into backup buffer • Sends full buffer message on the full queue for db2med

  11. Backup Process Model db2bm • Function stack trace while waiting for the next available buffer sqluReadAdrFromQueue sqlubGetNextBuffer • Function stack trace while reading pread64 sqloReadBlocks • Function stack trace after completing reading data sqluReadMessageFromQueue sqlubbuf

  12. Backup Process Model db2med • Controlled by number of output destinations or Open Sessions clause when using TSM • Reads data from full buffers and writes to the media • Sends empty buffer message on the empty queue for db2bm

  13. Backup Process Model db2med • Function stack trace while waiting for full buffer notification sqluReadAdrFromQueue sqluMCContinueBackup • Function stack trace while writing write sqlowrite

  14. Backup Process Model db2agent • Responsible for setting up the message queues and spawn db2med and db2bm • Once backup starts db2agent process will wait for the backup complete notification • Function stack while waiting for notification sqluReadMessageFromQueue SqluxReadAgentQueue sqlubcka

  15. Backup Process Model Message queues. • Communication between db2agent, db2bm and db2med is managed by message queues • Following message queues created 1 message queue for the db2agent 1 message queue for full buffers 1 message queue for empty buffers 1 message queue for each db2bm edu 1 message queue for each db2med edu

  16. Backup Process Model db2med with TSM • db2vend process used • db2med invokes the TSM code inside db2vend

  17. Backup Process Model db2vend • Independent process to execute the vendor API code • Similar to db2fmp • Runs outside DB2 address space Prevents unexpected API errors from crashing DB2

  18. Backup Performance Tuning Backup command options • WITH num-buffers BUFFERS • PARALLELISM n • BUFFER buffer-size

  19. Backup Performance Tuning Backup command options • WITH num-buffers BUFFERS Use at least twice as many buffers as backup sessions which will not cause the target devices to wait for data • PARALLELISM n Number of processes that are started to read data from the database Set n equal to the number of tablespaces being backed up • BUFFER buffer-size Buffer-size is a multiple of the table space extent size plus one page

  20. Backup Performance Tuning • If no options specified optimal values for number of buffers buffer size and parallelism are selected automatically • db2diag.log entry shows the values that are selected FUNCTION: DB2 UDB, database utilities, sqluxGetDegreeParallelism, probe:537 DATA #1 : <preformatted> Autonomic BAR - using parallelism = 10. FUNCTION: DB2 UDB, database utilities, sqlubTuneBuffers, probe:898 DATA #1 : <preformatted> Autonomic backup - tuning enabled. Using buffer size = 4481, number = 10.

  21. Backup Performance Tuning UTIL_HEAP_SZ • Uitlity Heap Size • Maximum memory that can be used simultaenously by Backup, Restore and LOAD • Used for backup buffers • More than buffer-size * number of buffers

  22. Backup Performance Tuning Fragmentation • db2bm reads contiguous blocks of data • Fragmentation due free extents can affect performance • REORG should be done to reduce fragmentation

  23. Backup Performance Tuning Prefetching • I/O performed by db2bm if tablespace has only one container • I/O performed by Prefetchers (db2pfchr) for tablespace with multiple containers • Parallel I/O improves performance

  24. Backup Performance Tuning Contention during online backup • db2bm must guarantee that the block of data being backed up does not change during the read operation • db2bm will wait on other shared users to complete their I/O operations to get exclusive access db2n

  25. Backup Process Model

  26. Restore Restore Types • Full database restore • Tablespace restore • Redirect restore • Incremental Restore

  27. Redirect Restore • Used to restore database on a different machine • Restores tablespaces to a different location • Add, Change or remove containers of a DMS tablespace • Modify containers of a SMS tablespace

  28. Redirect Restore • Issue RESTORE command with REDIRECT option db2 restore db sample redirect • Use SET TABLESPACE CONTAINERS command for each tablespace to specify the new container location db2 set tablespace containers for 2 using (path '<path>') • Issue RESTORE command with CONTINUE option db2 restore db sample continue

  29. Redirect Restore Automatic Storage • Use ON option to set the path for automatic storage tablespaces db2 restore db sample on <path> redirect • If no normal tablespaces then CONTINUE command db2 restore db sample continue

  30. Incremental Restore • Identify and restore the last incremental backup image This restore only restores the metadata and not the actual data • Identify and restore the most recent full backup image • Restore the last incremental backup image again This time the actual data will be restored

  31. Incremental Restore • Use db2ckrst utility to get the correct restore sequence db2ckrst -d <dbname> -t <timestamp> • Timestamp should be that of the last incremental backup you want to restore • Reads the database history file to give the correct restore sequence

  32. Redirect Incremental Restore • Restore Incremental backup image first db2 restore db sample incremental taken at <ts2> redirect • SET TABLESPACE CONTAINERS command db2 set tablespace containers for 5 using (file '<path>') • CONTINUE command db2 restore db sample continue • Restore the full backup and incremental after this db2 restore db mydb incremental taken at <ts1> db2 restore db mydb incremental taken at <ts2>

  33. Restore Process Model • db2bm Buffer Manipulator Reads data from buffer and writes to the disk • db2med Media Controller reads from the backup image and writes to the buffer • db2agent Controls the flow between db2bm and db2med

  34. Restore Performance Tunning • Increase the restore buffer size Multiple of the backup buffer size specified during the backup operation • Increase the number of buffers • Increase the value of the PARALLELISM parameter More number of db2bm will be used for write operation • Increase the utility heap size • Tablespaces with multiple containers will benefit from parallel I/O Multiple page cleaners (db2pclnr) will write data parallely

  35. Restore Performance Tunning • Increase the restore buffer size Multiple of the backup buffer size specified during the backup operation • Increase the number of buffers • Value of the PARALLELISM parameter equal to number of tablespaces One db2bm for each tablespace will be used for write operation • Increase the utility heap size • Tablespaces with multiple containers will benefit from parallel I/O Multiple page cleaners (db2pclnr) will write data parallely

  36. Restore Performance Tuning • If no options specified optimal values for number of buffers buffer size and parallelism are selected automatically • db2diag.log entry shows the values FUNCTION: DB2 UDB, database utilities, sqluxGetDegreeParallelism, probe:537 DATA #1 : <preformatted> Autonomic BAR - using parallelism = 14. FUNCTION: DB2 UDB, database utilities, sqludTuneBuffers, probe:898 DATA #1 : <preformatted> Autonomic restore - tuning enabled. Using buffer size = 3841, number = 14.

  37. Restore Process Model

  38. RF Performance Tunning • PREC_NUM_AGENTS Recovery agents (db2redow) PREC_NUM_AGENTS=number of online CPUs+1 • PREC_NUM_QSETSIZE Message Queue size Increase it if you reduce the PREC_NUM_QSETS • PREC_NUM_QSETS Number of message queues Decrease the size if PREC_NUM_QSETS is a high value

  39. RF Performance Tunning • Setting the parameters Create DB2BPVARS.cfg file with the following details PREC_NUM_AGENTS=<value> PREC_NUM_QSETS=<value> PREC_NUM_QSETSIZE=<value> db2set DB2BPVARS=<path to file/DB2BPVARS.cfg> Recycle the instance

  40. RF Performance Tunning • db2diag.log entry FUNCTION: DB2 UDB, recovery manager, sqlpAllocatePRCB, probe:2000 DATA #1 : <preformatted> Using parallel recovery with 9 agents 16 QSets 48 queues and 0 chunks PREC_NUM_AGENTS=9 PREC_NUM_QSETS=16 PREC_NUM_QSETSIZE=4

  41. Thank You

More Related