Transcript Oracle8i - Solution Beacon

Release 11i Apps DBA
Teaching An Old Dog New Tricks
NCOAUG – Summer 2001
Chicago, IL
Randy Giefer
[email protected]
© 2001 Solution Beacon, LLC. All Rights Reserved.
Welcome !




Welcome
Overview (Apps DBA Delta Training For 11i)
Audience Survey
Presenter Introduction
2
© 2001 Solution Beacon, LLC. All Rights Reserved.
Intended Audience

Technical
- Database Administrator
- Apps System Administrator
- Developer

Current Application Release
- Release 10.7, 11.0.x, 11i (11.5.1 – 11.5.4)

Current User Interface(s)
- Character, SmartClient, Citrix, OADM, NCA
3
© 2001 Solution Beacon, LLC. All Rights Reserved.
Today’s Goals



Educate
- High Level Summary
- Not The Complete, Exhaustive Source
- Provide Guidance And Direction
- Information To Build A Training Plan
Awareness, Try Not To Scare
Reference Materials
( Whitepaper available upon request )
[email protected]
www.solutionbeacon.com
4
© 2001 Solution Beacon, LLC. All Rights Reserved.
What is Release 11i?
The Biggest and Most Complex Release Ever !
 Increased Functionality
 New Technology
5
© 2001 Solution Beacon, LLC. All Rights Reserved.
What is Release 11i?
Increased Functionality …
Products – Applications Release 11i
- over 152 162 products
- over 15 17 product families
 Workflow – Enabled (over 15 products)
 Self-Service Web Apps
 BIS
 CRM

6
© 2001 Solution Beacon, LLC. All Rights Reserved.
What is Release 11i?
New Technology …
 Oracle 8i
 Internet Computing Architecture
 Java-Enabled!!!
Combined Together…
= Release 11i Technology Stack
7
© 2001 Solution Beacon, LLC. All Rights Reserved.
What is Release 11i?

Release 11i Technology Stack
- Internet Computing Architecture (ICA)
- Oracle 8i and 8i New Features

Release 11i Functionality
8
© 2001 Solution Beacon, LLC. All Rights Reserved.
What is Release 11i?

Release 11i Technology Stack
- Internet Computing Architecture (ICA)
- Oracle 8i and 8i New Features

