1 / 44

OPS-3: What’s New in 10.1 RDBMS?

OPS-3: What’s New in 10.1 RDBMS?. So many features, so few releases. Wei Qiu. Principal Software Engineer. Agenda. The database “ilities”. High Availability Problem Avoidability Visibility Scalability Maintainability. Database Consistency Checking. Seen these messages before?.

amal
Download Presentation

OPS-3: What’s New in 10.1 RDBMS?

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. OPS-3: What’s New in 10.1 RDBMS? So many features, so few releases Wei Qiu Principal Software Engineer

  2. Agenda The database “ilities” • High Availability • Problem Avoidability • Visibility • Scalability • Maintainability

  3. Database Consistency Checking Seen these messages before? Index orderId in order for recid 2010 could not be deleted. Wrong key in idx 10 for record 2010. Invalid size of an index entry.

  4. Database Consistency Checking Or how about these… Invalid RM block for area 10 rmdoins: pbk->free went negative dbkey 4096 bkwrite: bktbl dbk 4096 not equal to bkbuf dbk -1234 bkaddr called with negative blkaddr:  -1234

  5. Database Consistency Checking Stop problems before they happen • Shared memory overwrite protection • -MemCheck • Physical block consistency checking • -DbCheck • -AreaCheck “area name” • -Index Check “index name” • -TableCheck ‘table name”

  6. Database Consistency Checking Enabling the consistency checks • Database startup parameter • Managed via promon R&D Admin Functions • 8. Block level consistency check Current consistency check status: 1. -MemCheck: enabled 2. -DbCheck: enabled 3. -AreaCheck in area: "customer" enabled 4. -IndexCheck: disabled 5. -TableCheck: disabled Enter the option to enable/disable a consistency check:

  7. < 1% ~5% Database Consistency Checking Performance impact • Memory checking: unnoticeable impact • Block level checking Current consistency check status: 1. -MemCheck: enabled 2. -DbCheck: enabled 3. -AreaCheck in area: "customer" enabled 4. -IndexCheck: disabled 5. -TableCheck: disabled Enter the option to enable/disable a consistency check:

  8. Database Consistency Checking Looking for existing inconsistencies online • dbtool block consistency checking 3. Record Validation (logical validation) 5. Read or Validate Database Block(s) • Validation levels • 0: Block header info only • 1: Record header & record size • 2: Record overlap checking

  9. Online Tools More analysis available online • Online dbanalys - Includes chanalys info • Tools now online proutil <db> -C chanalys [ -scan ] • -scan: fewer locks, less consistent proutil <db> -C idxcheck • Idxcheck validation levels • Physical consistency • Keys to records • Records to keys • Validate key order • Locks associated tables contention

  10. The roll forward process myDb.bak myDb

  11. The roll forward process ftp myDb ai ai ai ai

  12. The roll forward process ftp myDb Hot Standby Roll forward ai ai ai ai ai ai ai ai X SYSTEM ERROR: Attempt to read block 18446744073709550382 which does not exist in area 8, database x. ** Save file named core for analysis by Progress Software Corporation.

  13. Ai Verify Ai validation before application rfutil <db> -C aiverify <type> • Partial: ai block and note header validation • Increases reliability of archived ai files • Full: partial + note data validation • Identifies point in time recovery • Running • At ai switch or on ai archival • Just before roll forward of extent • Preferably on hot standby

  14. Roll forward verification rfutil myDb -C aiverify full ftp myDb Hot Standby ai ai ai ai ai ai ai ai X rlNoteVerify: Note dbkey is negative -1234. (14099) Trid: 358 code = RL_CXINS version = 2 (12528) • Hot Stand by: • Validate/fix production db • Re-base hot standby • Recovery Scenario: • Roll forward to transaction

  15. or heard these… Why can’t you improve the buffer pool hit ratio on the database? The recovery subsystem is a bottle neck. Look at that BI buffer wait %. The problem Have you ever seen these error messages… Out of free shared memory. Use -Mxs to increase. Lock table overflow, increase -L on server.

  16. Increase startup parameters online Increase startup parameters without database restart proutil <db> -C increaseto <params> • <params>: -L, -B, -bibufs, -aibufs, -Mxs • Increase, not decrease • Resource restrictions apply • New shared memory segments • Security restrictions • Servers: automatically attach quickly • Self serve: attach w/db action over time • Segment size

  17. Increase startup parameters online Increasing available locks online proutil myDb -C increase -L 10000 Waiting for broker connection to newly added shared memory segments. Usr Name Type Pid 7 richb ABL 5957 The connections above have not attached to recently added shm segments. Do you wish to recheck? (y/n) (n): Increase params aborted because of shared memory allocation issue. (y): Increase params increasing lock table size (-L) from 1025 to 10016.

  18. Disaster recovery • Fail over/fail back More tools for high availability Replication enhancements • “Online” backup of replication target • Normal operating state required • SHR schema lock on source • ai file stores changes until complete • EMC’s SRDF* certification • Real time copies of logical data volumes • Data replication • Remote backup • *Symmetrix Remote Data Facility (SRDF)

  19. Agenda The database “ilities” • High Availability • Problem Avoidability • Visibility • Scalability • Maintainability

  20. Promon Better organized server grouping • Promon R&D Status • 17. Servers By Broker • A more organized view of existing data Sv Pend. Cur. Max. Port No Pid Type Protocol Logins Users Users Users Num 0 15275 Login TCP 5 0 0 15 2053 2 15501 Auto TCP 1 0 1 15 1025 3 15509 Auto TCP 1 0 1 15 1026 4 15511 Auto TCP 1 0 1 15 1027 5 15514 Auto TCP 1 0 1 15 1028 1 15381 Login TCP 5 0 0 5 2051 7 15609 Auto TCP 1 0 1 5 1030 8 15617 Auto TCP 1 0 1 5 1031 9 15629 Auto TCP 1 0 1 5 1032 10 15638 Auto TCP 1 0 1 5 1033

  21. Promon Improved user information • Promon R&D Other Displays • 7. Total Locks per User • User type display – _Connect-ClientType User Name Type PID TTY Total Record SHR/EXCL... 5 richb SELF/ABL 15494 /dev/pts/16 1 1 1 11 richb SELF/ABL 16101 /dev/pts/13 3 3 0 24 richb REMC/SQLC 15530 2 1 0 48 richb REMC/WTA 20182 mysystem 2 2 0 100 richb REMC/ABL 20183 mysystem 2 2 0 101 richb SQFC 20100 mysystem 5 4 0 150 richb REMC/APSV 20101 mysystem 1 1 0 175 richb SELF/APSV 20102 mysystem 1 1 0

  22. Statement Caching What code is executing against my database • List recent client statements • Promon R&D Status 18. Client Database-Request statement Cache • By user/server/all users current and future. • Last line or entire stack • ABL info obtained from DEBUG-LIST output • .i’s are in-lined

  23. Statement Caching Where is that ABL code executing User number : 24 User name : richb User type : REMC/ABL Login date/time : 03/06/08 15:30 Statement caching type : ABL Program Stack Statement caching last updated : 03/06/08 15:35 Statement cache information : 39 : proc7 /usr1/richb/x.ped 26 : proc6 /usr1/richb/x.ped 22 : proc5 /usr1/richb/x.ped 18 : proc4 /usr1/richb/x.ped 14 : proc3 /usr1/richb/x.ped 10 : proc2 /usr1/richb/x.ped 6 : proc1 /usr1/richb/x.ped 3 : /usr1/richb/x.ped

  24. Statement Caching What’s that SQL code executing User number : 23 User name : richb User type : REMC/SQLC Login date/time : 03/06/08 15:42 Statement caching type : SQL Statement Statement caching last updated : 03/06/08 15:42 Statement cache information : select count(*) from pub.customer

  25. Statement Caching VST support - _Connect proutil <db> -C updatevst • Need to load new schema fields • _Connect vst • _Connect-CachingType • _Connect-CacheLastUpdate • _Connect-CacheInfoType • _Connect-CacheLineNumber[32] • _Connect-CacheInfo[32]

  26. Agenda The database “ilities” • High Availability • Problem Avoidability • Visibility • Scalability • Maintainability

  27. Scalability I want more • Large file support for bulk load (> 2Gb) • Independent of DB large file status • IPv6 support • More ip addresses • only 30% ip addrs left (7 yrs) • Routing improvements • Required by government contracts • Configuration • -ipver IPv4 (default) or IPv6 • Property file: ipver=

  28. Scalability I want more • IPv6 support • Mixed mode (dual stack) • IPv6 can convert IPv4 address • Not available on windows • -minport/-maxport

  29. Internal Performance Improvements “clean” shutdown indicator Avoids long redo in roll forward Last 2 clusters always redone Improved read operation concurrency Latch enhancements and usage optimization Multi-user bi activity optimization Avoid rollback “block jump” notes Fewer notes written The need for speed

  30. Agenda The database “ilities” • High Availability • Problem Avoidability • Visibility • Scalability • Maintainability

  31. Roll forward protection myDb.bak myDb ai ai ai ai ai ai ai ai

  32. Roll forward protection myDb Roll forward ai ai ai ai ai ai ai ai X ** The database was last changed Mon Apr 1 15:37:38 2008. ** The after-image file expected Mon Apr 1 15:33:45 2008. ** Those dates don't match, so you have the wrong copy of one of them. roll forward open /usr1/x.a4 error: -1.

  33. Roll forward protection myDb ai ai ai ai ai ai ai ai X In the .lg file: rfutil -C roll forward session end. Single-user session begin for richb on /dev/pts/101. Begin Physical Redo Phase at 256 .

  34. Roll forward protection Non interruptible roll forward rfutil <db> -C roll forward oplock • Prevents “stray” database connections • Prostrct add allowed • Automatic disablement • At roll forward completion • Explicit disablement rfutil <db> -C roll opunlock • Recovers db • Stops the roll forward process

  35. Roll forward protection rfutil <db> -C roll forward oplock –a myDb.a1 myDb ai ai ai ai ai ai ai ai - Access to the database during roll forward process is not allowed because it will modify the database. - Write access to the database will not be allowed until the roll forward operations have completed. Connection attempts:

  36. Index Rebuild More control • Index rebuild packing factor • Max % of space used • Avoids costly index block splits proutil <db> -C idxbuild –pfactor <60 – 100> • Examine utilization % in idxanalys

  37. Index Fix Interface Specific choices • Idxfix uses idxbuild interface • Select indexes to fix by • Table, schema, area or activation state Select one of the following: All (a/A) - Fix all the indexes Some (s/S) - Fix only some of the indexes By Area (r/R) - Fix indexes in selected areas By Schema (c/C) - Fix indexes by schema owners By Table (t/T) - Fix indexes in selected tables By Activation (v/V) - Fix selected active or inactive indexes

  38. SQL Stored Procedures 64 bit stored procedure support. • 64 bit JVM availability • Java™ 1.5 certification • Can use same drivers • Additional schema • _SysProcBin, _SysProcText • 32 bit databases have schema already • Databases created in 64 bit environment proutil <db> -C enablestoredproc

  39. Binary Dump Specified - Improved Binary dump specified with “between” range dumping proutil <db> -C dumpspecified <field-info> <op1> <low-value> AND <op2> <high-value> <dir> • Option values: GT, GE, LT, LE, EQ • Dump specific ranges • Improved parallelism proutil db –C dumpspecified cust.custnum GE 5 and LE 100 /dumpdir

  40. In Summary We’ve made it even easier… • to achieve high availability • to see what’s going on • to maintain

  41. Relevant Exchange Sessions • OPS-1: How Healthy is Your Database Today? • OPS-9: Data Management Roadmap • OPS-12: A New Spin on Some Old Latches

  42. ? Questions

  43. Thank You

More Related