OPS1 DBA 101 How Healthy is Your Database

OPS-1: DBA 101 - How Healthy is Your Database Today? Ruanne Cluer Libor Laubacher Principal Tech Support Engineer OPS-1: DBA 101 - How Healthy is Your Database Today?

Agenda DBA 101 - How Healthy is Your Database Today? § § 2 Physical Database Limits Database Health Check Runtime Memory And Block Checking The 'recovery' part of DR OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Physical Database Limits 10. 1 B+ physical limit changes § Maximum extent size: 1 TB § Maximum extents per area: 1. 024 8 Maximum area size: 1, 024 TB (1 PB) § Maximum number of areas: 32, 000 8 Maximum database size: 32, 000 PB (32 EB) § Example: • 8 KB database blocksize with 64 rpb • Maximum number of records per area: 8, 796, 093, 022, 208 3 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Physical Database Limits 10. 1 B+ physical limit changes (cont. ) § 2 billion (2^32) records limit removed • Still applies for Type I areas § Errors SYSTEM ERROR: Attempt to read block 2147472480 which does not exist. (210) The maximum Area Size has been reached for Area: Please refer to the Progress Database Limit chapter in the Database Administration Guide and Reference. (9099) § New warning since 10. 0 B+ bkxtn: WARNING: Area: <area. Number> extent <extent. Name> has reached the <percentage> percent threshold for block usage - current block hiwater <max. Area. Block>. (13435) 4 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Physical Database Limits Extra space reservation § 10. 1 B+ feature § During crash recovery a database may grow exhausting rowids in an area § Reserve space at the end of each area to accommodate growth when the area is close to its limit § 1 K or 2 K DB block size – 1 GB reserved § 128 or 256 rpb area – 1 GB reserved § Any other combination – 5 GB § Areas are therefore allowed to expand the database up to max block limits minus reserved space 5 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Physical Database Limits Records per Block – Type I area RPB Maximum # of blocks per Storage Area ** Maximum physical size of Storage Area per DB blockisze (in GB) 1 4 8 32 67, 108, 864 64 256 512 64 33, 554, 432 32 128 256 128 16, 777, 216 16 64 128 256 8, 388, 608 8 32 64 ** excluding threshold reservation 6 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Physical Database Limits Records per Block – type II area (10. 1 B+) DB Maximum # of BS blocks per Storage Area ** 1 2 4 8 Maximum # of recids 32 rpb 64 rpb 128 rpb 1, 099, 511, 627, 35, 184, 372, 088, 8 70, 368, 744, 177, 6 140, 737, 488, 355, 3 776 32 64 28 549, 755, 813, 88 17, 592, 186, 044, 4 35, 184, 372, 088, 8 70, 368, 744, 177, 66 8 16 32 4 274, 877, 906, 94 8, 796, 093, 022, 20 17, 592, 186, 044, 4 35, 184, 372, 088, 83 4 8 16 2 137, 438, 953, 47 4, 398, 046, 511, 10 8, 796, 093, 022, 20 17, 592, 186, 044, 41 2 4 8 6 256 rpb 281, 474, 976, 710, 656 140, 737, 488, 355, 328 70, 368, 744, 177, 6 64 35, 184, 372, 088, 8 32 ** excluding threshold reservation 7 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Physical Database Limits Records per Block setting § Optimal space allocation • Increases areas addressable space • Reduces record fragmentation Records per (4 K) Block Head (2*RB+20) Create Limit = Overhead Remaining space 1 22 150 172 3924 32 84 150 234 3862 64 148 150 298 3798 128 276 150 426 3670 256 532 150 682 3414 OE 10: Type I Storage Area 8 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Physical Database Limits Records per Block – type II area (10. 1 B+) § 256 rpb is not a magic number • Use case: multiple users creating records for same table – massive fragmentation – performance problems – dump and load (still using 256 rpb) – still problems – getting rpb right is still important in Type II § Invest time tuning rpb settings 9 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Agenda DBA 101 - How Healthy is Your Database Today? § § 10 Physical Database Limits Database Health Check Runtime Memory And Block Checking The 'recovery' part of DR OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check What is it ? § No such thing as a “health check” standard § Continuous process, not an event • Physical checks • Logical checks § Better safe than sorry • Catch it early and minimize the damage than later and suffer costly downtime consequences … 11 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check What to run, when to run it and what to look for ? § Log files [daily] • Error reporting and trends § Tabanalys report [weekly] • Fragmentation, Scatter, Growth trends § Dbtool reports [weekly] • Physical and logical data integrity § Idxcheck report [weekly] • Index integrity § Database Statistics Report [monthly] • Storage Area High Water Mark, space requirements 12 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check tabanalys report – Record Fragmentation $ proutil <dbname> -C tabanalys > tabana. out § Baseline record fragmentation factor > 30% § Tables over 80% (Factor 1. 80) very strong candidates § Online defragmentation since 10. 1 A Block 5 100 bytes 13 Block 6 Update #1 +200 bytes OPS-1: DBA 101 - How Healthy is Your Database Today? Block 11 Update #2 +200 bytes +300 bytes © 2008 Progress Software Corporation

