How to Best Configure, Size, and Monitor the Oracle Database Fast Recovery Area

(1)

ORACLE PRODUCT

LOGO

Presenting with

How to Best Configure, Size, and Monitor the Oracle Database Fast Recovery Area

(2)

(3)

Fast Recovery Area – Key MAA Element

RMAN + Secure Backup + Flashback + Data Guard

Integrated backup & recovery, continuous data protection, disaster recovery

RMAN Secure Backup Flashback Data Guard

Primary Database

Active Standby Database

O l Data Guard Redo Shipping

RMAN, Flashback Oracle Secure Backup T RMAN, Flashback

Fast Recovery Area

(Fl hb k l

Database A

Fast Recovery Area

(B k Fl hb k l

Database A

Tape Drive

(Flashback logs

(4)

Fast Recovery Area (FRA)

Stores and manages all recovery-related files Stores and manages all recovery related files

Fast

Recovery Daily Apply

Validated Incremental

Weekly Archive

To Disk / Tape Database

(5)

(6)

Agenda

• Oracle Fast Recovery Area (FRA) overview

• How to best configure, size, and monitor the FRA

– Files to keep in FRA & relationship to Backup policy and Flashback – Configuring FRA Size and Location & EM view of the FRA

(7)

Files Necessary for Fast Recovery

Transient Files

• Archived Redo logs

Permanent Files

• Multiplexed Control File

• Backup files and autobackups

• Flashback logs (if FB enabled)

• Multiplexed Online Redo

• Guaranteed Restore Points

• Oracle knows when to delete or trim

• Age-out or backed-up to tertiary

• Complete the set for fast recovery

• Could fill up FRA so DBA must monitor

(8)

Best Practice: Generous FRA on ASM

All l t d fil i F t R A

• All recovery-related files in Fast Recovery Area

(9)

Configuring the Fast Recovery Area

• Size

– backups, archived logs, redo, flashback…

(10)

Two Configuration Parameters to create FRA

Set Space Quota and Storage location

Di k Q t

Set Space Quota and Storage location

• Disk Quota: DB_RECOVERY_FILE_DEST_SIZE

– Per Database; provision for all permanent and transient files

Location: DB RECOVERY FILE DEST

• Location: DB_RECOVERY_FILE_DEST – File system directory or ASM group

Set via DB Configuration Assistant, or DB initialization parameter file, or SQL (ALTER SYSTEM SET)

(11)

Fast Recovery Area in Enterprise Manager

Location Size Usage Breakdown

Vi l t

Location, Size, Usage Breakdown

(12)

Agenda

– Given Backup & Flashback policy, count files FRA needs to keep – How to get number and size of files. Multiply and add to FRA size

(13)

FRA Size: Summing Files in FRA by Example

2 Comprehensive Scenarios Show How to Sum Up All Files • Both scenarios: 7-day recovery window

RMAN> configure retention policy to recovery window of 7 days;

2 Comprehensive Scenarios Show How to Sum Up All Files

RMAN> configure retention policy to recovery window of 7 days;

• Scenario #1: all recovery files for window are in FRA

• Scenario #2: adds FRA backup to external storage to 1

• Flashback enabled in both scenarios

– We do not take into account disk mirroring –a good practice, omitted for simplicity

(14)

7-Day Window, No Tape - Scripts

Daily: Roll forward image copy and take incremental backup

# d il di k (k 7 d ld i )

Daily: Roll forward image copy and take incremental backup

# daily disk (keeps 7-day-old image copy)

recover copy of database with tag DB_BACKUP ntil time 's sdate 8'

until time 'sysdate-8';

backup incremental level 1 for recover of copy with tag DB BACKUP database;

(15)

7-Day Window, No Tape – Sum up needed files

FRA disk space needed add these file sizes:

= Control File size

O li R d L i

FRA disk space needed – add these file sizes:

+ Online Redo Log size

+ size of Archived Logs (for 8 days) + Database size (minus temp files)

+ size of Incremental Backups (for 8 days)

+ Flashback Logs size (initially Redo rate X FB retention time)

(16)

7-Day Window, with Tape - Scripts

Daily incremental and back up FRA to tape every 2 days

# Daily disk (7 day window using tape)

f d t b ith t DB BACKUP

Daily incremental and back up FRA to tape every 2 days

recover copy of database with tag DB_BACKUP; backup incremental level 1 for recover of

cop ith tag DB BACKUP database

copy with tag DB_BACKUP database;

# Tape backup executed once every 2 days

b k

backup recovery area;

(17)

7-Day Window, with Tape – File Count

FRA disk space needed add these sizes

= Control File size

O li R d L i

FRA disk space needed – add these sizes

+ Online Redo Log size

+ size of Archived Logs (for 3 days – tape every 2) + Database size (minus temp files)

+ size of Incremental Backups (for 3 days – tape)

+ Flashback Logs size (initially Redo rate X FB retention time)

(18)

Counting Files in FRA – Notes

• Easy to account for mirroring or multiple copies of logs

• Easy to account for mirroring, or multiple copies of logs

– multiply space requirement as needed (full FRA, or log type)

(19)

Counting Files in FRA – Recap

Function of Recovery Window Flashback Tape Use

O l i li itl d l t b l t ( t i ) fil i FRA

Function of Recovery Window, Flashback, Tape Use

• Oracle implicitly deletes obsolete (or trims) files in FRA

– Tertiary storage not part of FRA, hence explicit sbt delete command

• Tradeoff: use less FRA space for slower (tape) recovery

Both scenarios provide the same 7 day recovery window – Both scenarios provide the same 7-day recovery window

What about Flashback? … What about Flashback?

(20)

Configuring Flashback

Flashback Logs Managed by Oracle in FRA

• If Flashback is enabled, must specify Flashback window

DB FLASHBACK RETENTION TARGET

Flashback Logs Managed by Oracle in FRA

– DB_FLASHBACK_RETENTION_TARGET

• To check that Flashback window is met by logs in FRA

– SELECT oldest flashback scn

– SELECT oldest_flashback_scn,

oldest_flashback_time

(21)

FRA and Flashback Queries

Location, quota, in use/reclaimable space, number of files

SELECT * FROM v$recovery file dest; SELECT FROM v$recovery_file_dest;

For each file type, percent of FRA space it uses and is reclaimable and number of files of that type

reclaimable and number of files of that type

SELECT * FROM v$recovery_area_usage;

Estimated space used by Flashback logs Estimated space used by Flashback logs

SELECT estimated_flashback_size FROM

(22)

FRA Size: Queries to Determine File Sizes

B k d Fl hb k li i d fi hi h/h

• Backup and Flashback policies define which/how many files are kept for how long in FRA –

We already learned by example how to sum up those files – We already learned by example how to sum up those files

• To obtain FRA size need the size of each file

• To obtain FRA size, need the size of each file

(23)

FRA Size – How to Determine File Sizes

Using SQL Queries

Vi l t

Using SQL Queries

(24)

Sizes: Image Copy, Online Redo, Control File

• Size of Database Image Copy File

SELECT SUM(bytes) fsize FROM V$DATAFILE; SELECT SUM(bytes) fsize FROM V$DATAFILE;

• Size of Online Redo Log

SELECT SUM (bytes*blocksize) fsize FROM V$LOG; SELECT SUM (bytes blocksize) fsize FROM V$LOG;

• Size of Control File

SELECT (block size * file size blks) fsize

S C (b oc _s e e_s e_b s) s e

(25)

Sizes (2): Incremental Backups

• Size of Incremental Backups Generated so far

SELECT end_time, output_bytes

FROM V$RMAN_BACKUP_JOB_DETAILS_ _ _

WHERE input_type = 'DB INCR' ORDER BY end_time DESC;

(26)

Sizes (3): Archived Logs generated per day

SELECT TO_CHAR(first_time,'DD-MON-YYYY') "Date", SUM(bytes)

FROM (SELECT UNIQUE

blocks * block_size bytes, thread#,

sequence#,

resetlogs_change#, first time

first_time

FROM V$ARCHIVED_LOG$)

(27)

Sizes (4): Flashback Logs

• If Flashback is enabled

SELECT estimated_flashback_size, FROM V$FLASHBACK DATABASE LOG; FROM V$FLASHBACK_DATABASE_LOG;

– See also the value of the current flashback logs via:g

(28)

Agenda

– Additional considerations - multiple databases and Exadata – The DBA needs to remain involved

(29)

Configuring the FRA – Multiple Databases

To share one FRA across multiple Databases

S t l f CO S ll

• Set same value for DB_RECOVERY_FILE_DEST on all

• Use different DB_UNIQUE_NAMEs (if undefined, different DB NAME )

DB_NAMEs)

(30)

Configuring the FRA – Exadata Considerations

• FRA must be on local Storage – ASM diskgroup RECO

• With Disk Backups on External Storage RECO setWith Disk Backups on External Storage, RECO set to 20% of total disk space

• No External Backups, RECO is 60% of disk spaceNo External Backups, RECO is 60% of disk space

• File Locations

– Archived and Flashback Logs and all Backup go in FRAg p g

– Control Files and Redo Logs in High Redundancy Disk group

(31)

“Bad” Practices for Fast Recovery Area

A few “DO NOTs”

1. DO NOT use FORMAT to back up files to FRA

2 DO NOT specify LOG ARCHIVE DEST n to FRA location to archive files

A few DO NOTs

2. DO NOT specify LOG_ARCHIVE_DEST_n to FRA location to archive files to FRA. Instead use 'USE_DB_RECOVERY_FILE_DEST' , e.g.,

– LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST‘

3 DO NOT delete files from FRA using OS commands or asmcmd utility

3. DO NOT delete files from FRA using OS commands or asmcmd utility

– Instead use RMAN’s DELETE command

4. DO NOT specify DB_RECOVERY_FILE_DEST_SIZE to more than the il bl di k

available disk space

5. DO NOT keep Guaranteed Restore Points around forever. They take up disk space permanently

(32)

The DBA Must Remain Involved

FRA automates a lot, but the DBA is still in the loop:

O ti i i ( b d fi iti )

• Optimizing resource usage (scarce by definition)

• Tracking changes – in activity volume, in RTO/RPO

--d --dj ti / i iti i

and adjusting / prioritizing

O l k f i

… Oracle takes care of routine space management

(33)

How to Best Configure Size and

How to Best Configure, Size, and

Monitor the Oracle Database Fast

Recovery Area

Presented by: Andy Colvin Principal Consultant Enkitec

October 6, 2011

(34)

About Me/Enkitec

y Who am I?

y Principal Consultant at Enkitec

y Worked with Oracle dating back to version 7

y What is Enkitec?

y Oracle-centered Platinum Partner based in Irving, TX y Database consultants averaging over 15 years Oracle

experience

Making a name for ourselves in the Exadata world Making a name for ourselves in the Exadata world

(35)

Why I Use the FRA

y Ease of use

y Allows for DBA-level space management

y Provides for easier standardization of environments

y Uniform place for all recovery-related files

SQL> select NAME (SPACE USED/SPACE LIMIT)*100 "% USED" from V$RECOVERY FILE DEST; SQL> select NAME, (SPACE_USED/SPACE_LIMIT)*100 "% USED" from V$RECOVERY_FILE_DEST;

NAME % USED --- ---/u03/fast_recovery_area 85.87

(36)

Two Real World Customers

Customer #1 – Document Processing

y $350M Revenue, 8,500 employees y Services 50% of Fortune 100Services 50% of Fortune 100

Customer #2 – Energy Utility

(37)

Customer #1 – Document Processing

Customer #1 Document Processing

y 15TB database, production environment, p

y Repository for document management

y 270 GB of redo generated daily

(38)

#1

–

Accelerated Database Growth

#1 Accelerated Database Growth

7x Growth in 3 Years

16.000 12.000 14.000 16.000 e -T B 6.000 8.000 10.000 tabase Siz e 2.000 4.000 Da t

(39)

#1 – Document Processing - Solution

#1 Document Processing Solution

y FRA originally sized at 2TB (based on 7 day archive log retention)g y ( y g ) y Monitored database growth with OEM

y Monitored redo generation through Data Guard console in OEM y Increased db_file_recovery_dest_size to match the growth of the

database monthly

(40)

#1 – Document Processing - Solution

#1 Document Processing Solution

Data Guard Considerations

y Standby environment created identical to primary

y Exadata Exadata ÆÆ ExadataExadata

y Same size diskgroups for +DATA and +RECO

y All logs sent to FRA y All logs sent to FRA

(41)

#1 – Backup Commands

#1 Backup Commands

#RMAN archivelog backup command

# g p

backup archivelog all not backed up

y Avoid “delete obsolete” through RMAN – Allow the FRA to

manage files itself

y Example of FRA automatic file management:

(42)

#1 – FRA file management

g

RMAN> backup archivelog all not backed up;

skipping archived log file <archive log>; already backed on <date> skipping archived log file <archive_log>; already backed on <date> skipping archived log file <archive_log>; already backed on <date> skipping archived log file <archive log>; already backed on <date>

s pp g a c ed og e a c e_ og ; a eady bac ed o date

...

input archived log thread=1 sequence=7 RECID=26 STAMP=762905532p g q

(43)

Customer #2 – Energy Utility

Customer #2 Energy Utility

y Development databasep

y Customer billing transaction system – will service 5 million

customers

y 600GB database

y Backups going to FRA and tape y Weekly data refresh

(44)

#2 – Energy Utility - Solution

#2 Energy Utility Solution

y FRA sized at 150GB s ed at 50G

y Sizing based on:

Fl hb k l

y Flashback logs

y Archive log generation

y Database backups

(45)

#2 – Energy Utility - Solution

#2 Energy Utility Solution

Flashback Methodology

y Flashback retention set to 1 week

y Created a guaranteed restore point before each refresh, data Created a guaranteed restore point before each refresh, data

refreshed weekly

y Monitored the usage of the FRA through OEM reports, alerting

based on space available based on space available

y After process was repeated a few times, optimal FRA size was

(46)

#2 – Energy Utility - Solution

#2 Energy Utility Solution

Monitoring FRA Usage

SQL> SELECT * FROM V$FLASH RECOVERY AREA USAGE WHERE PERCENT SPACE USED > 0 ; SQL> SELECT FROM V$FLASH_RECOVERY_AREA_USAGE WHERE PERCENT_SPACE_USED > 0 ;

FILE_TYPE % USED % RECLAIM # FILES

CONTROL FILE .24 0 1 REDO LOG 3.72 0 3 ARCHIVED LOG 31.11 26.82 31 BACKUP PIECE 33.17 0 15 FLASHBACK LOG 17.6 0 42

(47)

Two Real World Customers

Customer #1 – Document Processing Customer #2 – Energy Utility

What we learned from each

y Configuring and sizing FRA was quick and easy

y Monitoring usage of the FRA is imperative especially with y Monitoring usage of the FRA is imperative, especially with

rapid database growth

(48)

FRA On Exadata and ODA

y Planning the size of the FRA is even more important with

Oracle’s engineered systems Oracle s engineered systems

y Disk sizing is more difficult to perform on these systems after

they are in place because the diskgroups share the same they are in place because the diskgroups share the same physical disks

y If you have the space leave plenty for RECO so that you can y If you have the space, leave plenty for RECO, so that you can

utilize image copies and longer flashback/backupset retention period

(49)

Questions?

Andy Colvin, Enkitec

http://www.enkitec.com http://blog.oracle-ninja.com

(50)

Best Practice: Provision, Monitor, Repeat

• Provision per Oracle’s recommendations

• Monitor your database’s steady state

Al t f k l d

– Also account for peak loads

– Understand how FRA deletes files and handles scarce or exhausted resources, and how a DBA can intervene,

(51)

Summary: FRA for Simple Fast Recovery

• Create FRA on ASM and let it manage all recovery files

Make FRA part of your High Availability strategy – Make FRA part of your High Availability strategy

• Provision Monitor RepeatProvision, Monitor, Repeat

– Understand how FRA works and when DBA action is necessary

(52)

Q&A

Q&

(53)

Resources

• OTN HA Portal:

http://www.oracle.com/goto/availability

• Maximum Availability Architecture (MAA):Maximum Availability Architecture (MAA):

http://www.oracle.com/goto/maa • MAA Blogs:

http://blogs oracle com/maa http://blogs.oracle.com/maa • Exadata on OTN:

http://www.oracle.com/technetwork/database/exadata/index.html • Oracle HA Customer Success Stories on OTN:

http://www.oracle.com/technetwork/database/features/ha-casestudies-098033.html

(54)

Key HA Sessions, Demos, Labs by

Oracle Development

Monday, 3 Oct –Moscone South * Wednesday, 5 Oct –Moscone South *

11:00a Auto Detect, Prevent and Repair Data Corruptions, Rm 102

12:30p Future of Oracle Exadata, Rm 104

12:30p RMAN: Not Just for Backups Anymore, Rm 304

2:00p Extreme Data Management, Moscone North Hall D

5 00 O l Hi h A il bilit S t O i R 104

10:15aOracle Active Data Guard - Lessons Learned, Rm 102

1:15pData Guard for Planned Maintenance, Rm 102

1:15pUnderstanding Oracle RAC Internals, Rm 103

1:15pClone Oracle with CloneDB and Direct NFS, Rm 270

5:00p Oracle High-Availability System Overview, Rm 104

5:00p GoldenGate Product Update and Strategy, Intercontinental-Sutter

Tuesday, 4 Oct –Moscone South *

10:15a Oracle Secure Backup - Best practices, Rm 304

Thursday, 6 Oct –Moscone South *

9:00aExadata Backup and Recovery, Rm 304

10:30aDeduplication and Compression for Backups, Rm 304

12 00 G S / 103

11:45a Oracle Exadata Technical Deep Dive, Rm 104

3:30p RMAN & Data Guard: Seven Cool Tips from Oracle, Rm 304

3:30p Consolidation on Oracle Exadata, Rm 103

12:00pData Guard Switchover / Failover, Rm 103

3:00pConfigure, Size, Monitor Fast Recovery Area, Rm 304

3:00pPeopleSoft with Active Data Guard, Moscone West 2022

Hands on Labs Marriott Marquis Salon 14 / 15

Demos Moscone South DEMOGrounds Hands-on Labs Marriott Marquis, Salon 14 / 15

Monday, Oct 3, 5:00 pm - 6:00 pm Oracle Active Data Guard

Tuesday, Oct 4, 10:15 am - 11:15 am Oracle Active Data Guard

Demos Moscone South DEMOGrounds

Mon & Tue 9:45a - 5:30p;Wed 9:00a - 4:00p

Maximum Availability Architecture (MAA) Exadata

(55)

Latin America 2011

December 6–8, 2011

Tokyo 2012

(56)

(57)