In this post and some upcoming posts, I’m going to write more about Oracle application servers, a subject we have addressed too little on the Pythian blog.

In this post, I am addressing how to bounce a whole application server, including all tiers and databases from one location. The reason being, I have a request from a client to have the application server be bounced automatically during the weekend to release swaps and to address memory leaks. The application server on this client includes one Mid tier, one Infra tier, and one database (a metarepository database) in three different Oracle homes on two boxes.

As you know, for application server, tiers should be stopped and started up in a specific order. On startup, the sequence is like: database –> listener –> Infra tier –> Mid tier.

For shutdown, the sequence is vice versa. It is not safe to shutdown the database or Infra tier before making sure that the Mid tier is totally done.

So, in order to address client’s request, the basic plan was to have a script to shutdown Mid tier, then handshake with a tscript on the Infra tier to let it know that it is safe to shutdown the Infra tier. We would use the same approach for the Infra shutdown script and database shutdown script.

Is there a simpler way?

opmnctl, the main tool for startup/shutdown of application server components, is able to bounce the whole farm. However, first time you try to run opmnctl status @farm, you may just see the status of AS component only for single box rather than for the whole farm. Why?

(more…)

If you check out the “Butterfly Effect” on Wikipedia, you’ll find a rather interesting reference to, “sensitive dependence on initial conditions in chaos theory.” Fascinating use of phrase that probably doesn’t mean much to normal people until it happens to you. I could give you lots of theoretical examples, but perhaps a real-life one will make more sense.

Last week, a client of Pythian’s came to us with an environment that had recently been upgraded from Oracle Applications 11.5.9 to 11.5.10. In the past, Pythian has not supported the Oracle Applications environment for this client, but that is one of the strengths of Pythian — we have DBAs with a broad range of knowledge and expertise to support just about anything thrown at us. The emails from the client users suggested a myriad of performance issues, ranging from forms being slow to POs not being processed.

I can sympathize with the client, as I used to be one (that’s how I came to work for Pythian — but it’s also another story that I shall relate sometime). Performance issues in Applications can be tricky at best, as there are so many diverse factors to consider. Not having supported Oracle Apps for this client before means that we were starting with a clean slate and just looking for things out of the ordinary based on experience with other clients, sort of “searching for a needle in a haystack.”

The first thing was to just login to the application and take a quick walk about the area — did anything stand out? What was the first impression? It took a bit of time to get connected initially, but the forms seemed to come up without any undue issues. One of the early comments in the email we had received mentioned that POs weren’t being processed by the workflows. Maybe a trip to “View Requests” was in order . . .

(more…)

Today I ran into an 11i E-Business Suite instance which is using Apache/JServ to do forms load-balancing. In fact, it’s my first customer to implement and successfully use Apache/JServ for load-balancing. Here is quick sketch of the instance architecture:

                  Users
                    | 11i URL:
                    | http://appsrv1.pythian.com:8000/
                    |
             ----------------
             |   appsrv1    |
             | apache/jserv |
             |  formservlet |
             ----------------
            /                \
           /                  \
----------------             ----------------
|   appsrv2    |             |   appsrv3    |
| apache/jserv |             | apache/jserv |
|  formservlet |             |  formservlet |
----------------             ----------------

            -------------------
            |    dbsrv01      |
            | CM/RepSrv       |
            |  Database       |
            -------------------

Almost all the customers that I have worked on till now, with a requirement for multiple middletier, have implemented load balancing using hardware load-balancers like Cisco Content Switch or F5 Big-IP.

This customer is running forms in servlet mode, a prerequisite for using Jserv to do load-balancing. If somebody wants to do forms load-balancing in socket mode and opts not to use any hardware LBRs, then the only option is a Metrics server.

Here is brief overview on how forms load-balancing happens in above architecture. (more…)

It is quite common for Oracle Tech support, while troubleshooting any 11i E-Business Suite Self Service Applications (SSWA) related error messages, to ask to enable debug logging in JServ configuration files.

The procedure to enable debug logging in JServ is to update following line in file $IAS_ORACLE_HOME/Apache/Jserv/etc/jserv.properties from log=false to log=true, and then bounce Apache using:

$ cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME
$ adapcctl.sh stop
$ adapcctl.sh start

After the bounce, we start seeing debug messages in the log file $IAS_ORACLE_HOME/Apache/Jserv/logs/jserv.log.

Please note, the logfile’s and config file’s locations can be different from those mentioned above. In case you have a shared tech stack implemented, replace $IAS_ORACLE_HOME with $CONF_TOP/iAS.

The main problem with this procedure is the bounce of Apache. In cases where the client is not a heavy SSWA user and the issue isn’t affecting a significant number of users, then often we will be asked to wait until off-business hours to do the bounce of Apache. These delays can make your metalink SR hop between different Oracle support offices in different timezones, which can lead to delays in resolution of the issue.

