Informix Backup/Recovery 2000

Informix Backup/Recovery 2000 John F. Miller III Erik van Veen

Outline • History • Architecture • Clients • ontape • onbar • onbar system tables • Data Transfer • Engine Threads • Internal Data Formats • Improvements • Future

History • 1.X Turbo - Only Quiescent mode archives • 4.X named OnLine for advanced archiving technology • 5.X same core technology • limitation revealed (scalability & extensibility) • 6.0 new client/server model developed • 7.1 & 7.20 same core technology • 7.21 new client (onbar) • 7.3/9.2 server API re-write • 9.21 removal of timestamp updating

Pre-DSA Archive CreationBad Grammar Archiver • Archive Checkpoint • Acquire archive timestamp • Free extents recorded • Reserved pages saved • Chunks backed-up by ascending chunk number • Before image of pages modified during archive are placed in physical log • tbtape scans physical log for unarchived before-images • Pages placed directly to tape based on their: • Page header • Timestamp counter Note: Excellent detailed description in 5.0 Admin guide

Understanding Archive Timestamps All Pages in the green region are sent directly to tape 0 Min-Stamp Archive Stamp Timestamp 50% away from Archive Stamp The timestamp at the start of the archive Not Archived All Pages in the red region have their timestamp updated before being archived Current Stamp The timestamp at the current point in time

Problems seen in Pre-DSA Architecture • No Parallelism • Data Streams • Division of labor (tape and disk I/O) • Changing a tape can hang the system • All or nothing restores • Not an Enterprise solution • No jukebox support • No integration with storage vendors

DSA Archive ArchitectureMajor Differences • True client-server architecture • Archived pages logically grouped by dbspaces • Granularity of creations • Granularity of restores • Warm restores • Physical log pages kept in temp tables

Architecture Overview CLIENT SERVER Network Connection ONINIT Archive Client Shared Memory

Architecture Overview Archive Client SMV Onbar Common Archive Code XBSA XBSA Ontape

ontape Dynamic Server sysmaster Architecture Overview -- OnTape Basic I/O ESQL/C

onbar Storage Manager Dynamic Server sysutils sysmaster Architecture Overview -- OnBar XBSA ixbar oncfg._ sqlhosts onconfig ESQL/C

OnBar System Tables • All tables reside in the sysutils database • Four main tables • bar_server • bar_object • bar_action • bar_instance

OnBar System Tables bar_server srv_name obj_ srv_name bar_object bar_instance obj_oid obj_oid ins_oid ins_aid, ins_oid act_oid bar_action act_aid, act_oid

bar_server • Contains a list of database servers • This table is populated based on sqlhosts file

bar_object • Describes each backup object • dbspaces • logical logs • A backup must be attempted on an object for this table to have any values

bar_action • List all backup and restore actions ATTEMPTED • Excluding cold restores

bar_instance • Track all successful backups • Description of each backed up object used to develop a restore strategy

ixbar file format jmiller_12 rootdbs R 0 77 0 25226 0 2000-10-12 14:50:46 1 0 0 77 0 - - jmiller_12 plog CD 0 78 0 25227 0 2000-10-12 14:51:44 1 0 0 78 0 - - jmiller_12 dbspace1 ND 0 79 0 25228 0 2000-10-12 14:51:44 1 0 0 79 0 - - jmiller_12 dbspace2 ND 0 80 0 25229 0 2000-10-12 14:51:45 1 0 0 80 0 - - jmiller_12 50 L 0 81 0 25230 0 2000-10-12 14:52:09 1 0 0 81 0 - -

BAR_ACT_LOG BAR_MAX_BACKUP BAR_NB_XPORT_COUNT BAR_XFER_BUF_SIZE BAR_RETRY BAR_BSALIB_PATH BAR_PROGRESS_FREQ BAR_HISTORY BAR_DEBUG_LOG BAR_DEBUG Archive Client - onbar Configuration Parameters

Moving Data between Client/Server ASF Network Connection ONINIT Shared Memory Archive Client

