Transcript RMAN in the Trenches: To Go Forward, We Must Backup Philip Rice Univ.

RMAN in the Trenches:
To Go Forward, We Must Backup
Philip Rice
Univ. of California Santa Cruz
NoCOUG Feb. 8, 2007
1
Overview








Motivation: Few RMAN sessions, & Giving Back
Experience Level: Beginner & Intermediate
Backup Types
Retention: Window vs. Redundancy – sizing
Compression, Optimization
Disk and tape -- MML, SBT
The Good, The Bad, The Ugly (a sampling)
A&Q – not Jeopardy
2
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Backups Not Glamorous, But Essential

From AskTom site, June 2006, responding to
question about up to the minute recovery, with
DB in NOARCHIVE mode...
 Lack of awareness about noarchive made him
sad, or maybe mad...
 "There is precisely one thing a DBA cannot get
wrong. Only one thing. Just one thing. That one
thing is 'recovery'. “
3
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Backup Types: Basics

Cold backups simple, not worth doing w/ RMAN
 Two formats, both check for corruption and
capture completion info for recovery:
 Backupset: several types of compression
 Image Copy: same as OS copy, no
compression, more suited to fast recovery
4
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Incremental Level 0 & 1

Incrementals (vs. Full): space saver
 Level 0: for RESTORE – datafile content
 Level 1: for RECOVER – block changes
 9i has Level 0 to 4, 10g has only 0 and 1
 If most blocks change daily, Incremental may not
be best, unless done with Image Copy in 10g
 Example:
Level 0 = 50 GB every Saturday,
Level 1 = 10 GB other days
5
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Incremental Level 0 & 1
Example: Daily space used for Incremental Level 0 and 1
50
40
30
20
10
0
1
2
3
4
5
6
7
8
9
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
Level 0 every 7 days (50 GB),
Level 1 other days (10 GB)
6
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Retention: Redundancy

Retention Policy based on either:
 REDUNDANCY -- number of backup copies
you want at any time -OR a RECOVERY WINDOW – time span for PIT
 REDUNDANCY 1 is default (docs: compatibility
with REPORT OBSOLETE in prior release)
 When new Level 0 is done, previous
incrementals are OBSOLETE, i.e. not needed
for retention requirement
 Maybe good for Dev DB, PIT of 0-6 in our
example, effectively no PIT guaranteed
7
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Cumulative sizing: Redundancy 1 example
Space requirement example for Redundancy 1 (retention default)
160
140
120
100
80
60
40
20
0
1
2
3
4
5
6
7
8
9
10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28
29
30 31
Level 0 every 7 days (50 GB), Level 1 other days (10 GB)
8
RMAN in the Trenches
NoCOUG Feb. 8, 2007
REDUNDANCY 1: DELETE placement


Now consider a half dozen DBs of the same size in one
backup script
Pseudocode for backup script:
for ORACLE_SID in [DBs to be backed up]
BACKUP...;
DELETE NOPROMPT OBSOLETE;
[End loop]



Putting DELETE at end needs extra 50 GB temporarily
each time through loop
DELETE in front is two Level 0s until next day, so that is
(50X6) = 300GB extra required
FRA clears automatically, when space exceeds quota
9
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Retention: Window 7 Days






For guaranteed PIT, specify number of days in
RECOVERY WINDOW
Window is PIT to any time in past <nn> days
Not sliding window with deletes after 7 days
After Level 0 on Day 8, prior Level 0 still needed
Max of three Level 0s at one time, normally two
In our example, max size is 270 GB
10
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Cumulative sizing: Window 7 Days example
Space requirement example for Retention Window of 7 days
300
250
200
150
100
50
0
1
2
11
3
4
5
6
7
8
9 10 11 12 13 14 15
16 17 18 19 20 21 22 23
24 25 26 27 28 29
30 31
Level 0 every 7 days (50 GB),
Level 1 other days (10 GB)
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Retention Window: Best Use of Space

For any DB where Level 1 is moderate size, best
use of space is one day less than multiple of
Level 0 frequency
 With a Level 0 every 7 days, that would be a
