Information Means The World.
Download
1 / 37

Enterprise Backup and Recovery for Oracle and non-Oracle Solutions - PowerPoint PPT Presentation


  • 412 Views
  • 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 [email protected] 312-399-0079 Today’s Agenda Oracle Backup Methods Hot backup RMAN Data Guard

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

PowerPoint Slideshow about 'Enterprise Backup and Recovery for Oracle and non-Oracle Solutions' - 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
Slide1 l.jpg

Information Means The World.

Information Means The World.


Enterprise backup and recovery for oracle and non oracle solutions l.jpg
Enterprise Backup and Recovery for Oracle and non-Oracle Solutions

Ari KaplanPresident, IOUG &Datalink Database [email protected]


Today s agenda l.jpg
Today’s Agenda Solutions

  • 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 l.jpg
All Data Is Not Created Equal Solutions

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 l.jpg
Oracle Tablespace Architecture Solutions

TABLESPACE

Segment

112K

(Table, Index, etc.)

Extent

28K

Extent

84K

Database Blocks

datafile 2

datafile 1

datafile 3

datafile 4

datafile 5


Oracle database architecture l.jpg
Oracle Database Architecture Solutions

Image from Oracle Corp


Slide7 l.jpg

Oracle Physical Files Solutions

  • 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 l.jpg
Oracle Backup Methods: Hot Backups Solutions

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 Oracle’s configuration on the fly

  • The database must be operating in ARCHIVELOG mode


Oracle backup methods rman l.jpg
Oracle Backup Methods: RMAN Solutions

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


Oracle backup methods rman10 l.jpg
Oracle Backup Methods: RMAN Solutions

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


Oracle backup methods data guard l.jpg
Oracle Backup Methods: Data Guard Solutions

  • 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 l.jpg
Oracle Backup Methods: Data Guard Solutions

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


Oracle backup methods export import l.jpg
Oracle Backup Methods: Export/Import Solutions

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 shouldn’t be edited and can only be used by Oracle


Oracle backup methods export import14 l.jpg
Oracle Backup Methods: Export/Import Solutions

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 l.jpg
Oracle Backup Methods: Data Pump Solutions

What:

  • Export/Import’s 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 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 l.jpg
Oracle Backup Methods: Data Pump Solutions

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

    • 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 l.jpg
Data Pump Best Practices Solutions

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


Slide18 l.jpg

Oracle Backup Methods: Oracle Flashback Solutions

What:

  • 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:

  • FLASHBACK TABLE LAOUG_audience,

    free_gift_list TO TIMESTAMP

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

Image from Oracle Corp


Slide19 l.jpg

Oracle Backup Methods: Oracle Flashback Solutions

Flashback Drop

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

Image from Oracle Corp


Oracle backup methods oracle flashback l.jpg
Oracle Backup Methods: Oracle Flashback Solutions

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


Storage backup methods l.jpg
Storage Backup Methods Solutions

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 l.jpg
Storage Backup Methods: Triple-Mirroring Solutions

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:

  • 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

    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


Storage backup methods array based replication l.jpg
Storage Backup Methods: Array-Based Replication Solutions

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


Storage backup methods snapshots l.jpg

File: ORACLE.DBF Solutions

File: ORACLE.DBF

File: ORACLE.DBF

Storage Backup Methods: SnapShots

Active File System

Snapshot.0

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.1

(Changed blocks

between Snapshots

are tracked)

C

B

C’

A

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 l.jpg
Storage Backup Methods: SnapShots Solutions

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.


Storage backup methods snapmanager for oracle l.jpg
Storage Backup Methods: SnapManager for Oracle Solutions

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 it’s 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.


Storage backup methods snapshots for cloning smo and flexclone l.jpg
Storage Backup Methods: SnapShots for Cloning Solutions(SMO and FlexClone)

Demo(05:04)


Storage backup methods snapshots for cloning l.jpg
Storage Backup Methods: SnapShots for Cloning Solutions

  • 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

    What:

  • 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

    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


Other backup methods deduplication l.jpg
Other Backup Methods: Deduplication Solutions

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


Other backup methods deduplication30 l.jpg
Other Backup Methods: Deduplication Solutions

PROS:

  • 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

    CONS:

  • Initial costs


Other backup methods encryption by oracle and storage vendors l.jpg
Other Backup Methods: Encryption Solutionsby Oracle and storage vendors

What:

  • 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 l.jpg
Other Backup Methods: Encryption Solutionsby 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 l.jpg
Recovery Methodologies Solutions

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


Summary q a l.jpg
Summary, Q & A Solutions

Ari Kaplan

[email protected]

312-399-0079


Addendum slides l.jpg
Addendum Slides Solutions


Datalink overview l.jpg
Datalink Overview Solutions

  • 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 l.jpg
Datalink Value Proposition Solutions

  • 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


ad