Transcript Roll forward

B2: What’s New in 10.1 RDBMS?
So many features, so few releases
Richard Banville
Progress Fellow
Agenda
The database “ilities”
 High Availability
• Problem Avoidability
• Visibility
• Scalability
 Maintainability
2
© 2008 Progress Software Corporation
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.
3
© 2008 Progress Software Corporation
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
4
© 2008 Progress Software Corporation
Database Consistency Checking
Stop problems before they happen
 Shared memory overwrite protection
• -MemCheck
 Physical block consistency checking
•
•
•
•
7
-DbCheck
-AreaCheck “area name”
-Index Check “index name”
-TableCheck ‘table name”
© 2008 Progress Software Corporation
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.
2.
3.
4.
5.
-MemCheck:
enabled
-DbCheck:
enabled
-AreaCheck in area: "customer" enabled
-IndexCheck:
disabled
-TableCheck:
disabled
Enter the option to enable/disable a consistency check:
8
© 2008 Progress Software Corporation
Database Consistency Checking
Performance impact
 Memory checking: unnoticeable impact
 Block level checking
Current consistency check status:
1.
2.
3.
4.
5.
-MemCheck:
enabled
-DbCheck:
enabled
-AreaCheck in area: "customer" enabled
-IndexCheck:
disabled
-TableCheck:
disabled
< 1%
~5%
Enter the option to enable/disable a consistency check:
9
© 2008 Progress Software Corporation
Database Consistency Checking
Looking for existing inconsistencies online
 dbtool block consistency checking
5. Read or Validate Database Block(s)
 Validation levels
• 0: Block header info only
• 1: Record header & record size
• 2: Record overlap checking
3. Record Validation (logical validation)
10
© 2008 Progress Software Corporation
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
11
Physical consistency
Keys to records
Records to keys
Validate key order
Locks associated tables
contention
–
–
–
–
–
© 2008 Progress Software Corporation
The roll forward process
myDb.bak
myDb
12
© 2008 Progress Software Corporation
The roll forward process
ftp
myDb
ai
13
ai
ai
ai
© 2008 Progress Software Corporation
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.
14
© 2008 Progress Software Corporation
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
15
© 2008 Progress Software Corporation
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
16
Recovery Scenario:
• Roll forward to transaction
© 2008 Progress Software Corporation
More tools for high availability
Replication enhancements
 “Online” backup of replication target
(foundational work)
• 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
• Disaster recovery
• Data replication
• Fail over/fail back
• Remote backup
*Symmetrix Remote Data Facility (SRDF)
17
© 2008 Progress Software Corporation
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.
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 %.
18
© 2008 Progress Software Corporation
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
19
© 2008 Progress Software Corporation
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.
20
© 2008 Progress Software Corporation
Agenda
The database “ilities”
 High Availability
• Problem Avoidability
• Visibility
• Scalability
 Maintainability
21
© 2008 Progress Software Corporation
Promon
Better organized server grouping
 Promon
Sv
No
22
R&D
Status
• 17. Servers By Broker
Pend.
Cur. data
Max.
• A more organized view of
existing
Pid
Type
Protocol Logins
0
2
3
4
5
15275
15501
15509
15511
15514
Login
Auto
Auto
Auto
Auto
TCP
TCP
TCP
TCP
TCP
1
7
8
9
10
15381
15609
15617
15629
15638
Login
Auto
Auto
Auto
Auto
TCP
TCP
TCP
TCP
TCP
Users
Users
5
1
1
1
1
0
0
0
0
0
0
1
1
1
1
15
15
15
15
15
2053
1025
1026
1027
1028
5
1
1
1
1
0
0
0
0
0
0
1
1
1
1
5
5
5
5
5
2051
1030
1031
1032
1033
© 2008 Progress Software Corporation
Users
Port
Num
Promon
Improved user information
 Promon
R&D
Other Displays
• 7. Total Locks per User
User Name
5
11
24
48
100
101
150
175
richb
richb
richb
richb
richb
richb
richb
richb
Type
SELF/ABL
SELF/ABL
REMC/SQLC
REMC/WTA
REMC/ABL
SQFC
REMC/APSV
SELF/APSV
PID
15494
16101
15530
20182
20183
20100
20101
20102
TTY
/dev/pts/16
/dev/pts/13
mysystem
mysystem
mysystem
mysystem
mysystem
Total
1
3
2
2
2
5
1
1
Record SHR/EXCL...
1
3
1
2
2
4
1
1
 User type display – _Connect-ClientType