6 or 13 or 20 day Recovery Window
12
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Retention Window: Use of Space Example
Space required (GB) for various retention periods
350
300
250
200
150
Daily size:
Level 0 or 1
100
6 day
retention
50
7 day
retention
0
1 2 3
4 5 6
7 8 9
10 11
12 13
14 15
16 17
18 19
20 21
22 23
24 25
26 27
28 29
30 31
Level 0 every week
13
RMAN in the Trenches
13 day
retention
NoCOUG Feb. 8, 2007
Compression







9i: NULL compression: blocks never used
10g: binary compression
10.2.0.2: empty blocks, used or not
Oracle recommends not mixing RMAN vs 3rd
party methods
We have hardware tape compression -- OK (?)
Driving benefit is disk savings, quicker to tape
COMPRESSION_RATIO column in some V$
and RC* catalog views
14
RMAN in the Trenches
NoCOUG Feb. 8, 2007
On the Space/Time Continuum: Example

Old Sun box (CPU speeds circa 2001):
 9.2 Level 0 non-compressed:
~29 minutes => 39.2 GB
 10.2 Level 0 compressed parallel 1:
~4 hours
=> 6.2 GB
 That's better than a 6:1 ratio for space,
but 8 times as long to run
 Sun V490 (2006): w/ parallel 2, 41 minutes
 Parallel of 4 could complete faster, but would
make more backup pieces (files) in backupset
15
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Restore optimization

Restore optimization, 9i+: file not restored from
backup if already in correct location and
expected info is found
 No action required, always “turned on”
 Helpful for interruption, e.g. after power failure
 Especially helpful with tape (slow)
16
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Backup Optimization

CONFIGURE BACKUP OPTIMIZATION ON;
 Applies to:
•
•
•
•

BACKUP DATABASE
BACKUP ARCHIVELOG with ALL or LIKE options
BACKUP BACKUPSET ALL
Use a channel of only one device type
When to use?
 Read-only tablespace
 Off-line tablespace
 Archives in original location multiple days
17
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Backup Optimization

Scenario: Multiplex archives to two locations,
backup each day, but keep archives on the
original disk location for 3 days
BACKUP archivelog all;
DELETE archivelog until time 'sysdate-3‘;

Optimization off, rc_backup_redolog shows one
backup copy for each distinct redo log sequence
number (multiplex ignored), but one backup copy
for each day that archive remains on disk
 With optimization on, just one backup copy
18
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Backing up to Disk and Tape

Disk:
 Fast and simple: readily monitored/diagnosed
 Improving $$ value
 Required for 10g Flash Recovery Area
 Tape:
 Separate physical media, removable
 More dependence on SysAdmin, Ops
 Both:
 Less urgency if tape fails
19
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Have Ops write to tape, instead of RMAN?

Simplicity makes it appealing
 Catalog only knows disk metadata, no
awareness of tape content
 Best hope is same PIT requirement for disk and
tape; let tape system age out backups
 10g can register backup piece in catalog, not 9i
20
RMAN in the Trenches
NoCOUG Feb. 8, 2007
RMAN write to tape, not Ops






Catalog knows about both destinations
Extra work worth it for many situations
SysAdmin install, symlink from Oracle Home
Vendor MML msgs, diagnostic limitation
Licensing issues – central vs. each client
Godot, or Agnostic Dial-A-Prayer
21
RMAN in the Trenches
NoCOUG Feb. 8, 2007
RMAN writes from disk to tape

BACKUP BACKUPSET
 Can go disk to disk, disk to tape, but not tape
to tape (VTL implication)
 Avoids need to read from source again
 Metadata known for both destinations
 If backup disk area dies, tape is automatically
used for recovery
22
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Clearing Out Old Entries in Tape Index




Only one RMAN retention period per DB
Do you have delete privileges in tape index?
Yes – RMAN is the driver:
 Use KEEP clause, plus DELETE OBSOLETE
 KEEP is not in CONFIGURE, must be in script
 Alternate: set retention policy for tape, do delete
obsolete for tape, separately for disk w/ shorter span
No – tape system controls maintenance:
 Must do CROSSCHECK plus DELETE EXPIRED
 Variation: different tape profiles, different periods
23
RMAN in the Trenches
NoCOUG Feb. 8, 2007
CONFIGURE Basics: Simplify Scripting






