Transcript Week 1, Lecture 1

Chapter 1
Backup and Recovery 101
& Intro to Cold and Hot Backups
1
Background on Book



2
The RMAN Recipes book started out as a
word document that one of the authors used
as a handout for the Database B&R class at
Regis University
One student remarked “you could turn this
into a book...”
Author sent the document to Apress, the
editor remarked “what you have here is a
cookbook or recipe format type book...”
Goals of Backup & Recovery




3
Protect the database from failure
Increase time between failures
Decrease time to recover
Minimize loss of data
Oracle Backup Methods

Recovery Manager (RMAN)
–
–
–
4
Built-in component of Oracle server
No additional license fee
Very powerful tool
Oracle Backup Methods (cont.)

User-managed Methods:
–
–
–
–
5
Still common within industry
Manually performed method of backup &
recovery
Often combined with 3rd party tools
Can’t utilize advanced RMAN features
Types of Failures






6
Statement Failure
User Process Failure
Network Failure
Instance Failure
User Error
Media Failure
Oracle B & R Architecture

Background Processes
–
–
–

Physical Structures
–
–
–
–
7
Checkpoint
Log Writer
Archiver
Datafiles
Redo Logs
Control Files
Undo Records
Oracle B & R Architecture (cont.)

Log Mode of Database
–
–

Flashback Technology
–
–
–
–
–
–
8
Archivelog
Noarchivelog (default)
Flashback Query & Versions Query
Flashback Transaction Query
Flashback Transaction Backout
Flashback Table
Flashback Drop
Flashback Database
Backup Types





9
Physical & Logical Backups
Whole & Partial Backups
Online & Offline Backups
Full & Incremental Backups
Consistent & Inconsistent Backups
Recovery Types





10
First, depending on Consistent vs. Inconsistent
Backups
Crash Recovery
Media Recovery
Complete
Incomplete
RMAN Architecture






11
Oracle Binaries (“rman” executable)
Control File
Recovery Catalog
Flash Recovery Area
Media Management Layer (MML)
Enterprise Manager
RMAN Architecture (cont.)






12
Client process
Default Channel
Additional Channels if necessary
Connection to Recovery Catalog
Auxiliary connections
Polling connection
Benefits of RMAN







13
Data Recovery Advisor
Simple commands
Automation of backups
Reporting capabilities
Aids in database duplication
Dry-run capability
Checks validity of backups
Benefits of RMAN (cont.)







14
Incremental backups
Automatically detects corruption
Block-level recovery
Compression
Encryption
Can be used with non-Oracle storage systems
Scripting language
Best Practices






15
Schedule Regular Backups
Backup your Controlfile
Multiplex the controlfile
Multiplex the redo logs
Run database in archivelog mode
Adopt proper storage strategy
Best Practices (cont.)





16
Plan Backup Retention Duration
Plan Your Backup Schedules
Configure Flash Recovery Area
Database Redundancy Set
Validate Recovery Strategy
Next Several Slides Cover User
Managed Cold and Hot Backups




17
Cold (offline)
Hot (online)
These backup methods are still used
extensively
Important to understand how they work
Cold Backup Steps
1.
2.
Determine which files need to be copied
Shutdown database (don’t use abort)
•
•
•
3.
4.
18
Normal
Immediate
Transactional
Copy all files to backup location
Startup database
Cold Backup: Step 1
SQL> select name from v$datafile;
SQL> select member from v$logfile;
SQL> select name from v$controlfile;





19
Any other files?
What about archive redo log files?
Can you roll forward from a cold backup?
How would you script this?
What are the advantages of scripting?
Cold Backup: Step 2
SQL> shutdown immediate;



20
Why immediate?
Will shutdown normal work?
Will shutdown abort work?
Cold Backup: Step 3

Use OS copy utility to copy files to backup location
–
–
–

DOS COPY command
Windows click and drag
Unix cp command
Example using Unix cp:
$ cp /ora01/oradata/system01.dbf /oradump/dev1/cold
$ ….
$ cp /ora01/oradata/user01.dbf /oradump/dev1/cold
21
Cold Backup: Step 4
22

