1 copyright © 2012, oracle and/or its affiliates. all ... · pdf file–ar...


Safe Harbor Statement

The following is intended to outline our general product

direction. It is intended for information purposes only, and

may not be incorporated into any contract. It is not a

commitment to deliver any material, code, or functionality,

and should not be relied upon in making purchasing

decisions. The development, release, and timing of any

features or functionality described for Oracle’s products

remains at the sole discretion of Oracle.


Optimizing Your E-Business Infrastructure More Performant, better Productivity and reduced Costs

Didier Wojciechowski

Solution Architect


About the speaker

15 years working on E-Business Suite .

5 Years in Oracle Services (Consulting) – France & Africa

Appstech consultant

2 Years in Oracle Sales (Presales) – Switzerland

3 Years in Oracle OnDemand

5 Years in Oracle Sales (Presales) – Global Sales support

4 Years Oracle/IBM Joint solution Center (IBM montpellier)

Oracle Apps specialist - Performance -Benchmarks

Member of the Global Sales Engineered Systems Solutions Architecture Group


Program Agenda

The challenges when deploying EBS

Performance

Availability

Manageability

Security

Product Certification

Q&A


Challenges when deploying EBS



Release 8.3.5 1992*

Release 9.3 1993*

Release 9.4 1994*

Release 10.5 1995*

Release 10.6 1996*

Release 10.7 Feb-1997

Release 11 May-1998

Release 11.5 (11i) May-2000

Release 11.5.10.2 (11i10.2) Jul-2005

Release 12 Feb-2007

Release 12.1 Apr-2009

Release 12.2 (Planned)

Background: Release History



Release 11i

Software 26 Gb

Production Database 31 Gb

Vision Database 65 Gb

Release 12.0.4

Software 28 Gb



Release 12.1.1

Software 35 Gb



Background: Footprint

Release 12.2

Software 60 Gb



FusionApps

Software ? Gb

Production Database ? Gb

1 Tb

COMING

SOON


Challenges when deploying EBS Background: Trend

Features Footprint

Installation Time



• Performance

• Maximum Availability

• Manageability and Maintenance

• Security



• OLTP response time to keep under the SLA

• OLTP peak day time

• Day-to-Day batch performance

• Nightly Batch window that are slipping during daytime

• Period closure that are facing delays

• Long running batches with auditors waiting for their

reports.

• SQL Plan changes

Challenge #1: Performance



• MiddleTier response time as the number of users

increase

• 24/7 requirements

• Service Level Agreement

• Unplanned outage (hardware failure, network, power,etc)

• Planned downtime (patching, upgrade)

Challenge #2: Maximum Availability



• Ongoing patches maintenance

• Service Request: Probleme reproduction, testing,

deployment

• Technology Stack maintenance

– Database upgrade

– Middle Tier upgrade

../..

Challenge #3: Maintenance and Manageability (1/3)



• Release updates: will there be enough hardware

resource(s) available to test or evaluate EBS release

updates:

– From 11i to R12.x

– From R12.0 to R12.1

– From R12.1 to R12.2

– To FusionApps

../..




• Many environments to maintain (5 to 25 or more)

• Environment provisioning

– Testing requirements to be adressed

– Environment sharing is difficult

– Cloning time turnover is critical

– Environment management is a full time job(s)



Performance


Performance

• In R12.0, most EBS critical concurrent programs now

have multi-threaded capabilities:

– AR Autoinvoice

– XLA CreateAccounting

– AP Invoice Validation

[…]

• But most implementers leave ‘by default’ options

Applicative parallelism


Performance

• Built-in database feature to provide optimized plan

recommendations

• SQL Tuning advisor is accessible from both:

– The database console

– The Grid Control

– Command line (SQL*Plus) using DBMS_AUTO_SQLTUNE

• SQL Tuning Advisor can be used to check if there is a

quickwin possible fix, waiting for a SR to be logged, or

the issue to be investigated further

SQL Tuning Advisor


Performance

SQL Tuning Advisor: Grid Control


Performance

SQL Tuning Advisor: Case Study


Performance

• Real Application Cluster can be considered to increase

the workload throughput.

• RAC may be the only viable and scalable solution to

achieve the performance

• A wise setup is required with EBS

Real Application Clusters


Performance

• Financials Accounting Hub

• 15 Millions of events to process

• RAC 4 nodes

• Concurrent managers configured in load balancing mode

over the 4 RAC nodes

Create Accounting Performance w/ RAC – Case Study (1/7)


Performance Create Accounting Performance w/ RAC – Case Study (2/7)


Performance


Night Batch Activity Daylight OLTP

CA

XLAACCPB

CE

XLAACCUP

Accounting Workers

Unevenly split over all

RAC nodes


Performance

• Concurrent Manager server load balancing results into

