<Insert Picture Here> Oracle 11g R2 The best Database for SAP Customers Relationship and Technology Update

(1)

<Insert Picture Here>

Oracle 11g R2

The best Database for SAP Customers Relationship and Technology Update

Pedro Lopes, CISSP

(2)

(3)

Oracle/SAP Relationship Overview

1988 2010 1992 1998 Start of SAP R/3 development (on Oracle DB) First SAP R/3 release (on Oracle DB) First SAP BW release (on Oracle DB) 2006 2007 2009 2003 1999 2005 2008 Oracle. SAP sign Reseller & Support Agreements Reseller & Support Agreements extended Reseller & Support Agreements extended Oracle DB 11g Rel.2

▪ OLTP Table Compression ▪ Secure Files Compression ▪ Deferred Segment Creation ▪ Online Patching

▪ Advanced Encryption ▪ Real ApplicationTesting ▪ Direct NFS

▪ Snapshot Standby Database ▪ Interval Partitioning,

Subpartitioning

▪ Oracle Cloud File System

(Jan 2011)

More Oracle DB 10g features:

▪ Index Key Compression ▪ Advanced Security (Encryption) ▪ MOPatch ▪ Database Vault ▪ Partitioning Engine for SAP ERP

Oracle DB 9i ▪ Automatic Memory Management ▪ Table Com- pression (Batch) ▪ Online Table Reorganization ▪ Real Application Clusters (RAC) Oracle DB 10g ▪ Database ▪ Table/Index Partitioning for SAP ERP (Service) ▪ RAC 2015 Oracle acquires Sun 2011

Oracle Linux (April 2011)

 Oracle Exadata (June 2011)

(4)

Oracle Customer Successes

(5)

Exadata Storage Server & Database Machine

• Exadata Storage Server

- Oracle Linux

- ASM

• Exadata Storage Server Software

- Priced per Disk Drive

• Sun Oracle Database Machine

- Exadata Storage Servers

- Exadata Storage Server Software

- Oracle Database 11.2 • Enterprise Edition

• Partitioning Option

• RAC

- Oracle Solaris, Oracle Linux

(6)

Exadata Benefits

• Extreme Performance for OLTP Applications —

- Transparently move hot data to Sun FlashFire, addresses the disk random I/O bottleneck.

- Get ten times faster I/O response time and use ten times fewer disks for business applications from Oracle as well as third-party providers.

• Extreme Performance for Mixed Workloads —

- Consolidate data warehousing, transaction processing, and mixed workloads on a massively parallel server grid, eliminates the cost of third-party specialty hardware, security, and management

solutions.

• Extreme Performance for Data Warehouses —

- Improves both query performance and concurrency by offloading intensive query processing and data mining scoring from database servers and bringing it closer to the data.

(7)

Oracle Database 11g R2 Features

Available for SAP customers

• Automatic Storage Management (ASM) to manage all

data

• Advanced Compression

• Online Patching

• Advanced Security

• SecureFiles

• Real Application Testing

(not included)

• Direct NFS

(8)

Oracle Database 11g R2 Features

Available for SAP customers

• Additional features for SAP

- Interval Partitioning

- Enhanced Add Column functionality

- Deferred Segment Creation

- Rename user / schema

- Non blocking online index creation and rebuild

(9)

Oracle Automatic Storage Management (ASM )

certified for SAP

• Oracle ASM is a volume manager and a file system for

Oracle database files that supports:

- single-instance Oracle Database and

- Oracle Real Application Clusters

• Oracle ASM is the preferred storage platform for SAP

systems running on Oracle single instance and RAC

- Since Oracle 11.2.0.2

• ASM is an alternative to conventional volume managers,

file systems, and raw devices.

- Eliminating the need for 3rd_{-party volume managers and file}

systems for managing the Oracle database files.

• ASM reduces Cost and Complexity without compromising

performance or availability.

(10)

Source: SAP Customer tests with production databases using all space features of Oracle 11g Rel.2

Oracle Database 11g R2

Space Savings for SAP customers

• Databases are:

- Up to 3 times smaller

• Backups are:

(11)

October 2010

“Big Picture” of supported Oracle releases

(12)

Oracle Database Update on SAP SDN

http://www.sdn.sap.com/irj/sdn/ora

