Oracle Applications 11i Concepts I

1. Oracle Applications 11i Concepts I Brian Hitchcock OCP 11i DBA -- OCP 10g DBA Sun Microsystems brian.hitchcock@sun.com brhora@aol.com Brian Hitchcock February 10, 2008 www.brianhitchcock.net Page 1

2. Presentation is Available

3. Contents • Oracle Applications Concepts I • What is Oracle Applications? • 11i Architecture • Oracle Application Tiers • Login to Oracle Applications • Online Oracle Application environments • Oracle Applications Concepts II • 11i Applications Filesystem • Applications DBA utilities • Database DBA vs 11i DBA • Backups • Resources

4. Focus • Generic Oracle Applications concepts • Not tied to specific installation(s) • Provide background for how Apps works • Help with support tasks in general • Technical • how it works • how to make it go

5. What is Oracle Applications? • Oracle Applications is: • Also called Oracle E-Business Suite (EBS) • Huge, takes 100Gb just to install • Doesn’t include database data or staging area • Multiple business applications • System of record • Can’t remove users for example • Built on top of Oracle database (RDBMS) • A mess, you’ll never control it • Great job security • You don’t have to be crazy, but you soon will be…

6. Why Use Oracle Applications? • Businesses have (or had) • Multiple business application systems • Peoplesoft for HR • Siebel for CRM (Customer management) • Other apps for orders, manufacturing etc. • ERP (Enterprise Resource Planning) • Provide single system for all business apps • One system of record • Latest trend is a single global instance

7. What is ERP? • From wikipedia Enterprise Resource Planning (ERP) systems attempt to integrate all data and processes of an organization into a unified system. A typical ERP system will use multiple components of computer software and hardware to achieve the integration. A key ingredient of most ERP systems is the use of a unified database to store data for the various system modules.

8. Oracle EBS Products • Each business application is a ‘product’ Oracle Assets Oracle General Ledger Oracle Payables Oracle Receivables Oracle Cash Management Oracle Bills of Material Oracle Capacity Oracle CRM Oracle Advanced Planning & Scheduling Oracle Purchasing Oracle Business Intelligence Oracle Engineering Oracle HRMS Oracle Inventory Oracle MRP Oracle Order Entry Oracle Order Fulfillment Oracle Payroll Oracle Project Costing Oracle Project Billing Oracle Purchasing Oracle TMS Oracle Work in Process. Oracle Process manufacturing.

9. Oracle Applications Modules • Business applications • Referred to as module or product • Have two or three letter short names • General Ledger – GL • Accounts Receivable – AR • Advanced Pricing – QP • Configurator – CZ • Each has a database schema • Db schema name is application short name • Db user for each application • Db user APPS has access to all applications schemas

10. Oracle Applications • Older applications • Oracle calls them “Professional” • Based on Oracle Forms • Need JDK plugin to render forms • Newer applications • Oracle calls them “Self-Service” • Browser based • Eventually, all apps will be browser based

11. Technical vs Functional Person • No one knows all of Oracle Applications • Don’t say you do in an interview • Technical • Database, configuration, patching • Infrastructure • How to stop/start the applications tier processes? • Functional • Business process of a product/module • How does General Ledger handle period close?

12. History of Oracle Applications • Major Versions • 10.7 • Character based client, no java, database 7.3.4 • 11.0.x • GUI client, no java in database (8.0.5) • 11.5.x (known as 11i) • GUI client, java, database 8.1.6 thru 10g • 12.x • Latest version, java, database 10g/11g

13. History of Oracle Applications • Versions, number of products, number of objects • 10.7 40 products, 25,000 objects • 11.0.x 50 products, 30,000 objects • 11.5.5 161 products, 200,000 objects (first version of 11i) • 11.5.6 175 products, 240,000 objects • 11.5.7 179 products, 280,000 objects • 11.5.8 191 products, 278,000 objects • 11.5.9 191 products, 308,000 objects • 11.5.10 209 products, 268,000 objects • 11.5.10.2 211 products, 284,000 objects From Installing, Upgrading and Maintaining Oracle E-Business Suite Applications 11.5.10+, Barbara Matthews et al

14. Where Do You Fit In? • 11.5.10.2 -- 211 products, 284,000 objects • Each product is a business application • All products need patching • Patching becomes a full-time job • Each product has new features • Many products are shared • Multiple dependencies • Order management linked to General Ledger

15. 11i Architecture • At a high level we have Tiers • (you will cry a lot while you support them) • Desktop Tier • User client • Application Tier • Lots of servers, more later • Database Tier • Oracle RDBMS

16. Tiers Client Desktop Tier Application Tier Database Tier Web Server Forms Server Database Server Web Browser Concurrent Processing Server Reports Server Admin Server Discover Server

17. Single Node Single Server – Single Node Client Desktop Tier Application Tier Database Tier Web Server Forms Server Database Server Web Browser Concurrent Processing Server Reports Server Admin Server APPL_TOP Discover Server

18. Two Nodes Two Server – Dual Node App Tier Server Db Tier Server Application Tier Database Tier Web Server Database Server Forms Server Client Desktop Tier Reports Server Application Tier Web Browser Admin Server Concurrent Processing Server Discover Server APPL_TOP APPL_TOP

