40179_Bednar.ppt Oracle Database Upgrade Assistant

40179_Bednar.ppt Oracle Database Upgrade Assistant

2. 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
Session id: 40179

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

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

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

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

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

8. Oracle Backup & Recovery
What 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





9. 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
–Database is backed up and restored
manually using OS commands

10. Recovery Manager:
Oracle’s Backup & Recovery Utility
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
Media Management Layer
Enterprise ManagerEnterprise Manager
& 3& 3
rdrd
Party Tools Party Tools
Network
RecoveryRecovery
ManagerManager

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

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

13. 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/Metadata movement between databases
Test Development Production
–Changing HW/OS platforms, version releases, character
sets
–Data archiving
–Logical backup of full or partial database

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

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

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

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

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

19. 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 last good backup
Keep more archive logs on disk using archive
log compression

20. 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.Backup and Restore Optimization
10.Trouble Free Backup and Recovery

21. 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
Caveat: This strategy improves backup times, but
not recovery times
AprMayJunJanFebMar
Read-only Tablespace Read-write Tablespace
Jul
Read-write TablespaceRead-only Tablespace
AprMayJun

22. 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
DW Recovery Methodology
Best Practice#4 – Nologging Operations

23. 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 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
DW Recovery Methodology
Best Practice#4 – Nologging Operations

24. 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 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
DW Recovery Methodology
Best Practice#4 – Nologging Operations

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

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

27. Next Steps for High Availability
High Availability Sessions from Oracle
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
Wednesday in Moscone Room 304Tuesday in Moscone Room 304
For More Info On Oracle HA Go To http://otn.oracle.com/deploy/availability/

28. Next Steps for High Availability
High Availability Sessions from Oracle
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
Thursday
For More Info On Oracle HA Go To http://otn.oracle.com/deploy/availability/
Real Application Clusters
Data Guard
Database Backup & Recovery
Flashback Recovery
LogMiner, Online Redefinition, and
Cross Platform Transportable
Tablespaces
Database HA Demos All Four Days
In The Oracle Demo Campground

29. Next Steps for DW / BI
Data Warehousing DB Sessions
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
TuesdayMonday
For More Info On Oracle BI/DW Go To http://otn.oracle.com/products/bi/db/dbbi.html

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

Thursday
Oracle Database 10g
Oracle OLAP
Oracle Data Mining
Oracle Warehouse Builder
Oracle Application Server 10g
Business Intelligence and Data
Warehousing Demos All Four Days
In The Oracle Demo Campground
For More Info On Oracle BI/DW Go To http://otn.oracle.com/products/bi/db/dbbi.html
Next Steps for DW / BI
Data Warehousing DB Sessions

31. Reminder –
please complete the
OracleWorld online session
survey
Thank you.