slide1 l.
Skip this Video
Loading SlideShow in 5 Seconds..
Information Means The World. PowerPoint Presentation
Download Presentation
Information Means The World.

Loading in 2 Seconds...

play fullscreen
1 / 37

Information Means The World. - PowerPoint PPT Presentation

  • Uploaded on

Information Means The World. Information Means The World. Enterprise Backup and Recovery for Oracle and non-Oracle Solutions Ari Kaplan President, IOUG & Datalink Database Practice 312-399-0079 Today’s Agenda Oracle Backup Methods Hot backup RMAN Data Guard

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Information Means The World.' - medwin

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Information Means The World.

Information Means The World.

enterprise backup and recovery for oracle and non oracle solutions
Enterprise Backup and Recovery for Oracle and non-Oracle Solutions

Ari KaplanPresident, IOUG &Datalink Database Practiceakaplan@datalink.com312-399-0079

today s agenda
Today’s 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
all data is not created equal
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)

oracle tablespace architecture
Oracle Tablespace Architecture




(Table, Index, etc.)





Database Blocks

datafile 2

datafile 1

datafile 3

datafile 4

datafile 5

oracle database architecture
Oracle Database Architecture

Image from Oracle Corp


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
oracle backup methods hot backups
Oracle Backup Methods: Hot Backups


  • Mark tablespace for online backup, copy the files, unmark the tablespace. Loop through all tablespaces


  • 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.)


  • 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 Oracle’s configuration on the fly
  • The database must be operating in ARCHIVELOG mode
oracle backup methods rman
Oracle Backup Methods: RMAN



  • 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

oracle backup methods rman10
Oracle Backup Methods: RMAN


  • 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


  • RMAN only backs up the database, not code or non-Oracle systems
oracle backup methods data guard
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

oracle backup methods data guard12
Oracle Backup Methods: Data Guard


  • 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


  • 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
oracle backup methods export import
Oracle Backup Methods: Export/Import


  • Logical backups of tables (rather than copying the physical blocks of data, it copies the series of commands used to recreate the tables)


  • Can recover on a table-by-table basis instead of the entire database
  • Good for complimenting other backup methods


  • 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 shouldn’t be edited and can only be used by Oracle
oracle backup methods export import14
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 full=y show=y log=cr_db.sql (it’s ugly)

  • Migrate among Oracle versions
  • Note: some OS’s have a 2G file-size limit so DBAs use the “split” and/or compress / tar commands
oracle backup methods data pump
Oracle Backup Methods: Data Pump


  • Export/Import’s replacement with significant improvements. Can do table-by-table logical backup and recoveries


  • 15-45 times faster than Export/Import
  • Direct mode
  • Parallel streams with multiple dump files (versus export’s 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/import’s BUFFER and RECORDLENGTH
oracle backup methods data pump16
Oracle Backup Methods: Data Pump


  • 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
    • a table contains BFILE columns or columns of opaque types
    • a table contains VARRAY columns with an embedded opaque type
    • supplemental logging is enabled and the table has a LOB column
data pump best practices
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

  • V$SESSION_LONGOPS keeps job progress information (in megabytes of table data transferred) that is periodically updated during the job

Oracle Backup Methods: Oracle Flashback


  • Oracle’s 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:


free_gift_list TO TIMESTAMP

(06-FEB-2007, 13:25:00);

Image from Oracle Corp


Oracle Backup Methods: Oracle Flashback

Flashback Drop

  • DROP command puts object into a “recycle bin” for quick recovery

Image from Oracle Corp

oracle backup methods oracle flashback
Oracle Backup Methods: Oracle Flashback


  • 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


  • Requires a significant amount of flashback area online, taking up lots of storage
  • Only works with newer releases of Oracle
storage backup methods
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
storage backup methods triple mirroring
Storage Backup Methods: Triple-Mirroring


  • 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


  • It’s 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


  • 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
storage backup methods array based replication
Storage Backup Methods: Array-Based Replication


  • Replication between two storage arrays that sends storage layer blocks to a standby site whenever there is a storage change at the primary site


  • 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


  • 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
storage backup methods snapshots




Storage Backup Methods: SnapShots

Active File System



  • 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


(Changed blocks

between Snapshots

are tracked)





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’

storage backup methods snapshots25
Storage Backup Methods: SnapShots


  • 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


  • 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.
storage backup methods snapmanager for oracle
Storage Backup Methods: SnapManager for Oracle


  • 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


  • 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 it’s most critically needed


  • 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.
storage backup methods snapshots for cloning
Storage Backup Methods: SnapShots for Cloning
  • Current SMO supports RH3, Solaris 8&9
  • 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


  • True database clone in minutes – significant time and space savings
  • Uses NetApp’s FlexClone technology
  • Ideal for test and development environments. Also useful for QA, auditing and compliance, data warehousing, statistical analysis, staging


  • 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
other backup methods deduplication
Other Backup Methods: Deduplication


  • 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
other backup methods deduplication30
Other Backup Methods: Deduplication


  • Can dramatically reduce the backup stream size—typically 10-20 times—and bandwidth required for backup
  • 10-40% lower acquisition cost than tape
  • When set up properly, deduplication can work well with multiple Oracle RMAN channels


  • Initial costs
other backup methods encryption by oracle and storage vendors
Other Backup Methods: Encryptionby Oracle and storage vendors


  • Unencrypted data opens up the company to several vulnerabilities—whether it’s a disgruntled employee or consultant stealing the tape or the tape being lost in the warehouse or while being transported off-site.
other backup methods encryption by oracle and storage vendors32
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 Oracle’s 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
recovery methodologies
Recovery Methodologies

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

summary q a
Summary, Q & A

Ari Kaplan


datalink overview
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)
datalink value proposition
Datalink Value Proposition
  • Independence
    • Unbiased guidance
    • Business needs drive recommendations
  • Storage focus & expertise
    • Insider’s view of manufacturer R&D 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