into uneven database server load and unpredicable

elapsed time

• Solution:

- Configure node affinity with one apps node per db node

- Split the load over the 4 apps node



Performance Create Accounting Performance w/ RAC – Case Study (5/7)

server02

server04

server01

server03

db_prod_01 db_prod_02

PROD01

appserver03appserver02

PROD02

Service BATCH_A

Web/

FormsCPMGR

Web/

FormsCPMGR

ASM01 ASM02

db_prod_03

PROD03

ASM03

db_prod_04

PROD04

ASM04

Service OLTP_BAL

Service BATCH_C

appserver01

CPMGR

Service BATCH_B Service BATCH_D

appserver04

CPMGR


Performance


Night Batch Activity Daylight OLTP

CE

CA

XLAACCPB 01

XLACCCUP 01

XLACCCUP 03

XLACCCUP 02

XLACCCUP 01

XLACCCUP 01

XLAACCPB 02CE

CE

XLACCCUP 02

XLACCCUP 02

XLAACCPB 03

XLACCCUP 03

XLACCCUP 03

XLACCCUP 03

CE

XLAACCPB 04

XLACCCUP 04

XLACCCUP 04


Performance

Lessons learnt for FAH/CreateAccounting:

• Understand the batchsize and workers settings

• Having a smaller number of powerfull RAC nodes is

better than having a high number of small RAC nodes

• Define one apps server per database node

• Use Instance affinity and FND specialization rules to split

the workload over multiple RAC instances



Performance

• Create the following Cluster Database Services: <PROD>_FORMS

<PROD>_SSA

<PROD>_BATCH_A

<PROD>_BATCH_B

• Specify the alias in the $TNS_ADMIN ifile file <PROD>_CLIENT_FORMS=

(DESCRIPTION=

(ADDRESS_LIST=

(LOAD_BALANCE=YES)

(FAILOVER=YES)

(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1531))

(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1531))

)

(CONNECT_DATA=

(SERVICE_NAME=<PROD>_FORMS)

))

LoadBalancing w/ RAC – Best Practices (1/3)


Performance

• Update in autoconfig on all nodes – Tools OH TWO_TASK’ (s_tools_two_task) to <PROD>_FORMS

– ‘iAS OH TWO_TASK’ (s_weboh_twotask) to <PROD>_SSA

– ‘Apps JDBC Connect Alias’ (s_apps_jdbc_connect_alias) to <PROD>_SSA

• Update in autoconfig on first node – Concurrent Manager TWO_TASK’ (s_cp_twotask) to <PROD>_BATCH_A

• Update in autoconfig on second node – Concurrent Manager TWO_TASK’ (s_cp_twotask) to <PROD>_BATCH_B



Performance

• Update APPLFSTT from all middle tier nodes to ensure

that the <PROD>_CLIENT_FORMS is specified <APPLFSTT oa_var="s_applfstt">

…;<PROD>_FORMS;…

</APPLFSTT>

• If this step is not performed, the following error may be

returned while opening the concurrent requests output

and logfile IAP-CANNOT READ FIELD (FIELDNAME=PARAMETER.CONFIG)



Performance

Usual situation:

• Plan regression after patch application

• Typical of R12 usage of GTT *, when the same program

is run for two different workloads from different

subsidiaries, depending on the order of company these

program are run, the optimizer may choose and hold a

path that will not be suitable for companies that are run

second and typically with a bigger workload.

SQL Plan Management (1/6)

* : Global Temporary Table


Performance

Prior to 11g

• Unpredictable changes can happen to an execution plan

• Avoiding plan changes the only method to avoid performance regression

– Lock Statistics to prevent them from changing

– Freezing an execution plan with a Stored Outline

– Hints

– Code changes

• No mechanism for plans to evolve



Performance

New with 11g

• SQL Plan management with 3 main components

– SQL Plan baseline capture

– SQL Plan baseline selection

– SQL Plan evolution



Performance

• Managed from

– Command lines (SQL*Plus) using DBMS_SPM

– Database console or Grid Control

• Monitored from

– DBA views DBA_SQL_PLAN_BASELINES

– Database console or Grid Control



Performance



Performance

• Oracle Applications fully supports the use of custom

partitioning of either Applications standard or custom

tables

• Custom partitioning = Changing the partitioning definition

of an existing applications table as delivered out-of-the-

box from the standard installation

Partitioning: Support Note


Performance

• Table availability

– Significantly reduce recovery times of key transaction tables by

recovering specific partitions first.

• Table manageability

– Backup, restore, and rebuild at the partition level.

– Index rebuilds can be performed at the partition level.

– Partition aware operations such as MOVE, EXCHANGE,

REBUILD can be used without affecting active partitions

Partitioning: Benefits (1/2)