Configuring Data Transfer Buffers • OnBar • BAR_NB_XPORT_COUNT • BAR_XFER_BUF_SIZE • Limitations • Changing the buffer size will render all previous object unrestorable • Maximum size is one online page smaller than 64KB • Maximum number of buffers is 99 • OnTape • TAPEBLK • ARCHIVE_BUF_COUNT • Limitations • Maximum number of buffers is 99

Monitoring Data Transfer Buffers onstat -g stq • Full Queue - Buffers/work server • Empty Queue - Buffers being filled by the archive client Stream Queue: (session 15 cnt 10) 0:b0e0400 1:b0f0400 2:b100400 3:b110400 4:b120400 5:b130400 6:b140400 7:b150400 8:b160400 9:b170400 Full Queue: (cnt 0 waiters 1) Empty Queue: (cnt 9 waiters 0) 0:0 1:b0f0400 2:b100400 3:b110400 4:b120400 5:b130400 6:b140400 7:b150400 8:b160400 Stream Queue: (session 13 cnt 10) 0:af6f400 1:af7f400 2:af8f400 3:affe400 4:b00e400 5:b01e400 6:b02e400 7:b03e400 8:b04e400 9:b05e400 Full Queue: (cnt 0 waiters 1) Empty Queue: (cnt 9 waiters 0) 0:0 1:af7f400 2:af8f400 3:affe400 4:b00e40 5:b01e400 6:b02e400 7:b03e400 8:b04e400

Tuning the Transfer Buffers • Memory required by Transfer Buffers • parallel session * buffer size * number of buffers • Oscillating performance is an indication that more memory buffers can improve performance

Major Archive Change • 7.30 & 9.20 the server changed • 9.21 & 7.30.UC7 & 7.31.UC the updating of timestamps was removed

Threads Involved in an Archive • Each archive session will have its own set of threads • Three threads for each archive • ontape • arcbackup1 • arcbackup2 onstat -g ath 76 ae83928 ac0cdc8 2 cond wait sm_read 1cpu ontape 77 ae987d0 ac0f758 2 sleeping forever 1cpu arcbackup1 78 ae98908 ac0d3b8 3 sleeping secs: 1 1cpu arcbackup2

Archive Threadsontape • The name of this thread is always ontape regardless of the archive client used • General coordinator of the backup session • Responsible for starting the two arcbackup threads • Passes errors to the client

Archive Threads arcbackup1 • This thread is called archive scanner • TheDUMB thread • Given a list of pages it sends them to the archive client, concentrating exclusively on I/O • Checks the format of the pages

Archive Threads arcbackup2 • This thread is called “Before Image Processor” • The thinker • Responsible for collecting all the images that are modified during the archive • Manager of the temp tables the archiver creates • Able to create multiple temp tables for a single dbspace

Threads Involved in a Restore • Each restore session will have its own set of threads • Two threads for each restore • ontape • ontape is the name of this thread regardless of the archive client • physrecover • writes archive pages back to disk onstat -g ath 24 aed8558 ac0bbf8 2 sleeping secs: 1 1cpu ontape 25 adc74e0 ac0d3b8 2 sleeping forever 1cpu physrecover

Data Stream Format Archive Trailer Archive Header Object 1 dbspace_X Object 2 dbspace_Y Object 3 dbspace_Z Data Data Data Tape Control Pages

Format of a Tape Object Dbspace Header Dbspace Trailer Dbspace Data Before image data for this dbspace Tape Control Pages

Blobpages • Archive examines blob free map pages to determine which blob pages to archive • Uses the the blob page header to determine the size • DSA does not lock the blobspace during the archive, but delete blobs are not freed until after the archive completes

SmartBlobs • First the extent meta data is backed up • From this meta data an extent list is created • The extent lists indicates which smart blob pages are sent to tape

onsmsync • Deletes backup history from the sysutils database and emergency boot file • Based on time • Interval • Generations • Regenerate a corrupted or lost ixbar file for sysutils • Regenerates a damaged sysutils from the ixbar file If you lose both the sysutils database and the emergency boot file, onsmsync cannot regenerate them from the storage manager.

