How to look good as a DBA?
OE Replication
Tony Brooks
Not like this Guy
Replication can make you look good
This Guy, not too sure?
2
© 2013 Progress Software Corporation. All rights reserved.
Great Timing
Early October 2010 Purchased Replication Plus
• BCP and Offload Production Reporting
Late October 2010 Implemented with Progress PS
• Replicated From Rocklea Qld to Sydney
Jan 13th 2011 – Historic Floods
• Able to Failover to Sydney Server
3
© 2013 Progress Software Corporation. All rights reserved.
Threats to Your Business
4
System Level
Logical Level
Site Level
Disk Crash
Data Corruption
Weather
Array Crash
Software Bugs
Accidents
OS Crash
User Error
Power Outage
CPU Thrash
Viral Attacks
Earthquake
© 2013 Progress Software Corporation. All rights reserved.
OpenEdge Replication
Continuous Availability With
Real-time replication
• Near zero-latency
• No data loss
• Guaranteed data integrity
Flexible database options
• Multiple target databases (local & Remote)
• Read-only query access (ReplicationPlus)
Automatic recovery
Easy deployment
5
© 2013 Progress Software Corporation. All rights reserved.
Replication
Restore backup to get
started
Read-Write
Application
“Mirror” txn log
over network
Active DB
Transaction
logs
6
© 2013 Progress Software Corporation. All rights reserved.
Apply Changes as
they arrive
Query
Application
“Hot--site” DB
“Hot
Failover/Failback
Terminology
After Imaging (AI) = Redo Logs
Before Image File (BI) = Primary Recovery Logs
7
© 2013 Progress Software Corporation. All rights reserved.
Overview
After Imaging is not new
After Imaging can be used for
• Point in time recovery
• Hot Site
• Replication (Log file shipping)
• Performance (Load sharing)
• Backup
8
© 2013 Progress Software Corporation. All rights reserved.
Database Recovery
9
Can recover to
•
Point of failure (most common)
•
Date & time
•
Transaction number
Example:
1.
Disk with database crashes
2.
Install new disk
3.
Restore database
4.
Roll forward all AI files since last backup
© 2013 Progress Software Corporation. All rights reserved.
Database Recovery - Continued
Result
• No loss
• Any open transactions backed out
(normal crash recovery)
Backup + AI = Recovered database
Backup without AI =
10
Lost Data !!!!
© 2013 Progress Software Corporation. All rights reserved.
Time Line
Tuesday
Monday
Backup
Backup
Wednesday
Thursday
Major Crash
How much can you afford to lose ?
– With AI could recover to point of failure
– Without AI you would have to restore the backup and redo Tuesday’s
work manually
– What happens if Tuesdays backup is no good?
11
© 2013 Progress Software Corporation. All rights reserved.
Benefits
Minimize loss of data
Very useful if last backup tape was faulty
Some data have no audit trail to allow
redo of the work
• Web transactions
• Phone orders
• EDI
Single point to collect all information written to the database
12
© 2013 Progress Software Corporation. All rights reserved.
Hot Site
Maintain a Hot Site in case of a major
system outage
Hot site can be in the same location or remote
System A
(Santa Clara,CA)
13
© 2013 Progress Software Corporation. All rights reserved.
System B
(Austin, TX)
Replication
Replicate a database to one or more
remote sites
One way
System A
System B
System C
14
© 2013 Progress Software Corporation. All rights reserved.
Performance (Load sharing)
Off load reporting to a second system that acts as a reporting server
Great for month-end or year-end reporting
Main Server
15
© 2013 Progress Software Corporation. All rights reserved.
Reporting Server
Backup
Instead of backing up main production database, backup a copy of the database on a
second system that has been rolled forward
System A
System B
Tape Drive
16
© 2013 Progress Software Corporation. All rights reserved.
Differences between AI & BI
BI File
• Mechanism for crash recovery
• Space is automatically reused
• Made up of clusters
• Cluster is made up of BI blocks
• BI blocks contain “notes”
AI File
• Is a copy of the BI “notes”
• Space is only reused when archived
• Sequential store of “notes” in AI blocks
17
© 2013 Progress Software Corporation. All rights reserved.
What are BI/AI Log “Notes” ?
18
Each note describes a single change to a database block
There are over 50 types of notes
Basis for crash recovery mechanism
Example creating a customer row
1.
Transaction begin
2.
Indexes change
3.
Row change
4.
Transaction end
© 2013 Progress Software Corporation. All rights reserved.
What are BI/AI Log “Notes” ?
―Continued
CREATE customer.
ASSIGN Name = "Tony Brooks”
SalesRep = "BBB”
Address = "4900 S. Ulster”
State = "CO".
Aimage
–(rfutil
scan
<dbname> -C aimage scan verbose -a <aifile>)
code = RL_TBGN (1637)
transaction index = 303 (1638)
dbkey = 0
update counter = 0 (1639)
Trid: 303 Thu Apr 13 09:43:56 2000. (2598)
User Id: Administrator. (2599)
code = RL_SEINC (1637)
transaction index = 303 (1638)
area = 6
dbkey = 96
update counter = 40 (9016)
19
© 2013 Progress Software Corporation. All rights reserved.
What are BI/AI Log “Notes”
―Continued
code = RL_RMCR (1637)
transaction index = 303
area = 9
dbkey = 1312
code = RL_CXINS (1637)
transaction index = 303
area = 10
dbkey = 640
code = RL_CXINS (1637)
transaction index = 303
area = 10
dbkey = 512
code = RL_CXINS (1637)
transaction index = 303
area = 10
dbkey = 224
code = RL_CXINS (1637)
transaction index = 303
area = 10
dbkey = 416
code = RL_RMCHG (1637)
transaction index = 303
area = 9
dbkey = 1312
code = RL_TEND (1637)
transaction index = 303
20
(1638)
update counter = 28 (9016)
(1638)
update counter = 220 (9016)
(1638)
update counter = 29 (9016)
(1638)
update counter = 1120 (9016)
(1638)
update counter = 239 (9016)
(1638)
update counter = 29 (9016)
(1638)
© 2013 Progress Software Corporation. All rights reserved.
Setup of AI
Backup
Add AI extents to the database
Enable AI
Enable AI Archiver
21
© 2013 Progress Software Corporation. All rights reserved.
Backup the database
Before you can enable AI you
must first backup the database
• probkup
• If using O/S backup run mark database a backed up (rfutil <dbname> -C mark backedup)
This backup is the starting point
for recovery
22
© 2013 Progress Software Corporation. All rights reserved.
Add AI extents to the database
Build a add.st file with the AI extents to add
a
a
a
a
a
d:\aitest\aitest.a1
d:\aitest\aitest.a2
d:\aitest\aitest.a3
d:\aitest\aitest.a4
d:\aitest\aitest.a5
f
f
f
f
f
10000
10000
10000
10000
10000
Run prostrct add <dbname> add.st
Formatting extents:
size
1264
1264
1264
1264
1264
23
After
After
After
After
After
area name
Image Area 1
Image Area 2
Image Area 3
Image Area 4
Image Area 5
© 2013 Progress Software Corporation. All rights reserved.
path name
d:\aitest\aitest.a1
d:\aitest\aitest.a2
d:\aitest\aitest.a3
d:\aitest\aitest.a4
d:\aitest\aitest.a5
Enabling AI
Run rfutil <dbname> -C aimage begin
• Automatically truncates BI file
D:\aitest>rfutil aitest -C aimage begin
OpenEdge Release 10.0B03 as of Thu Sep 8 09:15:50 EDT 2005
The BI file is being automatically truncated. (1526)
24
© 2013 Progress Software Corporation. All rights reserved.
Maintaining AI extents
Status of AI Extents
Switching AI extents
Archiving full AI extent /marking it empty
What happens if all extents are full?
Other
25
© 2013 Progress Software Corporation. All rights reserved.
Status of AI extents
26
AI files have three states
1.
Full - extent is ready to archive
2.
Busy - extent is currently being written to
3.
Empty - extent is empty and ready to use
4.
Locked – Replication enabled wait to be transfered
Use rfutil <dbname> -C aimage extent list to see the status of AI extents
© 2013 Progress Software Corporation. All rights reserved.
Status of AI extents
Example AI extent list
D:\aitest>rfutil aitest -C aimage extent list
OpenEdge Release 10.0B03 as of Thu Sep
27
Extent:
Status:
Type:
Path:
Size:
Used:
Start:
Seqno:
1
Busy
Fixed Length
d:\aitest\aitest.a1
10104
1
Wed Apr 12 15:56:01 2000
1
Extent:
Status:
Type:
Path:
2
Empty
Fixed Length
d:\aitest\aitest.a2
© 2013 Progress Software Corporation. All rights reserved.
8 09:15:50 EDT 2005
Switching AI extents
As a AI file fills, it will switch to the next extent (If empty)
Can force a switch by running
• rfutil <dbname> -C aimage new
28
© 2013 Progress Software Corporation. All rights reserved.
Archiving Full extents
Use a O/S copy to copy the full extent
• cp /ai1/mfgpro.a1 /aiarchive
Use rfutil <dbname> -C aimage extent full
- to return the oldest full extent
Then compress/copy to tape/rcp etc.
Mark extent as empty
• rfutil <dbname> -C aimage extent empty <aifile>
D:\aitest>rfutil aitest -C aimage extent empty
OpenEdge Release 10.0B03 as of Thu Sep 8 09:15:50 EDT 2005
Marked after-image extent d:\aitest\aitest.a1 EMPTY. (3789)
29
© 2013 Progress Software Corporation. All rights reserved.
What happens if all AI extents are Full
By default the broker will shut the database down
Start the broker with -aistall
• All update activity is suspended
• Message is written to database log file
• Resumes when AI file is marked empty
Can't switch to ai extent filename; it is full.
Backup ai extent and mark it as empty (3774)
30
© 2013 Progress Software Corporation. All rights reserved.
Checking for Empty Extents - UNIX
#!/usr/bin/ksh
AIEMPTY=`_rfutil test1 -C aimage list | grep "Empty" | wc -l`
if [ $AIEMPTY -lt 2 ]
then
echo Low number of empty AI files …
fi
31
© 2013 Progress Software Corporation. All rights reserved.
Other
Start a AIW (Enterprise DB)
-aibuffs usually 1.5 * -bibuffs (30-50)
AI blocksize to be >= to the BI blocksize
rfutil <db> -C truncate ai -aiblocksize 16
To estimate size of AI files for a day:
# of checkpoints per day * BI cluster size
32
© 2013 Progress Software Corporation. All rights reserved.
AI and Backups
Can use with AI
– Online/Offline probkup
– Offline O/S backup (tar, cpio etc)
– Offline third mirror
Probkup will NOT backup the AI files (must use O/S backup)
33
© 2013 Progress Software Corporation. All rights reserved.
AI and Backups
First part of a Progress (probkup) online/offline backup is switch AI extents
Backup will fail if no empty extent exists
17:22:41 probackup session begin for Administrator on CON:. (451)
17:22:41 Begin Physical Redo Phase at 0 . (5326)
17:22:41 Physical Redo Phase Completed at blk 0 off 419 upd 0. (7161)
17:22:41 Switched to ai extent d:\aitest\aitest.a3. (3777)
17:22:41 This is after-image file number 8 since the last AIMAGE BEGIN (3778)
17:15:19 probackup session begin for Administrator on CON:. (451)
17:15:19 Begin Physical Redo Phase at 0 . (5326)
17:15:19 Physical Redo Phase Completed at blk 0 off 419 upd 0. (7161)
17:15:19 Can't switch to after-image extent d:\aitest\aitest.a3 it is full. (3775)
17:15:19 Backup ai extent and mark it as empty. (3776)
34
© 2013 Progress Software Corporation. All rights reserved.
How to Recover with Just AI files
Restore Backup
Apply AI files in the same order as created
• rfutil <dbname> -C roll forward -a <aifile>
After the last AI file is applied, the database is considered crashed
Open the database
(start broker or single user session)
• Normal to get crash recovery messages
35
© 2013 Progress Software Corporation. All rights reserved.
Steps to setup Replication
Setup target system
Test network
Build replication properties file
proutil <dbname> -C enablesitereplication source
Do special backup
• probkup <dbname> mybackup.pbk –com -REPLTargetCreation
Restore db on target
Setup replication properties for target
proutil <dbname> -C enablesitereplication target
36
© 2013 Progress Software Corporation. All rights reserved.
Steps to setup Replication
Start the target db
• proserve <dbname> <dbparams> -DBService replagent –S <port>
Start the source db
• proserve <dbname> <dbparams> -DBService replserv -S <port>
Can be done while db is online
37
© 2013 Progress Software Corporation. All rights reserved.
What is new
Extract only the blocks in the AI file that are needed (good for coping files over
a WAN)
• rfutil db-name -C aimage extract -a ai-extent -o output-file
• In progress 9.1D09 and higher
AI file management daemon (10.1A)
• Broker startup
– -aiarcdir /usr1/aiarchives/
– -aiarcinterval 3600
– rfutil db-name -C aiarchiver enable
38
© 2013 Progress Software Corporation. All rights reserved.
OpenEdge Replication
Near real time applying of AI data
• Lag / delay depends on network speed and amount of updates
Can connect to database in a Read only Reporting mode
• With Replication Plus
• Careful with schema changes
Don’t’ skimp on the target system
Lock table size should be the same on target as source
39
© 2013 Progress Software Corporation. All rights reserved.
Best Practices
Only use fixed length AI extents
5 to 7 AI extents
Each extent at an manageable size 50-300Mb
AI extents should be on own disk for
• Reliability
• Performance
• Human error
Sometimes AI space is needed to do crash recovery (BI File grows = AI space is needed)
Have enough space for extended network outage
• Otherwise disable replication and resetup
40
© 2013 Progress Software Corporation. All rights reserved.
Best Practices - Continued
Can have 2 targets
• One local – one remote (different State / Country)
• But both need to confirm before AI space can be reused
If replicating locally make sure system has different power supply etc
Database replication is better than SAN
• SAN’s can replicate corruption
• Applying AI notes – checks data before being applied
41
© 2013 Progress Software Corporation. All rights reserved.
Best Practices - Continued
Scripts
• Switch AI files every 15min-1hour
• Archive AI full AI files
• Check for low number of empty extents
• Check how far behind the target system is
• Replication status
Switch just before (as a part of a backup)
• Month/Year end process
• Software/Hardware upgrades
Compress AI files as a part of archiving
• Copy / rsync to different system
42
© 2013 Progress Software Corporation. All rights reserved.
How to ensure Replication et al, are healthy?
43
© 2013 Progress Software Corporation. All rights reserved.
How to monitor Replication
dsrutil <db> -C monitor
Can also show you how far behind
• Target system is behind the source
Restarting after extended network issues
• dsrutil <dbname> -C restart server
44
© 2013 Progress Software Corporation. All rights reserved.
OpenEdge Management Overview
45
© 2013 Progress Software Corporation. All rights reserved.
Dashboard - My Dashboard
46
© 2013 Progress Software Corporation. All rights reserved.