Transcript 8.1.6 Database - 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

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 product acronyms
- over 15 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
8.1.6
Tier
Database
11
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
Client
JInitiator
Tier
Middle
Tier
Database
Server
8.1.6
Tier
Database
12
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
Client
JInitiator
Tier
Middle
Simple so far, right?
Tier
Database
Server
8.1.6
Tier
Database
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 ORACLE_HOME
Concurrent
Processing Server
Forms
Server
Reports
Server
8.0.6 ORACLE_HOME
Database
Server
8.1.6
Tier
Database
14
© 2001 Solution Beacon, LLC. All Rights Reserved.
Release 11i Technology Stack
!! Release 11.5.2+ !!
Client
JInitiator
Tier
Applications Technology Stack
Web HTTP Servers
Middle
Tier
Discoverer
Server *
Concurrent
Processing Server
Admin
Server
Apache
* Optional
8.1.6 ORACLE_HOME
Forms
Server
Reports
Server
8.0.6 ORACLE_HOME
Database
Server
8.1.6
Tier
Database
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.7 ORACLE_HOME
Forms
Server
Reports
Server
8.0.6 ORACLE_HOME
Database
Server
8.1.7
Tier
Database
16
© 2001 Solution Beacon, LLC. All Rights Reserved.
Technology Stack Products
!! Release 11.5.2, 11.5.3 !!
•
•
•
•
•
•
•
•
•
•
•
•
Oracle8i Enterprise Edition 8.1.6
Oracle8 Enterprise Edition 8.0.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.
17
Technology Stack Products
!! Release 11.5.4 !!
•
•
•
•
•
•
•
•
•
•
•
•
Oracle8i Enterprise Edition 8.1.7
Oracle8 Enterprise Edition 8.0.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) on the client
Oracle Forms 6i
Oracle Reports 6i
Oracle Graphics
© 2001 Solution Beacon, LLC. All Rights Reserved.
18
Technology Stack
Technology Stack - Miscellaneous Notes




Install is More Complex (# products), But
Easier Than 10.7 NCA
More Products
Server Partitioning (TWO_TASK)
Driven By Many Configuration Files
19
© 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
20
© 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.
21
Materialized Views
•
•
•
•
Summarize, Precompute, Aggregate Data
Stored Data (Not a View)
Snapshot
query_rewrite_enabled -> CBO
22
© 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
23
© 2001 Solution Beacon, LLC. All Rights Reserved.
Cost-Based Optimization
Need To Keep Statistics Current !
• FND_STATS
• Oracle Provided Concurrent Programs
- Analyze All Index Columns
- Gather Table Statistics
- Backup Table Statistics
- Restore Table Statistics
- Gather Column Statistics
- Gather All Column Statistics
- Gather Schema Statistics
24
© 2001 Solution Beacon, LLC. All Rights Reserved.
Database Resource Manager
•
•
•
•
•
•
Gives DBA More Control of System Resources
Consumer Groups and Resource Plans
Allocate and Assign Priorities
Limited to CPU Resources
Performance Bug #1291292 Fixed In 8.1.6.2
See Oracle Note 106948.1 For Examples
25
© 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
26
© 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
•
•
•
27
© 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
28
© 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)
29
© 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
30
© 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));
31
© 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
32
© 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
33
© 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
34
© 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.
35
Oracle8i – New Features
What do these new features give you?
36
© 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.”
Randy Giefer, 2001
•
Ladder Commercial
“Just because you have the power, doesn’t mean you
should use it.”
37
© 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
38
© 2001 Solution Beacon, LLC. All Rights Reserved.
New Look & 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!
39
© 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
40
© 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
41
© 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
42
© 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
43
© 2001 Solution Beacon, LLC. All Rights Reserved.
Conc Program - Screen Changes
44
© 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
45
© 2001 Solution Beacon, LLC. All Rights Reserved.
Define Concurrent Programs
New button on the Concurrent Programs screen:
Session Control
46
© 2001 Solution Beacon, LLC. All Rights Reserved.
Concurrent Program Screen Changes
47
© 2001 Solution Beacon, LLC. All Rights Reserved.
Concurrent Program – Screen Changes
Session Control Dialog
48
© 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
49
© 2001 Solution Beacon, LLC. All Rights Reserved.
Concurrent Manager – Screen Changes
Define Concurrent Manager Screen Changes
Consumer Group: Database Resource Group
50
© 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
51
© 2001 Solution Beacon, LLC. All Rights Reserved.
Network Test Screen
52
© 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
53
© 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
54
© 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.
55
© 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
56
© 2001 Solution Beacon, LLC. All Rights Reserved.
Cloning 11i Instances – adclone.pl
adclone.pl:



MetaLink Document: 280896 / Patch 1636653
A Supported Method
Limitations – Identical Configuration
Overview:
 Pre-clone, Copy, Post-clone, Wrap-up Tasks
57
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
The Top Ten Reasons to Upgrade to Release 11i are…
58
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 10 . . .
Needed an excuse to come to NCOAUG in
Chicago to find out more!!!
59
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 9 . . .
Oracle is live on it!
60
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 8 . . .
Oracle 8i, Oracle 8i, Oracle 8i!
61
© 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!
62
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 6 . . .
R10.7 Support Ends on June 30, 2002!
63
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 5 . . .
Web based GUI interface!
64
© 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?)
65
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 3 . . .
Locally Managed Tablespaces!
66
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
Number 2 . . .
Order Management Rewrite
!!!! (No More OEORPC Listener) !!!!
67
© 2001 Solution Beacon, LLC. All Rights Reserved.
Top Ten Reasons to Upgrade
and the # 1 reason to upgrade to Release 11i . . .
(drum roll please . . .)
68
© 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!!!!!!!!!
69
© 2001 Solution Beacon, LLC. All Rights Reserved.
Questions
Email:
[email protected]
Website: www.solutionbeacon.com
70
© 2001 Solution Beacon, LLC. All Rights Reserved.