19. Two Nodes – Shared APPL_TOP App Tier Server Db Tier Server Application Tier Database Tier Web Server Database Server Forms Server Client Desktop Tier Reports Server Application Tier Web Browser Admin Server Concurrent Processing Server Discover Server APPL_TOP

20. Desktop Tier • Browser • HTML for newer self-service applications • Java applet for forms-based applications • Must not block popup windows • Applet • Forms client applet • Requires JVM to run inside browser • Oracle JVM implemented as browser plug-in • JInitiator • Must have specific plug-in/JRE version • Configured at installation or through patching • Mozilla versus Firefox

21. Application Tier • Multiple servers • Web Server • Based on Apache • Forms server • Reports server • Concurrent Processing server • Admin server • Discoverer server • optional

22. Application Tier • For each of these servers • start/stop scripts • Log files • Configuration files • Software patches • Version information • Troubleshooting procedures • Bugs (no! really?)

23. Application Tier Servers • Web server • Must be running to serve • Web-based self-service applications • Oracle Applications login screen (web page) • Forms server • Must be running to serve forms-based applications • Reports server • Handles user requests for reports • Retrieves data from db and formats report • Sends report output to client browser

24. Application Tier Servers • Concurrent Processing server • Glorified batch processing • Process long running queries in background • Schedule jobs • Work shifts • Conflict resolution • Concurrent requests • Execute concurrent programs • Executed by concurrent managers

25. Types of Concurrent Managers • Internal concurrent manager • Controls all other concurrent managers • Standard concurrent manager • Process most concurrent requests • Specialized concurrent manager • Modified for special needs • Assigned to selected concurrent programs

26. Application Tier Servers • Admin server • Not really a server • Collection of Applications DBA tools • Hence the AD in the names of the utilities • Adadmin • Set maintenance mode required for patching • Recompile applications database schemas • Adpatch • Apply applications patches • Adconfig • Apply configuration changes to multiple products

27. Application Tier Servers • Discoverer server (isn’t Disco dead?) • Part of Oracle Application Server • Part of Oracle business intelligence (BI) • Hides complexity of database • Maps db schemas to business user terms • Supports ad-hoc query reporting • Users can bring system to a crawl… • Predefined reports • Standardize reporting across organization

28. Database Tier • Oracle database • APPS database user • Can access all product data • Doesn’t own product tables, indexes, sequences • Has grants synonyms to product schemas • Applications users • Separate and distinct from database users • FND_USER table, DBA_USERS table • Change passwords carefully

29. Applications Database APPS database user has grants synonyms to database schemas APPLSYS, GL, INV, AP and all other products APPLSYS GL INV AP GL tables GL indexes GL sequences INV tables INV indexes INV sequences AP tables AP indexes AP sequences AOL tables AOL indexes AOL sequences ALR tables ALR indexes ALR sequences AD tables AD indexes AD sequences etc, Grants and synonyms APPS AP packages AP views AP procedures AP functions AP triggers GL packages GL views GL procedures GL functions GL triggers INV packages INV views INV procedures INV functions INV triggers AOL code objects ALR code objects AD code objects

30. Applications Db Tablespaces • Before 11.5.10 • Each product had two tablespaces • Tables, indexes • 200 products, 400 tablespaces • As of 11.5.10 • Oracle Applications Tablespace Model (OATM) • 12 tablespaces total

31. Applications Db Tablespaces APPS_TS_TX_DATA -- transactional data APPS_TS_TX_IDX – transactional indexes APPS_TS_SEED – seed data, reference data APPS_TS_INTERFACE – interface tables APPS_TS_SUMMARY – summary objects APPS_TS_NOLOGGING – temporary objects APPS_TS_QUEUES – advanced queuing APPS_TS_MEDIA – graphics, spatial data APPS_TS_ARCHIVE – obsolete tables for compatibility UNDO – undo tablespace TEMP – temporary tablespace SYSTEM – system tablespace

32. Tiers and Servers • Single tier installation • App tier servers and database on single machine • Multiple tier installation • Database on one machine • App tier servers on one or more machines • Load balancing • HA • More complicated

33. Traditionally • Concurrent processing, db on same server • Concurrent processing uses database heavily • Reduce network latency • Other servers on apps tier server • May have multiple apps tiers for load balancing • Today • Network performance is better • Apps tier, db tier servers in datacenter • Concurrent processing can be on apps tier server • Only need to patch once

34. What Does It Look Like? • Login to Oracle Applications • See user responsibilities • Select responsibility • Bring up • Form based application • Users form • Web based application • Oracle Applications Manager

35. Login to Oracle Applications

36. Login to Oracle Applications

37. Login to Oracle Applications

38. Login to Oracle Applications HTML Based Forms Based

39. Login to Oracle Applications

40. Login to Oracle Applications

41. Login to Oracle Applications

42. Login to Oracle Applications

43. Login to Oracle Applications

44. Login to Oracle Applications

45. Login to Oracle Applications

46. Solution Beacon • Provides Oracle Applications environments • You can access for free • Must create an account with CSI number • www.solutionbeacon.com • Works from IE7 • Had to de-install all Java, JRE etc. • Had to de-install Windows Media Player • Metalink Doc Id. 430359.1 • When you click on form • JInitiator downloaded and installed

47. Solution Beacon

48. Solution Beacon

49. Solution Beacon

50. Solution Beacon