(13)

(14)

(15)

http://www.oracle.com/us/solutions/volume20-sapnewsletter-eng-360057.pdf http://www.oracle.com/us/solutions/ora4sap-tech-update-sun-2010-322381.pdf

http://www.oracle.com/us/solutions/sap/database/sap-tech-update-current-183568.html

•Your choice: Innovative, integrated and flexible solutions from

desktop to storage for your SAP Data Center

•Providing a complete IT Infrastructure stack to run SAP

Applications even better

•Oracle Solaris: Breaking SAP benchmark records •Oracle Delivers Oracle Solaris Enhancements

•SAP on Oracle Solaris: The Oracle Solaris Operating System •Oracle refreshes the entire x86 Rackmount server+blades

portf.

•Take virtualization to the next level •Going Green with Oracle Systems

•Oracle’s Infrastructure for the SAP Cloud

•Oracle Partner – EDB Business, – Tata Sky Ltd. •Oracle Sun Customer Success

(16)

Only the

Oracle Database

provides for SAP customers …

Best Performance & Scalability Best Deployment Flexibility Best Availability & Reliability Best Support for Very Large DBs Best Database Security Best Manageability & Self-Management

(17)

Only the

Oracle Database

provides for SAP customers …

(18)

MVRC & Row Level Locking

• Multi-version Read Consistency

- Readers don’t block writers - Writers don’t block readers

• Automatic row-level locking

- reduces data contention

- Row locks are stored with the data providing unlimited row locking

- No lock escalation • Benefits

- Oracle never escalates locks

- Higher multi-user concurrency

- Higher Scalability

- Best Performance

Row Locks

Locked

(19)

Multi Version Read Consistency

High Throughput Concurrency Model

Performance Table Stakes Oracle Other DB

Unique Concurrency Model Yes No Readers Don’t Block Writers Yes No Writers Don’t Block Readers Yes No Guarantees Consistent Queries Yes No Eliminates Lock Escalation Yes No Reduces Deadlocks under Load Yes No

(20)

RAC Scalability and Performance

Linear scaling across SMP & Clusters for SAP

• Scales beyond DB2’s

best bench-mark

• Significantly better availability

• Less expensive server infrastructure

• Enables incremental change

- Leverage Moore’s

Law

• Supports both ERP

and SAP BW environments

Results, as of March 25, 2008, have been certified by SAP AG, www.sap.com/benchmark.

# of CPU Cores T ho usa nd s SD Users 0 5 10 15 20 25 30 35 40 4 8 32 48 64 80

World Record SAP SD Benchmark Results

Single Node SMP 2 Nodes 3 Nodes 4 Nodes 5 Nodes

(21)

182.112 320.363 609.349 900.309 0 200.000 400.000 600.000 800.000 1.000.000 1.200.000 Q u e ry N a v ig a ti o n S te p s /H o u r 8 8 16 24 32 # of CPU Cores

Results as of November 18, 2009: have been certified by SAP AG, www.sap.com/benchmark. Please see notes page for SAP benchmark certification details for the above results.

Best Business Intelligence Performance

World Record SAP BI Data Mart Benchmark

2 Node RAC 3 Node RAC 4 Node RAC Fujitsu RX300 Single Node SMP IBM DB2 1,165,742

Oracle _Oracle Oracle _Oracle

(22)

Certification number: 2010039

SAP SD on RAC/Solaris

(23)

RAC for SAP Benchmark results

• RAC scale out with 1.9 scalability factor

platform independent:

- Platform (HP-UX, Solaris, AIX, and Linux)

- Oracle version (10.2, 11.2) - SAP applications • NetWeaver BW • SAP ECC 5.0/6.0 • Unicode • Non-Unicode

(24)

One of Oracle’s main differentiators vs. other DB’s

for SAP customers:

Oracle Real Application Clusters (RAC)

• Removes database Server as Single Point of Failure

• All RAC nodes are active and serve production workload

• Flexible way to scale applications using a simple scale-out model

• Additional servers can be added easily and dynamically

- without taking any of the active users offline

• Database remains online – provided at least one node is running

• Transparent and available for all kinds of SAP Applications

- ABAP and JAVA

(25)

Oracle: Two Choices for Scale

SAP ERP and SAP BW

