Information Means The World.

1. Information Means The World. Information Means The World.

2. Enterprise Backup and Recovery for Oracle and non-Oracle Solutions Ari KaplanPresident, IOUG &Datalink Database Practiceakaplan@datalink.com312-399-0079

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

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 Oracle’s 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 shouldn’t 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 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

15. Oracle Backup Methods: Data Pump 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

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

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 • V$SESSION_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: • 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

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

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. File: ORACLE.DBF 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’

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

27. Storage Backup Methods: SnapShots for Cloning(SMO and FlexClone) Demo(05:04)

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

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

31. Other Backup Methods: Encryptionby 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.

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

33. Recovery Methodologies An untested or undocumented backup and recovery plan is no plan at all

34. Summary, Q & A Ari Kaplan akaplan@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 • 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