Performance

• Performance

– Improves access path of most queries since the majority of the

access involves current data (as opposed to historical data)

– Optimizer automatically prunes unnecessary partitions.

– Analytical reports or period close jobs/reports improve by

scanning the current partition as opposed to all the partitions.

– Improves purge performance.

– Significantly improves upgrade performance

– Minimizes upgrade downtime

Partitioning: Benefits (2/2)


Performance

• Subleddger Accounting Architecture

• Payables (Trial Balances)

• Advanced Planning and Scheduling

• Projects Resources

• Workflow

• Daily Business Intelligence

• HR (Employee Directory)

• Engineering

Partitioning: Usage in standard products


Performance

Partitioning: Constant innovation

Core functionality Performance Manageability

Oracle 8.0 Range partitioning

Global Range indexes

Static partition pruning Basic maintenance:

ADD, DROP, EXCHANGE

Oracle 8i Hash partitioning

Range-Hash partitioning

Partition-wise joins

Dynamic partition pruning

Expanded maintenance:

MERGE

Oracle 9i List partitioning Global index maintenance

Oracle 9i R2 Range-List partitioning Fast partition SPLIT

Oracle 10g Global Hash indexes Local Index maintenance

Oracle 10g R2 1M partitions per table Multi-dimensional pruning Fast DROP TABLE

Oracle 11g Virtual column based partitioning

More composite choices

REF partitioning

Interval partitioning

Partition Advisor

Incremental stats mgmt

Oracle 11g R2 Hash-Hash partitioning

Expanded REF partitioning

“AND” pruning Multi-branch execution


Performance

• International Bank

• Data Volumes – GL_JE_LINES (1.1 Billion rows)

– GL_CODE_COMBINATIONS (203 Million rows)

– GL_BALANCES (1.3 Billion rows)

• Partitioning Method: Range (set_of_books_id)

• # of Partitions: 34

• Achieved 11.4M journal lines imported and posted per

hour

Partitioning: Case Study 1 (Ledger)


Performance

• Australian Bank

• Data Volumes – GL_JE_LINES (650 Million rows)

– GL_CODE_COMBINATIONS ( 8.5 Million rows)

– GL_BALANCES (200 Million rows)

• Partitioning Method: Range (period_name)


• Achieved 7.5M journal lines imported and posted per

hour

Partitioning: Case Study 2 (Ledger)


Performance

• Data Volumes – AP_LIABILITY_BALANCE (70M rows)

• Partitioning Method: Hash (org_id)


• Trial Balance report runtime reduced from 2 hours to 10

minutes.

Partitioning: Case Study 3 (Payables Trial Balance)


Performance

• Consider replacing uneffective, possibly outdated,

custom scripts with more efficient Grid control

monitoring.

Custom monitoring Scripts


Performance Custom monitoring Scripts: Case Study


Performance

RUEI: Real User Experience Insight

Network Switch / Tap

RAC

Web Servers

App Servers

Internet

Firewall

VPN

• How to accurately monitor user experience?

• Performance

• Different Locations/Geographies

• Transaction success/failure/abandonment

• How to objectively measure end-user satisfaction

with an application?

• Synthetic Transactions

• Compare actual to planned

• Capture and store ALL user activity at convergence

point

• Agentless - Zero performance impact

• No application changes required

Capture

Point

Challenge

Solution


Performance

• A few users facing unusual performance issue

• FRD tracing show no issue

• SQL tracing show no issue

• Man days spent in troubleshooting

• End user unhappy

• REUI installed for evaluation

RUEI: (Real) Case Study


Performance RUEI: Case Study: Page loading time per user (2/10)


Performance RUEI: Case Study: Page loading time satisfaction (3/10)


Performance RUEI: Case Study: Page load and reading time (4/10)


Performance RUEI: Case Study: Object size details


Performance RUEI: Case Study: Object and Size


Performance RUEI: Case Study: Traffic Size


Performance

• Most e-Business suite environments are customized.

• It is not unusual to see customizations listed in the 10

slots of the top 10 consumers in AWR Reports.

• Have ACS or OCS review a few significant

customizations.

• Make sure PL/SQL programs do not implement row-by-

row processing: Implement bulk collect for performance.

Customisations code review (1/2)


Performance

• Make sure exception handling is properly adressed, as

data corruption are a more serious matter than

performance.

Customisations code review (2/2)


Performance

• Use applicative parallelism

• Use SQL Tuning Advisor for quickwin advise

• Consider RAC to increase the workload throughput

• Use partitioning (XLA, eTAX)

• Leverage native and lightweight Grid monitoring

• Use RUEI for end-user real performance analysis

• Have your customizations code reviewed by experts

Summary


Q&A


Maximum Availability


“Anything that can go wrong, will go wrong.”