After all files are successfully copied

SQL> startup;
Cold Backups: Finished



23
Keep a record of what you backed up and
when
Test your backup plan
How would you automate cold backups?
Introduction to Hot Backups


Online database backup
Physical OS copy of
–
–
–

24
Datafiles
Controlfiles
Some archive redo logs
Hot Backups never include the online redo
logs
Hot Backup Advantages




25
Database open during backup
No downtime required
Somewhat simple to implement
Reliable
Hot Backup Disadvantages




26
More complicated than a cold
More database resources consumed
Redo logs can be very busy during a hot
backup
Why?
Hot Backup Steps
1.
2.
3.
4.
5.
6.
7.
8.
9.
27
Ensure database in archivelog mode
Determine which files to backup
Switch on-line redo logs
Alter tablespaces into backup mode
Use an OS utility to copy datafiles
Alter tablespaces out of backup mode
Switch on-line redo logs
Copy any archive redo logs generated during
backup
Backup the controlfile
Hot Backup: Step 1


Ensure database in archivelog mode
Doesn’t hurt to have automatic archiving enabled
SQL> archive log list;
If not enabled:
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
28
Hot Backup: Step 2

Determine which datafiles to backup
SQL> select tablespace_name, file_name from
dba_data_files order by 1, 2;

29
Why would the tablespace name be useful?
Hot Backup: Step 3

Switch on-line redo logs
SQL> alter system switch logfile;
30
Hot Backup: Step 4


Put each tablespace in backup mode
For each tablespace identified:
SQL> alter tablespace <tablespace_name> begin backup;



31
You can query view v$backup to view status of
datafiles
Can alter one tablespace at a time into backup mode
Can alter all tablespaces into backup mode
Hot Backup: Step 5

Make an OS copy of all datafiles
$ cp /ora01/oradata/dev1/system01.dbf
/oradump/dev1/hot
….
$ cp /ora01/oradata/dev1/users01.dbf
/oradump/dev1/hot
32
Hot Backup: Step 6


Take tablespaces out of backup mode
For each tablespace in backup mode
SQL> alter tablespace <tablespace_name>
end backup;
33
Hot Backup: Step 7

Switch on-line redo logs
SQL> alter system switch logfile;
34
Hot Backup: Step 8

Copy any archive redo logs generated during backup
SQL> archive log list

Compare that to archive log list issued at start of
backup
$ cp /orarch/arch023.log /oradump/dev1/hot
….
$ cp /orarch/arch55.log /oradump/dev1/hot
35
Hot Backup: Step 8 continued
36

To be safe, start with oldest minus one

Ensure that archive redo log isn’t being
written to when copying
Hot Backup: Step 9


Backup controlfile
Use SQL command
SQL> alter database backup controlfile to
‘<path/filename>’;
37
Hot Backup: Finished
38

Keep a record of what you backed up and
when

Test your backup plan

How would you automate hot backups?
Inside Hot Backup Mode


Common misconception is that datafile is not written to while in backup
mode
While in backup mode, a datafile still gets written to by DBWn
Oracle does three things while in backup mode:
1. Checkpoints the datafiles, flushing dirty buffers to disk
2. Checkpoint SCN for datafile is frozen while in backup mode
3. When a block first changes, the before image block (instead of just the
change vector) will first be written to the online redo logs


39
Main impact is on LGWR, writing more information to redo logs
DBWn continues to write blocks to datafiles
Inside Hot Backup Mode








40
Why put tablespaces into backup mode for Hot backups?
Oracle block size could be 8K, 16K, etc.
OS backup could be copying in 4K chunks
Oracle could be changing the block as the OS is backing it up
This is called a split or fractured block
The block is changing while it is being backed up, and therefore
what gets copied could be inconsistent, head and tail might not
match
To get around this Oracle logs the entire block to the online
redo before it changes it
In the event of a restore of a datafile from a Hot Backup, when
recovering, Oracle will restore entire blocks that were logged to
online redo, and then apply change vectors