Shared Cache

Large SMP Scale Up Commodity Server Scale-Out

(26)

Only the

Oracle Database

provides for SAP customers …

(27)

Oracle – Best Deployment Flexibility

• Same code, functionality & tools across operating systems

- Enterprise Manager Grid Control

- SAP BR*Tools

- SAP DBA Cockpit

• Customers can choose most cost effective platform

• Customers not locked into any particular platform

• Oracle to Oracle migration tools for changing OS platform:

- Fast and efficient

- O2O (Oracle to Oracle Offline migration)

- Triple O (Oracle to Oracle Online Migration)

(28)

• Real Application Testing Option –

Unique to Oracle

• Able to capture and replay full production database workload

• Important for 10.2 to 11.2 Upgrade tests,

• Important for testing new features

• More functionality, more flexibility, less expensive than 3rd party products

Oracle Database 11g R2

Real Application Testing

(29)

… … Client Client

…

Client Capture DB Workload

Real Application Testing with

Database Replay

• Recreate actual production database workload in test environment

• Capture workload in production including critical concurrency

• Replay workload in test with actual production timings

• Analyze & fix issues before moving to production

MiddleTier Storage Oracle DB Replay DB Workload Production Test Test migration to RAC

(30)

… … Client Client

…

Client Capture SQL

• Test impact of change on SQL query performance

• Capture SQL workload in production including statistics & bind variables

• Re-execute SQL queries in test environment

• Tune regressed SQL and seed SQL plans for production

MiddleTier Storage Oracle DB Re-execute SQL Queries Production Test Use SQL Tuning Advisor to tune regression

Real Application Testing

(31)

• Use Standby Database for testing

and development

- Eliminates cost of DR

- Justify full scale test and DR

environments by combining into one

• Preserves zero data loss while in

test/dev mode

- But no real time query or fast failover

• Similar to storage snapshots, but:

- Provides DR at the same time

- Single copy of storage

Physical Standby

Apply Logs

Snapshot Standby

Perform Testing Open Database Back out Changes

Continuous Redo Shipping

Snapshot Standby

(32)

Only the

Oracle Database

provides for SAP customers …

(33)

Presentation Application Database

Real Application Clusters for SAP

Architecture

Table A

Table B Table C

(34)

Real Application Clusters for SAP

Benefits

• High Availability

- Removes Database Server as Single Point of Failure

• Performance & Scalability

- Flexible way to linearly scale SAP applications

• scale-out model

• SAP Support

- ASM and ACFS are the preferred storage for SAP cust. (check SAP Note 1550133)

- RAC on third party shared cluster file system is still supported

• SAP Note 527843

• Total Cost of Ownership

(35)

Oracle Automatic Storage Management (ASM )

certified for SAP

• Oracle ASM is a volume manager and a file system for Oracle

database files that supports:

- single-instance Oracle Database and

- Oracle Real Application Clusters

• Oracle ASM is the preferred storage platform for SAP systems

running on Oracle single instance and RAC

- Since Oracle 11.2.0.2

• ASM is an alternative to conventional volume managers, file

systems, and raw devices.

- Eliminating the need for 3rd_{-party volume managers and file systems}

for managing the Oracle database files.

ASM reduces Cost and Complexity without

compromising performance or availability

(36)

Real Application Clusters for SAP

Some Customer Examples

Customer Name OS DB Size RAC Nodes

Conc. Users

CAT Logistics AIX 20 TB 7 x 4 20,000

Gruppo Hera Solaris 44 TB 2 2,000

Pirelli HP-UX 6 TB 4 >5,000

SWM Windows 8 TB 2 2,000

(37)

Data Guard for SAP

• Preferred DR solution for SAP customers

• Complements RAC and Flashback technologies

• Some SAP customer examples:

- Nike

- Chevron

- BASF

(38)

Oracle Database 11g R2 provides fully integrated

HA and DR solution for SAP Applications

• RAC

- High Availability

• SAPCTL / Clusterware

- Manages and protects SAP Enqueue Server for both ABAP and Java stacks

- Protects the Message Server

- Covers additional Dialog Instances in Cluster

• Data Guard

- Maximum Protection of Primary and Standby databases completely in sync

- Maximum Performance

• Transparent Failover of SAP Application Servers