Murphy’s Law



The Register, January 2012

Tieto, a prominent Swedish IT service supplier, had a storage

array fail on 25 November, causing five days of chaos …

...SBAB bank was heavily affected, despite having a 99.8%

uptime agreement with Tieto

The stoppage was caused by failures in a storage array and

compounded by an inadequate disaster recovery plan

involving tape backup files which could not be read.

http://www.theregister.co.uk/2012/01/13/tieto_emc_crash/

http://www.channelregister.co.uk/2012/01/16/tieto_vnx5700/

http://www.theregister.co.uk/2012/01/13/tieto_emc_crash/

http://www.channelregister.co.uk/2012/01/16/tieto_vnx5700



• Site outage due to natural disaster (fire, flood, fire, etc)

• Site outage due to failure (power outage, A/C outage)

• Local outage

– Planned maintenance (operating system or database upgrade)

– Faulty component (memory, CPU)

– Data corruption

– Bug

– Human error

Requirements



Adressing both planned and unplanned downtime

Flexible

Maintenance

and

Migrations

Media and

Storage

Failures

Human or

Application

Error

Database,

System,

Cluster, Site,

and

Geographic

Outages

Server

Failure

• Storage failure

•Data recovery

•Backups

Automatic

Storage

Management

Recovery

Manager

(RMAN),

Oracle Secure

Backup

ACFS

• Server failure

• Instance failure

• Server failure

•Rolling maintenance

•Active-Active: performance scale-out

MiddleWare

Clustering

Oracle RAC

• Fast point-in-time recovery

•Granular repair of logical corruptions

• Transaction

• Table

•Database

Flashback •Database failure

• System failure

• Site failure

• Zero data loss

•Automatic failover

•Best data protection

•Database rolling upgrade

•Offload read-only workload and backups

Active Data Guard

• Flexible maintenance

•Heterogeneous migrations

• Schema migration

•Bi-directional and multi-master replication

• Zero downtime maintenance

GoldenGate

EBS 12.2

•Online Patching



MAA Target Architecture

Primary Site

Oracle

RAC and

ASM

Disaster Recovery Site

Oracle Data Guard

Oracle

Database

Oracle

Standby

Database

Database Tier Oracle

RAC and

ASM

Application Tier

HA Storage



MAA Target Architecture: Database

Primary Site Disaster Recovery Site

Real Application Clusters

& ClusterwareFault Tolerant

Server Scale-Out

Data GuardFully Active

Failover Replica

FlashbackCorrect Errors by

Moving Back in Time

Automatic Storage

ManagementFault Tolerant

Storage Scale-Out

Recovery Manager &

Oracle Secure BackupLow Cost High Performance

Data Protection and Archival

Database Servers Database Servers

Storage Storage



MAA Target Architecture: Apps Tier Hardware Load BalancersRedundant Configuration

Application Tier

Database Tier

Multiple Web ServersLoad Balanced

Parallel Concurrent ProcessingFault tolerant batch processing

Database or Application Tier

Multiple Forms ServersLoad Balanced



A better alternative than other (former) cold failover (ex

HACMP over GPFS)

• ASM Compatible

• Same tools, interface, processes than RAC multi-nodes

• Not manual steps required after a failover.

RAC One node



Active DataGuard

NetworkBroker

ProductionDatabase

Logical StandbyDatabase Open for

Reports

SQLApply

Transform Redo to SQL

Physical StandbyDatabase

DIGITAL DATA STORAGE

DIGITAL DATA STORAGE

Backup

Redo Apply

Sync or Async Redo Shipping

Madrid

Paris

Milano


Maximum Availability Active DataGuard



Log Buffer

Online Logs

Archive Logs

Flashback Logs

Control Files

Data Files

SYSTEM

USER

TEMP

UNDO

Primary Database Standby Database

Data Guard

Oracle Apply

& Validation

End-to-end validation

Storage agnostic

Automatic block repair

Real-time reporting

No data or storage

type restrictions

Strong isolation

Detect silent corruption

Active Dataguard vs Storage Mirroring



Primary Volumes Target Volumes Network I/O

7X more

network volume

27X more

network I/Os

No Oracle validation

Poor isolation

Idle standby

systems

Log Buffer

Online Logs

Archive Logs

Flashback Logs

Control Files

Data Files

SYSTEM

USER

TEMP

UNDO

Active Dataguard vs Storage Mirroring



• Its limitation in term of report types supported were so

limited than it is not really usable.

Active DataGuard for reporting



• Use DataGuard/Snapshot Standy to keep fresh d – 1

pre-prod environmnent to reproduce production issues.

Active DataGuard and Snapshot Standby



• Deploying Oracle GoldenGate to Achieve Operational

Reporting for Oracle E-Business Suite.(Doc ID 1112325.1)