Database Health Check tabanalys report - Scatter Factor § How far a table’s records are from each other • i. e. how far are they from contiguous perfection • value close to 1 indicates a good quality of record allocation § When to D&L • bigger scatter most likely indicates a need for D&L – it is not the one and only metric – need to know more about your data • ignore scatter of small tables (< 1000 records) § “Logical” scatter 14 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check tabanalys report (cont. ) Table Records Record Size(B)-Fragments----Scatter Size Min Max Mean Count Factor PUB. Company 1000000 29. 2 M 25 40 30 1000060 PUB. Customer 200000 582. 4 M 27 20063 3053 350901 1. 0 1. 2 2. 0 4. 1 Binary load: (type I) PUB. Company 1000000 29. 2 M 25 40 30 1000000 PUB. Customer 200000 582. 1 M 27 20063 3053 299950 1. 0 4. 8 Binary load: (type II) PUB. Company 1000000 29. 2 M 25 40 30 1000000 PUB. Customer 200000 582. 1 M 27 20063 3053 301922 1. 0 15 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check Database Statistics Report $ prostrct statistics <dbname> > stats. out § Online: 9. 1 E 04, 10. 0 B 05+ § Storage Area High Water Mark [Active blocks] • prostrct add • Backup requirements § Database size [Total blocks] • Rowid limits [maxblocks] • Diskspace § Variable extent growth trends 16 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check Database Statistics Report (cont. ) Files in Area: /db 101/dd_9. d 1 /db 101/dd_9. d 2 /db 101/dd_9. d 3 Cust_Data 256, 049, 152 185, 204, 736 DB Block Usage: Cust_Data Active blocks: 168, 640 Data blocks: 160, 129 Free blocks: 8511 Empty blocks: 1600 Total blocks: 170, 240 Extent blocks: 3 Records/Block: 64 Cluster size: 1 Database Block Usage Summary Active Data Free Empty Extent Total blocks: blocks: 182, 862 173, 883 8979 12, 810 7 195, 672 Backup: ~715 MB 0. 51% MAXBLOCKS(33, 554, 432) 17 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check dbtool report $ § § 18 dbtool dbname Online and threaded (9. 1 D 06+) Option 5. Read or validate database blocks § 3 levels of validation, increasing in scope: • 0 - reads and validates block header • 1 - level 0 plus validates record size • 2 - level 1 plus checks if there is any record overlap Validates record blocks in one or all areas First error found in a block is reported in db. lg file then skip to the next record block OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check dbtool report (cont. ) § Logical data corruption § Option 3. Validate Records SYSTEM ERROR: Cannot read field 51 from record, not enough fields. (450) § Dump the record identified § Option 4. Record Version Validation 19 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check dbtool report (cont. ) § § 20 Option 6. Record fixup ‘Known errors’ that can be fixed programmatically § repairing small fragmented records for 10. 1 B+ 64 -bit recids § record versioning vs schema versioning Add fixup options as we encounter fixable problems Not documented, run under advisement OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check Index checks $ proutil dbname -C idxcheck –NL -Bp § Idxcheck report [weekly] • Online (since 10. 1 B 02+) § 4 levels of checks (since 10. 1 C+) • • • 21 1 - Validate physical consistency of index blocks 2 - Validate keys for each record 3 - Validate record for each key 4 - Validate key order (since 10. 1 C 01) L - Lock tables during the check R - Reset error limit, current: 500 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check Locking in online idxcheck § A shared schema lock aquired during the check, reading schema records with NO-LOCK § If –NL is used – L - Lock tables not available – indexes can be altered by other utilities § L - Lock tables is used – updates to the tables will be frozen as shared table locks on the tables as indexes are checked – indexes can not be altered by other utilities through an admin lock. 22 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Database Health Check Idxcheck § Status of online idxcheck process: _User. Status § error limitation default is 500 • R - Reset error limit, current: 10 § % complete, estimated time remaining status while • scanning blocks - total blocks below high water mark • validating keys - total number of index blocks § Schedule index fixup project SYSTEM ERROR: Index check found <error-cnt> errors. (2805) 23 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Log File Analysis with Log. Read What is it ? § Log files (can) produce a lot of data • Not always easy to analyse § Log. Read is a GUI utility for log files • No CHUI version available • View, manipulate, filter, sort, merge & translate § Written in ABL • Extensible: Add custom log handlers § Not officially supported • Available via PSDN 24 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Log File Analysis with Log. Read What can Log. Reader do ? § § Loads log files from many sources & versions Merges multiple logs Filtering “noise” from logs Localization • Date formats, code pages • Translation of PROMSGS § Sorting and searching § Timestamp adjustment § Handlers 25 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Log File Analysis with Log. Reader considerations: § Unload un-needed log files • otherwise content stays in memory § Ensure the Source & Target promsgs matches the log file version • i. e. not. lg file v 10 and promsgs v 8 § Utilities are associated with the relevant handler • database uses database handler 26 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Demo: Logging & Log. Read 27 OPS-1: DBA 101 - How Healthy is Your