See settings with SHOW ALL
Some CONFIGURE defaults are what you want
DEFAULT DEVICE TYPE – disk is assumed
... CHANNEL ... FORMAT ...;
... CONTROLFILE AUTOBACKUP FORMAT ...;
... DEVICE TYPE ... BACKUP TYPE
[BACKUPSET, COMPRESSED BACKUPSET, or COPY ];
24
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Specifying Device in Scripts

Script 1: BACKUP to disk
 Do not specify device in BACKUP command:
let it use disk default
 DELETE OBSOLETE: catalog knows about
tape too:
• If you do NOT have delete privilege from tape,
specify DEVICE DISK so RMAN does not try
communicate with tape
• If you DO have delete privilege from tape, you can
leave out device type reference
25
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Specifying Device in Scripts

Script 2: BACKUP BACKUPSET – disk to tape
 Must specify DEVICE TYPE SBT (acronym for
System Backup to Tape) so it does not try use
the default disk device
 Catalog Maintenance – device already
specified above:
• If you do NOT have delete privilege from tape, use
CROSSCHECK
• If you DO have delete privilege from tape, you can
use DELETE OBSOLETE
26
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Specifying Device in Scripts

Script 3: RESTORE/RECOVER
 With CONFIGURE to set up automatic allocation,
catalog knows everything necessary to complete
these tasks, no need to specify a device
 Inside retention period, everything comes from disk
 Outside of that period, one or more tape channels
would be automatically allocated as necessary,
depending on PARALLELISM we specified in
CONFIGURE command
27
RMAN in the Trenches
NoCOUG Feb. 8, 2007
The Good, The Bad, The Ugly (a Sampling)

Metadata handles details, e.g. RESTORE and
RECOVER does auto retrieve from tape with no
intervention when disk backup area is gone
 Many features well thought out, e.g.
 Complex rules for OPTIMIZATION, different
for disk and tape
 Use of features outside of script allows more
generic script, e.g. binary compression with
CONFIGURE, Block Change Tracking turned
on from SQL
28
RMAN in the Trenches
NoCOUG Feb. 8, 2007
The Good, The Bad, The Ugly (a Sampling)

Significant space savings from incrementals plus
several types of compression; PIT is easier with
more days on disk
 Bonus from doing incrementals: covers for when
object is created with NOLOGGING option
 Plenty of detail to learn, e.g.
 CHECK LOGICAL is not default
 Auto backup of spfile, but not pfile
29
RMAN in the Trenches
NoCOUG Feb. 8, 2007
The Good, The Bad, The Ugly (a Sampling)

For A Few Dollars More, can we fix the ugly?!?
 LIST command is for basic reporting:
 Can avoid need for RC* and V$* views
 VERBOSE or SUMMARY, nothing in between
 What if we want to see backupset file names?
Can’t use SUMMARY; VERBOSE shows a bit
of what we want in lots of output
 Limiting based on device type not easy
30
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Cover Your Sixes ...
31
RMAN in the Trenches
NoCOUG Feb. 8, 2007
...so you don’t get caught by surprise!
32
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Cover Your Sixes

The ALL keyword has somewhat different
meaning, depending on context:
 BACKUP ARCHIVELOG ALL gives one copy
of distinct log sequence from multiplexed
archive locations
 DELETE ALL refers to every log, including
multiplexed
33
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Cover Your Sixes

PARALLELISM:
 Number of automatic channels, e.g. tape
allocated for RESTORE
 Not number of server processes per channel
as I originally assumed
 Diminishing returns on more tape channels,
local experience found 2 is good
34
RMAN in the Trenches
NoCOUG Feb. 8, 2007
Cover Your Sixes

Similar commands below -- Be clear about what
you are deleting, originals or backups:
RMAN>DELETE FORCE ARCHIVELOG ALL
COMPLETED BEFORE 'SYSDATE-30';
------------------------------------------------------RMAN>DELETE FORCE BACKUP OF ARCHIVELOG
ALL COMPLETED BEFORE 'SYSDATE-30';
35
RMAN in the Trenches
NoCOUG Feb. 8, 2007
A&Q



Acknowledgements:
Timothy Chien, Oracle Product Mgr.
Bill Wagman, UC Davis
Presenter: Philip Rice price [at] ucsc.edu
A&Q
Answers: Wisdom to share?
Questions?
36
RMAN in the Trenches
NoCOUG Feb. 8, 2007