- Uses Oracle Net Services

High Availability and Disaster Recovery

Best Solution for SAP Applications

(39)

Standby Databases Are Not Idle

Standby database can be used to offload

the primary database, increasing the ROI

Real-time Queries Active Standby Database Production Database Continuous Redo Shipment and Apply

Offload Backups Offload

(40)

Patches are applied while the database is up and running:

• Works for both Single Instance and RAC

• No more planned downtime needed for patching

• Processes ―jump‖ to patched code at a safe execution point.

• Online patches are activated at an instance level

- Different than traditional patches activated at the $ORACLE_HOME level

• Applying an online patch is performed using OPatch and MOPatch

- also possible to apply via EM deployment procedures

• Each patch is assessed to determine if it can be applied online

- The ultimate goal is to make all patches online patches

Oracle Database 11g R2

Online Patching

(41)

Only the

Oracle Database

provides for SAP customers …

(42)

Most Efficient Use of Disk Space

• Variable length number representation

• Variable length character representation

• Variable length Unicode representation

(43)

Storage Efficiency: Technology Stack

• Most efficient storage of data

- Number values

- Bitmap Index

• Most comprehensive set of compression options

• Advantages increase when used in

Combination with other Database options – like Partitioning

• Easy administration

- Reduces the number of required reorganizations

Advanced Compression (Oracle 11g)

Partitioning + Compression (Oracle 10g)

Index Key Compression (Oracle 10g)

Data Compression (Oracle 10g)

(44)

• OLTP Table Compression

• RMAN Backup Compression – Unique to Oracle

• Expdp Compression

• Data Guard Network Compression – Unique to Oracle • SecureFile Compression for in-line & out-of-line LOB data

• Unique to Oracle for out-of-line LOBs - IBM can only do in-line compression

• Out-of-line LOBs are more used than in-line LOB data in SAP products such as;

- SAP CRM

- SAP XI

- SAP Portal

- SAP R/3 (almost all non-cluster tables use out-of-line LOBs)

Oracle Database 11g R2

Advanced Compression

(45)

<Insert Picture Here>

Testing results of Oracle Advanced Compression for SAP: “When we were recording the basic data over two days for the test, we couldn’t find any negative effects on the CPU or I/O load of the production system and the dialog response times didn’t change. The compression result was quite impressive. The amount of disk space shrunk from almost 3.6 TB to around 1.6 TB, resulting in a space saving of 55%.”

Manfred Fischer, Manager of SAP system Maintenance, SWM Services GmbH, Information and Process Technology

(46)

Oracle Database 11g R2

SecureFiles

SecureFiles

• Significantly faster access times compared to LOBs in SAP environments

• Increased transaction throughput on SAP cluster tables especially with RAC

• Prerequisite for compression of SAP tables containing LOBs

- (e.g. Cluster Tables)

SAP VBDATA Throughput (Insert/Read/Delete)

0 0,5 1 1,5 2 2,5 1 2 4 8 16 32 64 row size in KB P er fo rm an ce Im pr ov em en t F ac to r

(47)

Flashback Database to reduce downtime

Suitable for any DB size

• A new strategy as alternative to point in time recovery

• Eliminate the need to restore a whole database backup (reduces downtime)

• Integrated seamlessly with RMAN & BR*TOOLS (SAP note 1125923)

• Think of it as a continuous backup

• Restores just changed blocks

• It’s fast - recover in minutes, not hours

• It’s easy - single command restore

RMAN> flashback database TIMESTAMP