GoldenGate



• R12.2 is around the corner with hot patching capabilities

• This was the missing piece for a full MAA capabilities

R12.2 w/ Online Patching



Be the first to know

Don’t forget Monitoring


Availability

Consider …

• RAC for hardware failure

• Active DataGuard for

– site outage

– reporting or pivot

• Flashback for human or application error

• Moving to R12.2 to benefits from online patching

• Goldengate for database migration

Summary


Manageability


Manageability

• Automatic Storage Management (ASM) has been

introduced with 10gR2 to simplify the database files

management

• Can be administered from

– Grid Control

– asmca (GUI interface)

– asmcmd (command line interface)

– SQL*Plus

Automatic Storage Management


Manageability

• ORA-1653 cannot allocate extent of size x in tablespace y

• The number of occurrence of this error means it is time

to move on to Automatic Management with easier to

maintain syntax and lower maintenance cost: SQL> CREATE TABLESPACE apps_ts_data

DATAFILE ‘+DG_DATA’ SIZE 200G

;

Automatic Storage Management: ORA-01653


Manageability

• Still creating 2Gb datafile?

• Specify your datafiles bigfile, autoextend, and monitor

the expansion growth of your ASM datagroup using the

Cloud Control SQL> CREATE BIGFILE TABLESPACE apps_ts_index

DATAFILE ‘+DG_DATA‘ SIZE 100G

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

AUTOEXTEND ON;

Automatic Storage Management: Datafiles management


Manageability

• Need disk space?

• Expose a new LUN to the disk group.

• ASM will automatically balance the existing datafiles to

take into account the newly added disk group members

Automatic Storage Management: Disk Management (1/2)


Manageability

• Need to replace storage?

• Connect the new storage

• Expose the new LUNs to the disk group

• Delete the old LUNs from the disk group

• Wait for the ASM load balancing to finish

• Remove the old storage

Automatic Storage Management: Disk Management (2/2)


Manageability

Automatic Storage Management: Grid Control View (1/2)


Manageability

Automatic Storage Management: Grid Control View (2/2)


Manageability

• Noticable storage increase from 11i to R12.x

– Subledger Accounting Architecture

– E-Business Tax

• Government Regulations (Sarbanes-Oxley, etc) with

requirements to keep online years of accounting or

transactional data

Advanced Compression


Manageability

• Index Compression (8i)

• Table Compression (9iR2)

• Advanced Compression (11gR1)

• Advanced Compression further enhancements (11gR2)

• Hybrid Columnar Compression (11gR2 w/Exadata)

Advanced Compression: Background


Manageability

Also new with Advanced Compression

• DataGuard network compression

• Datapump compression

• RMAN backup compression

• SecureFiles compression

Advanced Compression: Background


Manageability

• Works by eliminating duplicate column values in leaf

index blocks

• Limitation:

– Does not work on single-column unique indexes

Advanced Compression: Index Compression (1/2)


Manageability

SQL> ALTER INDEX gl.gl_balances_n3 REBUILD NOCOMPRESS;

Index altered.

SQL> SELECT bytes/1024 FROM dba_segments WHERE segment_name='GL_BALANCES_N3';

BYTES/1024

----------

172416

SQL> VALIDATE INDEX gl.gl_balances_n3;

Index analyzed.

SQL> SELECT name, blocks, lf_blks, br_blks, opt_cmpr_count, opt_cmpr_pctsave FROM index_stats;

NAME BLOCKS LF_BLKS BR_BLKS OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

------------------------------ ---------- ---------- ---------- -------------- ----------------

GL_BALANCES_N3 21552 21293 54 2 38

SQL> ALTER INDEX gl.gl_balances_n3 REBUILD COMPRESS;

Index altered.

SQL> SELECT bytes/1024 FROM dba_segments WHERE segment_name='GL_BALANCES_N3';

BYTES/1024

----------

106240

Advanced Compression: Index Compression (2/2)

Reduction from 172Mb to 106Mb (39%)


Manageability

• Data is compressed at the database block level

• 2 methods of compression (11gR2)

– BASIC (or “DIRECT_LOAD OPERATIONS”)

• Compression during bulk load operations (Direct Load, Create Table as

Select)

• Data modified using conventional DML not compressed

– OLTP (or “FOR ALL OPERATIONS”)

Advanced Compression: Table Compression (1/2)


Manageability

SQL> SELECT bytes/1024

FROM dba_segments

WHERE segment_name = 'XLA_DISTRIBUTION_LINKS'

AND partition_name = 'AP';

BYTES/1024

----------

727424

SQL>ALTER TABLE xla.xla_distribution_links MOVE PARTITION ap COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL>SELECT bytes/1024

FROM dba_segments

