ten tips and tricks that every e-business suite apps dba ...days... · ten tips and tricks that...

Ten Tips and Tricks that Every E-Business Suite Apps DBA Should Know

Feb 2016

• Testing

• Understand the System’s Architecture

• Knowledge of One Version May not Translate into Knowledge of the Next Version

• Be Proactive

• Sizing Matters

• Set the Database Parameters Properly

• Gathering Stats

• How to Trace

• Data Lifecycle Management

• Monitoring the System

Agenda

Copyright © 2015 Accenture All rights reserved. 2

Who am I?


Dimas Chbane

• Consultant / DBA / Analyst

• Focus areas:

• Databases

• EBusiness Suite

• Blog: dimas-chbane.net

4

Quick Poll:

• Who is on:

– 12.2?

– 12.1?

– 12.0?

– 11.5.10?

Copyright © 2015 Accenture All rights reserved.

1- Don’t be like “this guy”


“I normally don’t test my code, but when I do it, I do it in Production!”

5

Or you may find yourself as this guy…


7

In summary…

• Test, test, test


2 – Understand your System’s Architecture


DB, Admin. &

Conc. Node

External

Client

HTTP Load

Balancer

Internal Web &

Forms Node 1

Internal Web &

Forms Node 2

HTTP Load

Balancer

External Web &

Forms Node 1

External Web &

Forms Node 2

Firewall 2

Internet

HTTPS

HTTPS HTTP

Internal

Client

HTTPS

HTTP

Firewall 1

DMZ Intranet

8

12.1 EBS Architecture

JSP

Forms

BI Publisher

BC4J

OC4J

We

b L

iste

ne

rUIX

Client

RAC & ASM

Global Single Data Model

JDB

C/

SQ

L N

et

HT

TP

/S

10g

Application Database

10gR2 | 11g |12c


Developer 10.1.2

COMMON_TOP

APPL_TOP

INST_TOP

Oracle Application

Server 10.1.3

12.1 EBS Technology Stack

Application Tier Database Tier

Oracle Database

10g|11g|12c


AS 10.1.2 HOME

AS 10.1.3 HOME

Forms

OC4J

OACore

OC4J

OAFM

OC4J

Apache(1.3)

frmweb

opmn

Forms-c4ws

OC4J

Client Application Database

12.1 EBS Components


Client

JDB

C/

SQ

L N

et

HT

TP

/S

Application Database

RAC & ASM

Global Single Data Model

Edition-Based Redefinition

WebLogic JSP

Forms

BI Publisher

BC4J

We

b L

iste

ne

r

WebLogic Server

UIX 11g

12.2 EBS Architecture

11g | 12c

Developer 10.1.2

COMMON_TOP

APPL_TOP

INST_TOP

Applications Tier – Dual File System

Oracle HTTP Server (OHS)

WebLogic Server (WLS)

Database Tier

Developer 10.1.2

COMMON_TOP

APPL_TOP

INST_TOP



FS_NE

Oracle Database 11gR2|12c

Fusion

Middleware

11g

12.2 EBS Technology Stack

R12.2 Components

AS 10.1.2 HOME

Weblogic + OHS

Forms

OACore

OAFM

frmweb

opmn

Forms-c4ws

Node

Manager

Admin

Server

OAEAApache

(2.2)

Client Application Database

3 - Knowledge of one version may not translate into knowledge of the next version


Examples: Oracle E-Business Suite 12.2 Configuration


In 12.2, Autoconfig has a reduced role in the

configuration of OHS and WLS.

The remaining configurations performed by

Autoconfig stay the same.

Need to use the Fusion Middleware Control and

the WLS Administration Console

Fusion Middleware Control WLS Administration

Console

Oracle Application Manager

and Autoconfig

Database Home SID name, Listener, dbPorts, etc

Oracle HTTP Server Performance directives, log

configuration, ports, mod_perl,

mod_wl_ohs, etc

Weblogic oacore, oafm, forms and

forms-c4ws services, etc

Oracle E-Business Suite Developer 10g, Product specific

config files, JVM Settings for

Admin Node, etc

16


How it works?

• Feedback Loop

• The applications context file will be kept synchronized with dependant OHS and

WLS configuration parameters