Agenda DBA 101 - How Healthy is Your Database Today? § § 28 Physical Database Limits Database Health Check Runtime Memory And Block Checking The 'recovery' part of DR OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Runtime Memory And Block Checking What is it ? § A means of protecting the database from hardware and software “bugs” § Earlier detection (in memory) of data inconsistencies at a block level before disk § Enabled through startup parameters § Enabled/disabled online through • promon 8 R&D 8 Administrative Functions 8 “ 8. Adjust consistency checks” § Available since 10. 1 B+ 29 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Runtime Memory And Block Checking Five levels of consistency checks: § Database level “-Db. Check” • all index and record blocks (except LOB blocks) § Area level “-Area. Check <area name>” • all index and record blocks in the specified area (except LOB blocks) § Index level “-Index. Check <index name>” • all index blocks of the specified index § Table level “-Table. Check <table name>” • all record blocks of the specified table (except LOB blocks) § Memory protection “-Mem. Check” • any potential memory violations in the buffer pool of all index and record blocks 30 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Runtime Memory And Block Checking Applying consistency checks: § Used on broker, single-user, proutil, roll forward § “-Db. Check” • Overrides –Area. Check, -Index. Check, -Table. Check • Consistency check applied like only “-Db. Check” was used, ignoring others • Applies to the whole database § “-Mem. Check” • Can be used with all the other options 31 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Runtime Memory And Block Checking Applying consistency checks: (cont. ) § Each option can only be enabled once • • “-Area. Check <area name>” “-Index. Check <index name>” “-Table. Check <table name>” “-Mem. Check” § Four options can be enabled at the same time § Isolate where the problem is suspected § Example of startup parameters: $ proserve dbname –Area. Check “Order. Area” –Index. Check “Customer. Cust. Num” –Table. Check “Item” -Mem. Check 32 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Runtime Memory And Block Checking When to use it ? § § § If corruption is in doubt, enable! Current operation will stop when checks fail Extra messages written to the db. lg file Usr 5: Invalid Record Block Detected Usr 5: 15: D RL_RMCHG (PL) adbkey: 6/21504 updctr: 8 dif. Len: -1 flags: recnum: 0 log. Op: 1 recsz: 179 newsz: 200 Usr 6: Invalid Index Block Detected Usr 6: 268: D RL_CXINS (PL) adbkey: 6/20768 updctr: 35 obj. Dbkey: 64 root: 20768 offset: 3 cs: 0 extracs: 4 attr: 1 33 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Runtime Memory And Block Checking When to use it ? (cont. ) § Performance impact • Memory Check < 1% • Physical checks < 5% § Usage limitations: • None of the physical checks are available during crash recovery • “-Mem. Check” is always available • None of these parameter can be used on Open. Edge® Replication target database 34 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Agenda DBA 101 - How Healthy is Your Database Today? § § 35 Physical Database Limits Database Health Check Runtime Memory And Block Checking The 'recovery' part of DR OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

The 'recovery' part of DR Quotes from cases where ‘going to backup’ is not an option. . "The hotspare host is only used to roll forward ai's and is not powerful enough to run production" “Restoring 30 GB database to production server would take about 4 hours due to remote location of backups" "There was no database to restore because the rewind tape device was used after each operation, so only the last db backup is on tape" "The index areas are not included in the OS backups. . it takes too long to idxbuild" 36 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

The 'recovery' part of DR Checklist for RECOVERY: § Can our backup can be restored? § Is time-to-restore acceptable downtime? § Once restored, do we know where to start with application recovery? § Is the production recovery document still valid? § How often do we schedule a recovery runthrough? 37 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

In Summary § Health checks are an iterative process • physical and logical § Records per Block (rpb) are important • even with Type II areas and (almost) no physical limits • provided you care about performance § Don’t assume that if the application is presently running well, that a database is therefore healthy • always check 38 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

For More Information, go to PSDN… § Best Practices for Records-Per-Block Settings § Database Statistics Tool • http: //www. psdn. com/library/entry!default. jspa? external. ID=51 § Log. Read 1. 0 Tool Overview (English and Spanish) • http: //www. psdn. com/library/entry. jspa? external. ID=1841 § Log. Read Source code • http: //www. psdn. com/library/entry!default. jspa? external. ID=349 39 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation

Relevant Exchange Sessions § OPS-2: Open. Edge Management in the Real World § OPS-3: What's New in 10. 1 RDBMS? § OPS-4: Complete Database Disaster Recovery Plan! § OPS-8: Alerts, Alarms, Pages and Harbingers of Trouble… § OPS-14: Effective Open. Edge Database Configuration § OPS-15: What was Happening with My Database, App. Server, Operating System. . . Yesterday, Last Month, Last Year? 40 OPS-1: DBA 101 - How Healthy is Your Database Today? © 2008 Progress Software Corporation