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
All Data Is Not Created Equal
Recovery Point amount of time that has elapsed
since the last known consistent copy of the
data Recovery Time amount of time required to
gain access to the data High Availability
ability to ensure failures are transparent to
users and applications (i.e. the recovery time
and recovery point are zero)
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
• Mark tablespace for online backup, copy the
  files, unmark the tablespace. Loop through all
  tablespaces
• PROS
• Able to recover to the point of the crash while
  the database is up and available
• Most DBAs are familiar with this method
• Can be used with other backup methods (SMO, RMAN,
  etc.)
• CONS
• The process of backing up and recovering through
  copying entire file systems can be prohibitively
  lengthy
• Only backs up the database itself and not
  customized code or non-Oracle systems such
  Exchange, applications, or SQL
• Database performance degrades during the backup
  process (10-15 on standard systems) and possibly
  more space and resources
• Need an automated script that looks at Oracles
  configuration on the fly
• The database must be operating in ARCHIVELOG mode

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

Image from Oracle Corp
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

• Data availability, data protection and disaster
  recovery solution
• What
• Replicates Oracle databases from one data center
  to another
• Ability to perform backups from the standby
  database instead of the production database
• Both physical and logical versions

Image from Oracle Corp
12
Oracle Backup Methods Data Guard

• PROS
• Synchronous mode enables quick recovery with no
  loss of data
• Alternate database is already up and running and
  in sync
• Works well in conjunction with SnapMirror
  (array-based replication)
• Logical mode can provide huge improvements in
  performance
• Depends on application, can be thousands of times
  faster
• Can significantly reduce bandwidth
• CONS
• Not supported by earlier versions of Oracle
• Only supports Oracle databases, not source code
  or non-Oracle systems
• Data will not be replicated if a table or loading
  process is in NOLOGGING mode
• Must purchase a license for the standby database
• The standby database must be running in order for
  changes to be applied, which impacts the
  performance of any other applications running
  there

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 complimenting 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. Can do table-by-table logical
  backup and recoveries
• PROS
• 15-45 times faster than Export/Import
• Direct mode
• Parallel streams with multiple dump files (versus
  exports singlefile) and adjust resources. Thus
  the order of data within dump file sets is less
  predictable than with export/import
• Suspend and restart data transfers (for adding
  storage, resume during off hours, etc.)
• Can copy data directly database to database
  through a network link
• Occurs on the Oracle database server, whereas
  exports/imports occur on the client. This results
  in improved performance, and means that directory
  objects are required
• Self-tuning compared to manually setting
  export/imports BUFFER and RECORDLENGTH

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 backup 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. Export and Import does
• 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. This is
  different from original Import, which logs any
  rows that are in violation and continues with the
  load
• 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 multipartition tables exists
  during a single-partition load
• a domain index exists for a LOB column

17
Data Pump Best Practices

• Accessing data over Database Links
• For exports, data from the source database
  instance is written to dump files on the
  connected database instance
• The source database can be a read-only database
• For imports the source is a database, not a dump
  file set, and the data is imported to the
  connected database instance
• Monitoring the progress of executing jobs
• VSESSION_LONGOPS keeps job progress information
  (in megabytes of table data transferred) that is
  periodically updated during the job

18
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)

Image from Oracle Corp
19
Oracle Backup Methods Oracle Flashback

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

Image from Oracle Corp
20
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
• Only works with newer releases of Oracle

21
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

22
Storage Backup Methods Triple-Mirroring

• What
• Copy the data in real time to three sets of
  redundant disks. Put the database in hot backup
  mode, break off one of the three sets. Back up
  data at a more leisurely pace from the mirror
  slices. The other two redundant data sets remain
  in use for production. Once the backup is
  complete, the mirror is synced back up with the
  primary copies
• PROS
• Its possible to slice the mirror instantaneously
  and backup 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

23
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

24
Storage Backup Methods SnapShots

• What
• Snapshots, often referred to as point-in-time
  copies, allow 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
25
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 a
  matter of 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.

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 reduces risks for
• Error-free backups and recoveries
• Restores
• Clones
• Initiate and manage backups
• Minimize the risk of data loss and corruption
  with increased backup frequency
• Near instantaneous restores of data when its
  most critically needed
• CONS
• There is a cost associated with purchasing
  non-Oracle 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.

27
Storage Backup Methods SnapShots for
Cloning(SMO and FlexClone)
Demo (0504)
28
Storage Backup Methods SnapShots for Cloning

• Current SMO supports RH3, Solaris 89
• SMO 2.0 online cloning (before had to shut down
  DB), easier to install
• 2.0 Dec/Jan add RH4 (32 and 64bit), Solaris 10,
  HP-UX (NFS-only), AIX (NFS-only)
• April (SAN)
• Tushar Patel and Saradhi Sreegiriraju are in the
  audience today
• 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 both NFS and iSCSI networking
  protocols

29
Other Backup Methods Deduplication

• What
• Deduplication 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

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
Addendum Slides
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