information means the world.. storage: a new paradigm for databases ari kaplan president, ioug &...
TRANSCRIPT
Information Means The World.Information Means The World.
Storage: A New Paradigm for Databases
Ari KaplanPresident, IOUG &
Datalink Database [email protected]
312-399-0079
Oracle: 30 years!
IPOs:Sun: March 4, 1986Oracle: March 12, 1986Microsoft: March 13, 1986
30 years of IT paradigms:- Mainframe- Client-Server- Internet- Mobile- Web 2.0
Storage paradigms:- Direct-attached disk- Tape backups- NAS- Disk-to-disk- Virtualization- Replication
• Introduction• Oracle-based Methods: Hot backup, RMAN, Data Guard, Export /
Import, Data Pump, Flashback• Snapshots: Enterprise Backup and Recovery• Snapshots: Development and testing for cloning• Enterprise Backup and Recovery:
– Disk-to-disk, enhanced data recovery, and VTL
• Disaster Recovery: storage-based replication• Cost savings: tiered architecture / ILM• Performance and high-availability: RAID-DP and aggregates• Storage security / encryption• De-duplication• Pros and Cons: Oracle and non-Oracle enterprise solutions
Agenda
A Silo Approach?
“I’ve always focused on the Oracle side due to implementing RAC. Every company I’ve worked with has had their storage team take care of that side of the house.”– One of America’s top Oracle RAC specialists
Databases Growing at Staggering Rate
• The WinterCorp survey found that the world’s largest databases have posted an annual compounded growth rate of approximately 75% since 1995.
0
20
40
60
80
100
120
1998 1999 2000 2001 2002 2003 2004 2005
Ter
abyt
es
Size of the Largest Data Warehouse in the WinterCorp TopTen™ Survey
Storage Solutions for DBA ChallengesBackup 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 200 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 20 TB of storage to get several 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 DBA’s time to manage the cloning process
RPO and RTO
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
Tap
e B
ackup
Ho
t backu
p
Tap
e Backu
pH
ot b
ackup
RM
AN
Data G
uard
Co
py-o
n-
Write
Sn
apsh
ots
WA
FL
Sn
apsh
ots
Asyn
c rep
lication
RM
AN
Syn
chro
no
us rep
lication
, D
ata Gu
ard
Snapshots: Enterprise Backup
and Recovery
Oracle Tablespace Architecture
datafile 1 datafile 2 datafile 3
datafile 4 datafile 5
Database Blocks
Extent28K
Extent84K
Segment112K
(Table, Index, etc.)
TABLESPACE
Oracle Database Architecture
Image from Oracle Corp
Oracle Backup Methods: Hot BackupsWhat:• 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 Oracle’s configuration on
the fly
Oracle Backup Methods: RMANRECOVERY MANAGERWhat:• 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
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-duplicationCONS:• RMAN only backs up the database, not code or
non-Oracle systems
Oracle Backup Methods: Data GuardWhat:• 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
Oracle Backup Methods: Data GuardPROS:• 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
Oracle Backup Methods: Export/ImportWhat:• 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 shouldn’t be edited and can only be used by Oracle
Oracle Backup Methods: Export/ImportAdditional 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 PumpWhat:• Export/Import’s replacement with significant
improvements
PROS:• 15-45 times faster than Export/Import• Parallel streams• Suspend and restart data transfers • Self-tuning
Oracle Backup Methods: Data PumpCONS:• 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– 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 an LOB column
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);
Oracle Backup Methods: Oracle Flashback
Flashback Drop• DROP command puts object into a “recycle
bin” for quick recovery
Oracle Backup Methods: Oracle Flashback
Oracle Backup Methods: Oracle FlashbackPROS:• 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• Can’t recover from media failure• Can’t undo operations such as shrink datafile• Can only flashback to the oldest SCN in the FRA• Only works with newer releases of Oracle
Storage Backup MethodsNon-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-MirroringWhat:• Copies the data in real time to three sets of redundant disks
PROS:• It’s 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
Storage Backup Methods: Array-Based ReplicationWhat:• 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
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 Physical Files
Redo Logs
Archive Logs
Ctl Files
Simpler Backup and Recovery
Active File System (CURRENT)
All
Oracle
Datafiles
7 AM8 AM
9 AM10 AM
11AM
Storage Backup Methods: SnapshotsInstant 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.
C’
Snapshot.0
A B C
Active File System
File: ORACLE.DBF
(Changed blocksbetween snapshotsare 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’
File: ORACLE.DBF
Snapshot.1
File: ORACLE.DBF
Technology IntegrationSnapshot for Backup/DR validated by Oracle
• Snapshots– Point-in-time copy– Created in a few
seconds– No performance
penalty
• TPC-C Published with 5 active snapshots
Snapshot During 50/50 4K Read/Write OLTP Workload
0
2000
4000
6000
8000
10000
12000
0 60 120 180 240 300 360 420 480 540
Time (seconds)
IO/S
ec
Snapshot taken
Real World Snapshot Performance
"In our test configurations, we found that conducting a single snapshot copy on the FAS3070 over the course of the test had no sustained impact on the overall performance. On the CX3-80, creating a single snapshot caused the overall performance level to drop to approximately 50 percent of the baseline performance. Additionally, the results show that the post snapshot performance did not recover to levels observed before the snapshot was taken.”
VeriTest Report, NetApp FAS3070 vs. EMC CX3-80: Nov. 2006
Storage Backup Methods: SnapManager for OracleWhat:• 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 solutions. As with the other storage-
based methods, an ROI analysis will demonstrate that this cost can be quickly 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.
Snapshots: Development and testing for Cloning
Using Snapshot Cloning to AccelerateApplication Development and Upgrade Cycle
• Quickly reconfigure multiple test, development, QA, DW, auditing, staging environments
• Rapid restore from online Snapshot copies
• Allows multiple test environments
• Accelerates test cycles• Helps deliver new Oracle
applications quickly• Use Oracle cloning
procedures to clone application environments
Test and Development Lab
OracleServer
TestDatabase
Clustered Filers
DatabaseTesting Cycle
OnlyTakes
Seconds
TestingBaseline
RunTest
EvaluateResults
RestoreBase
Environment
Before: Large E-Business SuiteProduction
Mirrored Copy
Test 1
…
Dev N
…
Test NTest 2
Dev 1 Dev 2
Challenges
• Copies consume lots of disk– < 10% data differences
for each instance
• Copies take a lot of time– Slower time to market
After, with Snapshot ClonesProduction
Test 1 Test 2
QA
Develop 1 Develop 2
Solution– Instantaneous copies
– Low storage overhead
• Faster TTM
• Higher quality
• Lower cost
Mirrored Copy
Oracle University and Cloning
• Refresh about 100 classes with 11 databases each weekend
• Entire operation now complete in 45 minutes!
Storage Backup Methods: SnapShots for Cloning(SMO and FlexClone)
Demo (05:04)
0
50
100
150
200
250
300
350
Scale Factor
Met
ric
Val
ue
Known Performance and Cost
Unpredictable Performance and
Cost
Scalability by Metric: To avoid surprise, require proof of scalability at your requirement levels
Test and dev with full size databaseTest and dev
with full size database)
Test and dev with subset of database
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
Disk-to-disk, enhanced data
recovery and VTL
Disk-to-Disk: Enhanced Data Recovery is the Solution
Why?• Rising downtime costs• Pressure to reduce recovery time (RTO)• Pressure to minimize data loss (RPO)• Increasing data sizes / shrinking backup window
• Tape backup & restore is unreliable• Media costs & media management complexity getting
out of control
Disk-to-Disk: Enhanced Data Recovery is the Solution
•A backup and restore architecture that adds a disk based storage array to a traditional tape only solution.
•Enables concept of “Backup to disk, Archive to tape”
•Enabled by falling disk prices (ATA & SATA)
•Leverages random access capability of disk
•Augments tape capabilities
VTL: Efficient Backup and Restore• VTL emulates industry standard
tape libraries
• Time to restore is improved with online and near-line backups
• Consolidated backups: integrates as appliance!
• Replace tape backups with VTL backups
• Faster than tape
• Non-sequential recovery
• Rapid failover supported—when a server fails you can remount the database volumes to another server
Tape Library for backup
Windows Servers
Backup Server
NearStoreVTL
Storage
DecruDataFort
UNIX Servers
Disaster Recovery: Storage-based
Replication
Replication Based Backup Solution ROI/TCO
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
• Reduces the number of copies of data backed up• Reduces IP network traffic for backup data• More frequent backups with more copies kept online• Reduces tape media and automation resources• Fast file & full restores• Shortest RPO & RTO• Centralization of remote backup• Replication to off-site location lowers tape media management
costs for off-site tape rotation
Oracle Backup Methods: Data GuardWhat:• 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
Oracle Backup Methods: Data GuardPROS:• 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
Oracle DB volumes on primary and standby site are SnapMirrored Remote site’s Oracle DB is kept in recovery mode When disaster strikes primary site, it’s a matter of breaking the SnapMirror and bringing up the remote
standby site’s Oracle DB in query/update mode
Storage-based Replication for Oracle
GigE / FCP
/vol/Oracle
Data, Log & Cntrl files
/vol/Oracle
Mirrored Data, Log and Cntrl files
Asynchronous replication over TCP/IP LAN or WAN. No distance limitation.
Primary Site
remote disk mirroring solutions
NAS/SANStorage
Appliance
Changed blocks are shipped to the target mirrored volume
Standby Site
NAS/SANStorage Appliance
GigE / FCP
NYC Chicago
Storage Backup Methods: Array-Based Replication
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
Cost savings: tiered architecture / ILM
Pooled Storage
Network
Oracle and Virtualization: Enterprise Grid
withTransparent Reconfiguration:
Compliance
Database
Backup Data
Secure Data
Scalable everything: performance, availability, capacity, etc. Single view; single point of control
CPUs
OS and Load
SharingSoftware
Single Virtual Machine
HighAccessData onPrimaryStorage
Single Virtual Storage Pool
Global Name Space and
Unified Management
Tiered Architecture / ILM
Oracle options for tiering data• Tablespaces on specific tiers of disks• Table and index partitioning – tiers of disk relate to specific partitions• Triggers, procedures, packages for migrating data• ILM solutions from Oracle or third-parties
– Oracle ILM Assistant• Define the Data Classes
• Create Storage Tiers for the Data Classes
• Create Data Access and Migration Policies
• Define and Enforce Compliance Policies– Third-party solution integration with Oracle
Cost savings example: 94% reduction in storage costs!
Oracle options for tiering data• Tablespaces on specific tiers of disks• Table and index partitioning – tiers of disk relate to specific partitions• Triggers, procedures, packages for migrating data• ILM solutions from Oracle or third-parties
– Oracle ILM Assistant
• Define the Data Classes • Create Storage Tiers for the Data Classes • Create Data Access and Migration Policies • Define and Enforce Compliance Policies
Performance and high-availability:
RAID-DP
RAID-DP
3121
1131
2213
3122
9587
7121211{
D D D D P DP
• RAID-DP is an advanced, cost-effective disk failure/error protection solution protecting information in the event of a double disk or media failure within a single RAID group
• RAID-DP is based on RAID4 adding a diagonal parity calculation to enhance overall performance versus competitive double parity adaptations based on RAID6
Why is RAID-DP Needed?Protection• ‘Traditional’ single-parity-drive RAID group no longer provides enough protection
– Reasonably-sized RAID groups (e.g. 8 drives) are exposed to data loss during reconstruction• RAID-DP’s double disk-failure protection does what RAID5 and RAID1/0 cannot:
– Reduces RISK: limits exposure to same RAID group second disk failure or non-recoverable media error
Cost• RAID 1 is too costly for widespread use
– Mirroring doubles the cost of storage– Not affordable for all data
• RAID-DP exceeds RAID1/0 protection levels without the associated doubling of capacity and cost
• RAID-DP has a comparable operational cost to RAID 4
Performance• Optimized for Performance• Reduces RAID group rebuild time
RAID-DP vs other RAIDs• >10,000 times more secure than
single-parity RAID
• More reliable than mirroring for double-disk failure
• 13% Parity Overhead vs 50% Overhead w/ Mirror(*)
• 75% more usable capacity than competitive offerings(*)
* Comparing 14d + 2p vs. 8+8 mirror
P
P DP
RAIDProtects against single disk failure
RAID DP Protects against any two-disk failure
The Cost of Data Availability & ProtectionCompare RAID-DP™ to RAID10
Count the drives needed for 2TB useable storage using 144GB disk drives
RAID-DP™
RAID-DP protects as well as RAID10 with less storage overhead
RAID 10
3030 1717
Redo Logs
Archive Logs
Ctl Files
All
Oracle
Datafiles
A Simpler Database Architecture
Aggregates: a Simpler Database Design
Multi-Disk Aggregate
Database
0.7
1.6
2.4
0
0.5
1
1.5
2
2.5
3
NetApp RAID 1/0 RAID 5
SPC-1 Results
Disk Utilization
0
20
40
60
80
100
1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31
Disk number
%B
usy
RAID-DP
Aggregates: Improved PerformanceTest OLTP
Database - 1Test OLTP
Database - 2
Transaction logArchive logFlashback
Data files All files
24 DiskAggregate
32 Disk Aggregate
8 Disk Aggregate
Test OLTP Database - 1
Test OLTP Database - 2
Transaction logArchive logFlashback
Data files All files
24 DiskAggregate
32 Disk Aggregate
8 Disk Aggregate
-24%
17%
-25%
-20%
-15%
-10%
-5%
0%
5%
10%
15%
20%
Disk Utilization tpm
Aggregates: Improved Performance
De-duplication
Other Backup Methods: De-duplicationWhat:• 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-dupedevice
Other Backup Methods: De-duplicationPROS:• 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, de-duplication can work well with
multiple Oracle RMAN channels
CONS:• Initial costs
Storage security
Encryption by Storage VendorsWhat:• 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.
1) Oracle solutionsDatabase Encryption:
– Store data encrypted within database tables and, thus, also encrypted on tape– Data may be unencrypted in indexes, temp tablespace, flashback, audit, and elsewhere
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
Encryption by Oracle
Recovery Methodologies
An untested or undocumented backup and recovery plan is no plan at all
Addendum Slides
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• 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