Slide 1: ORACLE
ORACLE
Slide 2: Oracle Database 10g Data Warehouse Backup & Recovery: Automatic, Simple, And Reliable
Session id: 40179 Oracle Database 10g Data Warehouse Backup & Recovery: Automatic, Simple, And Reliable George Lumpkin Director Product Management Oracle Corporation Tammy Bednar Sr. Product Manager Oracle Corporation
Slide 3: Agenda
Agenda Data warehouse characteristics Oracle Backup & Recovery review Understand your backup and recovery requirements DW Recovery Methodology – Best Practice#1 – Archivelog Mode – Best Practice#2 – Use RMAN – Best Practice#3 – Read-Only Tablespaces – Best Practice#4 – Nologging Operations –
Slide 4: You can back up your data warehouse in exactly the same way as you back up your OLTP database
You can back up your data warehouse in exactly the same way as you back up your OLTP database. But …
Slide 5: But …
But … Your OLTP system is 50GB and your data warehouse is 5TB. Do you have the budget to buy 100X more backup hardware to support your data warehouse? Your OLTP system must be recoverable in 30 minutes. Does your data warehouse require the same recovery time? Your OLTP system contains new cust
Slide 6: Data Warehouse Characteristics
Data Warehouse Characteristics Large amounts of data – Production systems up to 10s of terabytes Lower availability requirements than OLTP – Often hours or days, not minutes Data enters data warehouse via controlled processes (ETL) – Data feeds can be re-run if necessary Significant portions
Slide 7: Agenda
Agenda Data warehouse characteristics Oracle Backup & Recovery review Understand your backup and recovery requirements DW Recovery Methodology – Best Practice#1 – Archivelog Mode – Best Practice#2 – Use RMAN – Best Practice#3 – Read-Only Tablespaces – Best Practice#4 – Nologging Operations –
Slide 8: Oracle Backup & Recovery: What should you backup?
Oracle Backup & Recovery What should you backup? Oracle Database Architecture – Datafiles – Archive logs – Control file – Configuration Files
Slide 9: Backup and Recovery Methods and Tools
Backup and Recovery Methods and Tools Recovery Manager – Oracle’s tightly integrated utility for creating, managing, restoring and recovering databases Oracle Enterprise Manager – GUI interface to Recovery Manager Oracle Data Pump (export/import) – Logical database backups User Managed – Dat
Slide 10: Recovery Manager: Oracle's Backup & Recovery Utility
Recovery Manager: Oracle’s Backup & Recovery Utility Over 25 Man Years of Engineering Technology provides: Recovery – Intimate knowledge of database file Manager formats and recovery procedures Enterprise Manager – Manages and automates the backup, restore, and recovery process & 3rd Party Tools –
Slide 11: Backup Management
Backup Management List RMAN backups – View files included in a backup set Perform maintenance – Remove obsolete backups with a single click
Slide 12: Other Oracle10g B&R Enhancements
Other Oracle10g B&R Enhancements Compression of archive logs and backups Restore tolerates missing or corrupt backup – Automatically fails over to previous backup Automated recovery through a previous point in time recovery – Recovery through resetlogs Fully automated tablespace point-in-tim
Slide 13: Oracle Database 10g Data Pump
Oracle Database 10g Data Pump What is it? – High speed, parallel, bulk data and metadata movement of Oracle database contents – Replacement for original Export and Import – New server-side infrastructure for utilities – Public documented package interfaces What can you use it for? – Data/Metadat
Slide 14: Agenda
Agenda Data warehouse characteristics Oracle Backup & Recovery review Understand your backup and recovery requirements DW Recovery Methodology – Best Practice#1 – Archivelog Mode – Best Practice#2 – Use RMAN – Best Practice#3 – Read-Only Tablespaces – Best Practice#4 – Nologging Operations –
Slide 15: What is your Recovery Time Objective?
What is your Recovery Time Objective? Analyze and identify the cost associated with unavailable data Design recovery requirements around data criticality and logical relationships Build and integrate the backup and recovery plan Manage and evolve your processes as your data, IT infrastructur
Slide 16: What is your Recovery Point Objective?
What is your Recovery Point Objective? RPO RTO1 RTO2 Backups are Critical data Remainder of run at scheduled Is available & Data is re-inserted. interval. Database is open. No Restore Data is re-inserted backup Backup via ETL of data mm Hours mm Hours Time -nn Hours 0 Automated recovery Manual recov
Slide 17: How do you backup all of that data?
How do you backup all of that data? Backing up to Tape – The time to backup to tape is a function of your hardware # of tape devices + tape library = time to backup Divide and Conquer – Breakup the database backup over the course of several days – RMAN provides automatic ‘windowing’ capability
Slide 18: Agenda
Agenda Data warehouse characteristics Oracle Backup & Recovery review Understand your backup and recovery requirements DW Recovery Methodology – Best Practice#1 – Archivelog Mode – Best Practice#2 – Use RMAN – Best Practice#3 – Read-Only Tablespaces – Best Practice#4 – Nologging Operations –
Slide 19: Best Practice#1 – Archivelog Mode
DW Recovery Methodology Best Practice#1 – Archivelog Mode Archivelog Mode – Recover more completely from media failure – Create backups while the database is open and available – Provides more recovery options No Archivelog Mode – Backup only when the database is closed – Restore data as of the
Slide 20: Best Practice#2 – Use RMAN
DW Recovery Methodology Best Practice#2 – Use RMAN 1. Extensive Reporting 2. Easily integrates with Media Managers 3. Incremental Backups 4. Block Media Recovery (BMR) 5. Downtime Free Backups 6. Archive log validation and management 7. Backup and Restore Validation 8. Corrupt Block Detection 9. Bac
Slide 21: Best Practice#3 – Read-Only Tablespaces
DW Recovery Methodology Best Practice#3 – Read-Only Tablespaces Leverage partitioning and read-only tablespaces to minimize the amount of data to be backed-up – Read-only tablespaces only need to backed-up once – Older data can often be converted to read-only Jan Feb Mar Apr May Jun Jul Read-only
Slide 22: Best Practice#4 – Nologging Operations
DW Recovery Methodology Best Practice#4 – Nologging Operations Most data warehouses use NOLOGGING operation to accelerate bulk-data processing B+R considerations for NOLOGGING: – Do not backup a tablespace while nologging operations are running on that tablespace Schedule backups to run after
Slide 23: Best Practice#4 – Nologging Operations
DW Recovery Methodology Best Practice#4 – Nologging Operations Strategy #1: Recover nologging operations via ETL 1. Take a weekly backup of the data warehouse 2. Every night, store/backup a copy of the raw data files which are loaded into the data warehouse 3. To recover, restore the backup of the
Slide 24: Best Practice#4 – Nologging Operations
DW Recovery Methodology Best Practice#4 – Nologging Operations Strategy #2: Recover nologging operations via Incremental Backups 1. Take a weekly backup of the data warehouse 2. Every night, do an incremental backup of all modified tablespaces after all nologging operations are completed 3. To rec
Slide 25: Best Practice#5 – Exclude Tablespace
DW Recovery Methodology Best Practice#5 – Exclude Tablespace Not every tablespaces in a data warehouse needs to be backed-up – Oracle Temporary Tablespaces By default, RMAN does not back these up – “User Scratch Tablespaces” Tablespaces used by users to store incremental results and other temp
Slide 26: Summary
Summary Data warehouse characteristics Oracle Backup & Recovery review Understand your backup and recovery requirements DW Recovery Methodology – Best Practice#1 – Archivelog Mode – Best Practice#2 – Use RMAN – Best Practice#3 – Read-Only Tablespaces – Best Practice#4 – Nologging Operations
Slide 27: Next Steps for High Availability
Next Steps for High Availability High Availability Sessions from Oracle Tuesday in Moscone Room 304 Wednesday in Moscone Room 304 11:00 AM 8:30 AM How Oracle Database 10g Oracle Database 10g - RMAN and ATA Revolutionizes Availability and Storage in Action Enables the Grid 11:00 AM 3:30 PM Oracle Dat
Slide 28: Next Steps for High Availability
Next Steps for High Availability High Availability Sessions from Oracle Thursday Database HA Demos All Four Days In The Oracle Demo Campground 8:30 AM in Moscone Room 304 Oracle Database 10g Data Real Application Clusters Warehouse Backup and Recovery: Automatic, Simple, Reliable Data Guard 8:30 AM
Slide 29: Next Steps for DW / BI
Next Steps for DW / BI Data Warehousing DB Sessions Monday Tuesday 11:00 AM 8:30 AM #40153, Room 304 #40125, Room 130 Oracle Warehouse Builder: Oracle Database 10g: New Oracle Database 10g Release A Spatial VLDB Case Study 3:30 PM 3:30 PM #40177, Room 303 #40176, Room 303 Building a Terabyte Data Wa
Slide 30: Next Steps for DW / BI
Next Steps for DW / BI Data Warehousing DB Sessions Thursday Business Intelligence and Data Warehousing Demos All Four Days 8:30 AM In The Oracle Demo Campground #40179, Room 304 Oracle Database 10g Data Warehouse Oracle Database 10g Backup and Recovery Oracle OLAP 11:00 AM #36782, Room 304 Experien
Slide 31: Reminder – please complete the OracleWorld online session survey
Reminder – please complete the OracleWorld online session survey Thank you.
Slide 32: ORACLE
ORACLE