starbucks enterprise data warehouse (edw) vldb backup …...4 who it supports • production edw...
TRANSCRIPT
![Page 1: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/1.jpg)
Greg GreenSenior Database AdministratorNovember 14, 2007
Starbucks Enterprise Data Warehouse (EDW)VLDB Backup and Recovery Architecture
![Page 2: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/2.jpg)
2
Starbucks EDW Backup and Recovery Architecture
• Current EDW Environment • Leveraging RMAN Features
• Oracle Database 11g Features Applicable to Our Environment
• Comments and Lessons Learned
![Page 3: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/3.jpg)
3
Going Beyond Coffee
![Page 4: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/4.jpg)
4
Who it Supports
• Production EDW supports Starbucks internal business users• 5 TB VLDB warehouse, growing 2-3 TB per year• Provides reports to the store level – sales, staffing, etc.
• Thousands of stores will directly access the EDW database by early 2008• Web-based dashboard reports via company intranet
• Front-end reporting with Microstrategy• Leveraging Ascential Datastage ETL Tool
• Toad, SQL Developer, and other ad-hoc tools used by developers and QA
![Page 5: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/5.jpg)
5
Production Hardware4 Node RAC Database
• Servers – 4 CPU HP ia64 1.5 GHz CPU 16 GB memory
• Network – Private Interconnect Infiniband Ib
• Public Network –Gigabit Ethernet
• Storage – SAN• 16 (8 Usable)
Terabytes Raid 1+0 Storage 15k 146 GB Drives
• 8 Terabytes Raid 5 (3+1) 300 GB Drives
• DB Software –Oracle 10.2.0.3 EE
• Media Mgmt –NetBackup 5.1
• RMAN Backup
DMX-3paemc001
NetBackup Media Server
NBU 5.1SunFire V440
DMX-3
2 x MDS 9509 San Switches
HP rx4640
Tape LibraryScalar I2K
2 x LTO2 tape drives per FC port
2 x FC portsTotal of 4 x LTO2 tape drives
2 x 2G FC
connecti ons
2 x 2G FC connections
16 x 2G FC
connections
3 x 1G G
igE connections (Trunked)
Dedicated GigE Backup Network
Infiniband Private Interconnect
Public GigE Network1 x 1G
GigE
connections
4 x 2G FC Connections
Storage Area Network
![Page 6: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/6.jpg)
6
Data Layout
• Data Layout for Backups is Key – View the EDW database as having an ACTIVE and PASSIVE portion.
• Large Partitioned Fact Tables are spread across month level tablespaces (Critical for my RMAN Duplication Strategy)
• Older tablespaces can be regularly moved to read-only
NOV07JUL07 AUG07 SEP07 OCT07
Read-only (older data) Read-write (recent data)
![Page 7: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/7.jpg)
7
Backup Criteria
• In addition to RTO and RPO we also considered other factors• Minimize the load on the servers and array to perform the
backup tasks• Minimize overall backup time• Keep disk space consumed by backups to a minimum• Minimize backup scripting & infrastructure setup time• Reduce hardware and storage costs• Ensure backup process can scale as the warehouse grow
• RMAN+Flash Recovery Area (FRA) or Split Mirror Backups could meet these requirements• However, RMAN was chosen for it’s ease of implementation,
greater flexibility, and lower cost.
![Page 8: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/8.jpg)
8
Backup Strategy• RMAN Rolling Image Copy Backups
• Disk - Flash Recovery Area (FRA)• Monthly Level 0 Image Copy backups to FRA• Daily Level 1 Differential Incremental Backups• Daily Roll Image Copy forward for the rest of the month with ‘SYSDATE – 1’• Daily Disk Script:
{RECOVER COPY OF DATABASE WITH TAG 'WEEKLY_FULL_BKUP'UNTIL TIME 'SYSDATE - 1';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG WEEKLY_FULL_BKUP DATABASE;
BACKUP AS BACKUPSET ARCHIVELOG ALL NOT BACKED UP DELETE ALL INPUT;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 2 DAYS DEVICE TYPE DISK; }
• Tape• Weekly “Backup Recovery Area”• Rest of the week “Backup Backupset ALL”
![Page 9: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/9.jpg)
9
Backup Performance to FRA
• Nightly Incremental Backups• 45-60 minutes for recovery of image copy to ‘sysdate – 1’ and
new incremental backup + archive log backup. The uncompressed backup set is typically 200-250 GB on a nightly basis.
• 4 RMAN disk channels running on one cluster node
• Full Backups • Approximately 45 minutes to delete existing Image Copy (5 TB)• Approximately 5.5 hours to create a new level 0 backup
(1 TB every 70 minutes)
![Page 10: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/10.jpg)
10
Backup Performance to Tape
• Nightly Backup of Backupsets• Nightly Backup time to tape for 2 tape channels on one
cluster node takes 50 minutes (roughly 200 GB at 240 GB/hr)
• Weekly Backup of Recovery Area• Weekly Backup of Recovery area with 4 channels (2 channels
on 2 nodes) takes 15-16 hours (currently ~5.5 TB)• LTO2 tape drives => 4 * (30-35) MB/s
• Should see at least 500 GB/hr going to tape• Observing 330-340 GB/hr• Tuning exercise showed the bottleneck is on the Bus of
V440
![Page 11: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/11.jpg)
11
RMAN Duplication
• Clone 4-node RAC production to 2-node RAC non-production (certification)
• Lessons learned from our specific environments
Target Database (PROD) Auiliary Database (Cert)
5 Tb 1 Tb
Production Certification
4 Node RAC 2 Node RAC
![Page 12: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/12.jpg)
12
Starbucks EDW Backup and Recovery Architecture
• Current EDW Environment • Leveraging RMAN Features
• Oracle Database 11g Features Applicable to Our Environment
• Comments and Lessons Learned
![Page 13: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/13.jpg)
13
Oracle Database 11g RMAN New Features for Our Environment
• Multisection Backups• RMAN Command Script Substitution Variables
![Page 14: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/14.jpg)
14
Multi-Section Backups
• “Divide and Conquer” bigfiles
Example rman> backup tablespace test_data01 section size 2G;starting backup at 02-OCT-07allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=153 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=154 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number 00006 name=/opt/oracle/oradata/orcl/test_data01.dbfBacking up blocks 1 through 262144channel ORA_DISK_1: starting piece 1 at 02-OCT-07channel ORA_DISK_2: starting full datafile backup setchannel ORA_DISK_2: specifying datafile(s) in backup setinput datafile file number 00006 name=/opt/oracle/oradata/orcl/test_data01.dbfBacking up blocks 262145 through 524288
![Page 15: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/15.jpg)
15
RMAN Substitution Variables
• Per the RMAN 11g Backup and Recovery Guide• “You can create RMAN command files and stored scripts that
accept user input at runtime. Thus, backup scripts can use RMAN substitution variables for tags, filenames, restore point names, and so on.”
• How could this apply to our environment?• Read-Only Data Driven Backups• Spread Backup of Read-Only Tablespaces over a month
• Evens out usage of tape drives over the month with no “spikes”.
• Ensures all data has been backed up with the last 30 days and I’m not relying on a backup tape from 2 1/2 years ago.
![Page 16: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/16.jpg)
16
Read-Only Tablespace Backups
• Script ExampleCreate script read_only_tbs_backup{backup device type sbt copy of tablespace &1 section size 50G;backup device type sbt copy of tablespace &2 section size 50G;}
Shell Script:Runbackup.sh read_only_tbs_backup HST_200703HST_200704
![Page 17: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/17.jpg)
17
Oracle Database 11g RMAN Features of Interest
• Backup Compression• Undo Optimization• Network Duplicate from Active Database• Improved Long-Term Backups
![Page 18: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/18.jpg)
18
Starbucks EDW Backup and Recovery Architecture
• Current EDW Environment • Leveraging RMAN Features
• Oracle Database 11g Features Applicable to Our Environment
• Comments and Lessons Learned
![Page 19: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/19.jpg)
19
Comments and Lessons Learned
• Read OTN articles to find latest backup features• Read 11g Release Notes on RMAN• Size Flash Recovery Area for the worst case scenario
• Filling up the FRA is not fun, especially when it is in an ASM diskgroup
• Be prepared for a debate – Ask 10 dbas how to design a large backup and they’ll give you 10 different answers. Everyone has his/her own opinion on backups.
• You may have to break your existing standards to get the job done.
![Page 20: Starbucks Enterprise Data Warehouse (EDW) VLDB Backup …...4 Who it Supports • Production EDW supports Starbucks internal business users • 5 TB VLDB warehouse, growing 2-3 TB](https://reader033.vdocuments.us/reader033/viewer/2022050717/5e8987997270aa01b850c451/html5/thumbnails/20.jpg)
20
Summary
• Current EDW Environment
• Oracle Database 11g Features Applicable to Our Environment
• Comments and Lessons Learned