to_timestamp('2009-03-15 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

SQL> flashback database to restore point <restore point>

(48)

Table & Index Partitioning

SAP Support

• All types of partitioning are supported

• Range partitioning used by default

• Same set of tables can be partitioned in all systems

• Standard solution (part of installation)

• All types of partitioning are supported

• Partitioning not used by default

• Best candidates need to be selected for every system

• Oracle Support & Services for SAP can be engaged

• SAP provides Partitioning Engine: SAP Note 1333328

SAP NetWeaver BW

SAP OLTP

(e.g. ERP, CRM)

As of SAP_BASIS Release 700 and with Oracle 11.2, Sub- and Interval Partitioning are also supported (SAP Note 742243)

(49)

Only the

Oracle Database

provides for SAP customers …

(50)

Advanced Security Option (ASO)

Application Network Encryption  SAP Note 973450 Database Server Instance Database Files Database Backup

Transparent Data Encryption

 SAP Note 974876

Backup Set Encryption

(51)

• Column Encryption through TDE

• Client-Server Network Encryption

- SAP Application Server to Database Server

• Tablespace Encryption

• DG Secure Network Transport

• RMAN Backup Encryption

• Expdp Encryption

• SecureFile (unstructured LOB data) encryption

(52)

Separation of Duties

Oracle Database Vault for SAP

Application DBA Run application Application Users ALTER, DROP SELECT SOME_APP objects SAP Note 1241462

(53)

Oracle Database Vault

DBA HR DBA HR HR Realm HR

• Database DBA views HR

data

select * from HR.emp

Compliance and

protection from insiders

Fin

FIN DBA

• HR DBA views Fin. data

Eliminates security

risks from server

consolidation

Fin Realm

(54)

Oracle Database Vault

• Controls on privileged users

- Restrict highly privileged users from application data

- Provide Separation of Duty

- Security for database and information consolidation

• Real time access controls

- Control who, when, where and

how data is accessed

- Make decision based on IP address, time, auth…

Reports Protection Realms Multi-Factor Authorization Separation of Duty Command Rules

(55)

Oracle Database Vault

HR account FIN DBA HR FIN SELECT …. CREATE … Business hours Unexpected IP address

(56)

Only the

Oracle Database

provides for SAP customers …

(57)

Intelligent DB Self-Management

Integrated in SAP DBA Cockpit & SAP BR*Tools

• Automatic Workload Repository (AWR)

- Automatically collects and maintains SQL execution statistics

- Integrated with SAP DBA Cockpit

• Active Session History (ASH)

- Automatically captures and analyzes wait-time statistics

• Automatic Performance Diagnosis (ADDM)

- Self-diagnostic engine built into the Oracle Database

- Automatically and proactively identifies problems

- Notifies DBA with corrective advice

• Automatic SQL Tuning

- Tunes SQL without touching application code

(58)

(59)

What is the Oracle Exadata Database Machine?

- A complete system for data warehousing, OLTP, and / or database server & storage consolidation

- A system Oracle and selected partners sell, install (including optimal configuration of a working database), and support

• Components include standard Sun hardware, Oracle Database 11g Release 2, Oracle Linux (& Solaris 11 Express as an option for nodes)

• Oracle Exadata Storage Server Software manages storage and provides functionality not found on other storage

(60)

Exadata Database Machine Hardware

• Complete, Pre-configured, Tested for

Extreme Performance

- Database Servers

- Exadata Storage Servers

- InfiniBand Switches

- Ethernet Switch

- Pre-cabled

- Keyboard, Video, Mouse (KVM) hardware*

- Power Distribution Units (PDUs)

• Ready to Deploy

(61)

Exadata Hardware Architecture

Database Grid Intelligent Storage Grid

InfiniBand Network

• Redundant 40Gb/s switches

• Unified server & storage network • 14 High-performance low-cost storage servers • 8 Dual-processor x64 database servers OR • 2 Eight-processor x64 database servers

Scalable Grid of industry standard servers for Compute and Storage

• Eliminates long-standing tradeoff between Scalability, Availability, Cost

• 100 TB High Performance disk, or

336 TB High Capacity disk

•5.3 TB Smart Flash Cache

•Data mirrored across storage servers

(62)

Standardized and Simple to Deploy

• All Database Machines are the same

• Delivered ready-to-run

• Tested

• Highly supportable

• No unique configuration issues

• Identical to config used by Oracle Engineering

• Runs existing OLTP and DW applications

- Full 30 years of Oracle DB capabilities

- No Exadata certification required

• Leverages Oracle ecosystem

- Skills, knowledge base, people, partners

Deploy in Days, Not Months

(63)

Exadata Storage Server Building Block

• High-performance storage server built from industry standard components

• 12 disks - 600 GB 15000 RPM High

Performance SAS or 2TB 7200 RPM High Capacity SAS

• 2 Six-Core Intel Xeon Processors (L5640)

• Dual ported 40 Gb/sec InfiniBand

• 4 x 96 GB Flash Cards (384 GB/cell)

• Intelligent Exadata Storage Server Software

• Hardware by Sun

(64)

Exadata Database Machine X2-8 Full Rack

Extreme Performance for Consolidation, Large OLTP and DW • 2 x64 Eight-processor Database servers (Sun Fire 4800)

- High Core, High Memory Database Servers

- 128 CPU cores (64 per server)

- 2 TB (1 TB per server)

- 10 GigE connectivity to Data Center

• 16 x 10GbE ports (8 per server)

• 14 Exadata Storage Servers X2-2

- All with High Performance 600GB SAS disks OR

- All with High Capacity 2 TB SAS disks

• 3 Sun Datacenter InfiniBand Switch 36

- 36-port Managed QDR (40Gb/s) switch

• 1 ―Admin‖ Cisco Ethernet switch

• Redundant Power Distributions Units (PDUs)

(65)

Exadata Database Machine X2-2 Full Rack

Pre-Configured for Extreme Performance

• 8 x64 Dual-procesor Database Servers (Sun Fire X4170 M2)

- 96 cores (12 per server)

- 768 GB memory (96GB per server)

• Keyboard, Video, Mouse (KVM) hardware

(66)

Can Upgrade to a Full Rack

Exadata Database Machine X2-2 Half Rack

(67)

Can Upgrade to an Half Rack

Exadata Database Machine X2-2 Quarter Rack

Pre-Configured for Extreme Performance

(68)

Start Small and Grow

Field Upgradeable

Full

Rack

Half

Rack

Balanced Incremental Scaling for OLTP and DW

Quarter

(69)

Scale to 8 Racks by Just Adding Cables

Full Bandwidth and Redundancy

(70)

Complete Family Of Database Machines

For OLTP, Data Warehousing & Consolidated Workloads

• Quarter, Half, Full and Multi-Racks _{• Full and Multi-Racks}

Oracle Exadata X2-8 Oracle Exadata X2-2

(71)

Exadata Configurations

X2-8 Full Rack X2-2 Full Rack X2-2 Half Rack X2-2 Quarter Rack Database Server Nodes No. of Nodes 2 8 4 2 No. of CPUs ₁₆ ₁₆ ₈ ₄ No. of Cores ₁₂₈ ₉₆ ₄₈ ₂₄ Memory _{2 TB} _{768 GB} _{384 GB} _{192 GB}

No. of InfiniBand Switches 3 3 3 2

Exadata Storage Server Cells No. of Cells 14 14 7 3 No. of CPUs 28 28 14 6 No. of Cores 168 168 84 36 Number of Disk 168 168 84 36

Building blocks: X2-2 nodes = Sun Fire X4170 M2 w/ Intel Xeon X5670 CPUs X2-8 nodes = Sun Fire X4800 w/Intel Xeon X7560 CPUs

(72)

Exadata Product Capacity

X2-8 Full Rack X2-2 Full Rack X2-2 Half Rack X2-2 Quarter Rack Raw Disk1

High Perf Disk 100 TB 100 TB 50 TB 21 TB

High Cap Disk _{336 TB} _{336 TB} _{168 TB} _{72 TB}

Raw Flash1 _{5.3 TB} _{5.3 TB} _{2.6 TB} _{1.1 TB}

User

Data2

(assuming no compression)

High Perf Disk 28 TB 28 TB 14 TB 6 TB

High Cap Disk

100 TB 100 TB 50 TB 21 TB

1 – Raw capacity calculated using 1 GB = 1000 x 1000 x 1000 bytes and 1 TB = 1000 x 1000 x 1000 x 1000 bytes. 2 - User Data: Actual space for end-user data, computed after single mirroring (ASM normal redundancy) and after allowing space for database structures such as temp, logs, undo, and indexes. Actual user data capacity varies by application. User Data capacity calculated using 1 TB = 1024 * 1024 * 1024 * 1024 bytes.

(73)

Exadata – The Oracle Database Machine

Summary

Extreme Performance

•Runs Oracle Faster (DW/OLTP) •Scalable on Demand

•Smart Storage

•Optimized, Standardized, Integrated •Server, Storage, Network, Software •Ready within days

Lower Cost •Best Database Consolidation Platform •Reduced Storage requirements •Leverage Your Existing Investment

(74)