Release 11i Functionality
9
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
Client
Tier
Middle
Tier
Database
Server
Tier
10
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
Client
Tier
Middle
Tier
Database
Server
8i
Tier
Database
11
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
Client
JInitiator
Tier
Middle
Tier
Database
Server
8i
Tier
Database
8i ORACLE_HOME
12
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
Client
JInitiator
Tier
Middle
Simple so far, right?
Tier
Database
Server
8i
Tier
Database
8i ORACLE_HOME
13
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
!! Release 11.5.1 !!
Client
JInitiator
Tier
Applications Technology Stack
Web HTTP Servers
Middle
Tier
Apache
Admin
Server
WebDB
2.5
Discoverer
Server *
WebDB 2.2
Listener
* Optional
8.1.6 Web ORACLE_HOME
Concurrent
Processing Server
Forms
Server
Reports
Server
8.0.6 ORACLE_HOME
Database
Server
8.1.6
Tier
Database
8.1.6 ORACLE_HOME
14
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
!! Release 11.5.2, 11.5.3 !!
Client
JInitiator
Tier
Applications Technology Stack
Web HTTP Servers
Middle
Tier
Discoverer
Server *
Concurrent
Processing Server
Admin
Server
Apache
* Optional
8.1.6 iAS ORACLE_HOME
Forms
Server
Reports
Server
8.0.6 ORACLE_HOME
Database
Server
8.1.6
Tier
Database
8.1.6 ORACLE_HOME
15
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
!! Release 11.5.4 !!
Client
JInitiator
Tier
Applications Technology Stack
Web HTTP Servers
Middle
Tier
Discoverer
Server *
Concurrent
Processing Server
Admin
Server
Apache
* Optional
8.1.6 iAS ORACLE_HOME
Forms
Server
Reports
Server
8.0.6 ORACLE_HOME
Database
Server
8.1.7
Tier
Database
8.1.7 ORACLE_HOME
16
© 2001 Solution Beacon, LLC. All Rights Reserved.
Technology Stack Products
!! Release 11.5.1 !!
•
•
•
•
•
•
•
•
•
•
Oracle8i Enterprise Edition 8.1.6
Other Oracle Homes – App(8.0.6), WebDB(8.1.6)
WebDB 2.2 and WebDB 2.5 !
JRE (Java Runtime Environment) 1.1.8
JDK (Java Development Kit) 1.1.8
Oracle Web Discoverer 3.3.48 (Optional)
Oracle JInitiator (1.1.7) On The Client
Oracle Forms 6i
Oracle Reports 6i
Oracle Graphics
17
© 2001 Solution Beacon, LLC. All Rights Reserved.
Technology Stack Products
!! Release 11.5.2, 11.5.3 !!
•
•
•
•
•
•
•
•
•
•
•
•
Oracle8i Enterprise Edition 8.1.6
Other Oracle Homes – App(8.0.6), iAS(8.1.6)
WebDB 2.2 and WebDB 2.5 !
Apache Web Server (1.3.9, Full - jsp, jServ)
JServ (1.1)
JRE (Java Runtime Environment) 1.1.8
JDK (Java Development Kit) 1.1.8
Oracle Web Discoverer 3.3.48 (Optional)
Oracle JInitiator (1.1.7) On The Client
Oracle Forms 6i
Oracle Reports 6i
Oracle Graphics
© 2001 Solution Beacon, LLC. All Rights Reserved.
18
Technology Stack Products
!! Release 11.5.4 !!
•
•
•
•
•
•
•
•
•
•
•
•
Oracle8i Enterprise Edition 8.1.6 8.1.7
Other Oracle Homes - App(8.0.6), iAS(8.1.6)
WebDB 2.2 and WebDB 2.5 !
Apache Web Server (1.3.9, Full - jsp, jServ)
JServ (1.1)
JRE (Java Runtime Environment) 1.1.8
JDK (Java Development Kit) 1.1.8 1.2.2
Oracle Web Discoverer 3.3.48 (Optional)
Oracle JInitiator ( 1.1.7 1.1.8.7 )
Oracle Forms 6i
Oracle Reports 6i
Oracle Graphics
© 2001 Solution Beacon, LLC. All Rights Reserved.
19
Technology Stack Notes


