942

“This presentation is for informational purposes only and may not be incorporated into a contract or agreement.”

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Timothy Chien Senior Product Manager Oracle Corporation

Saravanan Shanmugam Senior Architect The Hartford “This presentation is for informational purposes only and may not be incorporated into a contract or agreement.”

Best Practices for Oracle Database 10g Backup and Recovery

Session Objectives y Backup and Recovery Considerations – –

Backup and Recovery Plan RMAN Best Practices

y RMAN Case Study: The Hartford y Summary/Q&A

Backup the Database Backup and Recovery Plan y Can you afford to lose any data? –

Assess tolerance for loss (Recovery Point Objective) y Prioritize data y PITR required?

–

Backup retention policy

y What is your Recovery Time Objective? –

H/W & S/W recovery time + Oracle recovery time (media + instance recovery)

Host: ESS01 Hardware: Sun OS: Sun 2.6.x.x Disk Capacity: 3.5T Avail: 6x18 MMV: Legato

Instance Name: PROD RDBMS: 9.01 Size:: 1T BU Method: Nightly online Mount Point: /oracle/901/ora data

Test the Recovery Plan y Media Failure –

Restore database files to a new location

y Block Corruption –

Block Media Recovery, Trial Recovery, LogMiner

y User Error –

Flashback, TSPITR, LogMiner

y Disaster –

Validate restoring all files to another host

Session Objectives y Backup and Recovery Considerations – –

Backup and Recovery Plan RMAN Best Practices

y RMAN Case Study: The Hartford y Summary/Q&A

Flash Recovery Area y Unified storage location for all recovery files and recovery related activities in an Oracle Database –

Control files, online redo logs, archived logs, flashback logs, RMAN backups

y Sizing Guidelines –

Control file backups and archived logs y Estimate number of archived logs generated between backups on the busiest day and multiply their size by 2 (includes margin of error)

–

Archived logs and flashback logs y Multiply the archived log size between backups by 4

–

Incremental backups y Add their estimated sizes

–

All above and on-disk backup y Add size of the database minus the size of temp files

Oracle Suggested Strategy y Much faster backup – –

Just propagate changes to recovery area Dramatic difference for large databases

y Much faster restore – –

Copy backup files from the Flash Recovery Area Or simply use the copy in the Flash Recovery Area

y Much more reliable due to inherent reliability of disks –

Tapes not needed for normal backup/recovery, only for archiving

Recovery Manager Best Practices Incremental Backup y Differential vs cumulative incrementals y Level 1 cumulative vs level 0 full backup y Useful for NOLOGGING operations Day of the week Sun

Mon

0

1

Day of the week

Tues

Wed

Thr

Fri

Sat

Sun

Mon

1

1

1

1

0

0

1

Incremental backup level

Differential incremental

Tues

Wed

Thr

Fri

Sat

1

1

1

1

0

Incremental backup level

Cumulative incremental

Decreasing Your Backup and Recovery Time y Backup the Control File – – – –

Required to open the database Contains database structure meta-data Recovery operations are less error prone Controlfile autobackup ON

y Read-Only Tablespaces – – –

Use range partitioning on large tables Required to be backed up one time Data may reside on CD-ROM or WORM

y Offload backups to physical standby database

Recovery Manager Best Practices Channel Parallelism y Allocate one channel per tape drive or disk subsystem – –

More channels per device => impacts restore time Check with media management vendor

y CONFIGURE .. PARALLELISM – – –

Balances the number of files to be backed up across channels Evens out the disks being read across channels Attempts to make each backup set the same size

Recovery Manager Best Practices Multiplexing

y Multiplexing => number of files read by one channel, at the same time, during backup –

– – – –

Min (FILESPERSET*, MAXOPENFILES**, files read per channel) Backup vs restore performance Larger vs smaller backup set With S.A.M.E. => multiplexing of 1 Otherwise => multiplexing between 4 and 8

* In 9i+, default is 64

** In 9i+, default is (8, FILESPERSET)

Recovery Manager Best Practices Memory Buffers y Input Buffers => blocks copied & validated – –

Number and size determined by MAXOPENFILES Monitor #, size of buffers in v$backup_async_io/v$backup_sync_io

y Output Buffers => blocks written to disk/tape – – – –

Four buffers per channel Default buffer size => Disk: 1 MB, SBT: 256 KB Monitor I/O rates in v$backup_async_io/v$_backup_sync_io Set MMV tape blocksize = BLKSIZE, >= 256 KB

