1 / 32

Oracle Database 10 g Data Warehouse Backup & Recovery: Automatic, Simple, And Reliable

Session id: 40179. Oracle Database 10 g Data Warehouse Backup & Recovery: Automatic, Simple, And Reliable. George Lumpkin Director Product Management Oracle Corporation Tammy Bednar Sr. Product Manager Oracle Corporation. Agenda. Data warehouse characteristics

foy
Download Presentation

Oracle Database 10 g Data Warehouse Backup & Recovery: Automatic, Simple, And Reliable

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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 • Best Practice#5 – Exclude Tablespace

  3. You can back up your data warehouse in exactly the same way as you back up your OLTP database.But …

  4. 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 customer orders, so it can never lose any transactions. Does you data warehouse require zero data loss? The best practices for DW backup/recovery should leverage the unique characteristics of your data warehouse

  5. 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 of data warehouse may be static • Older data generally does not change

  6. 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 • Best Practice#5 – Exclude Tablespace

  7. Oracle Backup & RecoveryWhat should you backup? • Oracle Database Architecture • Datafiles • Archive logs • Control file • Configuration Files • Oracle Database Architecture • Datafiles • Archive logs • Control file • Configuration Files • Oracle Database Architecture • Datafiles • Archive logs • Control file • Configuration Files • Oracle Database Architecture • Datafiles • Archive logs • Control file • Configuration Files • Oracle Database Architecture • Datafiles • Archive logs • Control file • Configuration Files

  8. Backup and Recovery Methods and Tools • Recovery Manager • Oracle’s tightly integrated utility for creating, managing, restoring and recoveringdatabases • Oracle Enterprise Manager • GUI interface to Recovery Manager • Oracle Data Pump (export/import) • Logical database backups • User Managed • Database is backed up and restored manually using OS commands

  9. Over 25 Man Years of Engineering Technology provides: Intimate knowledge of database file formats and recovery procedures Manages and automates the backup, restore, and recovery process Creates and maintains backup policies Catalogs all backup and recovery activities Operates on-line and in parallel for fast processing Corrupt block detection during backup and restore and the ability to validate backups Integrated with Enterprise Manager & 3rd Party Tools Recovery Manager: Oracle’s Backup & Recovery Utility Recovery Manager Enterprise Manager & 3rd Party Tools Media Management Layer Network

  10. Backup Management • List RMAN backups • View files included in a backup set • Perform maintenance • Remove obsolete backups with a single click

  11. 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-time recovery • Automatic channel failover on backup or restore • Tablespace Rename • Drop Database

  12. Oracle Database 10gData 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/Metadata movement between databases • Test Development Production • Changing HW/OS platforms, version releases, character sets • Data archiving • Logical backup of full or partial database

  13. 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 • Best Practice#5 – Exclude Tablespace

  14. 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 infrastructure, and business change

  15. What is your Recovery Point Objective? RPO Backups are run at scheduled interval. RTO1 Critical data Is available & Database is open. RTO2 Remainder of Data is re-inserted. No backup of data Restore Backup Data is re-inserted via ETL Time mm Hours mm Hours -nn Hours 0 Automated recovery procedures Manual recovery procedures Normal procedures Normal procedures

  16. How do you backup all of that data? • Backing up to Tape • The time to backup to tape is a function of your hardware • Divide and Conquer • Breakup the database backup over the course of several days • RMAN provides automatic ‘windowing’ capability # of tape devices + tape library = time to backup

  17. 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 • Best Practice#5 – Exclude Tablespace

  18. DW Recovery MethodologyBest 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 last good backup • Keep more archive logs on disk using archive log compression

  19. DW Recovery MethodologyBest Practice#2 – Use RMAN • Extensive Reporting • Easily integrates with Media Managers • Incremental Backups • Block Media Recovery (BMR) • Downtime Free Backups • Archive log validation and management • Backup and Restore Validation • Corrupt Block Detection • Backup and Restore Optimization • Trouble Free Backup and Recovery

  20. Jan Feb Mar Apr May Jun Jul Read-only Tablespace Read-write Tablespace Read-write Tablespace Apr May Jun Read-only Tablespace DW Recovery MethodologyBest 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 • Caveat: This strategy improves backup times, but not recovery times

  21. DW Recovery MethodologyBest 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 nologging operations • Devise a strategy for recovering data loaded using nologging operations

  22. DW Recovery MethodologyBest Practice#4 – Nologging Operations • Strategy #1: Recover nologging operations via ETL • Take a weekly backup of the data warehouse • Every night, store/backup a copy of the raw data files which are loaded into the data warehouse • To recover, restore the backup of the data warehouse, then ‘re-run’ the nightly ETL processes to roll the data warehouse forward • Pros • Minimal resources required to support backup scenario • Cons • Manual process of storing and replaying ETL

  23. DW Recovery MethodologyBest Practice#4 – Nologging Operations • Strategy #2: Recover nologging operations via Incremental Backups • Take a weekly backup of the data warehouse • Every night, do an incremental backup of all modified tablespaces after all nologging operations are completed • To recover, restore the backup of the data warehouse, then apply the nightly incremental backups to roll the data warehouse forward • Pros • Can be completely managed via RMAN • Simple and complete backup of new data • Cons • Backup required nightly following the ETL window

  24. DW Recovery MethodologyBest 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 temporary data sets • Not every tablespace needs to be backed-up as frequently • Some data may be less critical than other data • Weekly backups instead of nightly backups

  25. 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 • Best Practice#5 – Exclude Tablespace

  26. 11:00 AM How Oracle Database 10g Revolutionizes Availability and Enables the Grid 3:30 PM Oracle Recovery Manager (RMAN) 10g: Reloaded 5:00 PM Proven Techniques for Maximizing Availability 8:30 AM Oracle Database 10g - RMAN and ATA Storage in Action 11:00 AM Oracle Data Guard: Maximum Data Protection at Minimum Cost 1:00 PM Oracle Database 10g Time Navigation: Human-Error Correction 4:30 PM Data Guard SQL Apply: Back to the Future Next Steps for High AvailabilityHigh AvailabilitySessions from Oracle Tuesday in Moscone Room 304 Wednesday in Moscone Room 304 For More Info On Oracle HA Go To http://otn.oracle.com/deploy/availability/

  27. Database HA Demos All Four Days In The Oracle Demo Campground Real Application Clusters Data Guard Database Backup & Recovery Flashback Recovery LogMiner, Online Redefinition, and Cross Platform Transportable Tablespaces Next Steps for High AvailabilityHigh AvailabilitySessions from Oracle Thursday 8:30 AM in Moscone Room 304 Oracle Database 10g Data Warehouse Backup and Recovery: Automatic, Simple, Reliable 8:30 AM in Moscone Room 104 Building RAC Clusters over InfiniBand For More Info On Oracle HA Go To http://otn.oracle.com/deploy/availability/

  28. 11:00 AM #40153, Room 304 Oracle Warehouse Builder: New Oracle Database 10g Release 3:30 PM #40176, Room 303 Security and the Data Warehouse 4:00 PM #40166, Room 130 Oracle Database 10g SQL Model Clause 8:30 AM #40125, Room 130 Oracle Database 10g: A Spatial VLDB Case Study 3:30 PM #40177, Room 303 Building a Terabyte Data Warehouse, Using Linux and RAC 5:00 PM #40043, Room 104 Data Pump in Oracle Database 10g: Foundation for Ultrahigh-Speed Data Movement Next Steps for DW / BIData Warehousing DB Sessions Monday Tuesday For More Info On Oracle BI/DW Go To http://otn.oracle.com/products/bi/db/dbbi.html

  29. Next Steps for DW / BIData Warehousing DB Sessions Business Intelligence and Data Warehousing Demos All Four Days In The Oracle Demo Campground Thursday 8:30 AM #40179, Room 304 Oracle Database 10g Data Warehouse Backup and Recovery 11:00 AM #36782, Room 304 Experiences with Real-Time Data Warehousing using Oracle 10g 1:00PM #40150, Room 102 Turbocharge your Database, Using the Oracle Database 10g SQLAccess Advisor Oracle Database 10g Oracle OLAP Oracle Data Mining Oracle Warehouse Builder Oracle Application Server 10g For More Info On Oracle BI/DW Go To http://otn.oracle.com/products/bi/db/dbbi.html

  30. Reminder – please complete the OracleWorld online session surveyThank you.

More Related