Install is More Complex (# products), But Easier
Than 10.7 NCA
More Products
(All Products Get Installed, Regardless Of Licensing)


Server Partitioning (TWO_TASK)
Driven By Many, Many Configuration Files !!!
20
© 2001 Solution Beacon, LLC. All Rights Reserved.
Technology Stack Notes



Tech Stack – More Complex to Administer
Administrator Profile “Roles”
 DB – Database Administrator (8.1.7)
 App – Applications Administrator (8.0.6)
 Web – Web Administrator (iAS/8.1.6)
Numerous Start/Stop Scripts
21
© 2001 Solution Beacon, LLC. All Rights Reserved.
Technology Stack Notes
More Complex to Administer
$COMN_TOP/admin/scripts








adalnctl.sh
adcmctl.sh
adrepctl.sh
adtcfctl.sh
adfrmctl.sh
adfmsctl.sh
adfmcctl.sh
adapcctl.sh
Apps Listener
Concurrent Managers
Reports Server
TCF Server
Forms Server
Forms Metric Server
Forms Metric Client
Apache
22
© 2001 Solution Beacon, LLC. All Rights Reserved.
Technology Stack Notes
$ORACLE_HOME/appsutil/scripts


addbctl.sh
addlnctl.sh
Database
Database Listener
23
© 2001 Solution Beacon, LLC. All Rights Reserved.
Technology Stack – Many Ports
24
© 2001 Solution Beacon, LLC. All Rights Reserved.
R11i Tools To Assist You
Oracle Applications Environment Manager (oaem)




Facilitates .profile Establishment
( DB, App, Web )
No Need For Multiple UNIX .profile Scripts
Provides Start / Stop / Status – All Components
Download From Solution Beacon Website
www.solutionbeacon.com
25
© 2001 Solution Beacon, LLC. All Rights Reserved.
What is Release 11i?

Release 11i Technology Stack
- Internet Computing Architecture (ICA)
- Oracle 8i and 8i New Features

Release 11i Functionality
26
© 2001 Solution Beacon, LLC. All Rights Reserved.
Oracle8i – New Features
•
•
•
•
•
•
•
•
•
•
•
•
Materialized Views
Cost-based Optimization
Database Resource Manager
Partitioned Tables
Temporary Tables
Invoker Rights
Cache Fusion
Locally Managed Tablespaces
Function-based Indexes
STATSPACK
RMAN
Log Miner
© 2001 Solution Beacon, LLC. All Rights Reserved.
27
Materialized Views
•
•
•
•
Summarize, Precompute, Aggregate Data
Stored Data (Not A View)
Snapshot
query_rewrite_enabled -> CBO
28
© 2001 Solution Beacon, LLC. All Rights Reserved.
Cost-Based Optimization
•
•
•
•
•
•
•
Optimization Based on Hints & Statistics
Optimizer Reviews Potential Execution Plans
Compares Execution Plans
Chooses Lowest Cost
Release 11i, optimizer_mode = choose
Refer To Whitepaper For init.ora Parameters
$FND_TOP/sql/AFCHKCBO.sql
29
© 2001 Solution Beacon, LLC. All Rights Reserved.
Cost-Based Optimization
Need To Keep Statistics Current !
• FND_STATS
(Not ANALYZE, DBMS_STATS)
• Oracle Provided Concurrent Programs
- Analyze All Index Columns
- Gather Table Statistics
- Backup Table Statistics
- Restore Table Statistics
- Gather Column Statistics (Obsolete !!)
- Gather All Column Statistics (Obsolete !!)
- Gather Schema Statistics
© 2001 Solution Beacon, LLC. All Rights Reserved.
30
Database Resource Manager
•
•
•
•
•
Gives DBA More Control Of System Resources
Consumer Groups And Resource Plans
Allocate And Assign Priorities
Limited To CPU Resources
See Oracle Note 106948.1 For Examples
31
© 2001 Solution Beacon, LLC. All Rights Reserved.
Partitioned Tables
•
•
•
•
•
Tables Sub-divided Into Smaller Segments
More Manageable Than A Large Table
Separate Indexes, Segments
Balance I/O
Two Partitioning Methods
• Range Partitioning
• Hash Partitioning
32
© 2001 Solution Beacon, LLC. All Rights Reserved.
Temporary Tables
•
•
•
•
•
Name Says A Lot
Exists Only For Duration Of A Session
No REDO For SQL To Temporary Tables
No DML Locks
INV And GL Starting To Use
33
© 2001 Solution Beacon, LLC. All Rights Reserved.
Invoker Rights
•
•
•
Invoke Or Execute Procedures/Pkgs As If You
Were The Owner
One Instance/Occurrence Of A Package In
APPS Schema
No More Multiple Sets Of Books Architecture
(MSOBA) or Multiple Reporting Currencies
(MRC) Schema Duplication
34
© 2001 Solution Beacon, LLC. All Rights Reserved.
Cache Fusion
•
•
•
•
Parallel Server Environments
Increases Performance and Availability
Shares the Cache Across OPS Nodes
Big Feature of Oracle 9i
(Real Application Clusters)
35
© 2001 Solution Beacon, LLC. All Rights Reserved.
Locally Managed Tablespaces
•
•
•
•
Tablespaces That Manage Their Own Extents
Bitmap In Each Datafile
Each Bit Signifies A Data Block
Reduced Contention On The Data Dictionary
36
© 2001 Solution Beacon, LLC. All Rights Reserved.
Function-Based Indexes
•
•
•
Create Index Using A Function On Column
After Creation, Just Like Normal Index
Use This As A Tuning Aid
create index ap_chrg_allocations_n99
on ap_chrg_allocations_all
(abs(allocated_amount));
37
© 2001 Solution Beacon, LLC. All Rights Reserved.
STATSPACK
•
•
•
•
•
Historical Data Collection
BSTAT/ESTAT On Steroids
100+ Metrics
Stores Data In Database
Checkout Whitepaper For More Info
38
© 2001 Solution Beacon, LLC. All Rights Reserved.
RMAN
•
•
•
•
•
•
Catalog-based Backup & Recovery Tool
Automatically Determines:
Database Files, Archive Logs, Control Files
No Other OS files
Compresses Datafiles – Only Used Blocks
Incremental Backups – Only Changed Blocks
More Info - Oracle8i Recovery Manager User's
Guide and Reference
39
© 2001 Solution Beacon, LLC. All Rights Reserved.
Log Miner
•
•
•
•
•
Finally!
Readable Interface Into The REDO Logs
Generates DML SQL From Logs (No DDL)
“Supported” Oracle Analysis Tool
Aids in:
• Determining Trends
• Capacity Planning
• Point-in-Time Recovery
40
© 2001 Solution Beacon, LLC. All Rights Reserved.
Oracle8i – New Features
•
•
•
•
•
•
•
•
•
•
•
•
Materialized Views
Cost-based Optimization
Database Resource Manager
Partitioned Tables
Temporary Tables
Invoker Rights
Cache Fusion
Locally Managed Tablespaces
Function-based Indexes
STATSPACK
RMAN
Log Miner
© 2001 Solution Beacon, LLC. All Rights Reserved.
41
Oracle8i – New Features
What do these new features give you?
42
© 2001 Solution Beacon, LLC. All Rights Reserved.
Oracle8i – New Features, Summary
•
•
•
Flexibility
Power
Performance
“These new Oracle 8i features provide flexibility to Apps
DBAs and Developers, which gives them more control
and power over the environment. By correctly and
efficiently applying these features, greater performance
can be obtained.”
•
Ladder Commercial
“Just because you have the power, doesn’t mean you
should use it.”
43
© 2001 Solution Beacon, LLC. All Rights Reserved.
What is Release 11i?
•
Release 11i Technology Stack
- Internet Computing Architecture (ICA)
- Oracle 8i and 8i New Features
•
Release 11i Functionality
44
© 2001 Solution Beacon, LLC. All Rights Reserved.
New Look And Feel
•
•
•
•
•
•
•
•
Required Fields Are Color Highlighted
LOV Indicator
Bubble Help
Context Sensitive Help
Right Mouse Click – Popup Menus
Better Colors!
Folder Tab Metaphor - Alternate Regions
Displayed As Folder Tabs
For 10.7/11 NCA Installs, No More Floating
Toolbar!
45
© 2001 Solution Beacon, LLC. All Rights Reserved.
Workflow-Enabled Applications









Purchasing
General Ledger
Payables
Project Accounting
Receivables
HR
Account Generator
Oracle Business Intelligence
Oracle Service
46
© 2001 Solution Beacon, LLC. All Rights Reserved.
Workflow-Enabled Applications







Oracle Engineering
Oracle Inventory
Oracle Web Employees
Oracle Web Customers
Oracle Web Suppliers
Implementation Wizard
Oracle CRM
47
© 2001 Solution Beacon, LLC. All Rights Reserved.
Workflow-Enabled Applications



WF_PURGE Package
Oracle Note: 132254.1
Performance-Related Profile Options:
- Account Generator: Run in Debug Mode
- Purchasing: Workflow Processing Mode
48
© 2001 Solution Beacon, LLC. All Rights Reserved.
System Admin Screen Changes





Some, Not All
Define Concurrent Program
Define Concurrent Manager
Session Control Dialog
Network Test Screen
49
© 2001 Solution Beacon, LLC. All Rights Reserved.
Conc Program - Screen Changes
50
© 2001 Solution Beacon, LLC. All Rights Reserved.
Define Concurrent Programs
New fields on the Concurrent Programs screen:
 Priority
 Incrementor
 MLS Function
 Restart on System Failure
 Enable Trace
 NLS Compliant
51
© 2001 Solution Beacon, LLC. All Rights Reserved.
Define Concurrent Programs
New button on the Concurrent Programs screen:
Session Control
52
© 2001 Solution Beacon, LLC. All Rights Reserved.
Concurrent Program Screen Changes
53
© 2001 Solution Beacon, LLC. All Rights Reserved.
Concurrent Program – Screen Changes
Session Control Dialog
54
© 2001 Solution Beacon, LLC. All Rights Reserved.
Session Control Dialog



Consumer Group
- Database Resource Group
Rollback Segment
- First Commit Only!
Optimizer Mode
- FIRST_ROWS
- ALL_ROWS
- RULE
- CHOOSE
55
© 2001 Solution Beacon, LLC. All Rights Reserved.
Concurrent Manager – Screen Changes
Define Concurrent Manager Screen Changes
Consumer Group: Database Resource Group
56
© 2001 Solution Beacon, LLC. All Rights Reserved.
Network Test Screen

Additional Latency and Bandwidth Information
- Client to the Middle-tier
- Middle-tier to the Database-tier
57
© 2001 Solution Beacon, LLC. All Rights Reserved.
Network Test Screen
58
© 2001 Solution Beacon, LLC. All Rights Reserved.
New Profile Options




FND: Resource Consumer Group
- Database Resource Manager
Utilities:SQL Trace
Account Generator: Run in Debug Mode
Purchasing: Workflow Processing Mode
- Online
- Background
59
© 2001 Solution Beacon, LLC. All Rights Reserved.
License Manager

Admin Utility Used to:
- Add Products
- Add Languages
For UNIX:
$cd COMMON_TOP/admin/assistants/licmgr
$LicenseMgr
$AD_TOP/bin/adlicmgr.sh
60
© 2001 Solution Beacon, LLC. All Rights Reserved.
Cloning 11i Instances
My Definition of Cloning…
The process of reproducing an entire environment,
including the Oracle home ($ORACLE_HOME)
and Application top ($APPL_TOP) directory
structures.
Note that this may be different than what you
currently now as “refreshing” a database.
61
© 2001 Solution Beacon, LLC. All Rights Reserved.
Cloning 11i Instances
Two Methods:

Manually - Need To Change
- Dozens Of Configuration Files
- Numerous Site Profile Values
- Not Supported

Oracle’s adclone.pl
62
© 2001 Solution Beacon, LLC. All Rights Reserved.
Cloning 11i Instances – adclone.pl
adclone.pl:




MetaLink Document: 280896
Patch 1636653 1906056
A Supported Method
Limitations – Identical Configuration
Overview:
 Pre-clone, Copy, Post-clone, Wrap-up Tasks
63
© 2001 Solution Beacon, LLC. All Rights Reserved.
Debugging Tool – DDRTEST


DDRTEST (ddrtest.jsp)
Found On Oracle’s FTP Site
(ftp://oracle-ftp.oracle.com/apps/patchsets/AOL/DDRTEST/)
-
-
-
Display DBC File Contents
CLASSPATH And Important Class Versions
Tests JDBC Connection
Validates Login To Apps
Verifies Client Cookies
64
© 2001 Solution Beacon, LLC. All Rights Reserved.
Patchset Comparison Tool

Found On Oracle’s FTP Site:
ftp://oracle-ftp.oracle.com/apps/patchsets/PATCHSET_COMPARE_TOOL/
-
Updated Nightly By Oracle
Can Be Used For 10.7, 11.0.x And 11.5.x
Displays Currently Available Patchsets
Reads Your applptch.txt File
Produces Reports On Differences
Automate It
65
© 2001 Solution Beacon, LLC. All Rights Reserved.
Miscellaneous Tips And Tricks


FNDCPASS - Oracle Supported Utility For
Changing Passwords – Finally!
UNIX find Command:
$find . -exec grep -l 8001 {} \;

GNU Find Utilities – locate command
66
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
The Top Ten Reasons to Upgrade to Release 11i are…
67
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 10 . . .
Needed an excuse to get out of work!!!
68
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 9 . . .
Oracle is live on it!
69
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 8 . . .
Oracle 8i, Oracle 8i, Oracle 8i!
70
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 7 . . .
We’re all getting bored with 10.7 / 7.3.4 and
need something new!
(BTW, This Combination Is De-Supported)
71
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 6 . . .
R10.7 Support Ends on December 31, 2002!
72
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 5 . . .
Web based GUI interface!
73
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 4 . . .
No more SmartClient!
(What was Smart about it in the first place?)
74
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 3 . . .
Locally Managed Tablespaces!
75
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 2 . . .
Order Management Rewrite
!!!! (No More OEORPC Listener) !!!!
76
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
and the # 1 reason to upgrade to Release 11i . . .
(drum roll please . . .)
77
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
and the # 1 reason to upgrade to Release 11i . . .
The TRMs are finally online!!!!!!!!!
(Both On Documentation CD And On
MetaLink – eTRM)
78
© 2001 Solution Beacon, LLC. All Rights Reserved.
More Information
Email:
[email protected]
Utilities, Papers, Tools: www.solutionbeacon.com
See Us At OAUG:
- R11i Installation And Upgrade Workshop
- Upgrade SIG
- R11i Upgrade Panel
- Concurrent Manager Panel
79
© 2001 Solution Beacon, LLC. All Rights Reserved.
Questions
80
© 2001 Solution Beacon, LLC. All Rights Reserved.