WHERE segment_name = 'XLA_DISTRIBUTION_LINKS'

AND partition_name = 'AP';

BYTES/1024

----------

142976

Advanced Compression: Table Compression (2/2)

Reduction fron 727M to 142Mb (divide by 5)


Manageability

• Reduces storage consumption by a 2-3 factor

– Savings cascades into pre-prod, test, uat, dev

• Enhances Memory and Network Efficiency

• db sequentiel reads reduction

• Full table scan performance improvement

• Moderate variation in CPU consumption (from -7% to

+6%)

Advanced Compression: Other benefits


Manageability

• Use advisor to estimate space saving

– dbms_comp_advisor.getratio (9i to 11gR1)

– dbms_compression.get_compression_ratio (11gR2)

• Focus tables compression first on largest tables or tables

that contributes the most on I/O, eg:

– XLA_DISTRIBUTION_LINES

– XLA_AE_LINES

– GL_JE_LINES

– GL_IMPORT_REFERENCES

Advanced Compression: Best practices (1/3)


Manageability

• Exclude heavily accessed tables

– FND_CONCURRENT_REQUESTS

• Focus index compression on largest indexes first

• Indexes with repeating keys will offer the best

compression ratio

• Do not compress all indexes from a given table

– Validate indexes to check both the optimum number of columns

to compress and the compression ratio

– Compress the index that show the most significant ratio



Manageability

• Index partitions are good candidates

• Consider increasing INITRANS if significant ITL Waits

are observed in v$waitstat or in AWR

• Establish SQL Plan baseline to anticipate on possible

SQL Plan regressions



Manageability

• Compressed ~260 tables, ~1600 indexes, ~28 LOBS

using 11g SecureFiles

• Average overall storage saving: 3x

– Table compression 4x

– Index compression 2x

– LOB compression 2.3x

• Reduced database size from 18Tb to 11Tb

Advanced Compression: Case Study Global Internal Instance (1/2)


Manageability

• 11 environments (primary, standby, tests, etc)

• 7 Tb saved per environment

• 3-ways storage (online, mirror, backup)

• Total saving: 11*7*3 = 231Tb

Advanced Compression: Case Study Global Internal Instance (2/2)


Manageability Advanced Compression: Higlas – OOW (11i)


Manageability

• Data is grouped by column and then compressed using specified mode.

• Query Mode for data warehousing – Optimized for speed

– 10X compression is typical

– Full scans improved proportionally

• Archival Mode for infrequently accessed data – Optimized to reduce space

– 15X compression is typical

– Up to 50x performance

Hybrid Columnar Compression


Manageability

• OLTP Compression

Overall 3.6

Largest Table 4x

• Query High

Overall 15x

Largest Table x35

• Archive High

Overall 23x

Largest Table x52

Hybrid Columnar Compression: Global Internal Instance

10 10 10 1116

19 19 19 20 21

29

43

0

5

10

15

20

25

30

35

40

45

50

Siz

e R

ed

ucti

on

Facto

r b

y T

ab

le

OLTP Compression (avg=3.3)

Query Compression (avg=14.6)

Archive Compression (avg=22.6)

Up to 52x reduction in table size


Manageability

• Covered by Jean Baptiste M

ApplicationsTesting Suite


Security


“If you spend more on coffee than on IT security, then

you will be hacked…

…what's more, you deserve to be hacked!.”

Richard Clarke Special Advisor to the President

Cyberspace Security


Security

• CreditCard Data

• Bank account number

• Compensation

• Employment details

• Nationality / Citizenship

• Health Information

• Personal information

• Passwords

• etc…

What, where are the sensitive EBS data

• Standard tables

• Flexfields

• Backup tables

• Interface tables

• Interface files

• Customized tables

• Logfiles

• Audit and session tables


Security

Target of data breaches

Type Category % Breaches % Records

Database Server Servers & Applications 25% 92%Desktop Computer End-User Devices 21% 1%

2010 Data Breach

Investigations Report


Application Database Administrators

Data Must Be Protected in depth

Application Users

Botware

MalwareKey Loggers Espionage

Phishing

SQL Injection

Social Engineering

Web Users

Security Existing protection is not enough!

Data Must Be Protected in depth


Security

Database Firewall

Data Masking

TDE Tablespace Encryption

Audit Vault

Database Vault

Transparent Data Encryption

Proxy Authentication

Fine Grained Auditing

Oracle Label Security

Enterprise User Security

Virtual Private Database (VPD)

Database Encryption API

Strong Authentication

Native Network Encryption

Database Auditing

Oracle Database Security: Continus Innovation

Oracle7

Oracle8i

Oracle Database 9i

Oracle Database 10g

Oracle Database 11g


Security Maximum Security Architecture

Financials

HR

iStore Applications Block

Log

Allow

Alert