Recovery Manager Best Practices Memory Buffers

y Use tape I/O slaves and set LARGE_POOL_SIZE => reduce contention for shared pool y Enable disk I/O slaves only if OS does not support async y Monitor alert log for memory allocation errors

Recovery Manager Best Practices Backup Performance y Monitor disk read/write throughput using the effective_bytes_per_second column in the V$BACKUP_SYNC_IO, V$BACKUP_ASYNC_IO views, where type=’INPUT’ or type=‘OUTPUT’ y Tape performance issues – – –

Increase physical tape block size Check tape compression Monitor tape streaming => increase multiplexing

y Use DURATION backup parameter to minimize system overhead

Recovery Manager Best Practices Recovery Performance

y Minimize archive log application by using incrementals y Use block media recovery for isolated block corruptions y Keep sufficient number of archived logs on disk y Tune database for I/O, DBWR performance, CPU utilization

Recovery Manager Best Practices y Retention Policy – –

Defines how long backups or how many backups should be retained Tape recycle should be determined by RMAN

y Tag (automatically set in 9.2+) – –

Symbolic name for a backup Simplifies the capability to restore a specific backup by referring to a TAG

y Recovery Catalog – – –

Centralize backup meta-data Stored scripts Backup Options: Use the control file, Oracle export, or Physical Standby

Transportable Tablespace (TTS) y

Mechanism to quickly move a tablespace across Oracle databases –

y

Most efficient means to move bulk data between databases –

–

Supports cross platform transport via RMAN CONVERT File is simply copied at OS level + import tablespace metadata

Benefits – –

Publish structured data to multiple subscribers (data marts) Maintain/update reporting database via RMAN incrementally updated backups

File 1 File 2

OS copy expdat.dmp

File 1 File 2

expdat.dmp 24

Session Objectives y Backup and Recovery Considerations – –

Backup and Recovery Plan RMAN Best Practices

y RMAN Case Study: The Hartford y Summary/Q&A

The Hartford Overview * Founded in 1810 and a Fortune 100 company with $22.7 yearly revenue(2004) * Roughly about 30 databases, sized between 7.5 GB and 30 GB * HP-UX servers and EMC DMX-3000 storage * Mostly Data warehouse and DSS systems

Problem Statement * Need subsets of production data on secondary databases to offload reporting and analysis tasks (mainly, data marts) * Data subsets must be easily updated with production changes on monthly basis * Do this easily, quickly, and cost-effectively

Comparing Solutions * Hardware Mirroring * Oracle Streams * Data Guard – Logical Standby * Transportable Tablespace (TTS) • Copy datafiles to the target database • Generate tablespace metadata information from source database and plug in the datafiles in the target database

Transportable Tablespace (TTS) Procedure * Fit the best with our requirements: • • •

Allows specific data subsets to be replicated Only needs commodity, rather than higher-end storage Leverages existing Oracle database technology

* However, TTS requires copying data files to secondary database filesystem on each data refresh • •

Time consuming to copy large data files Data files must be in read-only during the copy

Our Solution - Incrementally Updated TTS (ITTS) * Utilize RMAN: • Incrementally updated backups - Take initial image copy of data files - Take subsequent incremental backups - Merge incrementals with data file image copies to create new image copies, on regular basis

• Block change tracking for fast incremental backups - Reads only changed blocks, instead of whole data file on incremental backup

• Recovery catalog - Keep longer history of backups vs. control file, e.g. monthly or quarterly incremental backups

* Our Solution: Use RMAN to incrementally update transported tablespaces

ITTS Overview Initial Month Source Database

Target Database Tablespace Copy

NFS Mounted Filesystems

Subsequent Months Source Database

Target Database Incremental Backups

Changed Blocks

Change Tracking File

NFS Mounted Filesystems

Changed Blocks Copy

ITTS Procedure - First Month 1. One time image copy of datafiles performed after placing tablespace in read-only mode run { allocate channel d1 device type disk format '/prepsp_0006/appl/oracle/prepsp/db1.dbf'; allocate channel d2 device type disk format '/prepsp_0006/appl/oracle/prepsp/db2.dbf'; BACKUP INCREMENTAL LEVEL 1 TAG "TEST9_BACKUP" for recover of copy with tag "TEST9_BACKUP" tablespace test1; }