OHS Changes require executing script adSyncContext.pl to synchronize the Context File

Auto Sync (adRegisterWLSListeners.pl)

Managing Configuration of Oracle HTTP Server and Web Application Services in Oracle E-Business Suite Release 12.2 (Doc ID 1905593.1)

Manual Sync (adSyncContext.pl)

17

18

4- Be Proactive

• Keep database and application versions current

• Release Schedule of Current Database Releases (Doc ID 742060.1)


19

The application side is more complicated…


20

5 – Sizing Matters


21

Sizing the Database

• Enkitec’s Sizing and Provisioning (eSP)


22

Sizing the middle tier


• JVM sizing should consider both memory and CPU domains.

• For 12.2: “Managing Configuration of Oracle HTTP Server and Oacore, Oafm, Forms

and Forms-c4ws Applications in Oracle E-Business Suite Release 12.2 (MOS ID

1905593.1)”

• Be conservative and consider additional resources requirements for online

patching

• Better have more managed instances in the cluster to scale up to the target

concurrency levels, than one with large heap

• The default heap size of 512M for the Admin Server is not enough for most installations

• Start with Xms =1 GB and Xmx = 2GB

• For 12.1: “JVM: Guidelines to setup the Java Virtual Machine in Apps E-Business Suite

11i and R12 (Doc ID 362851.1)”

23

6- Set the Database Parameters Properly

• Note 216205.1:Database Initialization Parameters for Oracle Applications 11i

• Note 396009.1:Database Initialization Parameters for Oracle Applications R12

• Note 174605.1:bde_chk_cbo.sql - Reports Database Initialization Parameters related to an Apps 12 or 11i instance


https://webiv.oraclecorp.com/cgi-bin/webiv/do.pl/GET?WwwID=note:216205.1



24

Automatic sizing may not be adequate

• Watch out for SGA automatically resizing


25

7 – Don’t let the CBO drive blind: Gather Stats Periodically

• But don’t go crazy on it…


26

Leading practices

• For most of the objects, gathering stats once a week is enough

– If there are volatile objects that require to be analyzed more frequently, gather stats only for those objects

• Only supported way to gather stats is using ‘Gather Schema Statistics’ concurrent program or FND_STATS

– Don’t use DBMS_STATS directly for EBS objects

– EBS collects histograms based on FND_HISTOGRAM_COLS

• Consider using GATHER_AUTO option

– Modification threshold defaults to 10%, but can be changed

– Stats will be gather on objects that have %DML > 10% (total number of rows)

• Consider using ‘Auto Sample Size’

– In DB 11g and 12c, ‘Auto Sample Size’ is the default

– Uses new hash algorithm that Oracle says that is almost as good as 100% sample size

• Remember of Dictionary and Fixed Objects stats

• Review: Best Practices for Gathering Statistics with Oracle E-Business Suite (Doc ID 1586374.1)


27

8 – Understand how to trace

Forms: Help Diagnostics Trace Trace with Binds and Waits


28

Tracing..

Framework pages: Shift/Ctrl + Diagnostics link Set Trace level Trace with binds and waits

• Set “FND: Diagnostics” to Yes.


29

More tracing…

Concurrent requests: Set “Concurrent: Allow Debugging” to Yes

• Select trace options at request submission time (Doc Id 301372.1 )


30

9 – Establish Data Life Cycle Management Policies and Procedures


• Need to define a data policy and strategy for

data retention.

• EBS Data:

• Transient

• Transactional

• Seeded

• Typical solutions

• Purge

• Archive

• Data Partitioning

• Engage in-house and Support Product Teams

• Reducing Your Oracle E-Business Suite Data Footprint using Archiving, Purging, and Information

Lifecycle Management (Doc ID 752322.1)

31

Examples for ATG data:


32

10 – Monitor the System .. and react to the problems!

• Several tools available


33

eDB360 Demo

• A shell script runs eDB360 on all databases

• Installs nothing and it is free!

• Set of files driven by one index.html file

• One ZIP file with a set of files (thousands)


3/23/2016 Enkitec © 33

34

Visit http://orapeeps.com


ten tips and tricks that every e-business suite apps dba ...days... · ten tips and tricks that...

Documents