Substitute

Network SQL

Monitoring

and Blocking

Sensitive

Confidential

Public

Audit

consolidation

Encrypted

Backups

Encrypted

Database

Encrypted

Exports

Data

Masking

Local DBA Privilege Mis-Use

DB Consolidation Security

Unauthorized Local Activity

Monitoring SQL

Encrypt

Control Access

Audit


Security

• Allow security Administrator to restrict areas from users, including priviledged users

• Prevent application by-pass

• Enforce who, where, when, and how using rules and factors User Factors: Name, Authentication type, Proxy Enterprise Identity

Network Factors: Machine name, IP, Network Protocols

Database Factors: IP, Instance, Hostname, SID

Runtime Factors: Date, Time

Database Vault

Procurement

HR

Finance

Application

select * from iby.iby_ext_bank_accounts

DBA


Security

• Administration, monitoring and reporting provided from

– Database control

– Grid Control with restrictions

– Database Vault Administrator console

– PL/SQL API (dbms_macadm)

Database Vault


Security Database Vault: My Oracle Support

- Oracle Support Document 1091083.1 (Integrating Oracle

E-Business Suite Release 12 with Oracle Database

Vault 11gR2).

- Oracle Database Vault Administrator’s Guide 11gR2

E23090-04

https://supporthtml.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=1091083.1

http://docs.oracle.com/cd/E11882_01/server.112/e23090/toc.htm




Security Database Firewall: What is SQL Injection (1/4)

• (Mis)users subvert the application to access to the database

• (Custom) Applications not designed defensively

• (Custom) Applications are given high levels of privilege

Application

SELECT * from stock

where

catalog-no =

'' union select cardNo, customerId, 0 from Orders --'

and location = 1

Bad

SELECT * from stock where

catalog-no =

'PHE8131'

and location = 1 Good



Parameters for SQL come from user input, for example from web browser. The application layer accepts the values for catalog-no and location (‘PHE8131’, ‘1’) and pastes them into the pre-canned query template. SELECT * from stock where catalog-no = ' ' and location =

Star Trek - The Next Generation Season 2 39.35 15




PHE8131 1

Description Price # in Stock

Output:



Instead of inputting a normal value for catalog-no, the user enters

' union select cardNo,

4511222233334444 11853 0

4612345678901234 11853 0

4675883388338833 11588 0

4514861356415750 11204 0

customerId, 0 from Orders --

SELECT * from stock where catalog-no = ' .

' and location =

' union select cardNo, customerId, 0 from Orders --

Description Price # in Stock

Payment

Card

details

The database receives the following query

Output



' union select cardNo,

customerId, 0 from

Orders --

Payment Card

details exposed!


Applications Block

Log

Allow

Alert

Substitute

Security

• Monitor database activity on the network

• Prevent • SQL Injection

• Unauthorized database activity,

• Miuse of database privilege

• Capture and log database interactions for forensic analysis and compliance reporting

Database Firewall: First line of defense


White List

Applications

Block

Allow

Security

• Black-list and White-list based policies enforce normal or expected behavior

• Policies evaluate factors such as time, day, network, and application

• Easily generate white-lists for any application

• Out of policy SQL statements can be logged, alerted, blocked or substituted

Database Firewall: Policies based model enforcement


Security

• Full Activity Report

• Database Administration

• Active Users

• Differential Audit

• Data Modification Detail and much more

Database Firewall: Compliance Reporting


Security

• Anyone having access at the OS level to the database

files can read any unencrypted data

• Oracle Advanced Security proposes two methods to

protect data at rest: – Column encryption

– Tablespace Encryption

• TDE can also be used with Oracle RMAN to encrypt

entire database backups to disk.

• Transparent for all applications

Transparent Data Encryption


Security Transparent Data Encryption: My Oracle Support

- Oracle Support Document 828229.1 (Using TDE

Tablespace Encryption with Oracle E-Business Suite

Release 12)

- Oracle Database Advanced Security Administrator’s

Guide 11gR2 E10746-03

https://supporthtml.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=828229.1https://supporthtml.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=828229.1

http://docs.oracle.com/cd/E11882_01/network.112/e10746/toc.htm




Security

• The act of anonymizing customer, financial, or company-confidential data to

create new, legible data that retains the data's properties, such as its width,

type, and format

• To protect confidential data in non-production environments when the data

is shared with non-production users without revealing sensitive information

Data Masking: What is this? Production Non-Production

LAST_NAME SSN SALARY

DUPONT 203-33-3234 40,000

DURANT 323-22-2943 60,000

LAST_NAME SSN SALARY

ANEL 111-23-1111 70,000

BEBEL 222-34-1345 40,000


Security

• Used in conjunction with cloning

• Create irreversibly scrambled versions of your

production DB for testing & development