2. Plug in the tablespace in destination database via network > impdp readonly/readonly DIRECTORY=exp_dir NETWORK_LINK=dtest2.world TRANSPORT_TABLESPACES=test1 TRANSPORT_FULL_CHECK=n TRANSPORT_DATAFILES= '/prepsp_0006/appl/oracle/prepsp/db1.dbf', '/prepsp_0006/appl/oracle/prepsp/db2.dbf'

ITTS Procedure - Subsequent Months 3. Drop tablespace in destination database with only ‘INCLUDING CONTENTS’ option (data files are preserved) SQL> drop tablespace including contents;

4. Check for any new datafiles and create image copy in the destination database location (tablespace must be in in read-only mode during image copy) run { allocate channel d1 device type disk format '/prepsp_0006/appl/oracle/prepsp/db3.dbf'; BACKUP INCREMENTAL LEVEL 1 TAG "TEST9_BACKUP" for recover of copy with TAG "TEST9_BACKUP" datafile 3; }

ITTS Procedure - Subsequent Months 5. Perform incremental backup and merge it with datafiles in destination database (tablespace must be in read-only mode during backup) run { allocate channel d1 device type disk format '/prepsp_0005/appl/oracle/prepsp/db1%t.dbf'; allocate channel d2 device type disk format '/prepsp_0005/appl/oracle/prepsp/db2%t.dbf'; BACKUP INCREMENTAL LEVEL 1 TAG "TEST9_BACKUP" for recover of copy with TAG "TEST9_BACKUP" tablespace test1; recover copy of tablespace test1 with TAG "TEST9_BACKUP"; }

6. Plug in the tablespace in destination database > impdp readonly/readonly DIRECTORY=exp_dir NETWORK_LINK=dtest2.world TRANSPORT_TABLESPACES=test1 TRANSPORT_FULL_CHECK=n TRANSPORT_DATAFILES= '/prepsp_0006/appl/oracle/prepsp/db1.dbf', '/prepsp_0006/appl/oracle/prepsp/db2.dbf'

ITTS Restrictions * The transported tablespace in destination database should always be in read-only mode * OMF-style format string should not be used when making initial image copies • If OMF file is used, ‘DROP TABLESPACE ..’ will drop metadata and data files.

ITTS Benefits * Much less time spent in copying data * Tablespaces can be easily transferred between database servers in different physical locations or cities •

Via NFS mounted filesystems or cluster filesystems

* Space savings when compared to hardware-based copy methods • •

Hardware-based methods work at volume-level vs. data filelevel Volume has fixed size (400 GB) and only holds tablespaces to be transported (100 GB) => 300 GB of allocated, but unused space

Session Objectives y Backup and Recovery Considerations – –

Backup and Recovery Plan RMAN Best Practices

y RMAN Case Study: The Hartford y Summary/Q&A

References y Best practices papers on OTN -> Database -> Availability – – –

Tuning Oracle9i Recovery Manager RMAN Backup and Recovery Optimization HP-Oracle 3+ TB/hr RMAN Backup Benchmark

y Customer case studies on OTN –

Incrementally Updating TTS using RMAN

–

Fannie Mae: Breaking the 1 TB/Hour Backup Barrier

y Oracle Backup Solutions Program (BSP) y Recovery Manager Discussion Forum –

http://otn.oracle.com --> Discussion Forum --> Recovery Manager

Additional References y Using RMAN & RAC – –

Real Application Cluster Administration Guide, Ch. 7 & 8 Metalink Note 243760.1: RMAN: RAC Backup and Recovery using RMAN

y Using RMAN & Data Guard – –

Using Recovery Manager with Oracle Data Guard in Oracle9i Using Recovery Manager with Oracle Data Guard in Oracle Database 10g

y Maximum Availability Architecture (MAA) White Papers

Summary y Implement B&R best practices methodically – – –

Assess B&R service levels, RPO, RTO, data criticality Document all operational procedures Test, test, test – media, block corruption, human error, disaster recovery

y Put Oracle Database 10g to the test –

RMAN – fully supported Oracle backup solution y Backup to disk as cache y Optimized incrementals, incrementally updated backups

–

Flashback – near-instant, fine-granular recovery y Flashback Query, Flashback Transaction, Flashback Drop, Flashback Table, Flashback Database

For more information on Oracle High Availability, Disaster Protection, Backup & Recovery, and Storage Management technology go to: http://otn.oracle.com/deploy/availability/

Q U E S T I O N S A N S W E R S