Let’s have quick review of JServ configuration files which are of interest to us. jserv.conf — this is the configuration file of mod_jserv module in Apache. Here’s a snippet from jserv.conf: (more…)

I recently implemented OID/SSO with E-Business Suite 11.5.10 CU2, and experienced some issues after the entire setup went smoothly. I hope this note might help others troubleshoot, as it took me a while to figure out the root cause of the problem.

Facts

1. E-Business Suite Version 11.5.10 CU2
2. 10G Version 10.1.2.2

You have done the install and everything went fine. After the bounce you see the following:

1. Login from a remote location
2. Navigate to Administrator System –> Security –> User –> create
3. Enter username and save. The error occurs.

Unable to call fnd_ldap_wrapper.create_user due to the following reason:
ORA-20001: Unable to call fnd_ldap_wrapper.create_user due to the following reason:
An unexpected error occured . Please contact System Administrator..(USER_NAME=OIDTEST)

(more…)

Welcome, readers, to the 92^nd Log Buffer, the weekly review of database blogs.

Brian “Krow” Aker started an interesting blog-thread with his post, The Death of Read Replication, the crux of which is that object caches, such as memcached, make the DBMS itself a little less central, particularly in “Web 2.0″ scenarios. “What does this mean? Less database servers. Bringing down your load means you push off the load to another tier. . . . Why do I need to go through MySQL at all… unless I just want it as a backup or for ad-hoc reporting?”

Ronald Bradford responds with an overview of the MySQL-plus-replication scene. Farhan Mashraqi concurred with Brian’s post, while Arjen also agrees, adding, “I’m not sure the new memory based MySQL storage engines coming out are so relevant, they might be fixing the wrong thing in the wrong place.”

Ronald (who, by the way, is on-deck for a his third Log Buffer on the 25^th) also surveys both the storage-engine stuff to be had at the MySQL Conference, and the prevalence lately of talk about Kickfire in MySQL blogs, something also mentioned by Peter Zaitsev on the MySQL Performance Blog.

Peter has another question on his mind: should you have your swap file enabled while running MySQL? He wants to hear your approach to this matter, having himself experienced variable results. Lots of responses already.

Here on the Pythian Group Blog, Paul Moen posted about a situation in which SHOW SLAVE STATUS lies.

Moving into Oracle stuff, our Alex Gorbachev also pointed out something that doesn’t quite work: the ASMCMD cp command in ASM 11g. He sure gives it a try, but finally concludes: “I couldn’t make the cp command work even a single time.” Except maybe on datafiles.

(more…)

We all have encountered this situation many times before: the cost-based optimizer assumes no correlation between two columns (until 11g), and this has the effect of erroneously reducing the cardinality of a row source. Incorrect cardinality estimates are one of many root causes for SQL performance issues. Consider the following example.

This code creates a table and populates data with four columns:

create table t_vc as
select mod(n, 100) n1, mod(n, 100) n2 ,
mod(n, 50) n3 , mod(n, 20) n4
from (select level n from dual connect by level <= 10001);

The first two columns, n1 and n2, have a strong correlation: n1 is always equals to n2.

Let’s collect statistics with histograms on all columns.

begin
dbms_stats.gather_Table_stats( user, 'T_VC', estimate_percent => null, method_opt => 'for all columns size 254');
end;
/

Let’s explain the plan for the query, specifying one predicate, exactly 100 rows with a value of 10.

(more…)

My name is Vasu Balla, and I’ve been with Pythian for about four months now. I have worked on Oracle E-Business Suite instances for over five years, and I’ve never had a moment where I felt bored.

I am constantly challenged with new technologies and new issues, and my Pythian team’s clients continue to present interesting issues. The most difficult are the ones that are very intermittent or not easily reproducible. If you try to get answers for these kind of issues from Oracle Tech Support by opening a SR, your chances are next to none. Our team recently encountered an issue a client had had with Oracle Configurator for two years. They had followed up with Oracle Tech Support for over a year and had eventually learned just to live with the problem. When Pythian came in, we were shocked to learn the history of the problem. It turned out to be one of the most exciting problems we’ve resolved.

I am new to blogging, but quite experienced in reading blog posts. My Google Reader collects over 100 articles per day, mostly around my interests, which are:

• Oracle 11i E-Business Suite Advanced topologies
  • Hardware Load Balancing
  • DNS based Load Balancing
  • Jserv Load Balancing
• Oracle 11i SSL Implementation at Load balancer level
• Forms load balancing using Jserv + forms servlet mode
• Oracle Concurrent Managers PCP setup
• Oracle 11i Apps with RAC configuration

Signing off, now — with a promise to post some interesting stuff on topics such as these. Stay tuned.