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.
© Copyright 2024