Title: Enterprise Backup and Recovery for Oracle and nonOracle Solutions
1(No Transcript)
2Enterprise Backup and Recovery for Oracle and
non-Oracle Solutions
Ari KaplanPresident, IOUG Datalink Database
Practiceakaplan_at_datalink.com312-399-0079
3Todays Agenda
- Oracle Backup Methods
- Hot backup
- RMAN
- Data Guard
- Export / Import
- Data Pump
- Flashback
- Storage-based solutions
- Triple-mirroring
- Array-based replication
- Storage Snapshots
- Other backup enhancements
- Enterprise Backup scheduling solutions
- Deduplication
- Encryption
- Oracle Secure Backup
- Third-party encryption
4RPO and RTO
Tape Backup Hot backup
Tape Backup Hot backup RMAN Data Guard
Copy-on-Write Snapshots
WAFL Snapshots
Async replication RMAN
Synchronous replication, Data Guard
Recovery Point Database-only is typically days
or hours of lost data in disaster, or too costly.
Combined with storage can be faster,
heterogeneous, and with less cost Recovery Time
Database-only can be days or hours. Combined with
storage can be minutes. Backup Window
Database-only is typically days for
terabyte-sizes. Combined with storage can be
seconds
5Oracle Tablespace Architecture
TABLESPACE
Segment 112K (Table, Index, etc.)
Extent 28K
Extent 84K
Database Blocks
datafile 2
datafile 1
datafile 3
datafile 4
datafile 5
6Oracle Database Architecture
Image from Oracle Corp
7Oracle Physical Files
- All of these should be backed up!
- Datafiles data, index, temporary, rollback,
system - Online and archived redo logs
- Control files
- Oracle executables and patches
- Export, Data Pump dump, RMAN files
- Auditing files
- Parameter files (init.ora, sqlnet.ora,
listener.ora, tnsnames.ora) - SPfiles
- Alert logs, bdumps, cdumps, udumps
- Password files
- Single sign-on files
- LOB or BFILE or library storage structures
- External tables
- Home-grown scripts
8Oracle Backup Methods Hot Backups
- What
- Online backup of the database
- PROS
- Can be used with other backup methods
- CONS
- Can be prohibitively lengthy
- Only backs up some of the Oracle files not
customized code or non-Oracle systems such as
Exchange, applications, and SQL - Performance degradation
- Need an automated script that looks at Oracles
configuration on the fly
9Oracle Backup Methods RMAN
- RECOVERY MANAGER
- What
- Block-level backups - datafile compression
- Parallel streams
- Many companies see a 10x performance depending
on the amount of updates that were made between
backups - Use of a recovery catalog for multiple databases
10Oracle Backup Methods RMAN
- PROS
- Significantly reduces RTO
- Significantly less storage space for incremental
backups (only changed blocks) - Parallel streams/channels
- Works well with snapshots, SnapMirror, SMO,
Decru, future de-duplication - CONS
- RMAN only backs up the database, not code or
non-Oracle systems
11Oracle Backup Methods Data Guard
- What
- Replicates Oracle databases from one data
center to another - Performs backups from the standby database
instead of the production database - Both physical and logical versions
12Oracle Backup Methods Data Guard
- PROS
- Synchronous mode
- Logical mode
- CONS
- Not supported by earlier versions of Oracle
- Only supports Oracle databases, not source code
or non-Oracle systems - Disables NOLOGGING mode
- Extra Oracle license
- Standby database must be online
13Oracle Backup Methods Export/Import
- What
- Logical backups of tables (rather than copying
the physical blocks of data, it copies the series
of commands used to recreate the tables) - PROS
- Can recover on a table-by-table basis instead of
the entire database - Good for complementing other backup methods
- CONS
- Can take a LONG time versus snapshot methods
- All data since the export was made is lost
- A database must be running to perform either an
export or import - Export files shouldnt be edited and can only be
used by Oracle
14Oracle Backup Methods Export/Import
- Additional Features
- Using the SELECT clause (compared to FlexClone)
- Logical copy of tables or the entire database
- Defragmenting
- Moving tables / data among schemas
- Moving tables / data among databases
- Recreating CREATE statements
- imp fully showy logcr_db.sql (its ugly)
- Migrate among Oracle versions
- Note some OSs have a 2G file-size limit so DBAs
use the split and/or compress / tar commands
15Oracle Backup Methods Data Pump
- What
- Export/Imports replacement with significant
improvements -
- PROS
- 15-45 times faster than Export/Import
- Parallel streams
- Suspend and restart data transfers
- Self-tuning
16Oracle Backup Methods Data Pump
- CONS
- Like the Export/Import utilities
- All data since the Data Pump started is lost
- Still can take a long time to back up and recover
versus methods such as snapshots - A database must be running to perform either an
export or import - Data Pump does NOT yet work with XML schemas and
XML schema-based tables - When you are importing data into an existing
table using either APPEND or TRUNCATE, if any row
violates an active constraint, the load is
discontinued and no data is loaded - Situations where external tables is used and
Direct Path Load is not - there is an active trigger
- a unique index exists
- the table is partitioned
- a referential integrity constraint exists
- fine-grained access control is enabled in insert
mode - the table has encrypted columns, table is in a
cluster - a global index on multi-partition tables exists
during a single-partition load - a domain index exists for an LOB column
17Oracle Backup Methods Oracle Flashback
- What
- Oracles flashback area allows for recovering a
table (or database) to any point-in-time in the
past by storing images of data online - Flashback Database
- Recover to point-in-time from Flash Recovery Area
- Flashback Table
- FLASHBACK TABLE LAOUG_audience,
- free_gift_list TO TIMESTAMP
- (06-FEB-2007, 132500)
18Oracle Backup Methods Oracle Flashback
- Flashback Drop
- DROP command puts object into a recycle bin for
quick recovery
19Oracle Backup Methods Oracle Flashback
- PROS
- This method provides online backup and recovery,
eliminating the need to recover from tape and
saving valuable recovery time and management
effort - Extremely fast recovery of tables with simple
commands - CONS
- Requires a significant amount of flashback area
online, taking up lots of storage - Cant recover from media failure
- Cant undo operations such as shrink datafile
- Can only flashback to the oldest SCN in the FRA
- Only works with newer releases of Oracle
20Storage Backup Methods
- Non-Oracle Storage Backup Solutions
- Non-Oracle solutions are sometimes undiscovered
in the world of DBAs - Can solve issues that simply cannot be done with
Oracle-only technology - May already be deployed in a company to support
Oracle/non-Oracle applications but the database
staff is unaware of its HUGE benefits to database
environments
21Storage Backup Methods Triple-Mirroring
- What
- Copies the data in real time to three sets of
redundant disks - PROS
- Its possible to slice the mirror instantaneously
and back up from the slices, thereby eliminating
performance hits of being in hot backup mode for
extended periods of time - CONS
- Expensive 50 more storage costs to keep a set
of production-sized disks - Companies still may not be able to meet backup
windows, especially if backing up from the mirror
takes more than 24 hours
22Storage Backup Methods Array-Based Replication
- What
- Replication between two storage arrays that sends
storage layer blocks to a standby site whenever
there is a storage change at the primary site -
- PROS
- Fills the gaps of Data Guard by replicating
non-Oracle systems and source code as well as
tables in NOLOGGING mode - Works best in conjunction with Data Guard by
jointly reducing replication traffic and reducing
or eliminating single points of failure - Significantly improves RTO and RPO
- CONS
- Source and target may need to be the same vendor,
unless using heterogeneous replication such as
Topio - There is a cost associated with purchasing and
maintaining a third-party array-based replication
solution. An ROI analysis will demonstrate that
this cost can be justified. The point at which it
is recouped will depend on the value of the data
23Storage Backup Methods Snapshots
- Instant backup and recovery of large data sets
using a sophisticated, scalable, and fail-safe
pointer system of storage blocks. Snapshots
represent a frozen view of data taken at a
specific point-in-time. Data and entire
environments can be restored to a known stable
point prior to the event that caused the
disruption or corruption. -
Snapshot.0
Active File System
Snapshot.1
(Changed blocks between snapshots are tracked)
C
C
A
B
Snapshot.0 file system version of ORACLE.DBF is
still composed of blocks A, B C Snapshot.1
file system version of ORACLE.DBF is composed of
blocks A, B C
24Storage Backup Methods Snapshots
- PROS
- Online backup and recovery is fast and seamless
and eliminates the need to recover from tape. It
is possible to recover a 50 TB database in
minutes and keep hundreds of snapshots online - CONS
- There is a cost associated with purchasing
snapshot products. As with the other
storage-based methods, an ROI analysis will
demonstrate that this cost can be justified. The
point at which it is recouped will depend on the
value of the data and the value of the speed of
backup or recovery, improvements in
manageability, etc.
25Local Backup and Recovery
Backup
Multiple SnapshotCopies per Hour
Disk-Based
Tape
300 GB at 60 GB/hr 5 hrs (Best Case)
Time to Backup (Hours)
Restore
Disk-Based
Tape
Redo Logs
Restore from Tape
Time to Recovery (Hours)
26Storage Backup Methods SnapManager for Oracle
- What
- GUI that uses snapshots for database backups,
recoveries, and cloning - Easy to install and integrates with Oracle RMAN,
Oracle RAC, Oracle ASM, Oracle 9i and 10g -
- PROS
- Greatly improves DBA efficiency in setting up
test and dev environments - Automates DBA tasks and enables error-free
backups and recoveries, restores and clones - Initiates and manages backups
- Minimizes the risk of data loss and corruption
with increased backup frequency - Provides near instantaneous restores
- CONS
- Cost associated with purchasing non-Oracle
products - ROI analysis will demonstrate that this cost can
be justified
27Storage Backup Methods SnapShots for
Cloning(SMO and FlexClone)
Scalability by Metric To avoid surprise, require
proof of scalability at your requirement levels
Unpredictable Performance and Cost
Known Performance and Cost
Demo (0504)
Test and dev with full size database
Test and dev with full size database)
Test and dev with subset of database
28Storage Backup Methods Snapshots for Cloning
- What
- True database clone in minutes significant time
and space savings - Uses NetApps FlexClone technology
- Ideal for test and development environments. Also
useful for QA, auditing and compliance, data
warehousing, statistical analysis, staging - PROS
- Consistent copies of FULL production databases
- Saves SIGNIFICANT time and management and space
to manage cloning environments - Works across FCP, iSCSI, NFS networking protocols
29Other Backup Methods De-duplication
- What
- De-duplication works with VTLs by detecting
redundant data streams during the backup process
and sending pointers to that data (versus actual
streams of blocks of data) when duplicate streams
are detected
De-dupe device
30Other Backup Methods Deduplication
- PROS
- Can dramatically reduce the backup stream
sizetypically 10-20 timesand bandwidth required
for backup - 10-40 lower acquisition cost than tape
- When set up properly, deduplication can work well
with multiple Oracle RMAN channels - CONS
- Initial costs
31Other Backup Methods Encryptionby Oracle and
storage vendors
- What
- Unencrypted data opens up the company to several
vulnerabilitieswhether its a disgruntled
employee or consultant stealing the tape or the
tape being lost in the warehouse or while being
transported off-site.
32Other Backup Methods Encryptionby Oracle and
storage vendors
- Oracle solutions
- Database Encryption
- Store data encrypted within the database and,
thus, also encrypted on tape - Oracle secure backup
- Encrypt Oracles RMAN backups before they are
written to tape - By encrypting at the database level, an
organization reduces much of its exposure. This
method is also free (for one direct-attached
storage device) - Typically, the DBA is solely responsible for
retaining the key and there are inherent
weaknesses with this strategy - 2) Storage vendor encryption
- Encryption at the storage level
- Multi-master key management that prevents single
points-of-failure for key loss - No auto-destruct capabilities
- By using a hardware and software appliance,
backup streams can be encrypted with negligible
impact on performance
33Recovery Methodologies
- An untested or undocumented backup and recovery
plan is no plan at all
34Summary, Q A
- Ari Kaplan
- akaplan_at_datalink.com
- 312-399-0079
35Addendum Slides
36Datalink Overview
- Independent information storage architect
specializing in solutions services spanning - Data availability
- Data recovery
- Storage management
- Focused on information storage solutions and
services since 1987 - Headquartered in Minneapolis, MN, with operations
throughout the U.S. - Publicly traded company (NASDAQ DTLK)
37Datalink Value Proposition
- Independence
- Unbiased guidance
- Business needs drive recommendations
- Storage focus expertise
- Insiders view of manufacturer RD roadmaps
- Extensive experience with complex, multi-vendor,
multi-platform infrastructures - Alignment of people, processes and technologies
- Track record of excellence
- Long-term partnerships
- Business value
38Storage Solutions for DBA Challenges
- Backup issues
- Scalability it can take a long time to
physically copy 2 TB of data to tape or disk - Cost it costs a lot to purchase 2 TB of storage
so you can perform physical image backups of 200
TB databases. It is costly to even purchase
hardware to test backups. - Performance keeping large databases in hot
backup mode negatively affects the performance of
high-transaction systems (inserts, updates,
deletes) - Complexity of systems multiple databases,
interlinked systems, different database versions,
RMAN/non-RMAN, RAC, ASM, etc. - Manageability it is often difficult to setup,
manage, and test backups - Recovery issues
- Manageability human errors, lost data,
inconsistent data, physical failures, corruption
can require restores. It can be a manual and
scary process to recover to a consistent
point-in-time - Performance how in the world do you recover a 2
TB database in 15 minutes? - Disaster Recovery / Replication
- How do you architect your database and
surrounding environment for DR? With no data
loss? With a 15-minute failover timeframe? - Data growth
- Cost DBAs tend to put storage on single class of
storage without archiving or tiering
considerations - Performance system response time is 5 seconds
now. What happens when my data triples in size? - Manageability it is often a political process to
get additional storage from non-DBA groups - Development and testing
- Cost it costs a lot to purchase 200 TB of
storage to get image copies of production to test
and development! - Scalability it is too much to provide 5, 10, 15
or more copies to development and testing teams - Manageability it can take 25 or more of a DBAs
time to manage the cloning process