Data Masking: Using EM

Production

Clone

Staging

Mask

Clone

Test


Security

• E-Business Suite Masking Template

– Metadata for the EM Masking tool

– Columns, Relationships, and Masking rules for PII and Sensitive

attributes for E-Business Suite products

• 950 Columns

– 65% HCM - Payroll, Employment Details, Personal Info

• Also TCA, ATG, Financials, Projects…

• Not split out by product or family

– De-identification needs to be done across the database

Data Masking: EBS Masking templates


Security

• De-Identify the data

– Scramble identifiers of individuals (PII) – Name, account, address,

location, drivers license…

• Mask sensitive data that, if associated with PII, would cause privacy

concerns

• Compensation

• Health

• Employment Information

• Maintain Data Validity not to break applications

Data Masking: What is being masked


Security

• Financial data

– Results

– Forecasts

• Unstructured data

– Descriptive Flex Fields (user extensible content)

– Except where we know the content

• Notes

• Attachments

Data Masking: What is not being masked


Security

• Key Drivers – Regulatory Compliance (SOX, PCI, Privacy, …)

• Risk assessment and compensating controls

• Demonstrate controls for compliance

– Security

• Detect misuse of privileges

• Key Requirements – Collect Audit trail data from many audit silos

– Automate review of the audit trail logs, and raise alerts

– Centralize audit policy management

– Secure the audit trail (Priviledged account may be mis-used to manipulate native audit trail or

syslog)

– Minimize performance impact on production systems

Audit Vault: The need for auditing


Security

• Automates the collection and consolidation of audit data

to support regulatory compliance and reduce security

risks.

• Provides

– compliance and entitle reports,

– alert notifications,

– centralized audit policy management.

• Works out of the box with e-Business Suite and other

packaged applications: no setup required

Audit Vault


Security

Two components:

• One central and standalone Audit Vault server

• A set of Audit Vault collection agents

Audit Vault: EBS Integration

CRM Data

ERP Data

Databases

HR Data

Audit

Data

Policies

Built-in

Reports

Alerts

Custom

Reports

!

Auditor


Security

Audit Vault: Out of the box Audit Reports


Security

• Out-of-the-box reports

– Privileged user activity

– Access to sensitive data

– Role grants

– DDL activity

– Login/logout

• User-defined reports

– What privileged users did on the financial database?

– What user ‘A’ did across multiple databases?

– Who accessed sensitive data?

• Custom reports

Audit Vault: Out of the box Audit Reports


Security

• Audit Vault Dashboard

– Enterprise overview

– Alerts and Reports

– Administration

– Audit Policies

• Audit Vault Policies

– Provision database audit settings centrally for compliance policies

– Collection of audit settings on the databases

– Compare against existing audit settings on source

– Demonstrate compliance

Audit Vault: Manageability


Security Audit Vault: My Oracle Support

- Oracle Support Document 1199033.1 Master Note for

Oracle Audit Vault)

- Oracle Audit Vault Administrator’s Guide 10.3 E23571-05

https://supporthtml.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=1199033.1https://supporthtml.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?id=1199033.1

http://docs.oracle.com/cd/E23574_01/admin.103/e23571/toc.htm




Security Network Security

- Consider implementing the Oracle Net valid node

checking in sqlnet.ora from your database server: tcp.invited_nodes = ebs-ap01, ebsap02

- Implement Oracle Advanced Security to encrypt the

network traffic between the app tier(s) and the database

servers

- Benefits from hardware acceleration on some platforms


Security Network Security: My Oracle Support

- Oracle Database Advanced Security Administrator’s

Guide 11gR2 E10746-03





Security Apps schema protection: Case study

Anything wrong here?


Security

• Restrict the use of APPS schema to the Application DBA

only

• Create read-only schema for query and troubleshooting

Apps schema protection: Best practices


Security

• See Oracle E-Business Suite R12 Configuration in a

DMZ (Doc ID 380490.1)

DMZ Setup


Security

• Lock down your apps account

• Create a read-only schema

• Consider Database Firewall to protect against SQL

injection (and more)

• Leverage Database Vault for separation of duties

• Scramble your data during cloning

• Encrypt your data, not only in the data but in all

directions

Summary (1/2)


Security

• Consider Audit Vault for reporting

• Consider Oracle Advanced Security to encrypt the

network traffic

Summary (2/2)


Product Certification


Certification EBS Certification to date

• 11i.10.2 is certified with database up to 11.2.0.3

• R12.0.6 is certified with database to 11.2.0.3

• R12.1.3 is certified with database to 11.2.0.3

• R12.2 may ship with 11.2.0.3


Certification Database Roadmap


Q&A

1 copyright © 2012, oracle and/or its affiliates. all ... · pdf file–ar...

Documents