Managing Temp Space • Versions 7.2 • A single dbspace has exactly one temp table • If a temp table fills archive aborts • Temp table is kept until the very last dbspace being archive is complete • Version 7.30 & 9.20 and higher • A single archive may have multiple temp tables in different dbspaces • If a temp table fills, the before image processor create a new table in a different dbspace • Upon the completion of a dbspace’s archive the associated temp table is dropped immediately

Validating Archives • How do I validate archives • What is actually validated • What other information is there for me • What else can go wrong with my validated restore

How do I validate my archives • Standalone - archecker connect directly to media • ontape • archecker -tdvs • AC_TAPEBLK, AC_TAPEDEV • Integrated - built into the product • onbar -r -v (version 7.3X) • onbar -v (9.20 & 8.30) • onbar -b -v (8.30)

What is actually validated • Format of each page on the archive is checked (similar to oncheck -cd) • Tape control pages are sanity checked • Each table is checked ensuring all pages of the table exist on the archive tape • Reserved page format is validated • Each chunk free list is verified • Table extents are checked for overlap (oncheck -pe)

Other Information for Me • AC_MSGPATH - message and debug log for archecker • {AC_STORAGE}/INFO • extent list for each dbspace • DBS.{dbspace #} • similar to oncheck -pe • time to process each tape or object • TAPE • Information about the number and type of pages processed • profile.{pid}

onbar Storage Manager archecker Dynamic Server sysutils sysmaster Archecker Integration XBSA ixbar oncfg._ sqlhosts onconfig ESQL/C

Algorithm: Scan Phase • Start reading all data from the data stream • Save the reserve and control pages • Check the format of the page • Mark each page as seen in bitmaps • At end of each tape sync all structures to disk

Algorithm: Verification Phase • Verifies the existence of: • chunk free list pages • blob pages • pages for each partition/table

Example Output Missing Pages • AC_MSGPATH archecker debug log • Table dbs1:cust missing pages ERROR: Table dbs1:cust partnum 0x00C0011A missing physical page 0x00D246B3 ERROR: Page 0x00d246b5 not found in bitmaps ERROR: Table dbs1:cust partnum 0x00C0011A missing physical page 0x00D246B5 ... STATUS: Table checks FAILED STATUS: BLOBChunk checks PASSED NOTE!

External Backups & Restores • Use third party products to make a backup • Backups created with third party products can rollforward Informix’s logical logs • Archive takes place while the system is online • Modifications to the system are blocked during the backup

External Backup • Flush all modified data to disk and suspend any write activity (including queries with temp tables) • onmode -c block • Backup the desired chunks in a dbspace using a third party product • Release the server • onmode -c unblock • Backup the logical logs

External Restore Common Command Lines • Salvage the logical logs before restoring • onbar -l -s • Restore the whole system • onbar -r -e • Restore a single dbspace • onbar -r -e dbspace_name

Suspended Restore vs Restartable Restore • Suspended Restore • The archive client has encountered an error before the restore has completed • Restartable Restore • The database server was terminated prior to the restore completing

Restartable Restore • Turned OFF by default • Only available with OnBar • onbar -RESTART • Requires RESTARTABLE_RESTORE be set to “ON” or “on” ONLY • What can restart when? • Physical Restore • Fully automatic • Cold and warm restore are restartable • Logical Recovery • Warm logical recovery may NOT be restarted • Cold logical recovery is fully restartable

New Releases and Future Plans • Progress frequency • Level 1 & 2 for smart blobs • No onarchive • Dynamic logs • Point in Time Table Level Restore

onstat -g arc • Shows the status of archive in progress • Displays the latest archives which have occurred num DBSpace Q Size Q Len Buffer partnum size scanner 1 rootdbs 37 0 27 0x1002a1 420 0x116ff7 Dbspaces - Archive Status name number level date log log-position rootdbs 1 0 10/12/2000.14:50 52 0x6b7018 plog 2 0 10/12/2000.14:51 52 0x6bb550 dbspace1 3 0 10/12/2000.14:51 52 0x6bb550 1 10/12/2000.14:54 53 0x7a404c dbspace2 4 0 10/12/2000.14:51 52 0x6bd018

Informix Backup/Recovery 2000