23
© 2008 Progress Software Corporation
1
0
0
0
0
0
0
0
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
24
© 2008 Progress Software Corporation
Statement Caching
Where is that ABL code executing
25
User number
User name
User type
Login date/time
:
:
:
:
24
richb
REMC/ABL
03/06/08 15:30
Statement caching type
Statement caching last updated
: ABL Program Stack
: 03/06/08 15:35
Statement cache information
: 39
26
22
18
14
10
6
3
:
:
:
:
:
:
:
:
proc7 /usr1/richb/x.p
proc6 /usr1/richb/x.p
proc5 /usr1/richb/x.p
proc4 /usr1/richb/x.p
proc3 /usr1/richb/x.p
proc2 /usr1/richb/x.p
proc1 /usr1/richb/x.p
/usr1/richb/x.p
© 2008 Progress Software Corporation
Statement Caching
What’s that SQL code executing
26
User number
User name
User type
Login date/time
:
:
:
:
23
richb
REMC/SQLC
03/06/08 15:42
Statement caching type
Statement caching last updated
: SQL Statement
: 03/06/08 15:42
Statement cache information
: select count(*) from pub.customer
© 2008 Progress Software Corporation
Statement Caching
VST support - _Connect
proutil <db> -C updatevst
• Need to load new schema fields
 _Connect vst
•
•
•
•
•
27
_Connect-CachingType
_Connect-CacheLastUpdate
_Connect-CacheInfoType
_Connect-CacheLineNumber[32]
_Connect-CacheInfo[32]
© 2008 Progress Software Corporation
Agenda
The database “ilities”
 High Availability
• Problem Avoidability
• Visibility
• Scalability
 Maintainability
28
© 2008 Progress Software Corporation
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=
– Explorer option
29
© 2008 Progress Software Corporation
Scalability
I want more

IPv6 support
• Mixed mode (dual stack)
– IPv6 can convert IPv4 address
– Not available on windows
• Avoiding confusion
– -minport/-maxport
– use IPv6 configured –H hostnames
30
© 2008 Progress Software Corporation
Internal Performance Improvements
The need for speed
 “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
31
© 2008 Progress Software Corporation
Agenda
The database “ilities”
 High Availability
• Problem Avoidability
• Visibility
• Scalability
 Maintainability
32
© 2008 Progress Software Corporation
Roll forward protection
myDb.bak
myDb
ai
33
ai
ai
ai
© 2008 Progress Software Corporation
ai
ai
ai
ai
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.
34
© 2008 Progress Software Corporation
Roll forward protection
myDb
ai
In the .lg file:
ai
ai
ai
ai
ai
ai
X
rfutil -C roll forward session end.
Single-user session begin for richb on /dev/pts/101.
Begin Physical Redo Phase at 256 .
35
© 2008 Progress Software Corporation
ai
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
36
© 2008 Progress Software Corporation
Roll forward protection
rfutil <db> -C roll forward oplock –a myDb.a1
myDb
ai
ai
ai
ai
ai
ai
ai
ai
Connection attempts:
- 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.
37
© 2008 Progress Software Corporation
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
38
© 2008 Progress Software Corporation
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
39
© 2008 Progress Software Corporation
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
41
© 2008 Progress Software Corporation
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
42
© 2008 Progress Software Corporation
In Summary
We’ve made it even easier…
 to achieve high availability
 to see what’s going on
 to maintain
43
© 2008 Progress Software Corporation
Relevant Exchange Sessions
44

OPS-1: How Healthy is Your Database Today?

OPS-18: Data Management Roadmap

OPS-19: What’s IPV6 and Why Should I Care?

OPS-28: A New Spin on Some Old Latches
© 2008 Progress Software Corporation
?
Questions
45
© 2008 Progress Software Corporation
What’s here that is also in 10.1B03?





46
Database Consistency Checking
• -memCheck, 0DbCheck, -AreaCheck, -IndexCheck, TableCheck
Dbtool: Read or Validate Database Blocks
Rfutil:
• aiverify
• roll forward oplock
Promon:
• Servers by Broker
• Total Locks per User
Record lock removal on NO-LOCK reads
© 2008 Progress Software Corporation
Thank You
47
© 2008 Progress Software Corporation
48
© 2008 Progress Software Corporation