Thursday, 20 October 2016

Oracle DBA in an Oracle Applications World

Transitioning from being a “normal” Oracle DBA into becoming an Oracle Applications DBA has two components. The first concerns the job or position itself, that is, how do you get a job as an Oracle Apps DBA? The second component has to do with the technology aspect of the job. In other words, what is the architecture behind the following items?
  • Oracle Applications
  • Oracle Apps
  • EBS
  • E-Business Suite
  • Oracle 11i
  • EBS 12
As a matter of clarification, the list above represents pretty much the same item, but sometimes the context matters. This article does not address how you become an Oracle Apps DBA, but what it does do is go into detail about what is behind the smoke, mirrors, and curtains of Oracle Applications. Before getting into the architecture, let’s take a short detour and clarify what is meant by “Oracle Applications.”

In Oracle-land, there is no shortage whatsoever of confusing or conflicting names, and in the realm of “Oracle Applications,” this is the case as well. A recent article posted elsewhere on the Internet addresses the confusion surrounding Oracle Fusion, something along the lines of: I can’t define fusion, but I know it when I see it. Just within the documentation for Applications, the name of apps product has changed, and how it is written differs as well. It’s not enough to use E-Business Suite, then logo it as e-businesssuite. It’s almost as bad as naming a product as “Application Server 10g (9.0.4).” If anyone from Oracle Corporation is reading this, please, can you get people within your company to stop making branding and naming so confusing? Please. It’s not that hard. Really.

The Architecture

If you have dealt with
  • Oracle Forms on the web, using Application Server and Developer Suite to develop, configure and deploy forms and reports, and
  • Working as an Oracle DBA, seeing a broad range of administrative and maintenance activities such as patching and cloning
...then you have already mastered about 90% of what is underneath OA (yet another reference to this product). Oracle Apps is supposed to be this high-speed, low-drag ERP application using the best of what Oracle has to offer in web and database components, right? Not entirely. In version 11.5.9, you can see one of the earliest versions of Application Server and an Oracle home based on version 8.0.6.

In one of the simplest configurations, an EBS environment can consist of two servers: one known as the database tier, and the other as the mid tier or applications tier. The database tier is exactly what it sounds like – this is the database backend of the application. The mid tier is just like Application Server (as we know it today). The AS piece is based on Apache, which is the foundation of the Oracle HTTP server.

The mid tier

Under the mid tier, or more precisely, running on the mid tier, you can see several services. All of the different services (OC4J, reports engine, forms, etc.) you see on Application Server exist in the mid tier, some with the same familiar names, plus others that are specific to Oracle Apps. The six servers within the mid or applications tier are:
  • Web server
  • Forms server
  • Reports server
  • Discoverer server
  • Concurrent Processing server
  • Admin server
With respect to Application Server, the server in the list above distinctly different from AS is the concurrent processing server. One way to think of this server is that it is a job or helper agent that coordinates jobs and processes between user requests and data processing within EBS; and just like the modern day Application Server, not every service above has to be on the same server.

It is a very close analogy to think of the Oracle Apps setup as a version of (for example) Forms and Reports Services and a backend database. What physical or logical relationship is there between the app server and the database? Aside from being accessible on the network, and the app server knowing where to go for the database (via tnsnames.ora), that’s it. In the Oracle Apps world, files generated on the mid tier can be, and at times need to be, placed on the database tier (primarily in an appsutil directory under ORACLE_HOME). These files are mostly in the form of context files, which contain configuration information. Other files are related to cloning.

A diagram here helps to illustrate the main components of each tier. The diagram originates from Figure 2-1 in Oracle Applications Concepts Release 11i. The way in which some components are on top of others gives way to how many of the subcomponents are referred to as <something>_TOP.

Oracle DBA in an Oracle Applications World

There are numerous “top” directories on the mid tier. Two in particular, seen over and over again in the documentation, are APPL_TOP and COMMON_TOP. Further, in a departure from the “normal” Oracle UNIX account owner of just oracle by itself, you will come across another key account named applmgr. The applmgr account plays a key role in Oracle Apps.

The database tier

What does the database side of things look like? By and large, the majority of this will be routine. However, and amazingly enough, the Oracle Apps database file layout may appear to be mind-boggling; not from its complexity, mind you, but from its utter lack of complexity. Also with a “top” structure, the database has four data tops, which point to data, index, system and temporary tablespace locations. You may see out of the can installations where all data related database files are located under one path/partition/mount point, all indexes under one, and system and temp the same way. Redo logs can be in two locations, and three control files spread out – to include having one located in the same place as redo logs. You may see hundreds of tablespaces with one or two files each, and you can see a four tablespace model.

Speaking of redo logs, and redo in general, one thing we know for sure in the real DBA world is that we want redo logs on (if possible) faster disks because of the amount of writes. Would you place a control file on a disk like this? If you haven’t seen the control file parallel write wait event, this is how you make it appear: install Oracle Apps as is. Current recommendations/best practice for sizing the redo log buffer is to use 1MB. Oracle has a note on MetaLink recommending that Oracle Apps DBAs set the redo log buffer size to 10MB.

Another area of departure from traditional databases has to do with initialization parameters. It is not uncommon to see many undocumented init parameters set in the init file (pfile or spfile, either way).

Common to both tiers

Patching consumes a good bit of your time in the EBS world. Truthfully, it’s hard not to view EBS as a significantly bug-ridden application. If you had to apply as many patches to your OLTP or DSS database as you do with Oracle Apps, the 5-nines concept of 99.999% meaning five minutes of downtime per year would mean 5 minutes of up time per year. Okay, it isn’t that extreme, but there is a huge amount of testing and QA that needs to take place. If you are not at all familiar with Financials or General Ledger, output from modules such as these are directly related to what is printed on invoices and mailed to customers for them to submit payment, aka your paycheck eventually, to your company’s accounts receivable department.

Patching is a good lead-in to the next topic: cloning. How do you clone your database today? In EBS, the database clone part is pretty straightforward, and the mid tier component is a bit more complicated. Fortunately, Oracle has developed a feature called Rapid Clone, and its steps boil down to this:
  • Run perl-based prep scripts on each tier (creates an XML file with configuration information, does not affect anything on the source system)
  • Copy relevant parts of each tier onto the target system (several TOPS from the mid tier and database files from the database tier)
  • Run perl-based config/clone scripts to re-configure the environment or context files for each tier (interviews you for information about the target)
Later versions 11.5.x series have the ability to use AutoConfig, and certain files will have AutoConfig generated text in them. The presence of this boilerplate text (just like what you see at the top of a listener or tnsnames.ora file) is an indicator that Rapid Clone may be available for use.

In Closing

In the next article about E-Business Suite (note how the references keep changing?), I’ll cover a migration/cloning of a production instance and review two reference books (commercially available). The (free) Concepts guide takes about 20 minutes to skim through in decent detail, and being armed up front with that knowledge about EBS goes a long way in demystifying Oracle Applications.