Enterprise Backup and Recovery for Oracle and nonOracle Solutions

1
(No Transcript)
2
Enterprise Backup and Recovery for Oracle and
non-Oracle Solutions
Ari KaplanPresident IOUG Datalink Database
Practiceakaplan_at_datalink.com312-399-0079
3
Todays 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

4
RPO 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
5
Oracle Tablespace Architecture
TABLESPACE
Segment 112K (Table Index etc.)
Extent 28K
Extent 84K
Database Blocks
datafile 2
datafile 1
datafile 3
datafile 4
datafile 5
6
Oracle Database Architecture
Image from Oracle Corp
7
Oracle 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

8
Oracle 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

9
Oracle 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

10
Oracle 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

11
Oracle 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

12
Oracle 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

13
Oracle 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

14
Oracle 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

15
Oracle 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

16
Oracle 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

17
Oracle 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)

18
Oracle Backup Methods Oracle Flashback

• Flashback Drop
• DROP command puts object into a recycle bin for
  quick recovery

19
Oracle 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

20
Storage 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

21
Storage 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

22
Storage 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

23
Storage 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
24
Storage 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.

25
Local 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)
26
Storage 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

27
Storage 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
28
Storage 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

29
Other 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

30
Other 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

31
Other 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.

32
Other 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

33
Recovery Methodologies

• An untested or undocumented backup and recovery
  plan is no plan at all

34
Summary Q A

• Ari Kaplan
• akaplan_at_datalink.com
• 312-399-0079

35
Addendums
36
Datalink 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)

37
Datalink 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

38
Storage 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